Cold Standby Manual Full recovery (recover Database,recover tablespace,recover datafile)

Cold Standby Manual Full recovery


1. Manual full recovery of three levels:
Recover database: All or most of the datafile are lost and are generally completed in the Mount state.
Recover tablespace: Non-critical table space corruption, some data files under the table space can not be accessed, usually in open under the completion.
Recover datafile: Single or few data files are corrupted and can be completed in the mount or open state.
Four key files: 1) system01.dbf, 2) Undo tablespace,3) Control file 4) Current log file




2. Manual Full Recovery premise: 1) A set of DataFile full, 2) Use the current control file, 3) archive logs and current online logs since the last backup are complete




3. Experiment 1: (Recover database)


3.1 Review the current status of the database and prepare the cold standby.
Sql> select * from Andy;


Id
----------
1


--Generate a cold standby script
sql> Select ' Ho cp ' | | name | | '/home/oracle/coldbak ' from V$controlfile;


' HOCP ' | | name| | ' /home/oracle/coldbak '
--------------------------------------------------------------------------------
Ho Cp/home/oracle/app/oradata/orcl/control01.ctl/home/oracle/coldbak
Ho Cp/home/oracle/app/flash_recovery_area/orcl/control02.ctl/home/oracle/coldbak


sql> Select ' Ho cp ' | | name | | '/home/oracle/coldbak ' from V$datafile;


' HOCP ' | | name| | ' /home/oracle/coldbak '
--------------------------------------------------------------------------------
Ho Cp/home/oracle/app/oradata/orcl/system01.dbf/home/oracle/coldbak
Ho Cp/home/oracle/app/oradata/orcl/sysaux01.dbf/home/oracle/coldbak
Ho Cp/home/oracle/app/oradata/orcl/undotbs01.dbf/home/oracle/coldbak
Ho Cp/home/oracle/app/oradata/orcl/users01.dbf/home/oracle/coldbak
Ho Cp/home/oracle/app/oradata/orcl/tbtb01.dbf/home/oracle/coldbak
Ho Cp/home/oracle/app/oradata/orcl/ogg01.dbf/home/oracle/coldbak


6 rows selected.


sql> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


--Cold standby
Sql> Ho Cp/home/oracle/app/oradata/orcl/control01.ctl/home/oracle/coldbak
Sql> Ho Cp/home/oracle/app/flash_recovery_area/orcl/control02.ctl/home/oracle/coldbak
Sql> Ho Cp/home/oracle/app/oradata/orcl/system01.dbf/home/oracle/coldbak
Ho Cp/home/oracle/app/oradata/orcl/sysaux01.dbf/home/oracle/coldbak
Ho Cp/home/oracle/app/oradata/orcl/undotbs01.dbf/home/oracle/coldbak
Ho Cp/home/oracle/app/oradata/orcl/users01.dbf/home/oracle/coldbak
Ho Cp/home/oracle/app/oradata/orcl/tbtb01.dbf/home/oracle/coldbak
Ho Cp/home/oracle/app/oradata/orcl/ogg01.dbf/home/oracle/coldbak


--Check cold standby
[email protected] coldbak]$ LL
Total 1997776
-rw-r-----. 1 Oracle oinstall 9748480 DEC 06:22 control01.ctl
-rw-r-----. 1 Oracle oinstall 9748480 DEC 06:25 control02.ctl
-rw-r-----. 1 Oracle oinstall 408748032 DEC 06:32 ogg01.dbf
-rw-r-----. 1 Oracle oinstall 639639552 DEC 06:31 sysaux01.dbf
-rw-r-----. 1 Oracle oinstall 734011392 DEC 06:31 system01.dbf
-rw-r-----. 1 Oracle oinstall 104865792 DEC 06:32 tbtb01.dbf
-rw-r-----. 1 Oracle oinstall 99622912 DEC 06:32 undotbs01.dbf
-rw-r-----. 1 Oracle oinstall 39329792 DEC 06:32 users01.dbf


Sql> startup;
ORACLE instance started.


Sql> insert INTO Andy values (2);


1 row created.


Sql> commit;


Commit complete.


Sql> select * from Andy;


Id
----------
1
2


3.2 Failed to simulate media, all data files lost


[[email protected] orcl]$ RM-RF *.dbf//database is deleted when open


sql> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Sql> startup;
ORACLE instance started.


Total System Global area 1068937216 bytes
Fixed Size 2220200 bytes
Variable Size 729812824 bytes
Database buffers 331350016 bytes
Redo buffers 5554176 bytes
Database mounted.
Ora-01157:cannot identify/lock data file 1-see DBWR trace file
Ora-01110:data file 1: '/HOME/ORACLE/APP/ORADATA/ORCL/SYSTEM01.DBF '


3.3 Full recovery process


--View files that need to be recovered
Sql> select File#,error from V$recover_file;


file# ERROR
---------- -----------------------------------------------------------------
1 FILE not FOUND
2 FILE not FOUND
3 FILE not FOUND
4 FILE not FOUND
5 FILE not FOUND
6 FILE not FOUND


6 rows selected.


Sql> select file#,checkpoint_change# from V$datafile;


file# checkpoint_change#
---------- ------------------
1 1969481
2 1969481
3 1969481
4 1969481
5 1969481
6 1969481


6 rows selected.


Sql> select file#,checkpoint_change# from V$datafile_header;


file# checkpoint_change#
---------- ------------------
1 0
2 0
3 0
4 0
5 0
6 0


6 rows selected.






A first restore all data files
[Email protected] orcl]$ cp/home/oracle/coldbak/*.dbf/home/oracle/app/oradata/orcl/


b Restore Database
sql> Recover database;
Media recovery complete.


C Open the database
sql> ALTER DATABASE open;


Database altered.


D Validation
Sql> select * from Andy;


Id
----------
1
2




Experimental 2:recover tablespace (state: Database Open)


Description: Corruption of non-critical tablespace, based on full recovery of tablespace, is actually the recovery of datafile under the table space


Simulation of this situation is very practical, usually a non-critical table space under the data file damage, but did not cause the Oracle crash, we only need to do a separate online recovery operation for the individual problematic tablespace, that is, the database as a whole when the recovery is online, While the local table space is offline, the database does not need to be shutdown.


1) To understand the current state, there is a LZY table space
Sql> Col file_name for A60
Sql> select File_id,file_name,tablespace_name from Dba_data_files;


file_id file_name Tablespace_name
---------- ------------------------------------------------------------ -------------------------
4/HOME/ORACLE/APP/ORADATA/ORCL/USERS01.DBF USERS
3/HOME/ORACLE/APP/ORADATA/ORCL/UNDOTBS01.DBF UNDOTBS1
2/HOME/ORACLE/APP/ORADATA/ORCL/SYSAUX01.DBF Sysaux
1/HOME/ORACLE/APP/ORADATA/ORCL/SYSTEM01.DBF SYSTEM
5/HOME/ORACLE/APP/ORADATA/ORCL/TBTB01.DBF LZY
6/HOME/ORACLE/APP/ORADATA/ORCL/OGG01.DBF goldgate


6 rows selected.


2) Prepare experimental data


Sql> CREATE TABLE Andy.andydemo (id int) tablespace lzy;


Table created.


Sql> inset into Andy.andydemo values (1);
Sp2-0734:unknown command beginning "inset into ..."-rest of line ignored.
sql> INSERT INTO Andy.andydemo values (1);


1 row created.


Sql> commit;


Commit complete.


Sql> select * from Andy.andydemo;


Id
----------
1
3) Simulation table space Corruption, database open, directly delete the data file under the table space


Sql> Ho rm-rf/home/oracle/app/oradata/orcl/tbtb01.dbf


4) Verify that the table on the table space is inaccessible.


Sql> alter system flush Buffer_cache; --Clear Data buffer


System altered.


Sql> select * from Andydemo;
SELECT * FROM Andydemo
*
ERROR at line 1:
Ora-01116:error in opening database file 5
Ora-01110:data file 5: '/HOME/ORACLE/APP/ORADATA/ORCL/TBTB01.DBF '
Ora-27041:unable to open File
linux-x86_64 error:2: No such file or directory
Additional Information:3


5) Look at the SCN situation
Sql> select file#,checkpoint_change# from V$datafile;


file# checkpoint_change#
---------- ------------------
1 1969484
2 1969484
3 1969484
4 1969484
5 1969484
6 1969484


6 rows selected.


Sql> select file#,checkpoint_change# from V$datafile_header;


file# checkpoint_change#
---------- ------------------
1 1969484
2 1969484
3 1969484
4 1969484
5 0
6 1969484


6 rows selected.


Description
V$datafile is to view the SCN information from the data file recorded in the control file.
V$datafile_header is to view data file SCN information in the data file header.


6) tablespace offline--immediate enables tablespaces to be taken offline immediately, without any data files being checked by Oracle


sql> alter tablespace Lzy offline immediate;


Tablespace altered.


7) Under Database Open, use Backup to restore all data files under this table space.


Sql> Ho cp/home/oracle/coldbak/tbtb01.dbf/home/oracle/app/oradata/orcl/


8) Restore Tablespace


sql> recover tablespace Lzy;
Media recovery complete.


9) Make Table space online


sql> alter tablespace Lzy online;


Tablespace altered. Note: The database state is always open at this time.


10) Inspection


Sql> select * from Andydemo;


Id
----------
1








Lab 3: (Recover datafile,database mount or open state)


Recovery datafile, unlike experiment 2, is the simulation undo file corruption: Because the undo data file is also a critical file, it can only be restored in the Mount state.


1) Simulation Environment:




sql> Delete Andy.andy; Note: Delete Andy and submit it, the old value is in Undo.
3 rows deleted.
sql>commit;


2) Delete datafile in open state
Sql> Ho rm-rf/home/oracle/app/oradata/orcl/undotbs01.dbf


sql> INSERT INTO Andy.andy values (4); /undo file is no longer in, DBWR not time to refresh, this record in redo
sql>commit;

3) Close the database
sql> shutdown abort;
ORACLE instance shut down. Abort buries the foreshadowing and, when fully restored, does undo rollback.


4) Start Database mount
Sql> startup;
ORACLE instance started.


Total System Global area 1068937216 bytes
Fixed Size 2220200 bytes
Variable Size 729812824 bytes
Database buffers 331350016 bytes
Redo buffers 5554176 bytes
Database mounted.
Ora-01157:cannot identify/lock data file 3-see DBWR trace file
Ora-01110:data file 3: '/HOME/ORACLE/APP/ORADATA/ORCL/UNDOTBS01.DBF '


5) Restore and restore the Undo data file


Sql> Ho cp/home/oracle/coldbak/undotbs01.dbf/home/oracle/app/oradata/orcl/undotbs01.dbf


Sql> Recover datafile 3
Media recovery complete.


6) Open Database (rollback of undo tablespace data will be completed)


sql> ALTER DATABASE open;


Database altered.


7) Verification
Sql> select * from Andy;


Id
----------
4


Summary: There are three levels of recovery process under all cold standby conditions. (the corresponding file name depends on your environment)


Recover database:


Sql> select File#,error from V$recover_file;
Sql> select file#,checkpoint_change# from V$datafile;
Sql> select file#,checkpoint_change# from V$datafile_header;
Sql> Ho cp/home/oracle/coldbak/*.dbf/home/oracle/app/oradata/orcl/
sql> Recover database;
sql> ALTER DATABASE open;


Recover tablespace:


Sql> select File#,error from V$recover_file;
Sql> select File_id,file_name,tablespace_name from Dba_data_files;
Sql> select file#,checkpoint_change# from V$datafile;
Sql> select file#,checkpoint_change# from V$datafile_header;
sql> alter tablespace Lzy offline immediate;
Sql> Ho cp/home/oracle/coldbak/tbtb01.dbf/home/oracle/app/oradata/orcl/
sql> recover tablespace Lzy;
sql> alter tablespace Lzy online;


Recover datafile:


Sql> select File#,error from V$recover_file;
Sql> select file#,checkpoint_change# from V$datafile;
Sql> select file#,checkpoint_change# from V$datafile_header;
Sql> Ho cp/home/oracle/coldbak/undotbs01.dbf/home/oracle/app/oradata/orcl/undotbs01.dbf
Sql> Recover datafile 3
sql> ALTER DATABASE open;




OK, reprint please indicate the source.

Cold Standby Manual Full recovery (recover Database,recover tablespace,recover datafile)