ORACLE cold backup and recovery

There are three methods for ORACLE backup and recovery:

(1) Data Pump (expdp/impdp)

(2) Cold backup

(3) RMAN backup

In terms of classification, (1) and (2) are collectively referred to as "cold" backup and (3) as "hot" backup.

Data Pump and cold backup can only restore the database to a certain time point (that is, the backup time point), while RMAN backup can be real-time backup and real-time recovery in archive mode, data can be almost lost, but archive is terrible for Data Warehouses. Archive logs are large and can be enabled if necessary, back up and clean up ARCHIVELOG.

This article only introduces cold backup, but does not introduce RMAN backup for the moment. If you need it, you can search for the backup document "Learn RMAN step by step" by GOOGLE.

Web: http://www.itpub.net/thread-810100-1-1.html

One Data Pump

The Data Pump backup mode adopts the EXPDP and IMPDP backup methods provided by ORACLE.

LINUX and WINDOWS systems:

You can run the "expdp-help" command to view the expdp Command Options.

1.1 prerequisites for Data Pump backup and recovery

Prerequisites for Data Pump backup and recovery

(1) backup and restoration paths

(2) export full database and import full database Permissions

System permissions and roles:

SELECT *

FROM Dba_Sys_Privs

WHERE a. Privilege LIKE '% EXP %'

OR a. Privilege LIKE '% IMP % ';

SELECT *

FROM Dba_Role_Privs

WHERE a. Granted_Role LIKE '% IMP %'

OR a. Granted_Role LIKE '% EXP % ';

1.2 implementation step 1.2.1 Creation Path

1) check whether the path exists

SELECT * FROM Dba_Directories;

2) create a backup and restore path

Create or replace directory directory_name AS 'specific absolute path ';

Example: create or replace Directory BACKUP_PATH AS '/home/oracle/dbbackup ';

--Create backup path

Grant read, write on directory directory_name TO & user_name;

For example:

Grant READ, write on Directory BACKUP_PATH TO Test;

--Grant the read/write path permission to a user

1.2.2 authorization

Grant the backup and restoration permissions of a user:

Grant export full database, import full database to test;

1.3 execute Backup and Restore scripts

Data Pump backup EXPDP command, database restore IMPDP command, the following uses PARALLEL mode, PARALLEL parameter depends on the number of CPU and CPU threads, but this value is not recommended to be too large.

Note that the backup script should be executed in the same line, and there cannot be line breaks in the middle of the script.

1.3.1 back up the entire database of the TEST user

Note that you need to add the tns_name parameter. You can also use the system user.

In addition, we recommend that you use the % u option for dumpfile after using the PARALLEL parameter to split one dumpfile into multiple ones. Otherwise, the PARALLEL parameter is ineffective.

(1) Corresponding backup script:

EXPDP test/oracle @ tns_name DIRECTORY = BACKUP_PATH DUMPFILE = EXPDP_TEST_FULL_20140526 _ % u. dmp schemas = test parallel = 8 LOGFILE = EXPDP_TEST_FULL_20140526.LOG

(2) Corresponding restoration script:

IMPDP test/oracle @ tns_name DIRECTORY = BACKUP_PATH DUMPFILE = EXPDP_TEST_FULL_20140526 _ % u. dmp schemas = test parallel = 8 LOGFILE = IMPDP_TEST_FULL_20140526.LOG

1.3.2 only backup TEST user metadata

(1) backup script

EXPDP test/oracle @ tns_name DIRECTORY = BACKUP_PATH DUMPFILE = EXPDP_TEST_METADATA_ONLY_20140526.DMP CONTEXT = METADATA_ONLY PARALLEL = 8 LOGFILE = EXPDP_TEST_METADATA_ONLY_20140526.LOG

(2) Corresponding restoration script

IMPDP test/oracle @ tns_name DIRECTORY = BACKUP_PATH DUMPFILE = EXPDP_TEST_METADATA_ONLY_20140526.DMP CONTEXT = METADATA_ONLY PARALLEL = 8 LOGFILE = IMPDP_TEST_METADATA_ONLY_20140526.LOG

(3) backup SHELL script:

#! /Bin/sh

Expdp_date = 'date + "% Y % m % d "'

Expdp test/oracle directory = backup_path dumpfile = expdp_metadata_only _ $ {expdp_date}. dmp schemas = test content = METADATA_ONLY logfile = expdp_metadata_only _ $ {expdp_date}. log

1.3.3 only backup TEST user data

(1) backup script

EXPDP test/oracle @ tns_name DIRECTORY = BACKUP_PATH DUMPFILE = EXPDP_TEST_DATA_ONLY_20140526 _ % u. dmp context = DATA_ONLY PARALLEL = 8 LOGFILE = EXPDP_TEST_DATA_ONLY_20140526.LOG

(2) Corresponding restoration script

IMPDP test/oracle @ tns_name DIRECTORY = BACKUP_PATH DUMPFILE = EXPDP_TEST_DATA_ONLY_20140526 _ % u. dmp context = DATA_ONLY PARALLEL = 8 LOGFILE = IMPDP_TEST_DATA_ONLY_20140526.LOG

(3) Back up SHELL scripts

#! /Bin/sh

Expdp_date = 'date + "% Y % m % d "'

Expdp test/oracle directory = backup_path dumpfile = expdp_DATA_ONLY _ $ {expdp_date} _ % u. dmp schemas = test content = DATA_ONLY logfile = expdp_DATA_ONLY _ $ {expdp_date}. log

1.3.4 only backup the TEST table

Use expdp/impdp to back up and restore table data. You can use the tables or include Command Options.

In UNIX, EXPDP backup is. Single quotes and brackets must be escaped. You can use the PARFILE option. Write the command in the parameter file system.

For example, expdp_parfile.txt

Userid = test/oracle @ tns_name directory = backup_path dumpfile = test_send_tables_20140526 _ % u. dmp parallel = 8 tables = ('test _ DATA_1, 'test _ DATA_2 ', 'test _ DATA_3 '...) logfile = test_send_tables_20140526.log

Call method:

Expdp parfile.txt expdp_parfile.txt

Secondary cold backup

Cold backup occurs when the database is shut down normally. When the database is shut down normally, it will provide us with a complete database. Cold backup copies key files to another location. You can clone a database based on the Importance file.

Considerations for cold Backup recovery:

The operating systems of the two database servers must be homogeneous (namely, aix-> aix or linux-> linux), and cannot be heterogeneous (linux-> aix ), otherwise, it is useless. If it is heterogeneous, you can only use the data pump method.

Advantages of cold backup:

(1) The concept of cold standby mode is easy to understand. copy the files to be backed up to a safe location.

(2) It is easy to restore to a certain point in time (you only need to copy the file back)

(3) It can be combined with the archiving method to restore the database in the "best state.

(4) low maintenance and high security.

Disadvantages of cold backup:

(1) when used separately, only recovery at a certain time point can be provided.

(2) During the whole backup process, the database must be in a consistent and closed state.

(3) If the disk space is limited, it can only be copied to tape and other external storage devices, and the speed will be very slow. In addition, the backup speed is related to the network bandwidth.

(4) cannot be restored by table or by user.

2.1 implementation steps

Files that must be copied in cold backup include:

(1) All data files

(2) All control files

(3) All online redo log files

(4) Init. ora file (optional)

(5). profile or. bash_profile (optional)

Note: Cold backup must be performed when the database is closed. When the database is turned on, the execution of database file system backup is invalid.

2.1.1 backup control file

To shut down the database for cold backup, you must back up the control file before closing the database, and then record the location of the data file, redo log file, and control file.

(1) backup control file:

Alter database backup controlfile to trace as '/tmp/control.txt'

The preceding operation backs up the control file content to the/tmp/control.txt text. The purpose is to recreate the control FILE or rename file.

(2) Data File Location

SELECT a. File_Name, a. Tablespace_Name, a. Bytes, a. Autoextensible, a. Online_Status

FROM Dba_Data_Files

UNION

SELECT B. FILE_NAME, B. Tablespace_Name, B. Bytes, B. Autoextensible, B. STATUS

FROM Dba_Temp_Files B;

(3) redo log file

SELECT * from v $ logfile;

(4) Control File

SELECT * from v $ controlfile;

2.1.2 shut down the database

(1) Stop listening

1) ps-ef | grep pmon

2) lsnrctl stop

(2) shut down the database

SQL> shutdown immediate

2.1.3 copy an object

Copy the control file, data file, and redo log file to another storage. You can use the SCP command for replication. To use the SCP command, you need to install the SSH software package. In general, the LINUX system is installed by default; in AiX, you need to install the SSH software package. If you do not have an SSH software package, you can search the Internet to download and install it.

SCP command on LINUX:

Usage: scp [-1246 BCpqrv] [-c cipher] [-F ssh_config] [-I dentity_file] [-l limit] [-o ssh_option] [-P port] [-S program] [[user @] host1:] file1... [[user @] host2:] file2

For example, to transmit the expdp_20140521.log on one machine to the/home/oracle/backup Directory of another machine, run the following command:

Scp expdp_20140521.log oracle@192.168.56.88:/home/oracle/backup

The SCP command can also copy folders and the-r option to identify recursive copies of subfolders.

The SCP command supports regular expressions, such as: * and other fuzzy query operations. For more information, see man scp help.

The speed of SCP replication depends on the network bandwidth.

Suggestion:

Before copying data, it is best to count the number of data files and redo files and compare them with the data in the database.

Script:

(1) ls *. dbf | wc-l

Comparison with dba_data_files and dba_temp_files

(2) ls *. log | wc-l

Comparison with v $ logfile

(3) ls *. ctl | wc-l

Comparison with v $ controlfile

Tri-cold Backup Recovery

If the installation path of the target database and the source database is the same, it is relatively simple. The rename file operation can be omitted. Otherwise, the rename file operation may be required.

Of course, if you create a control file manually, you do not need to perform the rename file operation. Because controlfile records the location of the data file and redo log file.

3.1 consistent database installation path

The prerequisites for this operation are:

(1) Consistent data file paths

(2) database instance names are consistent

(3) The locations of control files are consistent.

Procedure:

(1) After the replication is complete, you can start the database.

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE OPEN RESETLOGS;

If the above operation ends successfully, it indicates that the database restoration is normal. You can start the database listening and connect to the database.

Start the listener:

1) ps-ef | grep pmon

2) lsnrctl start

3.2 inconsistent data file locations

If the location of the data file is inconsistent, You need to manually create the controlfile and rename data files.

Cause:

The location of the data file recorded in the original control file is different from that in the target database. The database starts to read the content in the controlfile during the mount stage. If the corresponding data file cannot be found during the mount stage, an error is reported during database startup and the database cannot be started.

3.2.1 solution 3.2.1.1 enable the database to the nomount status

SQL> STARTUP NOMOUNT

3.2.1.2 rebuilding the control file

Rebuilding the control file is relatively simple. The main problem is that the format of the control file should not be wrong.

You can manually create a control file to view the specific information from control.txt in the previous step. to manually create a control file, you only need to initialize the database instance name, redo end, data file, and database character set. For more information, see the following sample script.

The main content (policcontrol.txt) of the control file is roughly as follows:

STARTUP NOMOUNT

Create controlfile reuse database "testdb" NORESETLOGS NOARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

# MAXLOGHISTORY 54288

LOGFILE

GROUP 1'/home/oracle/app/oradata/testdb/redo01.log' SIZE 128 m blocksize 512,

GROUP 2'/home/oracle/app/oradata/testdb/redo02.log 'SIZE 128 m blocksize 512,

GROUP 3 '/home/oracle/app/oradata/testdb/redo03.log' SIZE 128 m blocksize 512,

GROUP 4'/home/oracle/app/oradata/testdb/redo04.log 'SIZE 128 m blocksize 512

-- STANDBY LOGFILE

DATAFILE

'/Home/oracle/app/oradata/testdb/system01.dbf ',

'/Home/oracle/app/oradata/testdb/sysaux01.dbf ',

'/Home/oracle/app/oradata/testdb/undotbs01.dbf ',

'/Home/oracle/app/oradata/testdb/users01.dbf ',

'/Home/oracle/app/oradata/testdb/testdb01.dbf ',

'/Home/oracle/app/oradata/testdb/testdb02.dbf ',

'/Home/oracle/app/oradata/testdb/testdb03.dbf ',

'/Home/oracle/app/oradata/testdb/testdbindex. dbf ',

'/Home/oracle/app/oradata/testdb/undotbs02.dbf ',

'/Home/oracle/app/oradata/testdb/testdb04.dbf ',

'/Home/oracle/app/oradata/testdb/testdb05.dbf ',

'/Home/oracle/app/oradata/testdb/testdb06.dbf ',

'/Home/oracle/app/oradata/testdb/testdb07.dbf ',

'/Home/oracle/app/oradata/testdb/testdb08.dbf ',

'/Home/oracle/app/oradata/testdb/testdb09.dbf ',

'/Home/oracle/app/oradata/testdb/testdb10.dbf ',

'/Home/oracle/app/oradata/testdb/testdb11.dbf ',

'/Home/oracle/app/oradata/testdb/testdb12.dbf ',

'/Home/oracle/app/oradata/testdb/testdb13.dbf ',

'/Home/oracle/app/oradata/testdb/testdb14.dbf ',

'/Home/oracle/app/oradata/testdb/testdb15.dbf'

Character set AL32UTF8;

Note that the file format is a common problem in manual control file creation.

(1) Remove -- STANDBY LOGFILE

(2) do not have blank lines between DATAFILE and LOGFILE

(3) In addition, the control file does not contain temporary tablespace data files.

3.2.1.3 rename file Operation

The rename file operation is used to locate specific data files during database MOUNT. The rename operation is the operation command when the database is in the mount state.

SQL> ALTER DATABASE MOUNT;

SQL> @ renamefile. SQL

Run the renamefile. SQL script. The script content is as follows:

Alter database rename file '/home/oracle/app/oradata/testdb/test01.dbf' TO '/u01/app/oradata/test01.dbf ';

Specify the test01.dbf file of the original database to the new location of the target database. You can create a rename script for the data file in the controlfile of the source database, and then schedule the execution.

3.2.1.4 open a database

If the rebuild control file is successful and the renamefile operation is normal, you can start the database.

SQL> ALTER DATABASE RESETLOGS;

If the database starts normally, it indicates that the database is restored normally. Then, start the listener.

1) ps-ef | grep pmon

2) lsnrctl start

3.2.1.5 initialize temporary tablespace

Cause:

The control file does not contain temporary tablespace. Therefore, after the database is started, you may need to recreate the temporary tablespace.

Create temporary tablespace TESTTEMP tempfile '/home/oracle/app/oradata/test/testtemp. dbf' size 20G autoextend off;