Oracle Database Version: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0
Operating System Version: Enterprise Linux Enterprise Linux AS release 4 (October Update 8)
ORACLE_SID: ORCL10G2
DEVICE TYPE: DISK
- Perform Full Hot RMAN Backup including archivelogs.
> [oracle@oracle11g ~]$ rman target /
Sample Execution Log:run {
sql 'alter system archive log current';
BACKUP FULL
TAG = Full_Hot_Backup
format '/u01/backup/ORCL10G2/df_%t_%s_%p'
(database) plus ARCHIVELOG DELETE INPUT;
backup current controlfile;
}
- End of RMAN Full Database Backup -[oracle@oracle11g ~]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Wed Mar 28 13:38:04 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORCL10G2 (DBID=782066616)
RMAN> run {
sql 'alter system archive log current';
BACKUP FULL
TAG = Full_Hot_Backup
format '/u01/backup/ORCL10G2/df_%t_%s_%p'
(database) plus ARCHIVELOG DELETE INPUT;
backup current controlfile;
}2> 3> 4> 5> 6> 7> 8>
using target database control file instead of recovery catalog
sql statement: alter system archive log current
Starting backup at 28-MAR-12
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=1 recid=24 stamp=779118000
input archive log thread=1 sequence=2 recid=25 stamp=779118001
channel ORA_DISK_1: starting piece 1 at 28-MAR-12
channel ORA_DISK_1: finished piece 1 at 28-MAR-12
piece handle=/u01/backup/ORCL10G2/df_779118003_22_1 tag=FULL_HOT_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL10G2/archivelog/2012_03_28/o1_mf_1_1_7q591jd2_.arc recid=24 stamp=779118000
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL10G2/archivelog/2012_03_28/o1_mf_1_2_7q591k2r_.arc recid=25 stamp=779118001
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=1 recid=12 stamp=779033519
input archive log thread=1 sequence=2 recid=13 stamp=779033520
input archive log thread=1 sequence=3 recid=14 stamp=779033526
input archive log thread=1 sequence=4 recid=15 stamp=779033620
input archive log thread=1 sequence=5 recid=22 stamp=779034293
input archive log thread=1 sequence=6 recid=23 stamp=779034293
input archive log thread=1 sequence=7 recid=21 stamp=779034292
channel ORA_DISK_1: starting piece 1 at 28-MAR-12
channel ORA_DISK_1: finished piece 1 at 28-MAR-12
piece handle=/u01/backup/ORCL10G2/df_779118005_23_1 tag=FULL_HOT_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL10G2/archivelog/2012_03_27/o1_mf_1_1_7q2pkhf0_.arc recid=12 stamp=779033519
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL10G2/archivelog/2012_03_27/o1_mf_1_2_7q2pkj6b_.arc recid=13 stamp=779033520
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL10G2/archivelog/2012_03_27/o1_mf_1_3_7q2pkpnf_.arc recid=14 stamp=779033526
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL10G2/archivelog/2012_03_27/o1_mf_1_4_7q2pnnnt_.arc recid=15 stamp=779033620
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL10G2/archivelog/2012_03_27/o1_mf_1_5_7q2q9o16_.arc recid=22 stamp=779034293
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL10G2/archivelog/2012_03_27/o1_mf_1_6_7q2q9o2w_.arc recid=23 stamp=779034293
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL10G2/archivelog/2012_03_27/o1_mf_1_7_7q2q9nvp_.arc recid=21 stamp=779034292
Finished backup at 28-MAR-12
Starting backup at 28-MAR-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u02/oradata/ORCL10G2/system01.dbf
input datafile fno=00003 name=/u02/oradata/ORCL10G2/sysaux01.dbf
input datafile fno=00002 name=/u02/oradata/ORCL10G2/undotbs01.dbf
input datafile fno=00004 name=/u02/oradata/ORCL10G2/users01.dbf
channel ORA_DISK_1: starting piece 1 at 28-MAR-12
channel ORA_DISK_1: finished piece 1 at 28-MAR-12
piece handle=/u01/backup/ORCL10G2/df_779118007_24_1 tag=FULL_HOT_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:26
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 28-MAR-12
channel ORA_DISK_1: finished piece 1 at 28-MAR-12
piece handle=/u01/backup/ORCL10G2/df_779118153_25_1 tag=FULL_HOT_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 28-MAR-12
Starting backup at 28-MAR-12
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=3 recid=26 stamp=779118158
channel ORA_DISK_1: starting piece 1 at 28-MAR-12
channel ORA_DISK_1: finished piece 1 at 28-MAR-12
piece handle=/u01/backup/ORCL10G2/df_779118158_26_1 tag=FULL_HOT_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL10G2/archivelog/2012_03_28/o1_mf_1_3_7q596g46_.arc recid=26 stamp=779118158
Finished backup at 28-MAR-12
Starting backup at 28-MAR-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 28-MAR-12
channel ORA_DISK_1: finished piece 1 at 28-MAR-12
piece handle=/u01/app/oracle/flash_recovery_area/ORCL10G2/backupset/2012_03_28/o1_mf_ncnnf_TAG20120328T134240_7q596k10_.bkp tag=TAG20120328T134240 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 28-MAR-12
RMAN>
- To perform full database restore, follow the steps below.
- You may want to list the current backup of controlfile.
> RMAN> list backup of controlfile;
- List current full database backupRMAN> list backup of controlfile;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
22 Full 6.80M DISK 00:00:02 28-MAR-12
BP Key: 22 Status: AVAILABLE Compressed: NO Tag: FULL_HOT_BACKUP
Piece Name: /u01/backup/ORCL10G2/df_779118153_25_1
Control File Included: Ckp SCN: 415026 Ckp time: 28-MAR-12
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
24 Full 6.77M DISK 00:00:01 28-MAR-12
BP Key: 24 Status: AVAILABLE Compressed: NO Tag: TAG20120328T134240
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL10G2/backupset/2012_03_28/o1_mf_ncnnf_TAG20120328T134240_7q596k10_.bkp
Control File Included: Ckp SCN: 415037 Ckp time: 28-MAR-12
RMAN>
> RMAN> list backup of database;
- We will perform database restore from scratch including spfile and controlfile.RMAN> list backup of database;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
21 Full 504.21M DISK 00:02:25 28-MAR-12
BP Key: 21 Status: AVAILABLE Compressed: NO Tag: FULL_HOT_BACKUP
Piece Name: /u01/backup/ORCL10G2/df_779118007_24_1
List of Datafiles in backup set 21
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 414978 28-MAR-12 /u02/oradata/ORCL10G2/system01.dbf
2 Full 414978 28-MAR-12 /u02/oradata/ORCL10G2/undotbs01.dbf
3 Full 414978 28-MAR-12 /u02/oradata/ORCL10G2/sysaux01.dbf
4 Full 414978 28-MAR-12 /u02/oradata/ORCL10G2/users01.dbf
RMAN>
- At this point of time, we will first restore spfile.
- Set correct SID
> [oracle@oracle11g ~]$ export ORACLE_SID=ORCL10G2
- Connect to RMAN and startup in nomount mount
> [oracle@oracle11g ~]$ rman target /
> RMAN> startup nomount;
RMAN> startup nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/10.2.0/db_1/dbs/initORCL10G2.ora'
starting Oracle instance without parameter file for retrival of spfile
Oracle instance started
Total System Global Area 159383552 bytes
Fixed Size 1272552 bytes
Variable Size 58721560 bytes
Database Buffers 96468992 bytes
Redo Buffers 2920448 bytes
[/code]
Notice that even without spfile, it will startup in nomount mode.
- Restore spfile. You got the list of spfile backup from RMAN> list backup of controlfile;
Note: spfile and controlfile is automatically backed up when you perform FULL database backup.
> RMAN> restore spfile from '/u01/backup/ORCL10G2/df_779118153_25_1';Code: Select all
RMAN> restore spfile from '/u01/backup/ORCL10G2/df_779118153_25_1'; Starting restore at 28-MAR-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=36 devtype=DISK channel ORA_DISK_1: autobackup found: /u01/backup/ORCL10G2/df_779118153_25_1 channel ORA_DISK_1: SPFILE restore from autobackup complete Finished restore at 28-MAR-12 RMAN> [/quote] - Shutdown the instance and startup again in nomount mode. > RMAN> [b]shutdown immediate;[/b] [quote]RMAN> shutdown immediate; Oracle instance shut down RMAN> [/quote] > RMAN> [b]startup nomount;[/b] [quote]RMAN> startup nomount; connected to target database (not started) Oracle instance started Total System Global Area 285212672 bytes Fixed Size 1273276 bytes Variable Size 92275268 bytes Database Buffers 188743680 bytes Redo Buffers 2920448 bytes RMAN> [/quote] Notice that it is now using the previously restored spfile. - Restore the controlfile from backup. > RMAN> [b]restore controlfile from '/u01/app/oracle/flash_recovery_area/ORCL10G2/backupset/2012_03_28/o1_mf_ncnnf_TAG20120328T134240_7q596k10_.bkp';[/b] [quote]RMAN> restore controlfile from '/u01/app/oracle/flash_recovery_area/ORCL10G2/backupset/2012_03_28/o1_mf_ncnnf_TAG20120328T134240_7q596k10_.bkp'; Starting restore at 28-MAR-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=156 devtype=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:02 output filename=/u02/oradata/ORCL10G2/control01.ctl output filename=/u02/oradata/ORCL10G2/control02.ctl output filename=/u02/oradata/ORCL10G2/control03.ctl Finished restore at 28-MAR-12 RMAN> [/quote] - Mount the database. > RMAN> [b]alter database mount;[/b] [quote]RMAN> alter database mount; database mounted released channel: ORA_DISK_1 RMAN> [/quote] - Begin FULL database restore. > RMAN> [b]restore database;[/b] [quote]RMAN> restore database; Starting restore at 28-MAR-12 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00001 to /u02/oradata/ORCL10G2/system01.dbf restoring datafile 00002 to /u02/oradata/ORCL10G2/undotbs01.dbf restoring datafile 00003 to /u02/oradata/ORCL10G2/sysaux01.dbf restoring datafile 00004 to /u02/oradata/ORCL10G2/users01.dbf channel ORA_DISK_1: reading from backup piece /u01/backup/ORCL10G2/df_779118007_24_1 channel ORA_DISK_1: restored backup piece 1 piece handle=/u01/backup/ORCL10G2/df_779118007_24_1 tag=FULL_HOT_BACKUP channel ORA_DISK_1: restore complete, elapsed time: 00:02:06 Finished restore at 28-MAR-12 RMAN> [/quote] - Begin media/database recovery. > RMAN> [b]recover database;[/b] [quote]RMAN> recover database; Starting recover at 28-MAR-12 using channel ORA_DISK_1 starting media recovery unable to find archive log archive log thread=1 sequence=3 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 03/28/2012 14:07:21 RMAN-06054: media recovery requesting unknown log: thread 1 seq 3 lowscn 414978 RMAN> [/quote] - Now, open the database with RESETLOGS option. > [oracle@oracle11g ~]$ [b]sqlplus '/as sysdba'[/b] > SQL> alter database open RESETLOGS; [quote]SQL> alter database open RESETLOGS; Database altered. SQL> [/quote] - Verify that your database is now available. [quote]-- ************************************************************** -- database info -- ************************************************************** SET LINESIZE 120 SET PAGESIZE 40 COLUMN DATABASE_INFO HEADING "General Database Information" select 'Hostname : ' || i.host_name||chr(10)|| 'Inst Name : ' || i.instance_name||chr(10)|| 'Inst Status : ' || i.database_status||' and '||i.status||chr(10)|| 'Inst Started At : ' || to_char(i.startup_time,'DD-MON-YYYY HH24:MI:SS')||chr(10)|| 'Appl Sessions : ' || nvl(s.sessions,0)||chr(10)|| 'Appl Processes : ' || nvl(p.process,0)||chr(10)|| 'Locked Objects : ' || nvl(l.locks,0)||chr(10)|| 'Archiver : ' || i.archiver||chr(10)|| 'Uptime : ' || floor(sysdate - i.startup_time) || ' days(s) ' || trunc( 24*((sysdate-i.startup_time) - trunc(sysdate-i.startup_time))) || ' hour(s) ' || mod(trunc(1440*((sysdate-i.startup_time) - trunc(sysdate-i.startup_time))), 60) ||' minute(s) ' || mod(trunc(86400*((sysdate-i.startup_time) - trunc(sysdate-i.startup_time))), 60) ||' seconds' as Database_Info from sys.gv_$instance i, (select inst_id,count(*) sessions from sys.gv$session where nvl(username,'BACKGROUND') not in ('SYS','SYSTEM','BACKGROUND') group by inst_id) s, (select inst_id,count(*) process from (select p.inst_id,s.username, s.process from gv$session s, gv$process p where s.inst_id=p.inst_id and s.paddr=p.addr) where nvl(username,'BACKGROUND') not in ('SYS','SYSTEM','BACKGROUND') group by inst_id) p, (select inst_id,count(*) locks from sys.gv$locked_object group by inst_id) l where s.inst_id (+) = i.instance_number and p.inst_id (+) = i.instance_number and l.inst_id (+) = i.instance_number UNION Select 'Database Size (Mb): '||To_Char(Round((nb_ctl.nb * ctl_size.the_size) + (rlf_size.the_size/1024) + (dtf_size.the_size/1024) + (nvl(dtft_size.the_size,0)/1024))) From (select count(1) nb from v$controlfile) nb_ctl , (select round(sum(record_size)/1024) the_size from V$CONTROLFILE_RECORD_SECTION) ctl_size , (select round(sum(bytes)/1024) the_size from v$log) rlf_size , (select round(sum(bytes)/1024) the_size from dba_data_files) dtf_size , (select round(sum(bytes)/1024) the_size from dba_temp_files) dtft_size UNION Select 'Database SGA (Mb) : '||To_Char(Round(sum (value)/1024/1024)) from v$sga UNION SELECT 'Version : '||banner FROM v$version UNION select 'Database Name : ' || name || chr(10) || 'Open Mode : ' || open_mode from v$database; [/quote] [quote]General Database Information ------------------------------------------------------------------------------------------------------------------------ Database Name : ORCL10G2 Open Mode : READ WRITE Database SGA (Mb) : 272 Database Size (Mb): 928 Hostname : oracle11g.localdomain Inst Name : ORCL10G2 Inst Status : ACTIVE and OPEN Inst Started At : 28-MAR-2012 14:17:14 Appl Sessions : 0 Appl Processes : 0 Locked Objects : 0 Archiver : STARTED Uptime : 0 days(s) 0 hour(s) 6 minute(s) 51 seconds Version : CORE 10.2.0.5.0 Production Version : NLSRTL Version 10.2.0.5.0 - Production Version : Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Prod Version : PL/SQL Release 10.2.0.5.0 - Production Version : TNS for Linux: Version 10.2.0.5.0 - Production [/quote]