Oracle Cold Backup and Recovery

Original: Oracle Cold backup and Recovery

There are three ways Oracle backup and Recovery works:

(1) Data pump (EXPDP/IMPDP)

(2) Cold backup

(3) Rman backup

In terms of classification, (1) and (2) are called "Cold" backups, (3) called "Hot" backups.

Data pump and cold backup can only restore the database to a point in time (that is, the time of the backup), and Rman Backup in the archive mode, real-time backup, real-time restore, almost no loss of data, but for the Data Warehouse, open archive is a terrible thing, because the archive log is very large, Of course, if necessary also can be opened, need to do a good job of archivelog backup and cleanup.

This article only describes cold backup, Rman backup is not introduced, if necessary, Google search to think twice "step by step Learning Rman" Backup document.

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

A data pump

Data pump backup is implemented using Oracle's own EXPDP and IMPDP backups.

Linux and Windows systems:

The EXPDP-HELP command allows you to view the EXPDP command options

1.1 Prerequisites for Data pump backup and restore

Prerequisites for data pump backup and restore

(1) Backup and restore paths

(2) have export full database and import full database permissions

System Permissions and Roles:

SELECT *
From Dba_sys_privs A
WHERE a.privilege like '%exp% '
or a.privilege like '%imp% ';
SELECT *
From Dba_role_privs A
WHERE a.granted_role like '%imp% '
or a.granted_role like '%exp% ';

1.2 Implementation steps 1.2.1 Creating a path

1) Check if the path exists

SELECT * from Dba_directories;

2) Create a new 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 a backup path

GRANT read,write on DIRECTORY directory_name to &user_name;

For example:

Grant READ, WRITE on Directory backup_path to Test;

-- to assign permissions to a read-write path to a user

1.2.2 Authorization

To grant a user the right to backup and restore:

GRANT EXPORT full Database,import full DATABASE to test;

1.3 Performing backup and restore scripts

Data pump backup EXPDP command, database restore IMPDP command, the following are in parallel, the parallel parameter depends on the number of CPU and CPU threads, but this value is not recommended too large.

Note that the backup script should be placed on the same line to be executed, and there should be no newline in the middle of the script.

1.3.1 Backup test User Entire library

Note that you need to supplement the Tns_name parameter, and of course you can use the system user.

In addition, using the parallel parameter, it is recommended that dumpfile use the%u option to split a dumpfile into multiple, otherwise the parallel parameter effect is not obvious.

(1 ) corresponds to the backup script:

EXPDP test/[email protected]_name Directory=backup_path dumpfile=expdp_test_full_20140526_%u.dmp SCHEMAS=TEST Parallel=8 Logfile=expdp_test_full_20140526.log

( 2 ) corresponds to the Restore script:

IMPDP test/[email protected]_name Directory=backup_path dumpfile=expdp_test_full_20140526_%u.dmp SCHEMAS=TEST Parallel=8 Logfile=impdp_test_full_20140526.log

1.3.2 Backup test user metadata only

(1) corresponding backup script

EXPDP test/[email protected]_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 restore script

IMPDP test/[email protected]_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 Backup test user data only

(1) corresponding backup script

EXPDP test/[email protected]_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 restore script

IMPDP test/[email protected]_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) Backing 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 Backup Test table only

To back up and restore table data with EXPDP/IMPDP, you can use the tables or include command option.

UNIX under EXPDP Backup Yes, single quotes and parentheses need to be escaped, you can use the Parfile option. Write the command in the parameter file system.

such as Expdp_parfile.txt

Userid=test/[email protected]_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 Mode:

EXPDP Parfile=expdp_parfile.txt

Secondary Cold backup

A cold backup occurs when the database has been shut down properly and provides us with a complete database when it shuts down normally. Cold backup is to copy the key files to another location, you can clone a database according to the importance file.

Cold Backup Restore Considerations:

The operating systems of the two database servers must be isomorphic (i.e.: Aix->aix or linux->linux) and not heterogeneous (Linux->aix), otherwise it is useless. If it is heterogeneous, then the data pump can only be used in the way.

Advantages of Cold backup:

(1) Cold Standby mode concept easy to understand, will need to back up the files copied to a secure location

(2) Easy to restore to a certain point in time (just copy the file back)

(3) can be combined with the archiving method to do the "best state" recovery of the database.

(4) Low maintenance, high safety.

Disadvantages of cold backup:

(1) When used alone, can only be provided to "a point in time" recovery.

(2) in the whole process of re-implementing the backup, the database must be in a consistent shutdown state.

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

(4) cannot be restored on a per-table or per-user basis.

2.1 Implementation Steps

Files that must be copied in a 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: A cold backup must be performed in the case of a database shutdown, and performing a database file system backup is not valid when the database is in an open state.

2.1.1 Backup control files

Cold backup to close the database, back up the control file, and then record the location of the data file, the redo log file, and the control file before shutting down the database.

(1) Backup control file:

ALTER DATABASE BACKUP controlfile to TRACE as '/tmp/control.txt '

The above action is to back up the contents of the control file into the/tmp/control.txt text. The purpose is to reconstruct the control files or rename file use.

(2) Data file location

SELECT A.file_name, A.tablespace_name, A.bytes, A.autoextensible,a.online_status

From Dba_data_files A

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 Shutting down the database

(1) Stop monitoring

1) ps-ef |grep Pmon

2) Lsnrctl Stop

(2) Closing the database

Sql>shutdown Immediate

2.1.3 Copying files        Copy the control file, data file, and redo log file to another store. You can use the SCP command to replicate. The use of the SCP command requires the installation of an SSH package, in general, the Linux system is installed by default, and Aix needs to install the SSH software package. If you do not have an SSH package, you can search the Web for download and installation.

The SCP command on Linux:

USAGE:SCP [ -1246BCPQRV] [-C cipher] [-f ssh_config] [-I i dentity_file][-l limit] [-O ssh_option] [-P port] [-S program][ [Email Protected]]host1:]file1 ... [[Email Protected]]host2:]file2

For example: Transfer one machine expdp_20140521.log to the/home/oracle/backup directory of another machine, the command is as follows:

SCP Expdp_20140521.log [Email protected]:/home/oracle/backup

The SCP command can also copy a folder, the-r option, which identifies a recursive copy subfolder.

The SCP command supports regular expressions such as: * and other fuzzy query operations. Specific reference: Man SCP help

The speed of SCP replication is related to network bandwidth, which can be said to be largely dependent on network bandwidth.

Suggestions:

It is better to count the number of data files and redo files before copying 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

Three-cold backup restore

If the database installation path of the target library and the source library are the same, it is relatively simple to omit the rename file operation, otherwise you may need to rename the file operation.

Of course, if you create control files manually, you do not need to rename file operations. Because the location of the data files and redo log files is recorded in the Controlfile.

3.1 Database Installation path consistent

The prerequisites for this operation are:

(1) Consistent data file path

(2) The instance name of the database is consistent

(3) Consistent location of control files

Operation Steps:

(1) Once the copy is complete, you can pull the database

Sql>startup MOUNT;

Sql>alter DATABASE OPEN resetlogs;

If the above operation successfully ended, indicating that the database restore is not a problem, you can start the database listening, connect the database.

Start monitoring:

1) ps-ef |grep Pmon

2) lsnrctl Start

3.2 Inconsistent data File location

If the data file location is inconsistent, you will need to manually create the Controlfile file and the rename data file.

Reason:

The location of the data file recorded in the original control file is different from the location of the target library data file, and the database begins to read the contents of Controlfile in the Mount phase. The mount stage could not find the corresponding data file, the database startup process will be error, the database can not start.

The 3.2.1 workaround 3.2.1.1 to open the database to the Nomount state sql>startup Nomount 3.2.1.2 Rebuild the control file, which is relatively simple to reconstruct. The main problem is that you can control the file format without errors.

Manual creation of control file contents You can view specific information from the Control.txt we previously backed up, creating a control file manually requires only initializing the DB instance name, redo the date and data files, and the character set of the database. Refer to the following sample script for details.

The main content of the control file (from Control.txt) is broadly 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 128M BLOCKSIZE 512,

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

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

GROUP 4 '/home/oracle/app/oradata/testdb/redo04.log ' SIZE 128M 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 the FAQ for manually creating control files is a file format issue.

(1) Remove--STANDBY LOGFILE

(2) Do not have a blank line between datafile and logfile

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

3.2.1.3 Rename file operation

The rename file operation is intended to be able to locate a specific data file when the database is mount. The rename operation is an action command when the database is in the Mount state.

Sql>alter DATABASE MOUNT;

sql> @renamefile. sql

To execute the Renamefile.sql script, the script content is referenced as follows:

ALTER DATABASE RENAME FILE '/home/oracle/app/oradata/testdb/test01.dbf ' to '/u01/app/oradata/test01.dbf ';

Assigns the original database test01.dbf file to a new location on the destination database. You can controlfile the data files in the source database to form a rename script, and then schedule the execution.

3.2.1.4 Opening a database

If the rebuild control file succeeds and the renamefile operation is not a problem, then the database can be pulled.

Sql>alter DATABASE resetlogs;

If the database starts normally, it indicates that there is no problem with the database restore, then start listening.

1) Ps-ef|grep Pmon

2) lsnrctl Start

3.2.1.5 initializing a temporary table space

Reason:

There is no temporary tablespace in the control file, so it may be necessary to rebuild the temporary table space after the database is started.

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

Oracle Cold Backup and Recovery