Three DBA Work Memorandum: RMAN Backup, unused workload solutions

[Oracle] Memorandum of DBA three: rman backup, unused catalog, control file loss solution Author: Fenng Date: July 19 2004

The situation describes the customer report database failure, and the new system administrator misuses. Deleted some files. Ask: Did you delete those files? Answer: All important data files, all control files. The database is originally archive mode, with RMAN backup data, RMAN uses control files. Fortunately, the last RMAN FULL backup is included in the control file. The system is not set to automatic backup control files. Now the situation is that the database cannot start. Needless to say, the customer's backup program is not perfect, but at this time, these words are blamed, and the users are suspicious. Users are God, don't go to him. Also, the customer has a full backup (although not automatic backup control file, this cannot be recovered by conventional recovery steps). This is an absolute good news for us. Below we demonstrate solutions for this problem through analog operation.

Solution First, use control files to make a database system full backup: Code: ------------------------ The blue part is the input content, the black part is Sensitive information, pay attention -------------------------------------------------------------------------------------------------------------------------------------------------- -------- C: wuters> Rman Target / Recovery Manager: Release 9.2.0.1.0 - Production.copyright (C) 1995, 2002, Oracle Corporation. All Rights Reserved.Connected to Target Database: Demo (DBID = 3272375326) RMAN> Run {2> Allocate Channel C1 Type Disk; 3> Backup full tag 'fullbackup' format 'D: /kde/%D_%U_%S_%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 catalogallocated channel: C1channel C1: sid = 15 devtype = DISK Starting backup at 18-JUL-04channel C1: starting full datafile backupsetchannel C1 : specifying datafile (s) in backupsetincluding current SPFILE in backupsetincluding current controlfile in backupsetinput datafile fno = 00001 name = D: /ORACLE/ORADATA/DEMO/SYSTEM01.DBFinput datafile fno = 00002 name = D: / ORACLE / ORADATA / DEMO / UNDOTBS01 .DBFINPUT DATAFILE FNO = 00 004 name = d: /ore/oradata/demo/example01.dbfinput datafile fno = 00009 name = d: /oracle/oradata/demo/xdb01.dbfinput datafile fno = 00005 name = d: / oracle / oradata / demo / index01. DBFinput datafile fno = 00008 name = D: /ORACLE/ORADATA/DEMO/USERS01.DBFinput datafile fno = 00003 name = D: /ORACLE/ORADATA/DEMO/DRSYS01.DBFinput datafile fno = 00006 name = D: / ORACLE / ORADATA / Demo / ODM01.DBFINPUT DATAFILE FNO = 00007 Name = D: /oracle/oradata/demo/tools01.dbfchannel C1: Starting Piece 1 AT 18-JUL-04Channel C1: Finished Piece 1 AT 18-JUL-04PIECE HANDE = D: / KDE / DEMO_01FR79T_1_1.DBF Comment =

NoneChannel C1: Backup Set Complete, ELAPSED TIME: 00: 01: 17Finished Backup AT 18-JUL-04 SQL Statement: ALTER SYSTEM ARCHIVE LOG CURRENT RELEASED CHANNEL: C1 - As shown above, we made a database's full backup. The backup sheet includes a control file. Note the black body portion of the output content above. We will use in the restoration operations later. Analog errors, turn off the instance, delete all control files and all .dbf files. Then starup will see the following error message: SQL> startupORACLE instance started Total System Global Area 152115804 bytesFixed Size 453212 bytesVariable Size 100663296 bytesDatabase Buffers 50331648 bytesRedo Buffers 667648 bytesORA-00205:. Error in identifying controlfile, check alert log for more info View alert Log, it should be that the system cannot find the control file. The situation is now consistent with the customer problem. However, before we continue to tell, we also need to introduce a little background knowledge. Background knowledge: In the release after Oracle 816, Oracle provides a package: DBMS_BACKUP_RESTORE.DBMS_BACKUP_RESTORE package is created by dbmsbkrs.sql and prVtbkrs.plb. The two packages are called after running. Catproc.sql script running So this package having every database is an interface between the Oracle server and the operating system. Direct calls by the recovery manager. And it is said that the functionality of these two scripts is in some library files built into Oracle. This shows that we can call these packages in the case of database Nomount to achieve our recovery purposes. There is a detailed documentation in DBMSBKRS.SQL and PrvTBKRS.plb, for a textual problem, it is translated, but will directly reference some original descriptions directly below. The key contents: FUNCTION deviceAllocate (type IN varchar2 default NULL, 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 .-- IFEN, The Backups Are 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, this Gives The Type of Device To Use for Sequential - I / O. The allowed type 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 exchange design. - the name shop be unloaded 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 quest 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 I N varchar2 default null; ---- RestoreDataFileto Creates The Output File from a Complete Backup in The - Backup Set. If you are interested, you can read the annotation instructions for the two files. We first try to recover the control file: SQL> Startup force Nomount; SQL> Declare2 DevType varcha2 (256); 3 Done Boolean; 4 begin5 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, the control file is restored. Interpretation of the above content: The fifth line allocates a device channel because the operating system file is used, so it is empty, if it is recovered from the tape to use "SBT_TAPE"; the sixth line indicates to start RESTORE; the seventh line points out to be recovered Document target storage location; which backup sheet is restored from the eighth line; the ninth line releases the equipment channel. You may wish to verify the results of the above operation: SQL> Host Dir D: / Oraclevolume in Drive D: / 180504/2004 09:08 PM