Recovering database until time

Example of recovery database until time.


For checking after recovery I am going to create simple table with one row

 SQL> create user ulfet identified by ulfet;  
 User created.  
 SQL> grant dba to ulfet;  
 Grant succeeded.  
 SQL> conn ulfet/ulfet  
 Connected.  
 SSQL> create table test1 (id number);  
 Table created.  
 SQL> insert into test1 values(1);  
 1 row created.  
 SQL> commit;  
 Commit complete.

Now taking backup.

RMAN> backup database plus archivelog;

--note date

 SQL> select systimestamp from dual;  
 SYSTIMESTAMP  
 ---------------------------------------------------------------------------  
 13-JAN-16 11.03.34.646076 AM +04:00  
 --dropping table  
 SQL> drop table ulfet.test1;  
 Table dropped.  
 SQL> select systimestamp from dual;  
 SYSTIMESTAMP  
 ---------------------------------------------------------------------------  
 13-JAN-16 11.04.40.428868 AM +04:00  
 SQL>  

--start recovery process

--firstly start database in nomount mode for restoring controlfile

RMAN> startup nomount  
 connected to target database (not started)  
 Oracle instance started  
 Total System Global Area   523108352 bytes  
 Fixed Size           1337632 bytes  
 Variable Size        314574560 bytes  
 Database Buffers       201326592 bytes  
 Redo Buffers          5869568 bytes  
 RMAN>   

--drop control files

[oracle@dbserver oradata]$ cd mydb/  
 [oracle@dbserver mydb]$ ls  
 control01.ctl redo01.log redo03.log  system01.dbf undotbs01.dbf  
 example01.dbf redo02.log sysaux01.dbf temp01.dbf  users01.dbf  
 [oracle@dbserver mydb]$ pwd  
 /u01/app/oracle/oradata/mydb  
 [oracle@dbserver mydb]$ rm control01.ctl   
 [oracle@dbserver mydb]$ pwd  
 /u01/app/oracle/oradata/mydb  
 [oracle@dbserver mydb]$ cd ../../flash_recovery_area/  
 mydb/ MYDB/   
 [oracle@dbserver mydb]$ cd ../../flash_recovery_area/mydb/  
 [oracle@dbserver mydb]$ rm control02.ctl   
 [oracle@dbserver mydb]$   

--Restoring controlfile from autoback;

RMAN> restore controlfile from autobackup;  
 Starting restore at 13-JAN-16  
 allocated channel: ORA_DISK_1  
 channel ORA_DISK_1: SID=20 device type=DISK  
 recovery area destination: /u01/app/oracle/flash_recovery_area  
 database name (or database unique name) used for search: MYDB  
 channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/flash_recovery_area/MYDB/autobackup/2016_01_13/o1_mf_s_901017408_c9cw425g_.bkp found in the recovery area  
 channel ORA_DISK_1: looking for AUTOBACKUP on day: 20160113  
 channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/flash_recovery_area/MYDB/autobackup/2016_01_13/o1_mf_s_901017408_c9cw425g_.bkp  
 channel ORA_DISK_1: control file restore from AUTOBACKUP complete  
 output file name=/u01/app/oracle/oradata/mydb/control01.ctl  
 output file name=/u01/app/oracle/flash_recovery_area/mydb/control02.ctl  
 Finished restore at 13-JAN-16  

check if you want :)

[oracle@dbserver mydb]$ pwd  
 /u01/app/oracle/oradata/mydb  
 [oracle@dbserver mydb]$ ls control*  
 control01.ctl   
 [oracle@dbserver mydb]$ ls ../../flash_recovery_area/mydb/  
 control02.ctl  

alter database in mount mode for restore and recovery process

 RMAN> alter database mount;  
 database mounted  
 released channel: ORA_DISK_1  
 RMAN>   

--write commands for recovery process

 RMAN> run  
 {  
 set newname for database to '/u01/app/oracle/oradata/mydb/%b';  
 set until time "to_date('2016-01-13 11:03:34', 'yyyy-mm-dd hh24:mi:ss')";  
 restore database;  
 switch datafile all;  
 switch tempfile all;  
 recover database;  
 }2> 3> 4> 5> 6> 7> 8> 9>   
 executing command: SET NEWNAME  
 executing command: SET until clause  
 Starting restore at 13-JAN-16  
 Starting implicit crosscheck backup at 13-JAN-16  
 allocated channel: ORA_DISK_1  
 channel ORA_DISK_1: SID=20 device type=DISK  
 Crosschecked 9 objects  
 Finished implicit crosscheck backup at 13-JAN-16  
 Starting implicit crosscheck copy at 13-JAN-16  
 using channel ORA_DISK_1  
 Finished implicit crosscheck copy at 13-JAN-16  
 searching for all files in the recovery area  
 cataloging files...  
 cataloging done  
 List of Cataloged Files  
 =======================  
 File Name: /u01/app/oracle/flash_recovery_area/MYDB/autobackup/2016_01_13/o1_mf_s_901017408_c9cw425g_.bkp  
 File Name: /u01/app/oracle/flash_recovery_area/MYDB/autobackup/2016_01_13/o1_mf_s_901018889_c9cxlcbt_.bkp  
 using channel ORA_DISK_1  
 channel ORA_DISK_1: starting datafile backup set restore  
 channel ORA_DISK_1: specifying datafile(s) to restore from backup set  
 channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/mydb/system01.dbf  
 channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/mydb/sysaux01.dbf  
 channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/mydb/undotbs01.dbf  
 channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/mydb/users01.dbf  
 channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/mydb/example01.dbf  
 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/MYDB/backupset/2016_01_13/o1_mf_nnndf_TAG20160113T110002_c9cxhmmo_.bkp  
 channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/MYDB/backupset/2016_01_13/o1_mf_nnndf_TAG20160113T110002_c9cxhmmo_.bkp tag=TAG20160113T110002  
 channel ORA_DISK_1: restored backup piece 1  
 channel ORA_DISK_1: restore complete, elapsed time: 00:01:56  
 Finished restore at 13-JAN-16  
 Starting recover at 13-JAN-16  
 using channel ORA_DISK_1  
 starting media recovery  
 archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/flash_recovery_area/MYDB/archivelog/2016_01_13/o1_mf_1_2_c9cxl882_.arc  
 archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/oradata/mydb/redo03.log  
 archived log file name=/u01/app/oracle/flash_recovery_area/MYDB/archivelog/2016_01_13/o1_mf_1_2_c9cxl882_.arc thread=1 sequence=2  
 archived log file name=/u01/app/oracle/oradata/mydb/redo03.log thread=1 sequence=3  
 media recovery complete, elapsed time: 00:00:02  
 Finished recover at 13-JAN-16  

I have forgotten writing open table in resetlogs in end of command.
Now you can see, out database`s state is mounted, open it with resetlogs

SQL> conn /as sysdba  
 Connected.  
 SQL> select open_mode from v$database;  
 OPEN_MODE  
 --------------------  
 MOUNTED  
 SQL> alter database open resetlogs;  
 Database altered.  

--Now try to check

SQL> conn ulfet/ulfet  
 Connected.  
 SQL> select * from tab;  
 TNAME             TABTYPE CLUSTERID  
 ------------------------------ ------- ----------  
 TEST1             TABLE  
 SQL> select * from test1;  
     ID  
 ----------  
      1  
 SQL>   

That`s all

How to fix ORA-01130: database file version 11.1.0.0.0 incompatible with ORACLE version 11.0.0.0.0

Today while duplication database I faced strange error:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/09/2013 16:02:16
RMAN-06136: ORACLE error from auxiliary database: ORA-01503: CREATE CONTROLFILE failed
ORA-01130: database file version 11.1.0.0.0 incompatible with ORACLE version 11.0.0.0.0
ORA-01110: data file 1: '/u01/app/oracle/oradata/DUPLPROD/system01.dbf'

I checked all actions and tried again, but result was same. Error happened creation control file. Let me note that I used to duplication on the same host and Oracle version 11.1.0.7.0

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for IBM/AIX RISC System/6000: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
SQL>

SQL> show parameter compatible

NAME          TYPE   VALUE
-----------   ------   ----------
compatible    string   11.1.0.0.0
SQL>

I just added below line on duplication DB`s init file:

compatible='11.1.0'


Here is step by step creation duplication:

My production db is PROD and planning to create duplication from action database without backup and name will be DUPLPROD.

So, firstly
create listener, tns for duplication.

listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = PROD)
      (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
      (SID_NAME = PROD)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = DUPLPROD)
      (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
      (SID_NAME = DUPLPROD)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxxxx)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

tnsname.ora

PROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxx)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PROD)
    )
  )

DUPLPROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxx)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DUPLPROD)
    )
  )

create folders : ../oradata/DUPLPROD ../flashback/DUPLPROD etc


create init file (I created it under dbs)

create password file:

cd $ORACLE_HOME/dbs

orapwd password=oracle file=orapwDUPLPROD

file_name : initDUPLPROD.ora

DB_NAME=DUPLPROD
diagnostic_dest='/u01/app/oracle/admin/DUPLPROD'
DB_FILE_NAME_CONVERT=('/u01/app/oracle/oradata/PREPROD','/u01/app/oracle/oradata/DUPLPROD')
LOG_FILE_NAME_CONVERT=('/u01/app/oracle/oradata/PREPROD','/u01/app/oracle/oradata/DUPLPROD')
compatible='11.1.0'

Now, start DUPLPROD in nomount mode using pfile.

export ORACLE_SID=DUPLPROD
sqlplus "/as sysdba"

SQL> startup nomount pfile=/u01/app/oracle/product/11.1.0/db_1/dbs/initDUPLPROD.ora
ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size                  2152328 bytes
Variable Size             159385720 bytes
Database Buffers           50331648 bytes
Redo Buffers                5287936 bytes
SQL>



Test connectivity to auxiliary and target instance from the Both Sides.

sqlplus "sys/oracle@PREPROD" as sysdba

sqlplus sys/oracle@DUPLPROD as sysdba


Now connect to RMAN and make duplication:

-bash-3.00$ rman target sys/oracle@PREPROD auxiliary sys/oracle@DUPLPROD

or

-bash-3.00$ rman target sys/oracle@PREPROD

Recovery Manager: Release 11.1.0.7.0 - Production on Tue Jul 9 15:07:49 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: PREPROD (DBID=628957311)

RMAN> connect auxiliary sys/oracle@DUPLPROD

connected to auxiliary database: DUPLPROD (not mounted)

RMAN>


run
{
DUPLICATE TARGET DATABASE TO 'DUPLPROD' FROM ACTIVE DATABASE
NOFILENAMECHECK;
}