ORACLE - DBA MOU three: rman backup, unused catalog, the paper lost control solutiondba

DBA MOU three: rman backup, unused catalog, the paper lost control solution

[Oracle] DBA MOU three: rman backup, unused catalog, the paper lost control solution

Author: Fenng
Date: July 19 2004

Description of the situation

Fault customer database, the new system administrator misoperation. Delete a number of documents.
Asked: delete those files?
Answer: all important data files, all controlled documents. Database archiving is the original model, with rman backup data, rman use of controlled documents.
Fortunately, the last rman full backup is a control file, including. Automatic backup system is not set controlled documents. Now the situation is the database will not boot.

Needless to say, the customer's backup solution is not perfect enough, but this time again said those words to blame the users suspected of hindsight Zhuge Liang, the user is God, not to offend him. Also, customers Full backup (although not automatic backup control document, this can not be by conventional resume steps to recover). For us, this is definitely good news.

Below we have to operate through a mock demonstration of a solution to this issue.

Settlement process

Firstly, document control system for the entire database backup:

:———————— Blue part of the code is input, a black part of sensitive information, and need to be attention ——– ——————————————–
C: WUTemp> rman target /

Recovery Manager: Release 9.2.0.1.0 s - Production.

Connected to target database: DEMO (DBID = 3272375326)

RMAN> (run
2> allocate channel C1 type disk;
3> backup full tag 'FullBackup' format 'd: \ KDE \% d_ s_%%% u_ p.dbf' database include current controlfile;
4> sql 'alter system archive log current';
5> release channel C1;
6>)

Using target database controlfile instead of recovery catalog
Allocated channel: C1
Channel C1: sid = 15 = DISK devtype

Starting backup at 18-JUL-04
Channel C1: starting full datafile backupset
Channel C1: specifying datafile (s) in backupset
Including current SPFILE in backupset
Including current controlfile in backupset
Input datafile fno name = 00001 = D: \ ORACLE \ ORADATA \ DEMO \ SYSTEM01.DBF
Input datafile fno name = 00002 = D: \ ORACLE \ ORADATA \ DEMO \ UNDOTBS01.DBF
Input datafile fno name = 00004 = D: \ ORACLE \ ORADATA \ DEMO \ EXAMPLE01.DBF
Input datafile fno name = 00007 = D: \ ORACLE \ ORADATA \ DEMO \ XDB01.DBF
Input datafile fno name = 00005 = D: \ ORACLE \ ORADATA \ DEMO \ INDX01.DBF
Input datafile fno name = 00006 = D: \ ORACLE \ ORADATA \ DEMO \ USERS01.DBF
Input datafile fno name = 00003 = D: \ ORACLE \ ORADATA \ DEMO \ DRSYS01.DBF
Input datafile fno name = 00006 = D: \ ORACLE \ ORADATA \ DEMO \ ODM01.DBF
Input datafile fno name = 00005 = D: \ ORACLE \ ORADATA \ DEMO \ TOOLS01.DBF
Channel C1: 1 starting piece at 18-JUL-04
Channel C1: a finished piece at 18-JUL-04
Piece handle = D: \ KDE \ DEMO_01FR79OT_1_1.DBF comment = NONE
Channel C1: backup set complete, elapsed time: 00:01:17
Finished backup at 18-JUL-04

Sql statement: alter system archive log current

Released channel: C1

— As indicated above, we have done a Full database backup. Tablets including control of backup documents. Note that the above content of the output of the blackbody. We behind the recovery operation will be used.

Simulation wrong turn off the case, remove all the documents and all the control. DBF files. Then starup will see the following error message:

SQL> startup
ORACLE instance started.

Total System Global Area 152115804 bytes
Fixed Size 453,212 bytes
Variable Size 100663296 bytes
Database Buffers 50331648 bytes
Redo Buffers 667,648 bytes
ORA-00205: error in identifying controlfile, check alert log for more info

Show alert Log, the system should not control document. Now the same circumstances and customer issues. But before continuing on, we also need to introduce some background knowledge.

Background knowledge:

Oracle 816 in the subsequent version, Oracle has provided a package: DBMS_BACKUP_RESTORE.DBMS_BACKUP_RESTORE dbmsbkrs.sql package by the two scripts and prvtbkrs.plb created. Catproc.sql script will call these two packages. Every so databases have this package is Oracle servers and operating systems operation between IO interface. Recovery Manager directly by the call. It is said that these two scripts and the function is built into some of the Oracle in the document.

Thus, we can in the database nomount circumstances call these package to achieve our objective of the restoration. Dbmsbkrs.sql and prvtbkrs.plb in these two scripts are detailed in the documents, for length, not be translated January 1, but will be directly below the quoted text.

The key elements include:

FUNCTION deviceAllocate (
Type IN varchar2 default NULL
, The name IN varchar2 default NULL
, Ident IN varchar2 default NULL
, Noio IN boolean default FALSE
, Params IN varchar2 default NULL)
RETURN varchar2;

— Describe the device to be used for sequential I / O. For device types where
— Only one process at a time can use a device, this call allocates a device
— For exclusive use by this session. The device remains allocated until
— DeviceDeallocate is called or session termination. The device can be used
— Both for creating and restoring backups.
—
— Specifying a device allocates a context that exists until the session
— Terminates or deviceDeallocate is called. Only one device can be specified
— At a time for a particular session. Thus deviceDeallocate must be called
— Before a different device can be specified. This is not a limitation since
— A session can only read or write one backup at a time.
—
— The other major effect of allocating a device is to specify the name space
— For the backup handles (file names). The handle for a sequential file does
— Not necessarily define the type of device used to write the file. Thus it
— Is necessary to specify the device type in order to interpret the file
— Handle. The NULL device type is defined for all systems. It is the file
— System supplied by the operating system. The sequential file handles are
— Thus normal file names.
—
— A device can be specified either by name or by type.
— If the type is specified but not the name, the system picks an
— Available device of that type.
— If the name is specified but not the type, the type is determined
— From the device.
— If neither the type or the name is given, and the backups are files in
— The operating system file system.

— Note that some types of devices, optical disks for example, can be shared
— By many processes, and thus do not really require allocation of the device
— Itself. However we do need to allocate the context for accessing the
— Device, and we do need to know the device type for proper interpretation
— Of the file handle. Thus it is always necessary to make the device
— Allocation call before making most other calls in this package.
—
— Input parameters:
— Type
— If specified, and this gives the type of device to use for sequential
— I / O. The allowed types are port specific. For example a port may
— Support the type "TAPE" which is implemented via the Oracle tape
— API. If no type is specified, it may be implied by specifying a
— Particular device name to allocate. The type should be allowed to
— Default to NULL if operating system files are to be used.
—
— Name
— If specified, this names a particular piece of hardware to use for
— Accessing sequential files. If not specified, any available
— Device of the correct type will be allocated. If the device cannot
— Be shared, it is allocated to this session for exclusive use.
— The name should be allowed to default to NULL if operating system
— Files are to be used.
—
— Ident
— This is the users identifier that he uses to name this device. It
— Is only used to report the status of this session via
— Dbms_application_info. This value will be placed in the CLIENT_INFO
— Column of the V $ SESSION table, in the row corresponding to the
— Session in which the device was allocated. This value can also
— Be queried with the dbms_application_info.read_client_info procedure.
—
— Noio
— If TRUE, the device will not be used for doing any I / O. This allows
— The specification of a device type for deleting sequential files
— Without actually allocating a piece of hardware. An allocation for
— Noio can also be used for issuing device commands. Note that some
— Commands may actually require a physical device and thus will get
— An error if the allocate was done with noio set to TRUE.
—
— Params
— This string is simply passed to the device allocate OSD. It is
— Completely port and device specific.
—
— Returns:
— It returns a valid device type. This is the type that should be
— Allocated to access the same sequential files at a later date. Note
— That this might not be exactly the same value as the input string.
— The allocate OSD may do some translation of the type passed in. The
— Return value is NULL when using operating system files.

PROCEDURE restoreControlfileTo (cfname IN varchar2);

— This copies the controlfile from the backup set to an operating system
— File. If the database is mounted, the name must NOT match any of the
— Current controlfiles.
—
— Input parameters:
— Cfname
— Name of file to create or overwrite with the controlfile from the
— Backup set.

PROCEDURE restoreDataFileTo (dfnumber IN binary_integer
, Toname IN varchar2 default NULL);
—
— RestoreDataFileTo creates the output file from a complete backup in the
— Backup set.

If you are interested can go to read these two documents covering Notes.

We first try to resume control of documents:

SQL> startup force nomount;

SQL> DECLARE
2 devtype varchar2 (256);
3 done boolean;
4 BEGIN
5 devtype: = sys.dbms_backup_restore.deviceAllocate (type =>'', ident => 'T1');
6 sys.dbms_backup_restore.restoreSetDatafile;
7 sys.dbms_backup_restore.restoreControlfileTo (cfname => 'd: \ oracle \ Control01.ctl');
8 sys.dbms_backup_restore.restoreBackupPiece (done => done, handle => 'D: \ KDE \ DEMO_01FR79OT_1_1.DBF', params => null);
9 sys.dbms_backup_restore.deviceDeallocate;
10 END;
11 /

PL / SQL procedure successfully completed.

OK, control file recovery complete. On the above explanation:

Fifth-allocation of a device channel, because the operating system files, so here is empty, if it is from tape resume use "sbt_tape";
Specified in the sixth line began restore;
To be pointed out that the seventh goal of restoring document storage location;
Eighth line from the film which backup recovery;
Channel 9 will release equipment.

May wish to operate over the results of test:

SQL> host dir d: \ oracle
Volume in drive D is DATA
Volume Serial Number is DC79-57F8
Directory of d: \ oracle

07/18/2004 09:08 PM <DIR>.
07/18/2004 09:08 PM <DIR> ..
06/08/2004 3:21 PM <DIR> admin
07/18/2004 09:08 PM 1,871,872 CONTROL01.CTL
07/16/2004 11:27 AM <DIR> ORA92
07/18/2004 09:02 PM <DIR> oradata

In this way, we restore the success of the control files. If control document Full backup after separate done, then turn off examples, copy files to control specific location, and then rman implementation restore database; can.

However, we have somewhat different from the situation here.

Lost documents as the case may be, to continue the recovery operations are as follows:

Code :———————————————— ——————————–
SQL> DECLARE
2 devtype varchar2 (256);
3 done boolean;
4 BEGIN
5 devtype: = sys.dbms_backup_restore.deviceAllocate (type =>'', ident => 't1');
6 sys.dbms_backup_restore.restoreSetDatafile;
7 sys.dbms_backup_restore.restoreDatafileTo (dfnumber => 01, toname => '
D: \ oracle \ oradata \ demo \ SYSTEM01.DBF ');
8 sys.dbms_backup_restore.restoreDatafileTo (dfnumber => 02, toname => '
D: \ oracle \ oradata \ demo \ UNDOTBS01.DBF ');
9 sys.dbms_backup_restore.restoreDatafileTo (dfnumber => 03, toname => '
D: \ oracle \ oradata \ demo \ DRSYS01.DBF ');
10 sys.dbms_backup_restore.restoreDatafileTo (dfnumber => 04, toname => '
D: \ oracle \ oradata \ demo \ EXAMPLE01.DBF ');
11 sys.dbms_backup_restore.restoreDatafileTo (dfnumber => 05, toname => '
D: \ oracle \ oradata \ demo \ INDX01.DBF ');
12 sys.dbms_backup_restore.restoreDatafileTo (dfnumber => 06, toname => '
D: \ oracle \ oradata \ demo \ ODM01.DBF ');
13 sys.dbms_backup_restore.restoreDatafileTo (dfnumber => 07, toname => '
D: \ oracle \ oradata \ demo \ TOOLS01.DBF ');
14 sys.dbms_backup_restore.restoreDatafileTo (dfnumber => 08, toname => '
D: \ oracle \ oradata \ demo \ USERS01.DBF ');
15 sys.dbms_backup_restore.restoreDatafileTo (dfnumber => 09, toname => '
D: \ oracle \ oradata \ demo \ XDB01.DBF ');
16 sys.dbms_backup_restore.restoreBackupPiece (done => done, handle => '
D: \ KDE \ DEMO_01FR79OT_1_1.DBF ', params => null);
Sys.dbms_backup_restore.deviceDeallocate 17;
18 END;
19 /

PL / SQL procedure successfully completed.

— Our situation is that all data files are lost, then replicated ………..
— File No. counterparts from the entire front of the screen output when backup content. Therefore, the retention time in the backup operation Log is a very good habit.

SQL> startup force mount;
ORACLE instance started.

Total System Global Area 152115804 bytes
Fixed Size 453,212 bytes
Variable Size 100663296 bytes
Database Buffers 50331648 bytes
Redo Buffers 667,648 bytes
Database mounted.
SQL> Recover database using backup controlfile until cancel;
ORA-00279: change 243854 generated at 07/18/2004 20:57:03 needed for thread 1
ORA-00289: suggestion: D: \ KDE \ ARC00002.001
ORA-00280: 243854 for thread a change is in sequence # 2
Specify log: = (<RET> suggested | filename | AUTO | CANCEL)

D: \ KDE \ ARC00002.001
ORA-00279: change 244089 generated at 07/18/2004 20:58:18 needed for thread 1
ORA-00289: suggestion: D: \ KDE \ ARC00003.001
ORA-00280: 244089 for thread a change is in sequence # 3
ORA-00278: log file 'D: \ KDE \ ARC00002.001' no longer needed for this recovery

Specify log: = (<RET> suggested | filename | AUTO | CANCEL)
Cancel
Media recovery cancelled.

SQL> alter database open resetlogs;

Database altered.

Finally, to resetlogs.

Then, sweep the battlefield, the entire database immediate backup. If you are a DBA, it should further develop and improve back-up plan. Remedial measures, it is not too late.

Summing up

A control file in a backup significant recommendations for each of its separate backup, database version if permitted, should be set to automatically control file backup. At the same time should try to increase CONTROL_FILE_RECORD_KEEP_TIME this initialization parameter values. Backup information to be retained longer

2 should develop a perfect backup plan, or back-up plan once the gap would be to the system by the disaster. Remember, "could go wrong will go wrong places."

3 familiar with the internal RMAN backup mechanism, the use of a certain DBMS_BACKUP_RESTORE control in the critical Hou helpful.

4 backup script should redirect and the preservation of the Log. In order to find useful information is wrong.

References:

RMAN Recovery Without Recovery Catalog or Controlfiles by Bonnie Bizzarodbmsbkrs Bizzarodbmsbkrs.sql and prvtbkrs.plb document describes Notes (which can be in your system $ ORACLE_HOME / rdbms / admin / find.)

Related links:

In this paper, the more discussion, please refer to here:
Http://www.itpub.net/244345.html
DBA MOU bis: Exp mistakes of a case
Http://www.itpub.net/showthread.php?s=&threadid=238819
DBA MOU one: events tracking solution can not materialized to create a case
Http://www.dbanotes.net/Oracle/Oracle-Case-of-10046_I.htm

Original source
<a Href="http://www.dbanotes.net/Oracle/Rman_nocatalog_lost_controlfile_howto.htm">
Http://www.dbanotes.net/Rman_nocatalog_lost_controlfile_howto.htm </ a>

The author of this article:
Fenng, the incumbent of a US-funded company DBA, unworthily occupy the spare time of the database-related Technical Forum. Now concerned about how effective use of ORACLE Database Construction of enterprise applications. On the Oracle tuning, there is one point on troubleshooting. Personal technology site: http://www.dbanotes.net/. Can be contacted via e-mail dbanotes@gmail.com him.

This article DBAnotes.net Copyright reprint please indicate the source and author of this paper, as far as possible to retain all hyperlinks.

Bookmark it: These icons link to social bookmarking sites where readers can share and discover new web pages.