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

Source: Internet
Author: User

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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.