recreate controlfile to restore recover datatabas like flashback

PROD machine - Host computer on which the production database runs. Also denotes the ORACLE_SID of the production instance.
TEST machine - Host computer, physically distinct from the machine on which the production database runs.
TEMP - The ORACLE_SID of the instance used to access the restored database.

The backup - The set of backed up database structures (see REQUIREMENTS) used as the basis for recovering the dropped table. Once restored,  this set of structures is referred to as the partially restored database.

REQUIREMENTS
------------

The following data structures must be available from the backup:
- All system tablespace datafiles
- Depending on the release, all rollback segment/undo/sysaux tablespace datafiles
- All datafiles in which the table to recovered is stored
- Archive logs - see Note (a)

Notes:
(a) If the restored datafiles are part of a hot backup, or you wish to roll them forward, the required archivelogs must be available.

In an ideal world the partially restored database will be made available for recovery on a TEST machine. However this may not always be possible, and it may be necessary to restore to the same machine on which the 'source' (PROD) database resides. The latter is NOT RECOMMENDED as a user error in the recovery process can corrupt the production database.

If the datafiles comprising the partially restored database were in hot backup mode at the time the table was dropped, it is necessary to restore a previous backup. The reason for this is that the hot backup must be rolled forward past the 'end backup' markers (the time that the tablespaces were taken out of hot backup mode) before the database can be opened. Failure to recover
the restored datafiles past their end backup markers will result in the following error when an attempt is made to open the database:

ORA-1195 "online backup of file %s needs more recovery to be consistent"

I. RECOVERY ON A TEST MACHINE:
------------------------------

The following steps are to be used when performing recovery on a TEST machine, the assumption being that there is no instance with the ORACLE_SID "PROD" or "TEMP" already running on the TEST machine.

1. On the PROD database, connect as a privileged user and create an ASCII dump of the controlfile. For example:

SQL> connect / as sysdba
SQL> alter database backup controlfile to trace resetlogs;

A trace file will be generated in the user_dump_dest destination. Rename this trace file to ccf.sql and save it.

2. Create a directory on the TEST machine. The restored database will be located in this directory. In this example the directory is called /RESTORE. The only stipulation is that there is enough space to easily accommodate the restored database and any archivelogs required for recovery.  If filesystem space is at a premium, the archivelogs may be optionally
restored to /RESTORE as required by recovery, and removed after they have been applied.

3. Restore the backup to the /RESTORE directory.

4. Optionally, restore all archivelogs required for recovery to the /RESTORE directory.

5. Copy the init.ora file (initPROD.ora) from the PROD database to the /RESTORE directory on the TEST machine. This assumes that all database parameters are specified in only the init.ora file. If an include files (ifile) is referenced, also copy this file to /RESTORE.

6. Rename the initPROD.ora to initTEMP.ora on the TEST machine.

7. Edit the initTEMP.ora and make the following modifications:
a. control_files = /RESTORE/cntrlTEMP.dbf

b. if applicable, change any ifile references to point to the copied include file

c. log_archive% parameters should be changed to reflect the restored archivelogs:
log_archive_destination
log_archive_format
For example:
log_archive_destination=/RESTORE
log_archive_format=arch_%s.dbf

d. %dump_dest parameters should be changed to point to suitable directories

e. If audit is on, turn it off.

8. Ensure that the shell environment is set correctly. At the very least, the following environment variables must be defined:

a. ORACLE_HOME
b. ORACLE_SID (should be set to TEMP)

9. Connect as a privileged user. For example:

SQL> connect / as sysdba

10. Start the instance, specifying TEMP's init.ora:

SQL> startup nomount pfile=/RESTORE/initTEMP.ora

11. The ccf.sql file (created in Step 1) contains the syntax necessary to create a new controlfile. Copy this script to the TEST machine and edit it to save only the text between (and including) 'CREATE CONTROLFILE' and it's terminating semi-colon. Modify the following sections:

a. LOGFILE. Edit this section to reflect the names of the online logs to be created for the TEMP instance. If the PROD instance has a large number of large online log members, it is advisable to specify a reduced number of smaller log members. You MUST however specify at least two online log groups.
b. DATAFILE. Edit this section to reflect the names of the restored datafiles only.
c. Miscellaneous:
- Remove the REUSE keyword
- Optionally change the ARCHIVELOG keyword to NOARCHIVELOG (so the TEMP database will not operate in archivelog mode)

An example of the completed ccf.sql script might be:

CREATE CONTROLFILE DATABASE "PROD" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 20
MAXINSTANCES 1
MAXLOGHISTORY 337
LOGFILE
GROUP 1 '/RESTORE/log1PROD.dbf' SIZE 1M,
GROUP 2 '/RESTORE/log2PROD.dbf' SIZE 1M
DATAFILE
'/RESTORE/sys1PROD.dbf',
'/RESTORE/rbs1RBS.dbf',
'/RESTORE/users1PROD.dbf'
;

Then execute ccf.sql script to create control file(s).

12. Recover the database. The database is to be recovered to a time before the table was dropped. There are two options here:

a. Time-based incomplete recovery. Database recovery is stopped at the specified date and time.
b. Cancel-based incomplete recovery. Database recovery is stopped on an archivelog boundary i.e. the granularity of cance-based recovery is the archivelog.

Example of time-based recovery:

SQL> recover database until time '2010-01-10:12:00:00' using backup controlfile

In the above example, apply archivelogs as requested. Recovery will stop automatically at 12:00 on Januaru 10th, 2010.

Example of cancel-based recovery:

SQL> recover database until cancel using backup controlfile

As soon as you have applied the last desired archivelog, type CANCEL to stop recovery.-- 否则打不开

13. Open the database with the RESETLOGS option:

SQL> alter database open resetlogs;

14. Export the table(s).

15. Import the table(s) into the PROD database.

16. Once the necessary tables have been imported, the TEMP instance can be shutdown and all associated files removed. It is worthwhile verifying that the import has completed successfully before removing the TEMP instance.

II. RECOVERY ON A PRODUCTION MACHINE:
-------------------------------------

If a TEST machine is not available for performing recovery, the PROD machine can be used. It is important to exercise extreme caution when doing this. The restored database will be operate under the TEST instance as before. During this procedure the restored database's name is changed in order to avoid problems in acquisition of the mount lock.

1. Take a FULL backup of the database running against the PROD instance before performing any recovery.

2. While connected to the PROD instance, create an ASCII dump of the controlfile:

SQL> connect / as sysdba
SQL> alter database backup controlfile to trace resetlogs;

3. Create a /RESTORE directory as before (Step 2 above).

4. Restore the backup (and optionally the archivelogs) to the /RESTORE directory (Steps 3 and 4 above).

5. Create the initTEMP.ora file (Steps 5, 6, 7 above). In addition to the changes already made, modify the db_name parameter, for example: 
db_name=TEMP

6. Ensure that the shell environment is set correctly (Step 8 above):
a. ORACLE_HOME
b. ORACLE_SID (should be set to TEMP)

7. Start the TEMP instance (Steps 9, 10 above). It is critical to ensure that the correct pfile is used to start the instance.

8. Modify the ccf.sql file (Step 11 above). It is critical to ensure that the DATAFILE and LOGFILE names reference the backup location and NOT the PROD instance database's files. In addition to the changes already made, modify the first line to set the new database name, for example: 
from: CREATE CONTROLFILE DATABASE "PROD" RESETLOGS NOARCHIVELOG
to: CREATE CONTROLFILE SET DATABASE "TEMP" RESETLOGS NOARCHIVELOG

9. Recover the database (Step 12 above).

10. Open the database (Step 13 above).

11. Export the table(s).

12. Import the tables(s).

13. Cleanup the TEMP instance (Step 16 above).文章来源地址https://www.toymoban.com/news/detail-686217.html

到了这里，关于recreate controlfile to restore recover datatabas like flashback的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！