WINDOWS下的程序员出身,偶尔也写一些linux平台下小程序, 后转行数据库行业,专注于ORACLE和DB2的运维和优化。 同时也是ios移动开发者。欢迎志同道合的朋友一起研究技术。 数据库技术交流群:58308065,23618606
全部博文(599)
分类: Oracle
2011-11-23 20:14:10
RMAN: RAC Backup and Recovery using RMAN [ID 243760.1] | |||
| |||
|
修改时间28-OCT-2011 类型 BULLETIN 状态 PUBLISHE |
|
Applies to:
Oracle Server - Standard Edition
Oracle Server - Personal Edition
Oracle Server - Enterprise Edition - Version: 9.0.1.0 and later [Release: 9.0.1 and later]
Information in this document applies to any platform.
Purpose
The purpose of this document is to give a quick guide about how to perform Backup and Recovery on RAC databases. We will follow this points:
1. Verify the database mode and archive destination.
2. Verify connectivity using sqlnet for target and catalog.
3. Determine your backup Device.
4. Understand how to create an RMAN persistent configuration for a RAC env.
5. Create backups to disk using the new persistent configuration parameters.
6. Backupset Maintenance using the configured retention policy.
7. Restore and Recover
a. Complete
b. Incomplete
8. Review and understand the impact of resetlogs on the catalog.
9. RMAN Sample Commands.
Scope and Application
· This discussion is for a 2-node Oracle RAC Cluster.
· The logs are being archived to their respective node.
· We are allocating channels to each node to enable the autolocate feature of RMAN in a RAC env.
RMAN: RAC Backup and Recovery using RMAN
1. Verify the databases are in archivelog mode and archive destination.
a. NODE 1: thread 1
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/app/oracle/product/9.2.0/dbs/arch
Oldest online log sequence 20
Next log sequence to archive 21
Current log sequence 21
b. NODE 2: thread 2
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/app/oracle/product/9.2.0/dbs/arch
Oldest online log sequence 8
Next log sequence to archive 9
Current log sequence 9
2. Verify connectivity to the target nodes and catalog if used.
$ setenv TNS_ADMIN $ORACLE_HOME/network/admin
$ sqlplus /nolog
SQL> connect as sysdba
SQL> connect as sysdba
SQL> connect
3. Set your testing areas.
Testing HOME for logs: /u02/home/usupport/rman
Backups HOME Location: /rman/V920
4. Connect using RMAN to verify and set the controlfile persistent configuration.
The controlfiles are shared between the instances so configuring the controlfile on node 1 also sets it for all nodes in the RAC cluster.
* Always note the target DBID
connected to target database: V920 (DBID=228033884)
* Default Configuration
RMAN> SHOW ALL;
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u02/app/oracle/product/9.2.0/dbs/snapcf_V9201.f'; # default
* Make changes to the default that fit your business requirements.
Note the retention policy can be set "TO REDUNDANCY x" or it can be set "TO RECOVERY WINDOW OF x DAYS", this is new in Oracle9i.
In this example, using PARALLELISM 2 as 2 nodes are used.
The PARALLELISM will than automatically start 2 channels and will use the related CONFIGURE CHANNEL for additional clauses.
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/rman/V920/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/rman/V920/snapcf_V92321.f';
CONFIGURE CHANNEL 1 DEVICE TYPE DISK connect ;
CONFIGURE CHANNEL 2 DEVICE TYPE DISK connect ;
* Review/Verify your new configuration.
RMAN> SHOW ALL;
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/rman/V920/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE CHANNEL 1 DEVICE TYPE DISK CONNECT ;
CONFIGURE CHANNEL 2 DEVICE TYPE DISK CONNECT ;
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/rman/V920/snapcf_V92321.f';
5. Make a backup using the new persistent configuration parameters.
* Backup database with differential incremental 0 and then archived logs using the delete input option.
BACKUP INCREMENTAL LEVEL 0 FORMAT '/rman/V920/%d_LVL0_%T_%u_s%s_p%p' DATABASE;
BACKUP ARCHIVELOG ALL FORMAT '/rman/V920/%d_AL_%T_%u_s%s_p%p'DELETE INPUT;
* Backup again using differential incremental level 1
BACKUP INCREMENTAL LEVEL 1 FORMAT '/rman/V920/%d_LVL1_%T_%u_s%s_p%p' DATABASE;
BACKUP ARCHIVELOG ALL FORMAT '/rman/V920/%d_AL_%T_%u_s%s_p%p' DELETE INPUT;
* To simplify this from Oracle9i we can also use PLUS ARCHIVELOG
BACKUP INCREMENTAL LEVEL 0 FORMAT '/rman/V920/%d_LVL0_%T_%u_s%s_p%p'
DATABASE PLUS ARCHIVELOG FORMAT '/rman/V920/%d_AL_%T_%u_s%s_p%p' DELETE INPUT;
This uses a different algorithm than backup database and backup archivelog in separate commands, the algorithm for PLUS ARCHIVELOG is:
1. Archive log current
2. Backup archived logs
3. Backup database level 0
4. Archive log current
5. Backup any remaining archived log created during backup
6. Backup set Maintenance using the configured retention policy
RMAN> LIST BACKUP SUMMARY;
RMAN> LIST BACKUP BY DATAFILE;
RMAN> LIST BACKUP OF DATABASE;
RMAN> LIST BACKUP OF ARCHIVELOG ALL;
RMAN> LIST BACKUP OF CONTROLFILE;
These above can be enhanced with the "until time" clause as well as the archivelog backups using "not backed up x times" to cut down on many copies of a log in several backup sets.
Then continuing with SMR Server Managed Recovery use the change archivelog from...until...delete to remove old logs no longer needed on disk.
To check/delete obsolete backups or archivelogs we use:
RMAN> REPORT OBSOLETE;
RMAN> DELETE OBSOLETE;
or
RMAN> DELETE NOPROMPT OBSOLETE;
To check the database files:
RMAN> REPORT SCHEMA;
7. Restore and Recover
Complete Recovery
With the database mounted on the node1 and no-mount on node2 connect to the target and catalog using RMAN.
rman target / catalog
This script will restore and recover the database completely and open.
All previous backup will still be available for use because there was not RESETLOGS command given.
run {
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN;
}
Incomplete Recovery
If you are using instance registration the database must be mounted to register with the listener. This means you must use the current controlfile for restore and recovery or setup a dedicated listener if not already done. RMAN requires a dedicated server connection and does not work with using instance registration before mounting the controlfile.
Using the autobackup controlfile feature requires the DBID of the TARGET database. It must be set when the database is not mounted and only the controlfile and spfile (from 9.2) can be restored this way.
1. Shutdown node1 and node2
2. Startup no-mount node2 and node1
3. Start rman
rman trace reco1.log
RMAN> CONNECT CATALOG
RMAN> SET DBID=228033884;
RMAN> CONNECT TARGET
4. Restore the controlfile from autobackup
% rman trace recocf.log
RMAN> SET DBID=228033884;
RMAN> CONNECT TARGET
RMAN> RUN
{
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE disk TO '/rman/V920/%F';
ALLOCATE CHANNEL d1 DEVICE TYPE disk;
RESTORE CONTROLFILE FROM AUTOBACKUP
MAXSEQ 5 # start at sequence 5 and count down (optional)
MAXDAYS 5; # start at UNTIL TIME and search back 5 days (optional)
MOUNT DATABASE;
}
5. Verify what is available for incomplete recovery.
We will recover with the highest available redo information. In a RAC database, both thread must be considered to determine highest available redo. The options are "until time", "until scn", or "until sequence". We will use the log sequence in this case.
* First we need to find the highest sequence of each thread:
SQL> select max(sequence#) from v$archived_log where thread#=1;
MAX(SEQUENCE#)
--------------
25
SQL> select max(sequence#) from v$archived_log where thread#=2;
MAX(SEQUENCE#)
--------------
13
* Next is to find the thread with lowest NEXT_CHANGE# scn.
In this case the next_change# SCN in thread 1 sequence 25 is lower than sequence 13 thread 2. In a RAC environment, we use the lower to ensure we have the redo required from BOTH threads. In other words, we use the lower (thread# 1) to ensure that ALL scn (s) in thread #1 exist in the available sequence for thread #2.
So we will set sequence 26 for thread 1 for RMAN 'until sequence' recovery, because RMAN stops the recovery before applying the indicated sequence. Log sequence for recovery needs always be sequence+1 to end at +1 after applying the prior sequence.
SQL> select sequence#, thread#, first_change#, next_change#
from v$archived_log
where sequence# in (13,25);
SEQUENCE# THREAD# FIRST_CHANGE# NEXT_CHANGE#
-------------------- -------------- ------------------------- -------------------------
25 1 1744432 1744802
13 2 1744429 1744805
SQL> select sequence#, thread#, first_change#, next_change#
from v$backup_redolog
where sequence# in (13,25);
SEQUENCE# THREAD# FIRST_CHANGE# NEXT_CHANGE#
-------------------- -------------- ------------------------- -------------------------
25 1 1744432 1744802
13 2 1744429 1744805
--- SET UNTIL SEQUENCE 26 THREAD 1 ----
6. Get the sentences to add TEMPFILES after opening DB.
Locally Managed Temporary Tablespaces are not restored by RESTORE command, we need to create them manually after recovery is complete.
If using LMT Temporary tablespace the controlfile will have the syntax to add the tempfile after recovery is complete. The following command will give us the create controlfile sencence:
SQL> alter database backup controlfile to trace;
Example:
# Commands to add tempfiles to temporary tablespaces.
# Online tempfiles have complete space information.
# Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/dev/db/rV92B_temp_01.dbf' SIZE 41943040 REUSE AUTOEXTEND OFF;
# End of tempfile additions.
#
7. Run the rman script
Since log sequence 13 thread 2 next_change# is 3 changes ahead of thread 1 sequence 25 we are using sequence 14 to stop recovery. This will restore the data files and recover them completely using the online logs.
run {
SET UNTIL SEQUENCE 26 THREAD 1;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}
Finally add the tempfiles with sentences from step 6.
8. Review and understand the impact of resetlogs on the catalog.
After resetlogs there are 2 incarnations in the recovery catalog. Only one incarnation can be current at one time for a given DBID. The Inc Key keeps track of the database incarnations.
RMAN> LIST INCARNATION OF DATABASE V920;
List of Database Incarnations
DB Key Inc Key DB Name DB ID CUR Reset SCN Reset Time
----------- ----------- ------------ ---------------- ------- -------------- ---------------
2656 2657 V920 228033884 NO 1 29-MAY-03
2656 3132 V920 228033884 YES 1744806 13-JUN-03
9. RMAN Sample Commands
* With a dedicated listener (not using instance registration) restoring the controlfile.
run {
ALLOCATE CHANNEL D1 TYPE DISK CONNECT ;
ALLOCATE CHANNEL D2 TYPE DISK CONNECT ;
SET UNTIL SEQUENCE 14 THREAD 2;
RESTORE CONTROLFILE;
ALTER DATABASE MOUNT;
RELEASE CHANNEL D1;
RELEASE CHANNEL D2;
}
* Backup Archivelog
BACKUP ARCHIVELOG ALL NOT BACKED UP 3 TIMES;
BACKUP ARCHIVELOG UNTIL TIME 'SYSDATE-2' NOT BACKED UP 2 TIMES;