RAC Full Recovery Learning

1. View Existing database files

Select  from v$datafile;name--------------------------------------------------------------------------------+data/ Rac/datafile/system. 259.866566403+data/rac/datafile/undotbs1. 260.866566407+data/rac/datafile/sysaux. 261.866566407+DATA/RAC/DATAFILE/UNDOTBS2. 263.866566409+data/rac/datafile/users. 264.866566409

2, make a full backup

 as Copy database;

3. CREATE TABLE Space

Sql>Create tablespace zxm datafile size 2m; Tablespace created. SQL> Create tablespace user01 datafile'+data'size 1M; Tablespace created. SQL>alter tablespace USER01 add datafile size 1m; Tablespace altered. SQL>SelectName fromV$datafile;name--------------------------------------------------------------------------------+data/rac/datafile/system.259.866566403+data/rac/datafile/undotbs1.260.866566407+data/rac/datafile/sysaux.261.866566407+DATA/RAC/DATAFILE/UNDOTBS2.263.866566409+data/rac/datafile/users.264.866566409+data/rac/datafile/zxm.287.866732569+data/rac/datafile/user01.284.866732649+data/rac/datafile/user01.288.8667327338Rows selected.

4. Create Sample Data

 sql> CREATE TABLE test as  select  * from   User_tables; Table created. SQL  > CREATE table test2 as  select  * from   test; Table created. SQL  > >  > select  tablespace_name,segment_name from   user _segments; Tablespace_name segment_name ------------------------------------------------------------user01 TESTUSER01 TEST2  

5. Close database Delete file, simulate disaster scenario

[Email protected] admin]$ srvctl Stop Database-d rac[[email protected] admin]$ export Oracle_sid=+Asm1[[email protected] admin]$ asmcmd-Pasmcmd [+] >Lsdata/RECV/Asmcmd [+] >CD Dataasmcmd [+data] >Lsrac/TEST/Asmcmd [+data] >CD Racasmcmd [+data/rac] >Lsarchivelog/Controlfile/datafile/Onlinelog/Parameterfile/Tempfile/Spfilerac.oraasmcmd [+data/rac] >CD Datafileasmcmd [+data/rac/datafile] >Lssysaux.261.866566407SYSTEM.259.866566403TEST.282.866646695UNDOTBS1.260.866566407UNDOTBS2.263.866566409USER01.284.866732649USER01.288.866732733USERS.264.866566409ZXM.287.866732569Asmcmd [+data/rac/datafile] > RM USER01.284.866732649Asmcmd [+data/rac/datafile] > RM USER01.288.866732733Asmcmd [+data/rac/datafile] > RM zxm.287.866732569[email protected] admin]$ export Oracle_sid=Rac1[[email protected] admin]$ sqlplus/ asSysdbasql*plus:release10.2.0.4.0-Production on Fri Dec +  the: to: A  theCopyright (c)1982, -, Oracle. All rights reserved.connected to an idle instance. SQL>Startup;oracle instance started. Total System Global Area1610612736bytesfixed Size2280840bytesvariable Size416100984Bytesdatabase Buffers1157627904Bytesredo Buffers34603008bytesdatabase mounted. ORA-01157: Cannot identify/LockData file6-See DBWR Trace Fileora-01110: Data File6:'+data/rac/datafile/zxm.287.866732569'

6. The recover command can only be restored on an existing physical file, so the data file needs to be created first

sql> ALTER DATABASE Create datafile6;D atabase altered. SQL> Recover DataFile6; ORA-00283: Recovery session canceled due to Errorsora-01110: Data File6:'+data/rac/datafile/zxm.287.866732569'ORA-01157: Cannot identify/LockData file6-See DBWR Trace Fileora-01110: Data File6:'+data/rac/datafile/zxm.287.866732569'Enter the directory to see the newly created file: Zxm.287.866734565Asmcmd [+data/rac] >CD Datafileasmcmd [+data/rac/datafile] >Lssysaux.261.866566407SYSTEM.259.866566403TEST.282.866646695UNDOTBS1.260.866566407UNDOTBS2.263.866566409USERS.264.866566409ZXM.287.866734565the files in the control are: SQL>SelectName fromV$datafile;name--------------------------------------------------------------------------------+data/rac/datafile/system.259.866566403+data/rac/datafile/undotbs1.260.866566407+data/rac/datafile/sysaux.261.866566407+DATA/RAC/DATAFILE/UNDOTBS2.263.866566409+data/rac/datafile/users.264.866566409+data/rac/datafile/zxm.287.866732569+data/rac/datafile/user01.284.866732649+data/rac/datafile/user01.288.8667327338rows selected. The need to rename a data file is to modify the control file SQL> ALTER DATABASE Rename file'+data/rac/datafile/zxm.287.866732569'To'+data/rac/datafile/zxm.287.866734565';D atabase altered. Operation again, this time the recovery was successful SQL> Recover DataFile6; Media recovery complete.

7. Recover Other data files

Sql>ALTER DATABASE open;alter database open*ERROR at line1: ORA-01157: Cannot identify/LockData file7-See DBWR Trace Fileora-01110: Data File7:'+data/rac/datafile/user01.284.866732649'only one file has been recovered, just a total of 3 deleted, and now re-create the remaining two SQL> ALTER DATABASE Create DataFile'+data/rac/datafile/user01.284.866732649';D atabase altered. SQL> Recover DataFile7; ORA-00283: Recovery session canceled due to Errorsora-01110: Data File7:'+data/rac/datafile/user01.284.866732649'ORA-01157: Cannot identify/LockData file7-See DBWR Trace Fileora-01110: Data File7:'+data/rac/datafile/user01.284.866732649'go to file directory to see that the filename is not created by the Asmcmd [+data/rac/datafile] >Lssysaux.261.866566407SYSTEM.259.866566403TEST.282.866646695UNDOTBS1.260.866566407UNDOTBS2.263.866566409USER01.288.866735415USERS.264.866566409ZXM.287.866734565SQL> ALTER DATABASE Rename file'+data/rac/datafile/user01.284.866732649'To'+data/rac/datafile/user01.288.866735415';D atabase altered. SQL> Recover DataFile7; Media recovery complete. SQL> ALTER DATABASE Create DataFile'+data/rac/datafile/user01.288.866732733'; Asmcmd [+data/rac/datafile] >Lssysaux.261.866566407SYSTEM.259.866566403TEST.282.866646695UNDOTBS1.260.866566407UNDOTBS2.263.866566409USER01.284.866736519USER01.288.866735415USERS.264.866566409ZXM.287.866734565SQL> ALTER DATABASE Rename file'+data/rac/datafile/user01.288.866732733'To'+data/rac/datafile/user01.284.866736519'; SQL> ALTER DATABASE Rename file'+data/rac/datafile/user01.288.866732733'To'+data/rac/datafile/user01.284.866736519';D atabase altered. SQL> Recover DataFile8; Media recovery complete. Open Data SQL>ALTER DATABASE open;database altered.

RAC Full Recovery Learning