[oracle][rman]recover database to a point before resetlogs(previous incarnation)

CONNECT TARGET / NOCATALOG

# step 1: start and mount a control file that knows about the incarnation to which
# you want to return. if the current control file does not know about it, then
# you must restore an older control file
STARTUP NOMOUNT;
RESTORE CONTROLFILE UNTIL TIME 'SYSDATE-250';
ALTER DATABASE MOUNT;

# step 2: obtain the primary key of old incarnation
LIST INCARNATION OF DATABASE trgt;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS   Reset SCN  Reset Time
------- ------- -------- -------------    -------  ---------- ----------
1       2       TRGT     1334358386       PARENT   154381     OCT 30 2001 16:02:12
1       116     TRGT     1334358386       CURRENT  154877     OCT 30 2001 16:37:39

# step 3: in this example, reset database to incarnation key 2
RESET DATABASE TO INCARNATION 2;

# step 4: restore and recover the database to a point before the RESETLOGS
RESTORE DATABASE UNTIL SCN 154876;
RECOVER DATABASE UNTIL SCN 154876;

# step 5: make this incarnation the current incarnation and then list incarnations:
ALTER DATABASE OPEN RESETLOGS;
LIST INCARNATION OF DATABASE trgt;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- ------- ---------- ----------
1       2       TRGT     1334358386       PARENT  154381     OCT 30 2001 16:02:12
1       116     TRGT     1334358386       PARENT  154877     OCT 30 2001 16:37:39
1       311     TRGT     1334358386       CURRENT 154877     AUG 13 2002 17:17:03

% rman target / catalog rman/rman@catdb

Recovery Manager: Release 10.1.0.2.0 - Production

Copyright (c) 1995, 2003, Oracle.  All rights reserved.

connected to target database: TRGT (DBID=1334531173)
connected to recovery catalog database

RMAN> RESYNC CATALOG;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of resync command on default channel at 11/01/2001 12:00:43
RMAN-20003: target database incarnation not found in recovery catalog

RMAN> RESET DATABASE;

oracle rman recover under nocatalog mode and without controlfile (DBMS_BACKUP_RESTORE)

dbmsbkrs.sql - dbms backup restore
prvtbkrs.plb - prvt backup restore

deviceAllocate (type, name, ident, noio, params) return varchar2
restoreControlfileTo
restoreDataFileTo

# backup databases
rman target / nocatalog << __EOF__
run {
 allocate channel C1 type disk;
 backup full tag 'FullBackup' format 'd:KDE%d_%u_%s_%p.dbf' database include current controlfile;
 sql 'alter system archive log current';
 release channel C1;
}
__EOF__

# simulate a database corruption
sqlplus "/ as sysdba" << __EOF__
shutdown immediate;
__EOF__
rm $ORADATA/$ORACLE_SID/*.dbf
rm $ORADATA/$ORACLE_SID/*.ora


# restore controlfile
sqlplus "/ as sysdba" << __EOF__
startup force nomount;

DECLARE
  devtype varchar2(256);
  done boolean;
BEGIN
  devtype:=sys.dbms_backup_restore.deviceAllocate(
    type=>'', -- can also be 'sbt_tape'
    ident=>'T1');
  sys.dbms_backup_restore.restoreSetDatafile;
  sys.dbms_backup_restore.restoreControlfileTo(cfname=>'d:\oracle\Control01.ctl');
  sys.dbms_backup_restore.restoreBackupPiece(
    done=>done,
    handle=>'D:\KDE\DEMO_01FR79OT_1_1.DBF',
    params=>null);
  sys.dbms_backup_restore.deviceDeallocate;
END;
/
__EOF__

# restore datafile
sqlplus "/ as sysdba" << __EOF__
DECLARE
 devtype varchar2(256);
 done boolean;
BEGIN
DECLARE
 devtype varchar2(256);
 done boolean;
BEGIN
 devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',
  ident=>'t1');
 sys.dbms_backup_restore.restoreSetDatafile;
 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>01,
  toname=>'d:oracleoradatademoSYSTEM01.DBF');
 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>02,
  toname=>'d:oracleoradatademoUNDOTBS01.DBF');
 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>03,
  toname=>'d:oracleoradatademoDRSYS01.DBF');
 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>04,
  toname=>'d:oracleoradatademoEXAMPLE01.DBF');
 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>05,
  toname=>'d:oracleoradatademoINDX01.DBF');
 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>06,
  toname=>'d:oracleoradatademoODM01.DBF');
 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>07,
  toname=>'d:oracleoradatademoTOOLS01.DBF');
 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>08,
  toname=>'d:oracleoradatademoUSERS01.DBF');
 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>09,
  toname=>'d:oracleoradatademoXDB01.DBF');
 sys.dbms_backup_restore.restoreBackupPiece(done=>done,
  handle=>'D:KDEDEMO_01FR79OT_1_1.DBF',
   params=>null);
 sys.dbms_backup_restore.deviceDeallocate;
END;
/
__EOF__

# startup and recovery
sqlplus "/ as sysdba" << __EOF__
startup force mount;
recover database using backup controlfile until cancel;

-- the following message is prompted and answer should be entered manually
-- Specify log: {=suggested | filename | AUTO | CANCEL}
-- D:KDEARC00002.001 
__EOF__

# reset logs and open the database
sqlplus "/ as sysdba" << __EOF__
alter database open resetlogs;
__EOF__

the guide line of nocatalog (controlfile) rman backup:
+ should backup controlfile independently and increase CONTROL_FILE_RECORD_KEEP_TIME to make the entries kept as long as possible
+ should make a complete backup plan
+ should be familiar to rman facility

expansion controlfile section 19 failed

the control file section 19 is the archive log section.
it caused by too many invalid archive log entries are kept in control file.
to resolve the problem just run the following command with RMAN.

RMAN> delete NOPROMPT expired archivelog all ; 

the following message could be found in alert.log

...
kccrsz: denied expansion of controlfile section 19 by 65535 record(s)
the number of records is already at maximum value (65535)
krcpwnc: following controlfile record written over:
RECID #14147320 Recno 46258 Record timestamp
05/09/07 05:20:02
Object type=245 Object recid=24025 Object timestamp=
04/05/07 12:25:34
kccrsz: denied expansion of controlfile section 19 by 65535 record(s)
the number of records is already at maximum value (65535)
krcpwnc: following controlfile record written over:
RECID #14147321 Recno 46259 Record timestamp
05/09/07 05:20:02
Object type=245 Object recid=24026 Object timestamp=
04/05/07 13:01:33
kccrsz: denied expansion of controlfile section 19 by 65535 record(s)
the number of records is already at maximum value (65535)
krcpwnc: following controlfile record written over:
RECID #14147322 Recno 46260 Record timestamp
05/09/07 05:20:02
Object type=245 Object recid=24027 Object timestamp=
04/05/07 18:42:45
...

kccrsz: denied expansion of controlfile section 19 by 65535 record(s)
the number of records is already at maximum value (65535)
krcpwnc: following controlfile record written over:
RECID #4125758 Recno 41618 Record timestamp

controlfile has two sections. The reusable and the non-reusable sections. The reusable section is used by rman. The non-reusable section should never be overwritten if you provided enough space for the controlfile.

alter system set control_file_record_keep_time=0 scope=spfile;

附metalink上的解释：

Problem Descrīption
-------------------

In the "alert.log", you find the following warning messages:

kccrsz: denied expansion of controlfile section 9 by 65535 record(s)
the number of records is already at maximum value (65535)
krcpwnc: following controlfile record written over:
RECID #520891 Recno 53663 Record timestamp
...
kccrsz: denied expansion of controlfile section 9 by 65535 record(s)
the number of records is already at maximum value (65535)
krcpwnc: following controlfile record written over:
RECID #520892 Recno 53664 Record timestamp

The database is still running.

The CONTROL_FILE_RECORD_KEEP_TIME init parameter is set to 7.

If you display the records used in the LOG HISTORY section 9 of the controlfile:

SQL> select * from v$controlfile_record_section where type='LOG HISTORY' ;

TYPE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
------------- ------------- ------------ ----------- ---------- ----------
LOG HISTORY 65535 65535 33864 33863 520892

The number of RECORDS_USED has reached the maximum allowed in RECORDS_TOTAL.

Solution Descrīption
--------------------

Set the CONTROL_FILE_RECORD_KEEP_TIME to 0:

* Insert the parameter CONTROL_FILE_RECORD_KEEP_TIME = 0 IN "INIT.ORA"

-OR-

* Set it momentarily if you cannot shut the database down now:

SQL> alter system set control_file_record_keep_time=0;

Explanation
-----------

The default value for

* the CONTROL_FILE_RECORD_KEEP_TIME is 7 days.

SQL> select value from v$parameter
2 where name='control_file_record_keep_time';

VALUE
-----
7

* the MAXLOGHISTORY database parameter has already reached the maximum of
65535 and it cannot be increased anymore.

SQL> alter database backup controlfile to trace;
=> in the trace file, MAXLOGHISTORY is 65535

The MAXLOGHISTORY increases dynamically when the
CONTROL_FILE_RECORD_KEEP_TIME is set to a value different from 0,
but does not exceed 65535. Once reached, the message appears in the
alert.log warning you that a controlfile record is written over.

Remark
~~~~~~
Though, setting control_file_record_keep_time to zero is dangerous for users
making RMAN backups: RMAN users might not be able to restore backups.

References
----------
Note 1057885.6 RDBMS: KCCRSZ, ORA-470, ORA-449, ORA-1092, DENIED EXPANSION
CONTROL FILE

Did this article help solve your problem? Select Yes No Does Not Apply Would you recommend this document to others? Select Yes No Not Sure

TIP: Click help for a detailed explanation of this page.
Bookmark Go to End

Subject: RDBMS: KCCRSZ, ORA-470, ORA-449, ORA-1092, DENIED EXPANSION CONTROL FILE
Doc ID: Note:1057885.6 Type: PROBLEM
Last Revision Date: 13-MAY-2002 Status: PUBLISHED

Problem Descrīption:
====================

While running Oracle 8.0.X you try to bring the database up after a shutdown
and receive the following error messages related to denied expansion of the
control file:

ORA-470: LGWR process terminated with error
Cause: The log writer process terminated abnormally.
Action: Check the accompanying messages, if any, and the background
process trace file.
Correct the problem mentioned in the other messages.
Then shut down and restart the instance.
If the trace file mentions any other background process errors,
check the trace file for the mentioned process until the root
error is found.

ORA-449: background process '' unexpectedly terminated with error ''
Cause: A foreground process needing service from a background process has
discovered the background process died.
Action: Refer to the message code given in the message and the trace file
for the foreground and the background processes.

ORA-1092: ORACLE instance terminated. Disconnection forced.
Cause: The instance connected to was terminated abnormally, probably due to
a SHUTDOWN ABORT.
The current process was forced to disconnect from the instance.
Action: Contact the database administrator to determine when the instance is
restarted.
Attempt to reconnect after the instance is running again.

In your "alert.log" you see the following:

kccrsz: denied expansion of controlfile section ## by ## record(s)
the number of records is already at maximum value (65535)

kccrsz: expanded control file section ## from # to # records
requested to grow by # records added #blocks of records

Solution Descrīption:
=====================

Records in some sections of the control file are circularly reusable while
records in other sections are never reused. The "INIT.ORA" parameter
"CONTROL_FILE_RECORD_KEEP_TIME" applies to reusable sections. It specifies the
minimum age in days that a record must have before it can be reused. This value
defaults to 7 days if not specified in your "INIT.ORA". In the event a new
record needs to be added to a reusable section and the oldest record has not
aged enough, then the record section expands.

The parameter that controls the expansion of this file is specified during
database creation, namely "MAXLOGHISTORY". In Oracle8 versions prior to 8.0.6,
there is a known issue whenever MAXLOGHISTORY = 65535. This issue is recorded
in Bug 636522. You can avoid this issue by stopping the expansion of the
control file by setting CONTROL_FILE_RECORD_KEEP_TIME = 0. If
"CONTROL_FILE_RECORD_KEEP_TIME" is set to 0, then reusable sections never
expand and records are reused as needed.

Explanation:
============

In Oracle versions prior to 8.0.6, there was a problem associated with
controlfile expansion. If MAXLOGHISTORY is set to 65535 (64K) during database
creation (this is the default value), then when log sequence number reaches
65535, LGWR dies. This problem is now fixed in 8.0.6.

If a patch for Bug 636522 is available, you should look into the feasibility
of applying to your database instance or upgrade to a release of Oracle 8.0.6
or higher. If this is not an option, check the "MAXLOGHISTORY" value. This
value can be retrieved by issuing the command "alter database backup
controlfile to trace" and examining the resulting trace file in your
"USER_DUMP_DEST" directory. If it is set to 65535 then you may want to consider
setting it to a lower value. Changing the "MAXLOGHISTORY" requires recreating
control file. Aftwards, you should set the parameter
"CONTROL_FILE_RECORD_KEEP_TIME" to 0 in your "INIT.ORA" and restart the
database. You can also use "ALTER SYSTEM" to change the parameter's value to an
one time only basis.

References:
===========

Bug 636522

Search Words:
=============

CONTROL FILE KCCRSZ ORA-470 ORA-449 ORA-1092