One thing that happens with Rman backup (sysaux table space is missing)

Today's whim, want to play Rman this command, their own virtual machine for a long time have not backed up, so to a full prepared, sure enough gave me an error.

Rman> Backup starting backup at 20-oct-16allocated channel:ora_disk_1channel ora_disk_1:sid=134 device Type=DISKRMAN -06169:could not read file header for DataFile 2 error reason 1rman-00571: ============================================== =============rman-00569: =============== ERROR MESSAGE STACK follows ===============rman-00571: ===================== ======================================rman-03002:failure of Backup command at 10/20/2016 05:19:19rman-06056:could not Access DataFile 2

Then go into the database and start querying why not back up

sql> select file#,name from v$datafile;     file#---------- NAME--------------------------------------------------------------------------------          1/u01/oracle/product/oradata/wrc/system01.dbf          2/u01/oracle/product/11.2.0/db_1/dbs/MISSING00002          3/u01/oracle/product/oradata/wrc/undotbs01.dbf     file#---------- NAME--------------------------------------------------------------------------------          4/u01/oracle/product/oradata/wrc/users01.dbf          5/u01/oracle/product/11.2.0/db_1/dbs/MISSING00005          6/u01/oracle/product/11.2.0/db_1/dbs/missing000066 rows selected. sql> select ts#,name from  V$tablespace;       ts# name---------- ------------------------- -----         0 SYSTEM          2 UNDOTBS1         4 USERS          1 SYSAUX          3 TEMP         6 EXAMPLE          7 rman

Found these two commands can not tell me in the end which table space is missing, and later on the Internet to check information

Sql>  select a.file#,a.name,b.name from v$datafile a,v$tablespace b  where a.ts#=b.ts#  2  ;     FILE# NAME                             name---------- ------------------------------ ------ ------------------------         1 /u01/oracle/product/ oradata/wr system           c/system01.dbf          2 /u01/oracle/product/11.2.0/db_ SYSAUX            1/dbs/MISSING00002          3 /u01/oracle/product/oradata/wr UNDOTBS1            c/undotBs01.dbf         4 /u01/oracle/product/oradata/wr users            c/users01.dbf      file# name                            name---------- ------------------ ------------ ------------------------------         5 / u01/oracle/product/11.2.0/db_ example           1/ Dbs/missing00005         6 /u01/oracle/product/11.2.0/db_  rman           1/dbs/missing000066 rows  selected.

A query lost three files, and then began to find a way to solve

Rman This table space is best deleted, but do not forget to delete the time to

Including contents and datafiles

And then began to delete example this table space, this display has index and relationship so can not be deleted, then found that there is indeed an object, delete all objects and then delete the table space should also be possible, and finally to the Sysaux table space, sysaux This table space is the system table space , the secondary table space for the system table space, such as storing some report information, does not have a basic picture of performance.

The first way

Sql> drop tablespace sysaux including contents and Datafiles;drop tablespace sysaux including contents and Datafiles*er ROR at line 1:ora-13501:cannot drop Sysaux tablespace

The second way

sql> alter database datafile 2 offline drop  2  ;D Atabase  altered. Sql> select a.file#,a.name,b.name from v$datafile a,v$tablespace b where  a.ts#=b.ts#;     file#---------- Name--------------------------------------------------------------------------------name------------------------------          1/u01/oracle/product/oradata/orcl/system01.dbfsystem          2/u01/oracle/product/oradata/orcl/sysaux01.dbfsysaux      file#---------- Name--------------------------------------------------------------------------------name------------------------------          3/u01/oracle/product/oradata/orcl/undotbs01.dbfundotbs1          4/u01/oracle/product/oradata/orcl/users01.dbf     file#---------- Name--------------------------------------------------------------------------------name------------------------------use Rs         5/u01/oracle/product/oradata/orcl/example01.dbfexample

It can be seen that the obvious is not deleted, this time began to think that the Sysaux is not written to control files, so you can delete no matter how you delete, and then began to think through the initialization of the control file, see if you can succeed.

Create a script first

select    d.value       | |   '/'        | |  LOWER  (rtrim  (i.instance, chr  (0))        | |   ' _ora_ '        | |  p.spid       | |   ' TRC '  trace_file_name  FROM  (select p.spid           FROM v$mystat m, v$session s, v$process p          where m.statistic# = 1 and s.sid =  M.SID AND P.ADDR = S.PADDR)  p,        (SELECT  t.instance          from v$thread t, v$ parameter v         where v.name =  ' threAd '            AND  (v.value = 0 or  t.thread# = TO_NUMBER  (V.value))  i,        ( select value          from v$parameter          WHERE NAME =  ' user_dump_dest ')  d/

Execute the script under Linux

Sql> @gettrcnameTRACE_FILE_ NAME---------------------------------------------------------------------------------------------------/opt/ Oracle/admin/eygle/udump/eygle_ora_8415.trcalter database backup controlfile to trace;

Edit to recreate the control file by looking at this eygle_ora_8415.trc file

startup nomountcreate controlfile reuse database  "ORCL"  RESETLOGS   noarchivelog    maxlogfiles 5    maxlogmembers 3     MAXDATAFILES 100    MAXINSTANCES 1     maxloghistory 1168logfile  group 1  '/u01/oracle/product/oradata/wrc/redo01.log '    SIZE 50M BLOCKSIZE 512,  GROUP 2  '/u01/oracle/product/oradata/wrc/ Redo02.log '   SIZE 50M BLOCKSIZE 512,  GROUP 3  '/u01/oracle/ Product/oradata/wrc/redo03.log '   size 50m blocksize 512,  group 4   '/u01/oracle/product/oradata/wrc/redo04.rdo '   SIZE 50M BLOCKSIZE 512--  standby logfiledatafile   '/u01/oracle/product/oradata/wrc/system01.dbf ',   '/u01/ Oracle/product/11.2.0/db_1/dbs/sysaux02.dbf ',   '/u01/oracle/product/oradata/wrc/undotbs01.dbf ',   '/u01/oracle/product/oradata/wrc/ Users01.dbf ',   '/u01/oracle/product/11.2.0/db_1/dbs/sysaux03.dbf ',   '/u01/oracle/product/ 11.2.0/DB_1/DBS/SYSAUX04.DBF ' character set zhs16gbk;

Enter Sqlplus run this script, and then start the database recovery, suddenly found another error

Sql> RECOVER databaseora-00283:recovery session canceled due to errorsora-01610:recovery using the BACKUP controlfile option must be donesql> ALTER DATABASE archivelog;database altered.

1. Recover database using Backup Controlfile

If you lose the current control file, use the cold backup control file to restore, to tell Oracle, do not use the SCN in the Controlfile as the end point of recovery;

2. Recover database until Cancel

If you lose current/active redo, manually specify the end point.

3. Recover database using Backup controlfile until cancel;

If the current controlfile is lost and current/active redo is lost, the archive log is automatically applied and the maximum recovery can be achieved.

4. Recover database until cancel using Backup controlfile;

The results are as follows:

If the control file is missing, restore backs up the control files after you must use the Using Backup Controlfile option. Until cancel, however, is not fully recovered, that is, the current/active redo is lost, or an archive file is missing from the restore database and terminates.

Conclusion:

1, for the restore old control files, and the archive log and cuurrent/active redo are not lost. If all archived logs and online logs are intact, you can not lose data. Similar to recover database

2, the current control file is not lost (do not need to restore the old control file), at this time there is an archive log or current/active log has been lost, then terminated. Maximum possible recovery data

3, 4: I am in the Oracle 10.2.0.4 Environment test effect is the same, that is applicable to the restore old control files, after the restoration to the control file backup that moment, the system will be prompted to apply control file backup after the archive log, if not stopped. is also the largest possible recovery data.

Sql> alter database archivelog;database altered. Sql>  recover database using backup controlfile until cancel;o Ra-00279: change 1995899 generated at 10/20/2016 07:46:55 needed for  thread 1ora-00289: suggestion :/u01/oracle/product/flash_recovery_area/orcl/archivelog/ 2016_10_20/o1_mf_1_47_%u_.arcora-00280: change 1995899 for thread 1 is in  sequence  #47Specify  log: {<ret>=suggested | filename | auto  | cancel}/u01/oracle/product/oradata/wrc/redo04.rdoora-00310: archived log contains  sequence 45; sequence 47 requiredORA-00334: archived log:  '/u01/ Oracle/product/oradata/wrc/redo04.rdo ' sql> /u01/oracle/product/oradata/wrc/redo04.rdosp2-0734:  unknown command beginning  "/u01/oracl ..."  - resT of line ignored. Sql>  recover database using backup controlfile until cancel;o Ra-00279: change 1995899 generated at 10/20/2016 07:46:55 needed for  thread 1ora-00289: suggestion :/u01/oracle/product/flash_recovery_area/orcl/archivelog/ 2016_10_20/o1_mf_1_47_%u_.arcora-00280: change 1995899 for thread 1 is in  sequence  #47Specify  log: {<ret>=suggested | filename | auto  | cancel}/u01/oracle/product/oradata/wrc/redo03.loglog applied. Media recovery complete.

Found control file inside can't find sequence#47, and then only a redo log location to try, and finally succeeded

sql> ALTER DATABASE Open;alter database Open*error at line 1:ora-01589:must use Resetlogs or noresetlogs option for DA Tabase opensql> ALTER DATABASE open Resetlogs;database altered.

Finally open the database, need to establish is resetlogs or noresetlogs mode, but found or not, Sysaux still exist, that Sysaux lost is not saved, need to recreate the library.

This article from the "12111303" blog, reproduced please contact the author!

One thing that happens with Rman backup (sysaux table space is missing)