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)