Shulou(Shulou.com)05/31 Report--

This article will explain in detail what to do about the loss of sysaux table space in rman backup. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Today, I had a whim and wanted to play rman this command. I haven't backed up my virtual machine for a long time, so I made a full report, which sure enough gave me an error report.

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 Greater 2016 05:19:19RMAN-06056: could not access datafile 2

Then go to the database and start querying why it can't be backed 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 that these two commands could not tell me which tablespace was lost, and then looked up the information on the Internet.

SQL > select a.filewriting recorder a.namememe b.name from v$datafile a memoir vandalism tablespace b where a.ts#=b.ts# 2 FILE# NAME NAME- 1 / u01/oracle/product/oradata/wr SYSTEM c/system01.dbf 2 / u01 u01/oracle/product/oradata/wr UNDOTBS1 c/undotbs01.dbf SYSAUX 1/dbs/MISSING00002 _ u01/oracle/product/oradata/wr UNDOTBS1 c/undotbs01.dbf 3 / Oracle 4 / u01/oracle/product/oradata/wr USERS c/users01.dbf FILE# NAME NAME-- -5 / u01 RMAN 1/dbs/MISSING000066 rows selected oracle _ EXAMPLE 1/dbs/MISSING00005 6 / u01 / oracle _ RMAN 1/dbs/MISSING000066 rows selected.

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

Rman is the best table space to delete, but don't forget to delete it when you delete it.

Including contents and datafiles

Then later began to delete the example tablespace, this shows that there are index and relationships, so it cannot be deleted. At that time, it is possible to delete all objects and then delete the tablespace. Finally, to the sysaux tablespace, although the sysaux tablespace is the system tablespace, the auxiliary tablespace of system tablespace, such as storing some report information, basically will not have any image on the performance.

The first way

SQL > drop tablespace sysaux including contents and datafiles;drop tablespace sysaux including contents and datafiles*ERROR at line 1:ORA-13501: Cannot drop SYSAUX tablespace

The second way

SQL > alter database datafile 2 offline drop 2; Database altered.SQL > select a filebook. Name from v$datafile a 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--USERS 5/u01/oracle/product/oradata/orcl/example01.dbfEXAMPLE

You can see that it is obviously not deleted, at this time began to think about whether the sysaux is written to the control file, so no matter how you delete it, you can't delete it, and then you start to initialize the control file to see if it 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 and recreate the control file by looking at the 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 Universe oracle.SIZE 50m BLOCKSIZE 512, GROUP 2'/ u01Grexoracleqqt MAXINSTANCES BLOCKSIZE 512, GROUP 3'/ u01 Universe oracleproduct50m BLOCKSIZE 512, GROUP 3'/ u01apace oracleproductoradataRed03.log' SIZE 50m oradata512 GROUP 4'/ u01Uniplex SIZE SIZE 50m BLOCKSIZE 512rel-STANDBY LOGFILEDATAFILE'/ u01UniUniverse oracleUniverse productSystem01.dbfills,'/ u01UniplicoracleUniproductUniverse 11.2.0 UniDUBSU01UniUniplex dbSysaux02.dbfills,'/ u01UniUniplex oradatawrcundotbs01.dbfills,'/ u001prime oradatawrcundotbs01.dbfills,'/ u001 oracleend productoradatawrcwards '/ u01Universe oracleActureproductUniverse 11.2.0There is a u01/oracle/product/11.2.0/db_1/dbs/sysaux04.dbf'CHARACTER SET ZHS16GBK for dbAction1Uniplex, sysaux03.dbfags,' /

Enter sqlplus to run this script, then start to restore the database, and suddenly find 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, which is used to tell oracle not to use scn in controlfile as the end point for recovery.

2. Recover database until cancel

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

3. Recover database using backup controlfile until cancel

If the current controlfile is lost and the current/active redo is lost, the archive log will be applied automatically to achieve maximum recovery.

4. Recover database until cancel using backup controlfile

The results are as follows:

If the control file is lost, after the control file backed up by restore, you must use the using backup controlfile option. On the other hand, until cancel is not fully recovered, that is, if the current/active redo is lost, or an archive file is missing from the restore database, it is terminated.

Conclusion:

1. It is applicable to the old control files of restore, and there is no loss of archived logs and cuurrent/active redo. If all archived logs and online logs are intact, data can not be lost. Similar to recover database

2. The current control file is not lost (the old control file of restore is not required). If the archive log or current/active log is lost, it will be terminated. Maximum possible recovery of data

3, 4: the effect of my test in oracle 10.2.0.4 environment is the same, that is, it is applicable to the old control file of restore. After restoring to the backup of the control file, the system will prompt you to apply the archive log after the backup of the control file. If not, stop. It is also the most possible way to recover data.

SQL > alter database archivelog;Database altered.SQL > recover database using backup controlfile until cancel;ORA-00279: change 1995899 generated at 10 take 20 for thread 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: {= 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 recover database using backup controlfile until cancel.-rest of line ignored.SQL > Oracle ORA-00279: change 1995899 generated at 10 47Specify log 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: {= suggested | filename | AUTO | CANCEL} / u01/oracle/product/oradata/wrc/redo03.logLog applied.Media recovery complete.

It was found that sequence#47 could not be found in control file, so it could only be tried in the location of one redo log, and finally succeeded.

SQL > alter database open;alter database open*ERROR at line 1:ORA-01589: must use RESETLOGS or NORESETLOGS option for database openSQL > alter database open resetlogs;Database altered.

Finally, to open the database, you need to make whether it is in resetlogs or noresetlogs mode, but it is found that it still does not work, and sysaux still exists, indicating that the loss of sysaux cannot be saved and the database needs to be re-created.

About rman backup sysaux table space loss how to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.