Restore A non-System File deleted by Oracle in archive Mode

Source: Internet
Author: User

Restore A non-System File deleted by Oracle in archive Mode
As we all know, our core production databases usually run in archive mode, not to mention configuring the DG Environment. Enabling archiving and ensuring that all archives are not lost can ensure that any modifications made to the database will not be lost. Archive logs are the root of recovery. If archive is lost, even if RMAN is powerful, it cannot recover the lost data. Therefore, the RMAN policy we usually configure is full backup + archive + automatic backup of control files. The archive here is not the archive generated since the database was created (that volume is too large), but the archive log generated when the RMAN non-consistent backup is performed, it is used to ensure that the database can be pushed forward to a consistent state so that the database can be opened smoothly. The following test only describes the importance of archiving logs for data recovery and does not use RMAN for data recovery.

-------------------------------------- Split line --------------------------------------

Oracle 11g rac modify archive Mode

Oracle manual full recovery case (archive Mode)

Oracle manual recovery case (non-archive Mode)

Instructions for setting Oracle archive Mode

Backup script in Oracle 10g archive Mode

Oracle archive mode and non-archive mode switch

-------------------------------------- Split line --------------------------------------

Test 1: Archive logs are not lost-connect to Oracle to ensure that they are running in archive mode [oracle @ ora10g ~] $ Sqlplus/as sysdba
SQL * Plus: Release 10.2.0.1.0-Production on 8 13:46:53 2014 Copyright (c) 1982,200 5, Oracle. all rights reserved. connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-ProductionWith the Partitioning, OLAP and Data Mining options SQL> archive log listDatabase log mode Archive Mode -- archive mode: Automatic archival EnabledArchive destination offline online log sequence 172 Next log sequence to archive 174 Current log sequence 174SQL> set lin 130 pages 130SQL> col name for a45SQL> select file #, name from v $ datafile; FILE # NAME ---------- upload 1/u01/app/oracle/oradata/ora10g/system01.dbf 2/u01/app/oracle/oradata/ora10g/undotbs01.dbf 3/u01/app/oracle/oradata /ora10g/g04/u01/app/oracle/oradata/ora10g/users01.dbf 5/u01/app/oracle/oradata/ora10g/example01.dbf -- create test table space, user, and table SQL> create tablespace zlm_test datafile '/u01/app/oracle/oradata/ora10g/zlm01.dbf' size 50 m; tablespace created. SQL> create user zlm identified by zlm default tablespace zlm_test; User created. SQL> grant connect, resource to zlm; -- Grant permissions to grant succeeded. SQL> select file #, name from v $ datafile; FILE # NAME ---------- upload 1/u01/app/oracle/oradata/ora10g/system01.dbf 2/u01/app/oracle/oradata/ora10g/undotbs01.dbf 3/u01/app/oracle/oradata /ora10g/g04/u01/app/oracle/oradata/ora10g/users01.dbf 5/u01/app/oracle/oradata/ora10g/example01.dbf 6/u01/app/oracle/oradata/ ora10g/zlm01.dbf -- added file 6 as the physical medium for test table Storage 6 rows selected. SQL> create table zlm. test1 as select rownum as id, object_name from dba_objects where rownum <= 5; Table created. SQL> col object_name for a15
SQL> select * from zlm. test1; ID OBJECT_NAME ---------- --------------- 1 ICOL $2 I _USER1 3 CON $4 UNDO $5 C_COBJ # -- view the status of the current online Log File SQL> select group #, status, archived from v $ log; GROUP # status arc ---------- -------------- --- 1 inactive yes 2 inactive yes 3 current no -- CURRENT log GROUP is 3, not archived -- Archive CURRENT log (multiple times) SQL> alter system archive log current; System altered. SQL> alter system archive log current; System altered. SQL> alter system archive log current; System altered. here we have archived the current log file three times to force the online log to be archived and written to the archive log, because we want to test archiving, otherwise, files are automatically searched in the online log when they are restored. Even in non-archive mode, the online log can still be recovered as long as the online log has not been refreshed.
SQL> select group #, status, archived from v $ log; GROUP # STATUS ARC ---------- -------------- --- 1 INACTIVE YES 2 INACTIVE YES 3 CURRENT NO -- although it looks consistent with the previous step, however, at this time, 3rd online logs have been refreshed. For the sake of insurance, archive the SQL statement again (optional)> alter system archive log current; System altered. SQL> select group #, status, archived from v $ log; GROUP # status arc ---------- ---------------- --- 1 current no 2 inactive yes 3 active yes -- now the 3rd GROUP of logs in a new round have been archived-Database Consistency is disabled, delete the test file datafile 6SQL> shutdown immediate at the OS level
Database closed. Database dismounted. ORACLE instance shut down. SQL>! [Oracle @ ora10g ~] $ Cd $ ORACLE_BASE/oradata/ora10g [oracle @ ora10g ora10g] $ ll-lrthtotal 1.7G-rw-r ----- 1 oracle oinstall 51 M Sep 5 10:13 test02.dbf-rw-r ----- 1 oracle oinstall 301 M Sep 5 test01.dbf-rw-r ----- 1 oracle oinstall 201 M Sep 16 temp01.dbf-rw-r ----- 1 oracle oinstall 51 M Sep 18 49 redo02.log-rw-r ----- 1 oracle oinstall 51 M Sep 18 13:51 redo03.log-rw-r ----- 1 oracle oinstall 51 M Sep 18 13:51 zlm01.dbf-rw-r ----- 1 oracle oinstall 31 M Sep 18 13:51 users01.dbf-rw- r ----- 1 oracle oinstall 166 M Sep 18 undotbs01.dbf-rw-r ----- 1 oracle oinstall 561 M Sep 18 system01.dbf-rw-r ----- 1 oracle oinstall 271 M Sep 18 51 sysaux01.dbf- rw-r ----- 1 oracle oinstall 51 M Sep 18 redo01.log-rw-r ----- 1 oracle oinstall 101 M Sep 18 51 example01.dbf-rw-r ----- 1 oracle oinstall 7.2 M Sep 18 control03.ctl-rw-r ----- 1 oracle oinstall 7.2 M Sep 18 control02.ctl-rw-r ----- 1 oracle oinstall 7.2 M Sep 18 control01.ctl [oracle @ ora10g ora10g] $ rm-f zlm01.dbf [oracle @ ora10g ora10g] $ exitexit -- restart Database SQL> startupORACLE instance started. total System Global Area 285212672 bytesFixed Size 1218992 bytesVariable Size 88082000 bytesDatabase Buffers 192937984 bytesRedo Buffers 2973696 bytesDatabase mounted. ORA-01157: cannot identify/lock data file 6-see DBWR trace fileORA-01110: data file 6: '/u01/app/oracle/oradata/ora10g/zlm01.dbf' can be seen, in this case, the database cannot be opened because the database file does not exist physically, but there are records in the control file. The prompt is "cannot identify/lock data file 6 ", if the data file header information is not consistent with the data file header information recorded in the control file, you will be prompted to restore the xxx file -- manually create a datafile 6SQL> alter database create datafile 6; Database altered. note: At this time, only an inconsistent datafile 6 is created, which can also be achieved through the RMAN restore datafile 6; command. The function is the same

 

For more details, please continue to read the highlights on the next page:

  • 1
  • 2
  • 3
  • Next Page

Related Article

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.