I'm going to take a backup of my database using RMAN.
The RMAN backup files have been saved under the $ORACLE_HOME/dbs directory.
I'll connect to the database, and list the data that is currently in the table. I'll also add a new entry(Rivaldo) to the table
Next, to test my backup I'll the datafile as shown below.
I'll re-connect to the database and see if I can add another entry(Figo) to the table.
As you see above I was first not able to do a shutdown immediate,
Now if you go and try RMAN recovery, you'll see it will fail. Even listing the backup will fail.
I went back and did a shutdown abort, but even that did not help in listing the backup.
Next, I mounted my database and then tried to list the RMAN backup. This worked. The moral of the story, to list the backups in RMAN the database should be mounted.
Now that I'm able to list the backups let us try an restore the datafile(system01.dbf) that we had lost.
On the host system we see that the file is now back
Next, we will recover the file
Now let us open the database and see if the datafile was actually recovered. There we go, when we did the RMAN backup Rivaldo and Figo was not in the list. Rivaldo and Figo were added later, but after the recovery process the datafile has Rivaldo and Figo in it.
$ rman nocatalog target / Recovery Manager: Release 11.2.0.1.0 - Production on Fri Mar 4 17:56:08 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: TESTGM (DBID=2787571766) using target database control file instead of recovery catalog RMAN> backup database; Starting backup at 04-MAR-11 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=101 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/gm_oradata/testgm/system01.dbf input datafile file number=00002 name=/gm_oradata/testgm/sysaux01.dbf input datafile file number=00003 name=/gm_oradata/testgm/undotbs01.dbf input datafile file number=00004 name=/gm_oradata/testgm/users01.dbf channel ORA_DISK_1: starting piece 1 at 04-MAR-11 channel ORA_DISK_1: finished piece 1 at 04-MAR-11 piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/01m6d3ur_1_1 tag=TAG20110304T175643 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 04-MAR-11 channel ORA_DISK_1: finished piece 1 at 04-MAR-11 piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/02m6d3vk_1_1 tag=TAG20110304T175643 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 04-MAR-11 RMAN> quit Recovery Manager complete. $ |
The RMAN backup files have been saved under the $ORACLE_HOME/dbs directory.
I'll connect to the database, and list the data that is currently in the table. I'll also add a new entry(Rivaldo) to the table
$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Mon Mar 7 11:38:10 2011 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select * from address_book; ID NAME ---------- ------------------------------ 1 Pele 2 Maradona 3 Ronaldo 4 Romario SQL> insert into address_book values ('5','Rivaldo'); 1 row created. SQL> select * from address_book; ID NAME ---------- ------------------------------ 1 Pele 2 Maradona 3 Ronaldo 4 Romario 5 Rivaldo SQL> commit; Commit complete. SQL> quit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production |
Next, to test my backup I'll the datafile as shown below.
$ cd /gm_oradata $ ls lost+found testgm $ cd testgm $ ls control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf control02.ctl redo02.log sysaux01.dbf temp01.dbf users01.dbf $ mv system01.dbf system01.dbf.orig |
I'll re-connect to the database and see if I can add another entry(Figo) to the table.
$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Mon Mar 7 11:43:49 2011 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select * from address_book; ID NAME ---------- ------------------------------ 1 Pele 2 Maradona 3 Ronaldo 4 Romario 5 Rivaldo SQL> insert into address_book values ('6', 'Figo'); 1 row created. SQL> select * from address_book; ID NAME ---------- ------------------------------ 1 Pele 2 Maradona 3 Ronaldo 4 Romario 5 Rivaldo 6 Figo 6 rows selected. SQL> shutdown immediate; ORA-01097: cannot shutdown while in a transaction - commit or rollback first SQL> commit; Commit complete. SQL> shutdown immediate; ORA-01116: error in opening database file 1 ORA-01110: data file 1: '/gm_oradata/testgm/system01.dbf' ORA-27041: unable to open file IBM AIX RISC System/6000 Error: 2: No such file or directory Additional information: 3 Additional information: 2 Additional information: 4194304 SQL> |
As you see above I was first not able to do a shutdown immediate,
Now if you go and try RMAN recovery, you'll see it will fail. Even listing the backup will fail.
$ rman nocatalog target / Recovery Manager: Release 11.2.0.1.0 - Production on Mon Mar 7 11:55:52 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: TESTGM (DBID=2787571766) using target database control file instead of recovery catalog RMAN> list backup; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of list command at 03/07/2011 11:55:58 ORA-01116: error in opening database file 1 ORA-01110: data file 1: '/gm_oradata/testgm/system01.dbf' ORA-27041: unable to open file IBM AIX RISC System/6000 Error: 2: No such file or directory Additional information: 3 Additional information: 2 Additional information: 4194304 RMAN> |
I went back and did a shutdown abort, but even that did not help in listing the backup.
SQL> shutdown abort; ORACLE instance shut down. SQL> quit $ rman nocatalog target / Recovery Manager: Release 11.2.0.1.0 - Production on Mon Mar 7 12:01:18 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database (not started) RMAN> list backup; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of list command at 03/07/2011 12:01:33 RMAN-06403: could not obtain a fully authorized session ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist IBM AIX RISC System/6000 Error: 2: No such file or directory RMAN> |
Next, I mounted my database and then tried to list the RMAN backup. This worked. The moral of the story, to list the backups in RMAN the database should be mounted.
$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Mon Mar 7 12:04:06 2011 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to an idle instance. SQL> startup mount; ORACLE instance started. Total System Global Area 1.0289E+10 bytes Fixed Size 2215712 bytes Variable Size 5268046048 bytes Database Buffers 4999610368 bytes Redo Buffers 18743296 bytes Database mounted. SQL> RMAN> list backup; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 1 Full 995.00M DISK 00:00:15 04-MAR-11 BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20110304T175643 Piece Name: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/01m6d3ur_1_1 List of Datafiles in backup set 1 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 1467357 04-MAR-11 /gm_oradata/testgm/system01.dbf 2 Full 1467357 04-MAR-11 /gm_oradata/testgm/sysaux01.dbf 3 Full 1467357 04-MAR-11 /gm_oradata/testgm/undotbs01.dbf 4 Full 1467357 04-MAR-11 /gm_oradata/testgm/users01.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 2 Full 7.42M DISK 00:00:01 04-MAR-11 BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20110304T175643 Piece Name: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/02m6d3vk_1_1 SPFILE Included: Modification time: 04-MAR-11 SPFILE db_unique_name: TESTGM Control File Included: Ckp SCN: 1467365 Ckp time: 04-MAR-11 RMAN> |
Now that I'm able to list the backups let us try an restore the datafile(system01.dbf) that we had lost.
RMAN> restore datafile '/gm_oradata/testgm/system01.dbf'; Starting restore at 07-MAR-11 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=1 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00001 to /gm_oradata/testgm/system01.dbf channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0/dbhome_1/dbs/01m6d3ur_1_1 channel ORA_DISK_1: piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/01m6d3ur_1_1 tag=TAG20110304T175643 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 Finished restore at 07-MAR-11 RMAN> |
On the host system we see that the file is now back
isvp17> pwd /gm_oradata/testgm isvp17> ls -l system01.dbf -rw-r----- 1 oracle sys 713039872 Mar 07 12:12 system01.dbf isvp17> ls control01.ctl redo02.log system01.dbf undotbs01.dbf control02.ctl redo03.log system01.dbf.orig users01.dbf redo01.log sysaux01.dbf temp01.dbf |
Next, we will recover the file
RMAN> recover datafile '/gm_oradata/testgm/system01.dbf'; Starting recover at 07-MAR-11 using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 25 is already on disk as file /home/test/1_25_743963641.dbf archived log for thread 1 with sequence 26 is already on disk as file /home/test/1_26_743963641.dbf archived log for thread 1 with sequence 27 is already on disk as file /home/test/1_27_743963641.dbf archived log for thread 1 with sequence 28 is already on disk as file /home/test/1_28_743963641.dbf archived log for thread 1 with sequence 29 is already on disk as file /home/test/1_29_743963641.dbf archived log for thread 1 with sequence 30 is already on disk as file /home/test/1_30_743963641.dbf archived log for thread 1 with sequence 31 is already on disk as file /home/test/1_31_743963641.dbf archived log file name=/home/test/1_25_743963641.dbf thread=1 sequence=25 archived log file name=/home/test/1_26_743963641.dbf thread=1 sequence=26 archived log file name=/home/test/1_27_743963641.dbf thread=1 sequence=27 archived log file name=/home/test/1_28_743963641.dbf thread=1 sequence=28 archived log file name=/home/test/1_29_743963641.dbf thread=1 sequence=29 media recovery complete, elapsed time: 00:00:21 Finished recover at 07-MAR-11 RMAN> |
Now let us open the database and see if the datafile was actually recovered. There we go, when we did the RMAN backup Rivaldo and Figo was not in the list. Rivaldo and Figo were added later, but after the recovery process the datafile has Rivaldo and Figo in it.
SQL> alter database open; Database altered. SQL> select * from address_book; ID NAME ---------- ------------------------------ 1 Pele 2 Maradona 3 Ronaldo 4 Romario 5 Rivaldo 6 Figo 6 rows selected. SQL> |
No comments:
Post a Comment