Recover from a DROP or TRUNCATE table by using RMAN

To recover from a dropped or truncated table, a dummy database (copy of primary) will be restored and recovered to point in time so the table can be exported.  Once the table export is complete, the table can be imported into the primary  database. This dummy database can be a subset of the primary database. However, the 'dummy' database must include the SYSTEM, UNDO (or ROLLBACK), and the  tablespace(s) where the dropped/truncated table resides.

Read more

Standby MRP needs old log sequence even after restore of incremental backup at standby

At standby applied incremental backup but  MRP  process at standby is looking for very old sequence.

SQL>SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" 

  FROM (SELECT THREAD# ,SEQUENCE# 

  FROM V$ARCHIVED_LOG 

Read more

LOG MINING IN ORACLE DATABASE

Suppose data in a table has been deleted. Audit trail was not enabled, therefore we do not have a clue of the user who has performed deletion and there are around 30 users that have DELETE rights on that table. Management want to know the name of the user, here we can use Oracle LOG MINING feature to filter out the transactions performed during that period.

Note: Make sure the Archive log must be enabled previously, if you want to use LOG Mining technique.

CONSIDERATIONS

Database : ORCL

Schema : SCOTT Table : STUDENTS

Date of Deletion : 10-MAY-2014

1. Gather Archived Log 

Make archived logs of 10-MAY-2014 available in your local hard disk. In my case it is already available at the Flash Recovery Area.

2 . Create Tablespace for Log Miner 

By default LOG MINER tables resides in SYSAUX tablespace, but it is efficient to keep it in a separate tablespace for easy manageability & maintenance.

SQLPLUS SYS@ORCL AS SYSDBA

CREATE TABLESPACE TS_LOGMNR DATAFILE ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TS_LOGMNR_01.DBF’ SIZE 25M AUTOEXTEND ON MAXSIZE UNLIMITED;

Use the following statement to move all of LOG MINER objects from SYSAUX to TS_LOGMNR tablespace.

 EXEC DBMS_LOGMNR_D.SET_TABLESPACE(TS_LOGMNR);

3) Start Log Mining Load Archive log file into Log Miner.

BEGIN DBMS_LOGMNR.ADD_LOGFILE(C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCLARCHIVELOG\2014_05_12O1_MF_1_2_7JY3ZNWV_.ARC, DBMS_LOGMNR.NEW);

 END;

/

Perform Log Mining 

BEGIN DBMS_LOGMNR.START_LOGMNR(OPTIONS=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG+ DBMS_LOGMNR.COMMITTED_DATA_ONLY);

 END;

Format query results.

SET TRIM ON SET LINES 200

COL USERNAME FORMAT A15

COL TIMESTAMP FORMAT A20

COL SESSION_INFO FORMAT A200

SPOOL C:LOGMNR.TXT

SELECT USERNAME, TO_CHAR(TIMESTAMP,’DD-MON-YYYYY HH24:MI:SS’) TIMESTAMP, SESSION_INFO, SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE SEG_OWNER='SCOTT’ AND SEG_NAME=’STUDENTS’ AND OPERATION=’DELETE’;

SPOOL OFF;

Stop Log Miner.

BEGIN DBMS_LOGMNR.END_LOGMNR;

END;

/

Open C:LOGMNR.TXT and look for DELETE statements. If you do not locate the problematic query,repeat the step 2 (above) with another archive log file, and keep repeating it until you caught the query and theuser that has performed the DELETE operation.

Note: V$LOGMNR_CONTENTS has many columns, and you can customize your results by modifying the abovequery.

Read more

Restoring an RMAN Backup to Another Node /Clone Database

In certain circumstances, it may be desirable to restore a database from an RMAN backup onto a machine other than the original host. 
For example,Restore and recover data at a given point in time for UAT/Testing, or to duplicate a production instance.
The example assumes:
the target database is on host A
the database is to be restored onto host B
the directory structure of host B is different to host A
the ORACLE_SID will not change for the restored database
a recovery catalog is being used
the backups were carried out to disk (for illustrative purposes, and to disassociate from any media manager specific issues)

The following steps are required:
1. backup the target on host A
2. list the datafile locations on host A
3. make the backup available to host B
4. make a copy of the init.ora available to host B
5. edit the init.ora to reflect directory structure changes
6. configure SQL*Net connectivity from host to the recovery catalog and duplicated database
7. set up a password file for the duplicated database
8. startup nomount the duplicated database
9. RMAN restore the controlfile(s)
10. mount the database
11. restore and rename the datafiles
12. recover and open the database
These steps are expanded further below.

1.Backup the Target on Host A
The target database needs to be backed up using RMAN. The following  commands of RMAN doing an online database backup. In this example, the backup sets are written to disk.

RMAN>
run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
#backup the whole db
backup tag whole_database_open format '/oracle/backups/df_%u' database;
# switch out of the current logfile
sql 'alter system archive log current';
#backup the archived logs
backup archivelog all format '/oracle/backups/al_%u';
# backup a copy of the controlfile that contains records for the other backups just made
backup current controlfile tag = cf1 format '/oracle/backups/cf_%u';
}

2.List Datafile Locations on Host A
The datafile numbers and location on host A are required. These datafile locations will change on host B 
select file#, name from v$datafile;
file#   name
-----   -----------------------------------------------------------
1       /oracle/OFA_base/u01/oradata/TEST/system01.dbf
2       /oracle/OFA_base/u01/oradata/TEST/rbs01.dbf
3       /oracle/OFA_base/u01/oradata/TEST/temp01.dbf
4       /oracle/OFA_base/u01/oradata/TEST/tools01.dbf
5       /oracle/OFA_base/u01/oradata/TEST/users01.dbf
6       /oracle/OFA_base/u01/oradata/TEST/users02.dbf
7       /oracle/OFA_base/u01/oradata/TEST/rbs02.dbf
8       /oracle/OFA_base/u01/oradata/TEST/rcvcat.dbf

The log file names should also be recorded
select group#, member from v$logfile;
group#    member
---------   -----------------------------------------------------------
1            /oracle/OFA_base/u01/oradata/TEST/redo01.log
2            /oracle/OFA_base/u01/oradata/TEST/redo02.log
3            /oracle/OFA_base/u01/oradata/TEST/redo03.log

3 Make the Backups Available to Host B
3.1 Disk Backups
During restore, RMAN will expect the backup sets to be located in the same directory as written to during the backup. For disk backups, the DBA can accomplish this in many ways: set up an NFS directory, mounted on both host A and host B create the same directory structure on host A and host B, use of symbolic links on host B,FTP backup to host B.
If backup directory is not same at HOST B then catalog backup files using RMAN.
3.2 Tape Backups
The media management software must be configured such that host B is a media manager client, and can read the backup sets. The media management vendor should be consulted for support on this issue.

4. and 5. init.ora on host B
The "init.ora" needs to be made available on host B. Any location specific parameters must be ammended. For example,
*_dump_dest
log_archive_dest*
control_files

6. SQL*Net configuration
If running rman from host A:
a. connectivity to the catalog remains unchanged
b. configure tnsnames.ora on host A to connect to duplicated db on host B configure listener.ora on host B to accept connections for duplicated database
If running rman from host B:
a. configure tnsnames.ora on host B to connect to catalog listener.ora on catalog host remains unchanged
b. configure tnsnames.ora on host B to connect to duplicated db on host B configure listener.ora on host B to accept connections for duplicated database
If running rman from host C (ie, neither host A or host B):
a. connectivity to the catalog remains unchanged
b. configure tnsnames.ora on host C to connect to duplicated db on host B configure listener.ora on host B to accept connections for duplicated database

7. Setup Password File
In order to allow RMAN remote connections, a password file must be setup for the duplicated database. 
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=password1

8. Startup nomount the database
SVRMGR> startup nomount pfile=<location of init.ora>

9. RMAN restore the controlfile(s)
Restore the controlfile(s). 
RMAN>
run{
allocate channel c1 type disk;
restore controlfile;
}

10. Mount the database
SVRMGR> alter database mount;

11. Rename and restore the datafiles
Rename and Restore the Files, and perform database recovery. RMAN can be used to change the location of the datafiles from the location on host A (see Section 2) to the new location on host B.
RMAN>
run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
set newname for datafile 1 to '/oracle/datafiles/system01.dbf';
set newname for datafile 2 to '/oracle/datafiles/rbs01.dbf';
set newname for datafile 3 to '/oracle/datafiles/temp01.dbf';
set newname for datafile 4 to '/oracle/datafiles/tools01.dbf';
set newname for datafile 5 to '/oracle/datafiles/users01.dbf';
set newname for datafile 6 to '/oracle/datafiles/users02.dbf';
set newname for datafile 7 to '/oracle/datafiles/rbs02.dbf';
set newname for datafile 8 to '/oracle/datafiles/rcvcat.dbf';
restore database;
switch datafile all;
}


12. Recover and open the database
Perform incomplete recovery:
SVRMGR> recover database using backup controlfile until cancel;
Forward the database applying archived redo log files until you decide to stop recovery by typing cancel at the prompt (assuming that you have required archived redo log files in the log_archive_dest directory). You may archive the source database redo log files and apply them at the target database if required.
SVRMGR> alter database open resetlogs;
Note: this will create the online redo logs in the same location as that on host A. If this directory location does not exist, then this will fail with:
ora344 : unable to recreate online log <name>
The workaround is to rename the logfiles prior to opening the database:
SVRMGR> alter database rename file '<host A location>' to '<host B location>';
Alternatively, the logfile groups can be dropped and recreated. 

Read more

Full Database Recovery or Disaster Recovery.

You use the RESTORE and RECOVER commands to restore and recover the database.RMAN checks last good backup set and restore the datafiles to the state they were in when that backup set was created. When restoring database files with RMAN, it reads the datafile header and makes the determination as to whether the file needs to be restored.
 
If you cannot restore datafiles to their default locations, then you must update the control file to reflect the new locations of the datafiles. Use the RMAN SET NEWNAME command within a RUN command to specify the new filename. Afterward, use a SWITCH command, which is equivalent to using the SQL statement ALTER DATABASE RENAME FILE, to update the names of the datafiles in the control file. SWITCH DATAFILE ALL updates the control file to reflect the new names for all datafiles for which a SET NEWNAME has been issued in a RUN command.(Restore files to another location will  be demonstrated in another post)

The recovery is done by allocating a channel for I/O and then issuing the RMAN restore database command. The database must be in MOUNT state when restoring or recovering the entire database.

SQL> startup mount;
   or
RMAN> startup force mount;

Then connect to rman and perform:
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open;

Another option with controlfile restoration
SET DBID <database_id>; # use database id from RMAN, not required if using recovery catalog
CONNECT TARGET <target_connect_string>;
STARTUP NOMOUNT;
RUN
{
# You need to allocate channels if not using recovery catalog.
ALLOCATE CHANNEL CH1 TYPE DISK;
# Optionally you can use SET NEWNAME and SWITCH commands to restore datafiles to a new location.
RESTORE CONTROLFILE FROM AUTOBACKUP;
ALTER DATABASE MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;

Another Method: Restore Spfile and Controlfile
1 Assuming that we have a full backup under /bkp
2 Start RMAN
$ rman target / nocatalog
3 Start the DB (nomount) and restore the pfile
RMAN> set DBID=248804095;
startup nomount;
run
{
restore spfile to pfile '/oracle/product/10.2.0/dbs/initTEST.ora'  from'/bkp/c2488040952009013004';
shutdown immediate;
}
4 Start the DB with the pfile that we just restore:
RMAN> set DBID=248804095;
RMAN> startup nomount pfile = '/oracle/product/10.2.0/dbs/initTEST.ora';
5 Restore the control files
run
{
restore controlfile from '/bkp/c2488040952009013004';
alter database mount;
}
6 Restore the data files
run
{
restore database;
recover database;
}
7 Start the DB
RMAN> alter database open resetlogs;

Use RESETLOGS after a point in time recovery  or recover a database using a backup of the control file. RESETLOGS will initialize the logs, reset your log sequence number, and start a new "incarnation" of the database. 

During the recovery, RMAN automatically restores backups of any needed archived redo logs. If  RMAN restores archived redo logs to the flash recovery area during a recovery, then it automatically deletes the restored logs after applying them to the datafiles. Otherwise, you can use the DELETE ARCHIVELOG command to delete restored archived redo logs from disk when they are no longer needed for recovery. For example, you can enter the following command: RECOVER DATABASE DELETE ARCHIVELOG;

Read more