Pages

Tuesday, February 23, 2010

Disaster Recovery using RMAN Backups

Every DBA has to ensure that his hours spent in planning and testing the backup-recovery strategy works, especially it should be relied upon in any unexpected scenarios occur. We DBAs are always tend to be at ease when our scheduled backup jobs are performing well and are error-free, followed by couple of recovery testings scenarios.

Nevertheless, there are always scenarios which may be left unanticipated during the B/R Testing. One such scenario was haunting me since couple of days. And it took me a while to figure it out (due to known and unknown recovery conditions).

Let's take an example. Say I have a complete RMAN Backup including archivelog backups on Disks. Let's assume that the backups are all in recoverable conditions. And then, suddenly the Production Database Server goes off. Boom!!! The only way to recover the server is to rebuild it or to have us recover the Instance from the RMAN Backups on to another Server. In this post, we will see how to recover or reconstruct a Database by only using the RMAN Backups.

2 pre-conditions to use the RMAN Backups for recovery are:

  1. Any Server should be available in a state where Oracle Product can be installed.
  2. Same version of Oracle Product needs to be installed with which the RMAN Backup was taken
In my case the O/S is Windows and the Oracle Database Product is 9i R2 (9.2.0.8) Standard Edition.

The following steps would outline the procedure of recovering the Database using only RMAN Backups:

  1. Create the Oracle Service
  2. Create the relevant folders under admin and oradata folders
  3. Restore the spfile from autobackup
  4. Restore the controlfile from autobackup
  5. Restore the Database
  6. Recover the Database
  7. Open the Database resetting the logs
We create an Oracle Service using oradim without passing any parameter file

c:\>oradim -new -sid DBTEST -intpwd DBTEST

Now, we need to create the relevant folders for Oracle Database, as follows:

  1. Create BDUMP, CDUMP, and UDUMP folders under 'c:\oracle\admin' folder,
  2. Create DBTEST folder under 'c:\oracle\oradata' folder

We connect to RMAN and start the Recovery process. First we need to set the DBID of the Database. At times we may or maynot know the DBID of the Database. There are 2 ways to find the DBID:

  1. If any RMAN Logs are maintained, the DBID can be found when the initial connection is made to the target
  2. If autobackup is enabled ans has the autobackup format set, with say '%F', then the filename of the autobackup has the DBID.
    In my case the file name is 'c-1103102985-20100216-02', and the DBID is '1103102985' in the filename.

And then we need to set the autobackup location which will help us identify the location where all the RMAN files.

C:\>rman
Recovery Manager: Release 9.2.0.8.0 - ProductionCopyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database (not started)

RMAN> set dbid=1103102985;
executing command: SET DBID

RMAN> startup force nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file C:\ORACLE\PRODUCT\9.2.0\DB_1\DATABASE\INITDBTEST.ORA'
trying to start the Oracle instance without parameter files ...
Oracle instance started
Total System Global Area 97590688 bytes
Fixed Size 454048 bytes
Variable Size 46137344 bytes
Database Buffers 50331648 bytes
Redo Buffers 667648 bytes

RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '\\testdb\dbtest\%F';
executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Then we have to restore the SPFILE from the autobackup file. We can either give the keyword "autobackup" or directly reference the path and filename of the autobackup in the 'from' part of the command. I did the latter.

RMAN> restore spfile to 'c:\spdbtest01.ora' from "c:\c-1103102985-20100216-02";
Starting restore at 16-FEB-10
using channel ORA_DISK_1
channel ORA_DISK_1: autobackup found: c:\c-1103102985-20100216-02
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 16-FEB-10

Now, we need to shutdown the instance and startup with the restored parameter file.

RMAN> shutdown immediate
Oracle instance shut down

Remember that we have just restored the SPFILE in a non-default location and hence we would require referring a PFILE that should point to the SPFILE so as to start the instance. We need to create a text file, 'init.ora', and then mention the path and name of the restored SPFILE in it. The initDBTEST.ora would contain the following statement:

SPFILE=C:\spdbtest01.ora

And then, we need to reference the newly created PFILE to start the instance.

Alternately, you could also restore the SPFILE to the default home location, under DATABASE directory, where Oracle automatically looks for an SPFILE and thus avoid creating the PFILE to point at the SPFIL. I have chosen the former option for the demonstration.

Start the instance in NOMOUNT state using the parameter file pointing to the spfile.

RMAN> startup force pfile=’C:\initDBTEST.ora’ nomount
Oracle instance started

Total System Global Area 907482184 bytes
Fixed Size 455752 bytes
Variable Size 276824064 bytes
Database Buffers 629145600 bytes
Redo Buffers 1056768 bytes

Now, we need to restore the controlfile to bring back the database. Again here, we can either give the keyword ‘AUTOBACKUP’ or directly reference the path and filename of the autobackup in the ‘FROM’ part of the command.

RMAN> restore controlfile from "c:\c-1103102985-20100216-02";
Starting restore at 16-FEB-10
using channel ORA_DISK_1
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=C:\ORACLE\ORADATA\DBTEST\CONTROL01.CTL
output filename=C:\ORACLE\ORADATA\DBTEST\CONTROL02.CTL
output filename=C:\ORACLE\ORADATA\DBTEST\CONTROL03.CTL
Finished restore at 16-FEB-10

With the restored controlfiles, now we are in a position to restore the Database, for which we need to mount the Instance first and the fire the restore command. Once restore is complete we need to fire the recover command for applying the archivelogs.

RMAN> alter database mount;
using target database controlfile instead of recovery catalog
database mounted

RMAN> restore database;
Starting restore at 16-FEB-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=16 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\ORADATA\DBTEST\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\ORADATA\DBTEST\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\ORADATA\DBTEST\DRSYS01.DBF
restoring datafile 00004 to C:\ORACLE\ORADATA\DBTEST\EXAMPLE01.DBF
restoring datafile 00005 to C:\ORACLE\ORADATA\DBTEST\INDX01.DBF
restoring datafile 00006 to C:\ORACLE\ORADATA\DBTEST\TOOLS01.DBF
restoring datafile 00007 to C:\ORACLE\ORADATA\DBTEST\USERS01.DBF
.
.
.
restoring datafile 00023 to C:\ORACLE\ORADATA\DBTEST\CWMLITE01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_1_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 2
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_2_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 3
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_3_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 4
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_4_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 5
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_5_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 6
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_6_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 7
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_7_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 8
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_8_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 9
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_9_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 16-FEB-10

RMAN> recover database;
Starting recover at 17-FEB-10
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=2
channel ORA_DISK_1: restored backup piece 1
piece handle=\\TESTDB\DBTEST\DBTEST_03L65IJE_1_1_20100216 tag=FULL_160210_1230 params=NULL
channel ORA_DISK_1: restore complete
archive log filename=C:\ORACLE\ORADATA\DBTEST\ARCHIVE\ARC00002.001 thread=1 sequence=2
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=3
channel ORA_DISK_1: restored backup piece 1
piece handle=\\TESTDB\DBTEST\DBTEST_05L65Q1B_1_1_20100216 tag=FULL_160210_1430 params=NULL
channel ORA_DISK_1: restore complete
archive log filename=C:\ORACLE\ORADATA\DBTEST\ARCHIVE\ARC00003.001 thread=1 sequence=3
unable to find archive log
archive log thread=1 sequence=4
RMAN-00571: =========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: =========================================================
RMAN-03002: failure of recover command at 02/17/2010 10:25:28
RMAN-06054: media recovery requesting unknown log: thread 1 scn 34558001

Don't Panic when you see the above RMAN Error Message. You just need to open the database with resetlogs as the recovery is over.

RMAN> alter database open resetlogs;
database opened

RMAN>

Looks simple! Yet it is a critical test case, as this is the what your full RMAN backups should do, i.e. A Full Recovery.

Tuesday, February 2, 2010

How to Enable Auditing in 10g Database

Check the values of audit parameters of your 10g Database, as it is a default disabled feature.

SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string E:\ORACLE\ADMIN\ORCL\ADUMP
audit_sys_operations boolean FALSE
audit_trail string NONE

The AUDIT_TRAIL parameter can be used to enable auditing by setting one of the following values:
none or false - Auditing is disabled.
db or true - Auditing is enabled, with
all audit records stored in the database audit trial (SYS.AUD$).
db,extended
- As db, but the SQL_BIND and SQL_TEXT columns are also populated.
xml-
Auditing is enabled, with all audit records stored as XML format OS files.
xml,extended - As xml, but the SQL_BIND and SQL_TEXT columns are also
populated.
os- Auditing is enabled, with all audit records directed to the
operating system's audit trail.

SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
System altered.

The Database needs to be bounced for the change to take affect.

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 1299288 bytes
Variable Size 335547560 bytes
Database Buffers 1266679808 bytes
Redo Buffers 7086080 bytes
Database mounted.
Database opened.

SQL> ALTER SYSTEM SET audit_trail=db,extended scope=spfile;
System altered.

The SQL_BIND and SQL_TEXT columns are populated, when extended option in AUDIT_TRAIL is enabled.

SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string E:\ORACLE\ADMIN\ORCL\ADUMP
audit_sys_operations boolean FALSE
audit_trail string DB

The AUDIT_SYS_OPERATIONS parameter enables or disables the auditing of operations issued by users connecting with SYSDBA or SYSOPER privileges, including the SYS user. All audit records are written to the OS audit trail.

SQL> ALTER SYSTEM SET audit_sys_operations=true scope=spfile;
System altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 1299288 bytes
Variable Size 335547560 bytes
Database Buffers 1266679808 bytes
Redo Buffers 7086080 bytes
Database mounted.
Database opened.

SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string E:\ORACLE\ADMIN\ORCL\ADUMP
audit_sys_operations boolean TRUE
audit_trail string DB, EXTENDED

Modifications of the data in the audit trail can be audited using the following statement:

SQL> AUDIT INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS;
Audit succeeded.

To enable auditing on a schema/user we can use the following syntaxes

For auditing DDL (CREATE, ALTER & DROP of objects) statements
AUDIT ALL BY ACCESS;
For auditing DML (INSERT UPDATE, DELETE, SELECT, EXECUTE) statements
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY BY ACCESS;
For auditing SYSTEM EVENTS (LOGON, LOGOFF etc.) statements
AUDIT EXECUTE PROCEDURE BY BY ACCESS;

To monitor the audit trail we can use the DBA_AUDIT_TRAIL view.