Check the configuration parameters of Rman.
Connect to Rman without catalog. Controlfile is used to store Rman metadata:
You need to set the environment before invoking Rman:
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1
ORA_CRS_HOME=/u01/app/crs
ORACLE_PATH= $ORACLE_BASE/common/oracle/sql:.:$ORACLE_HOME/rdbms/admin
ORACLE_SID=mars1
$rman target / nocatalog
RMAN> show all;
RMAN>exit;
———————————–
rmanbackupconf.sh
rman target / nocatalog <<EOF
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 ‘/orabackup/rman/%F’;
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL 1 DEVICE TYPE DISK CONNECT ‘SYS/redhat@mars1’;
CONFIGURE CHANNEL 2 DEVICE TYPE DISK CONNECT ‘ SYS/redhat@mars1’;
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM ‘AES128’; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/orabackup/rman/snapcf_racdbtst1.f’; # default
configure controlfile autobackup format for device type disk to ‘/orabackup/rman/ctrl/%F’;
configure channel device type disk format ‘/orabackup/rman/backup_db_%d_S_%s_P_%p_T_%t’;
exit;
EOF
————————————————————————————–
Check the archive log status and destination:
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +ORCL_DATA1
Oldest online log sequence 82
Next log sequence to archive 83
Current log sequence 83
Check existing tablespaces and datafiles:
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
——————————
SYSTEM +ORCL_DATA1/mars/datafile/system.282.648988143
SYSAUX +ORCL_DATA1/mars/datafile/sysaux.283.648988181
UNDOTBS1 +ORCL_DATA1/mars/datafile/undotbs1.284.648988195
UNDOTBS2 +ORCL_DATA1/mars/datafile/undotbs2.286.648988247
USERS +ORCL_DATA1/mars/datafile/users.287.648988271
DATA +ORCL_DATA1/mars/datafile/data01.dbf
OBJECTS +ORCL_DATA1/mars/datafile/objects01.dbf
INDX +ORCL_DATA1/mars/datafile/index01.dbf
Create a test tablespace and a test table to be used to check Restore and Recover:
SQL> create tablespace recop1;
Tablespace created.
SQL> create table restable1 tablespace recop1 as select sysdate timestamp from dual;
Table created.
Check tablespaces and datafiles, note that all of them are located on ASM’s ORCL_DATA1 disk group:
SQL> select tablespace_name,file_name from dba_data_files
Check the timestamp we inserted on the test table, we will recover until this time later:
SQL> select * from restable1;
TIMESTAMP
—————–
01-02-07 15:49:06
————————————————————————————–
Execute backup using script
backup.sh
#!/bin/csh -x
# rman_backup_as_copy_to_FS
# —————————-
# 29-01-07 Alejandro Vargas
# —————————-
# This script make a backup copy to file system
# This backup can be restored on File system as a regular hot backup
# Or can be restored to ASM by using rman
# ——————————————————————————-
# This script does:
# 1) Administrative tasks:
# crosscheck
# delete obsolete
# 2) Archive log current on 1st Instance
# 3) Archive log current on 2nd Instance
# 4) Rman backup as copy to file system including controlfile and archivelogs
# 5) Archive log current on 1st Instance
# 6) Archive log current on 2nd Instance
# 7) Rman backup as copy archivelogs not backed up and print backupset list to log
# ——————————————————————————–
# This script works with 2 nodes only, if you have more than 2 nodes you need to customize it.
#
# This script use aliases and Environment variables set on .cshrc
# to setup the environment to point to the Database:
# setenv DBS_HOME /u01/app01/oracle/product/10gDB
# setenv BASE_PATH /usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/X11R6/bin:/root/bin
# alias 10db ‘setenv $ORACLE_HOME $DBS_HOME; setenv PATH $ORACLE_HOME/bin:$BASE_PATH’
# This script do require as parameters the 2 instance names
# It will use them to archive all required logs from instances 1 and 2
# ——————————————————————–
set v_inst1=mars1
set v_inst2=mars2
# Rman Backup Location variable
# —————————–
set v_rman_loc=/orabackup/rman
# Step 1: Administrative tasks, crosscheck and delete obsolete
# ————————————————————
#10db
setenv ORACLE_SID $v_inst1
rman target / nocatalog <<EOF
crosscheck backupset;
crosscheck copy;
crosscheck archivelog all;
delete noprompt expired backup ;
delete noprompt obsolete;
exit
EOF
# This script run from 1st node. We use an external identified DBA user, ops$oracle, to execute
# the archive log current. From the same session we connect as ops$oracle into the 2nd instance
# You need remote_os_authent=TRUE on both instances to connect remotely without password
# Step 2: Archive log current on 1st Instance
# Step 3: Archive log current on 2nd Instance
# ——————————————-
sqlplus -s sys/pass@$v_inst1 as sysdba << EOF
select instance_name from v\$instance
/
alter system archive log current
/
connect sys/pass@$v_inst2 as sysdba;
select instance_name from v\$instance
/
alter system archive log current
/
exit
EOF
# On step 4 we use 4 channels. This needs to be customized according the number of cpu’s/IO
# channels available. Rman is invoked in nocatalog mode, we need to have configured
# ORACLE_HOME, ORACLE_SID and PATH on the environment, as we did in the previous steps.
# Step 4: Rman backup as copy to file system including controlfile and archivelogs
# ——————————————————————————–
rman target / nocatalog <<EOF
run {
allocate channel backup_disk1 type disk format ‘$v_rman_loc/%U’;
allocate channel backup_disk2 type disk format ‘$v_rman_loc/%U’;
backup as COPY tag ‘%TAG’ database include current controlfile;
release channel backup_disk1;
release channel backup_disk2;
}
exit
EOF
# Step 5 and 6: Archive log current on 1st and 2nd Instances
# ———————————————————-
sqlplus -s sys/pass@$v_inst1 as sysdba << EOF
select instance_name from v\$instance
/
alter system archive log current
/
connect sys/pass@$v_inst2 as sysdba;
select instance_name from v\$instance
/
alter system archive log current
/
exit
EOF
# Step 7: Rman backup as copy archivelogs not backed up and print backupset list to log
rman target / nocatalog <<EOF
backup as copy archivelog all format ‘$v_rman_loc/%d_AL_%T_%u_s%s_p%p’ ;
list backupset;
exit
EOF
# Redirecting rman output to log will suppress standard output, because of that
# running separately.
rman target / nocatalog log=$v_rman_loc/backupset_info.log <<EOF
list backup summary;
list backupset;
list backup of controlfile;
exit
EOF
# eof backup
————————————————————————————–
This script does:
1) clean up the catalog (crosscheck / delete obsolete)
2) archive log current on both instances
3) backup database as copy to File System
4) archive log current on both instances
5) backup as copy archived logs
6) log actual backups
See the Backup Log.
The Backup generated the following files:
Controlfile and spfile backup:
Datafiles:
Rman Backups list:
backupset_info.log
Backup log:
/tmp/rman_backup.err
Insert some other records into the test table:
Insert into the test table new rows,check its content:
SQL> insert into restable1 select sysdate from dual;
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> commit;
Commit complete.
SQL> select * from restable1;
SQL> alter system archive log current;
System altered.
Simulate a crash by manually deleting some datafiles:
Execute on both instances:
SQL> select instance_name from v$instance;
INSTANCE_NAME
—————-
mars1
SQL> shutdown abort
ORACLE instance shut down.
SQL> select instance_name from v$instance;
INSTANCE_NAME
—————-
mars2
SQL> shutdown abort
ORACLE instance shut down.
Inside ASM some files accidentally deleted!!!:
$asmcmd
ASMCMD> cd ORCL_DATA1/mars/datafile/
ASMCMD> ls
ASMCMD> rm USERS.264.606653719 <<<<< Note, only possible because the
ASMCMD> rm RECOP1.273.613410453 <<<<< Database is down!!!
ASMCMD> ls
ASMCMD> EXIT
$sqlplus / as sysdba
Enter user-name: / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 96470608 bytes
Database Buffers 184549376 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 – see DBWR trace file
ORA-01110: data file 5: ‘+ORCL_DATA1/mars/datafile/users.264.606653719′
NOTE: Drop tablespace from inside the database is not recoverable with Rman; Rman will also deleted the backup copy of any deleted tablespace!!!!
—————————————————————————————
RECOVER PROCESS
Execute recover until time using the existing backup:
Set the database to work as single instance to perform the recovery and stop it:
SQL> show parameters cluster_database
NAME TYPE VALUE
———————————— ———– ——————————
cluster_database boolean TRUE
cluster_database_instances integer 2
SQL> alter system set cluster_database=false scope=spfile sid=’*’;
System altered.
SQL> shutdown abort
ORACLE instance shut down.
Check the backup files and take note of the Database ID (highlighted):
$cd /orabackup/rman/
$ls
backupset_info.log
cf_D-MARS_id-1122898414_5djc2p5c
ctrl
data_D-MARS_I-1122898414_TS-DATA_FNO-6_4rjc2nqs
data_D-MARS_I-1122898414_TS-DATA_FNO-6_5ajc2p4e
data_D-MARS_I-1122898414_TS-INDX_FNO-8_4tjc2nr1
data_D-MARS_I-1122898414_TS-INDX_FNO-8_5cjc2p5a
data_D-MARS_I-1122898414_TS-OBJECTS_FNO-7_4sjc2nqu
data_D-MARS_I-1122898414_TS-OBJECTS_FNO-7_5bjc2p50
data_D-MARS_I-1122898414_TS-SYSAUX_FNO-2_4njc2nn7
data_D-MARS_I-1122898414_TS-SYSAUX_FNO-2_56jc2p08
data_D-MARS_I-1122898414_TS-SYSTEM_FNO-1_4ojc2nn9
data_D-MARS_I-1122898414_TS-SYSTEM_FNO-1_57jc2p0a
data_D-MARS_I-1122898414_TS-UNDOTBS1_FNO-3_4pjc2npd
data_D-MARS_I-1122898414_TS-UNDOTBS1_FNO-3_58jc2p33
data_D-MARS_I-1122898414_TS-UNDOTBS2_FNO-4_4qjc2npm
data_D-MARS_I-1122898414_TS-UNDOTBS2_FNO-4_59jc2p41
data_D-MARS_I-1122898414_TS-USERS_FNO-5_4vjc2nr8
data_D-MARS_I-1122898414_TS-USERS_FNO-5_5ejc2p5k
MARS_AL_20080324_52jc2ns2_s162_p1
MARS_AL_20080324_54jc2ns6_s164_p1
MARS_AL_20080324_5gjc2p80_s176_p1
MARS_AL_20080324_5hjc2p82_s177_p1
MARS_AL_20080324_5ijc2p88_s178_p1
MARS_AL_20080324_5jjc2p8a_s179_p1
MARS_AL_20080324_5kjc2p8c_s180_p1
MARS_AL_20080324_5ljc2p8e_s181_p1
MARS_AL_20080324_5mjc2p8h_s182_p1
MARS_AL_20080324_5njc2p8j_s183_p1
MARS_AL_20080324_5ojc2p8m_s184_p1
MARS_AL_20080324_5pjc2p8o_s185_p1
MARS_AL_20080324_5qjc2p8q_s186_p1
MARS_AL_20080324_5rjc2p8t_s187_p1
$rman target / nocatalog
Recovery Manager: Release 10.2.0.1.0 – Production on Thu Feb 1 16:34:20 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> set dbid=-1122898414
executing command: SET DBID
RMAN> startup nomount;
Oracle instance started
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 96470608 bytes
Database Buffers 184549376
Redo Buffers 2973696 bytes
1) we do restore the controlfile from a time previous to the crash:
RMAN> restore controlfile from ‘/orabackup/rman/cf_D-MARS_id-1122898414_5djc2p5c’;
Starting restore at 01/02/2008 16:36:03
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=+ORCL_DATA1/mars/controlfile/current.256.606653653
Finished restore at 01/02/2008 16:36:20
2) we mount the database:
RMAN> mount database;
database mounted
released channel: ORA_DISK_1
3) we set until which time we want to recover, using the ‘set until time’ clause, the we do restore and recover, in this example the three commands are passed to Rman within a single block:
RMAN> run { set until time=”to_date(’01-FEB-08 16:14:28′,’DD-MON-YY HH24:MI:SS’)”;
2> restore database;
3> recover database; }
executing command: SET until clause
Starting restore at 01/02/2008 16:40:26
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=148 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=153 devtype=DISK
Finally we need to restablish Cluster Mode and open both instances.
1)Mount instance 1 and set cluster_database=true :
SQL> show parameters cluster_database
NAME TYPE VALUE
———————————— ———– ——————————
cluster_database boolean FALSE
cluster_database_instances integer 2
SQL> alter system set cluster_database=true scope=spfile sid=’*’;
System altered.
SQL> shutdown immediate
ORACLE instance shut down.
2) Restart the database in cluster Mode:
srvctl start database -d mars
srvctl start service -d mars
crs_stat –t
chkcrs
3) Check restore point on test table:
SQL> select * from restable1;
TIMESTAMP
—————–
01-02-08 15:49:06
http://www.comp.dit.ie/btierney/Oracle11gDoc/server.111/b28301/backrest002.htm