oracle10g Database Recovery (RMAN recovery) tutorial

oracle10g Database Recovery (RMAN recovery) tutorial

I. Automatic management of documents

Ii. use of the Rman function, including all data archive maintenance and reporting capabilities to "invoke the Rman recovery script" by using Oracle Enterprise Manager

Rman recovery: Restore and Recover commands

Restore: Rman looked up the backup set from where we backed up and then put these

The set is moved to the location of the current data file. "Equivalent to copy command in user management"

Recover: Start using an archive log file or online redo log files to restore your changed data back.

First use Rman to do a backup, back to their own directory rback;

Rman> Backup Database format '/u01/rback/rm_%u ' plus archivelog delete input;

Test 1 (Recover a table space through Rman):

"Create some simulated data, and then simulate the destruction of the database"

Sql>conn Scott/tiger

Sql> select * from tab;

Tname Tabtype Clusterid

------------------------------ ------- ----------

E1 TABLE

E22 TABLE

USER1 TABLE

DEPT TABLE

EMP TABLE

BONUS TABLE

Salgrade TABLE

Delete some information

Sql> Delete from E22;

Rows deleted.

Sql> select * from E22;

No rows selected

"Note: There is data in the E22 during the first Rman backup, and the information in the E22 table is deleted after the backup is complete."

"Analog data file Missing"

$CD/U01/ORACLE/ORADATA/ORCL

$RM-RF users01.dbf

"Clean up the memory under the SYSDBA."

Sql> alter system flush Buffer_cache;

Sql> alter system flush Shared_pool;

"Enter Scott to view the E22 table."

Sql> select * from E22;

SELECT * FROM E22

*

ERROR at line 1:

Ora-01116:error in opening database file 4

Ora-01110:data file 4: '/U01/ORACLE/ORADATA/ORCL/USERS01.DBF '

Ora-27041:unable to open File

linux-x86_64 error:2: No such file or directory

Additional Information:3

"Not just E22, no other tables, because the data files are gone."

Execute script

Rman>

run{

SQL "Alter TABLESPACE users offline immediate";

Restore tablespace users;

Recover tablespace users Delete archivelog;

SQL "Alter tablespace users online";

}

SQL Statement:alter tablespace users offline immediate

Starting restore at 17-apr-14

Using channel Ora_disk_1

Using channel Ora_disk_2

Channel ora_disk_1:starting datafile backupset restore

Channel ora_disk_1:specifying DataFile (s) to restore from

Backup set

Restoring DataFile 00004 to

/u01/oracle/oradata/orcl/users01.dbf

Channel ora_disk_1:reading from backup piece

/u01/rback/rm_4op5u3sf_1_1

Channel ora_disk_1:restored backup Piece 1

Piece Handle=/u01/rback/rm_4op5u3sf_1_1

tag=tag20140417t032503

Channel Ora_disk_1:restore complete, elapsed time:00:00:05

Finished restore at 17-apr-14

Starting recover at 17-apr-14

Using channel Ora_disk_1

Using channel Ora_disk_2

Starting Media recovery

Media recovery complete, elapsed time:00:00:06

Finished recover at 17-apr-14

SQL Statement:alter tablespace users online

Recovery completed, found that Scott users still have no data under the E22 table

Sql> select * from E22;

No rows selected

Recovery succeeded.

Test 2 (Recover a data file)

"Insert some data into the E22 table and view the

Sql> Select COUNT (*) from E22;

COUNT (*)

----------

42

"Delete data files again"

$CD/U01/ORACLE/ORADATA/ORCL

$ RM-RF USERS01.DBF

"Clean up the memory under the SYSDBA."

Sql> alter system flush Buffer_cache;

Sql> alter system flush Shared_pool;

"View Data"

Sql> select * from E22;

SELECT * FROM E22

*

ERROR at line 1:

Ora-01116:error in opening database file 4

Ora-01110:data file 4: '/U01/ORACLE/ORADATA/ORCL/USERS01.DBF '

Ora-27041:unable to open File

linux-x86_64 error:2: No such file or directory

Additional Information:3

"Recover Data Again"

run{

SQL "ALTER DATABASE datafile 4 offline";

Restore DataFile 4;

Recover datafile 4 Delete archivelog;

SQL "ALTER DATABASE datafile 4 online";

}

SQL Statement:alter database datafile 4 offline

Starting restore at 17-apr-14

Using channel Ora_disk_1

Using channel Ora_disk_2

Channel ora_disk_1:starting datafile backupset restore

Channel ora_disk_1:specifying DataFile (s) to restore from

Backup set

Restoring DataFile 00004 to

/u01/oracle/oradata/orcl/users01.dbf

Channel ora_disk_1:reading from backup piece

/u01/rback/rm_4op5u3sf_1_1

Channel ora_disk_1:restored backup Piece 1

Piece Handle=/u01/rback/rm_4op5u3sf_1_1

tag=tag20140417t032503

Channel Ora_disk_1:restore complete, elapsed time:00:00:01

Finished restore at 17-apr-14

Starting recover at 17-apr-14

Using channel Ora_disk_1

Using channel Ora_disk_2

Starting Media recovery

Media recovery complete, elapsed time:00:00:04

Finished recover at 17-apr-14

SQL Statement:alter database datafile 4 Online

Rman>

Enter file $CD/U01/ORACLE/ORADATA/ORCL view found USER01.DBF

Once again, under Scott's user, see

Sql> Select COUNT (*) from E22;

COUNT (*)

----------

42

Found the data recovered.

Recovery succeeded.

Of course, if you have off-site backup, we can directly use offsite recovery to help us achieve, and here we have to look at

oracle10g Database Recovery (Offsite recovery)

Loss of all data files, parameter files, control files, password files

Turn on automatic backup of control files and parameter files
Rman>configure Controlfile autobackup on;

Do an Rman full backup
Rman>backup database format '/u01/rback/rm_22 _%u ' plus archivelog delete input;

Toggle log File
Sql>alter system switch logfile;

Close Database
sql> shutdown immediate;

***********
"Simulate missing all Files"
Delete all Files
Cd/u01/oracle/oradata/orcl
$RM-F *

Delete all parameter files
CD $ORACLE _home/dbs
$ rm-f Spfileorcl.ora
$RM-F Pfileorcl.ora
Delete password file
$ rm-f ORAPWORCL
**************

1. Find any parameter file to start the database
$/u01/oracle/admin/orcl/pfile

2. Set the environment variable before booting to mount state
$ Export ORACLE_SID=ORCL

3. Start to Nomount "note start with Init.ora"
sql> startup Nomount pfile= '/u01/oracle/admin/orcl/pfile/init.ora ';

4. Restore the parameter file.
rman> restore SPFile from Autobackup;

Starting restore at 20-mar-14
Using target database control file instead of recovery catalog
Allocated Channel:ora_disk_1
Channel ora_disk_1:sid=156 Devtype=disk
Recovery area Destination:/u01/oracle/flash_recovery_area
Database name (or database unique name) used for SEARCH:ORCL
Channel Ora_disk_1:autobackup found in the recovery area
Channel Ora_disk_1:autobackup found:/u01/oracle/flash_recovery_area/orcl/autobackup/2014_03_20/o1_mf_s_842721192 _9lod9bdd_.bkp
Channel Ora_disk_1:spfile Restore from Autobackup complete
Finished restore at 20-mar-14

5. Restart the database "start the database with a recovered parameter file"
rman> shutdown immediate;

rman> startup Nomount;

6. Recovery control File
rman> restore controlfile from Autobackup;

Starting restore at 20-mar-14
Allocated Channel:ora_disk_1
Channel ora_disk_1:sid=156 Devtype=disk
Recovery area Destination:/u01/oracle/flash_recovery_area
Database name (or database unique name) used for SEARCH:ORCL
Channel Ora_disk_1:autobackup found in the recovery area
Channel Ora_disk_1:autobackup found:/u01/oracle/flash_recovery_area/orcl/autobackup/2014_03_20/o1_mf_s_842721192 _9lod9bdd_.bkp
Channel Ora_disk_1:control file Restore from Autobackup complete
Output Filename=/u01/oracle/oradata/orcl/control01.ctl
Output Filename=/u01/oracle/oradata/orcl/control02.ctl
Output Filename=/u01/oracle/oradata/orcl/control03.ctl
Finished restore at 20-mar-14

7. Boot to mount status
Rman> ALTER DATABASE Mount;

8. Restore the database
rman> Restore Database;

Starting restore at 20-mar-14
Starting implicit crosscheck backup at 20-mar-14
Allocated Channel:ora_disk_1
Channel ora_disk_1:sid=156 Devtype=disk
Crosschecked 3 Objects
Finished implicit crosscheck backup at 20-mar-14
Starting implicit crosscheck copy at 20-mar-14
Using channel Ora_disk_1
Finished implicit crosscheck copy at 20-mar-14
Searching for all files in the recovery area
Cataloging files ...
Cataloging done
List of cataloged Files
=======================
File Name:/U01/ORACLE/FLASH_RECOVERY_AREA/ORCL/AUTOBACKUP/2014_03_20/O1_MF_S_842721192_9LOD9BDD_.BKP
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/u01/oracle/oradata/orcl/system01.dbf
restoring DataFile 00002 to/u01/oracle/oradata/orcl/undotbs01.dbf
restoring DataFile 00003 to/u01/oracle/oradata/orcl/sysaux01.dbf
restoring datafile 00004 to/u01/oracle/oradata/orcl/users01.dbf
Channel ora_disk_1:reading from backup Piece/u01/rmbak/rm_22_03p3lprb_1_1
Channel ora_disk_1:restored backup Piece 1
Piece Handle=/u01/rmbak/rm_22_03p3lprb_1_1 tag=tag20140320t171211
Channel Ora_disk_1:restore complete, elapsed time:00:01:05
Finished restore at 20-mar-14

9. Restoring the Database
rman> Recover database;

Starting recover at 20-mar-14
Using channel Ora_disk_1
Starting Media recovery
Channel ora_disk_1:starting archive log restore to default destination
Channel ora_disk_1:restoring Archive Log
Archive Log thread=1 Sequence=8
Channel ora_disk_1:reading from backup Piece/u01/oracle/flash_recovery_area/orcl/backupset/2014_03_20/o1_mf_annnn_ Tag20140320t171310_9lod97wc_.bkp
Channel ora_disk_1:restored backup Piece 1
Piece HANDLE=/U01/ORACLE/FLASH_RECOVERY_AREA/ORCL/BACKUPSET/2014_03_20/O1_MF_ANNNN_TAG20140320T171310_9LOD97WC_ . BKP tag=tag20140320t171310
Channel Ora_disk_1:restore complete, elapsed time:00:00:01
Archive Log filename=/u01/arch/1_8_842690420.dbf thread=1 sequence=8
Archive Log filename=/u01/arch/1_9_842690420.dbf thread=1 sequence=9
Archive Log filename=/u01/arch/1_9_842690420.dbf thread=1 sequence=10
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR message STACK follows ===============
RMAN-00571: ===========================================================
Rman-03002:failure of recover command at 03/20/2014 17:34:53
Rman-11003:failure during parse/execution of SQL statement:alter database recover logfile '/u01/arch/1_9_842690420.dbf '
Ora-00310:archived log contains sequence 9; Sequence Required
ora-00334:archived log: '/u01/arch/1_9_842690420.dbf '
"This mistake is not to be considered."

10. Open the database in Resetlogs mode
rman> ALTER DATABASE open resetlogs;

11. Add Temporary table space
Sql>alter tablespace temp Add tempfile '/u01/oracle/oradata/orcl/temp01.dbf ' size 10m;

Create a password file
$ orapwd file= $ORACLE _HOME/DBS/ORAPWORCL password=oracle force=y
"Force=y, if you've ever had something, replace it."