Views

Showing posts with label Restore. Show all posts
Showing posts with label Restore. Show all posts

Monday, September 25, 2017

Oracle Backup and Restore Case Scenarios - Case Study 6 (Magic Restore)

Oracle Backup and Restore Case Scenarios - Case Study 6

Case 6: Magic Restore: Recover a lost datafile without backup

If a datafile has been dropped mistakenly before the backup, it can be brought back. RMAN restores it from online log and archive log. This is done through RMAN new feature 
RMAN Automatic  Datafile Creation.

SQL> select file_id, file_name ,tablespace_name,AUTOEXTENSIBLE,status  from dba_data_files order by file_id;

FILE_ID FILE_NAME                        TABLESPACE_NAME    AUT STATUS
-------- ------------------------------------------------- ------------------ --- ----
1  /u01/app/oracle/oradata/ORADB1/system01.dbf       SYSTEM   YES AVAILABLE
2  /u01/app/oracle/oradata/ORADB1/sysaux01.dbf       SYSAUX   YES AVAILABLE
3  /u01/app/oracle/oradata/ORADB1/undotbs01.dbf      UNDOTBS1 YES AVAILABLE
4  /u01/app/oracle/oradata/ORADB1/users01.dbf        USERS    YES AVAILABLE
5  /u01/app/oracle/oradata/ORADB1/example01.dbf      EXAMPLE  YES AVAILABLE


CREATE NEW TABLESPACE tbs_restore

SQL> CREATE TABLESPACE tbs_restore DATAFILE '/u01/app/oracle/oradata/ORADB1/tbs_restore_01.dbf' SIZE 20M ONLINE;

Tablespace created.



TAKE RMAN FULL BACKUP OF DATABASE

[oracle@localhost ORADB1]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Sep 25 14:56:29 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORADB1 (DBID=2294695355)

RMAN> backup database plus archivelog;


Starting backup at 25-SEP-2017 14:56:40
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=48 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=13 RECID=1 STAMP=955328309
input archived log thread=1 sequence=14 RECID=2 STAMP=955328309
input archived log thread=1 sequence=15 RECID=3 STAMP=955328320
input archived log thread=1 sequence=16 RECID=4 STAMP=955383829
input archived log thread=1 sequence=17 RECID=5 STAMP=955383888
input archived log thread=1 sequence=18 RECID=13 STAMP=955400510
input archived log thread=1 sequence=19 RECID=11 STAMP=955400510
input archived log thread=1 sequence=20 RECID=12 STAMP=955400510
channel ORA_DISK_1: starting piece 1 at 25-SEP-2017 14:56:41
channel ORA_DISK_2: starting compressed archived log backup set
channel ORA_DISK_2: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=14 STAMP=955466468
input archived log thread=1 sequence=2 RECID=15 STAMP=955541254
input archived log thread=1 sequence=3 RECID=16 STAMP=955552300
channel ORA_DISK_2: starting piece 1 at 25-SEP-2017 14:56:41
channel ORA_DISK_1: finished piece 1 at 25-SEP-2017 14:56:48
piece handle=/u01/app/oracle/fast_recovery_area/ORADB1/backupset/2017_09_25/o1_mf_annnn_TAG20170925T145641_dwklykl7_.bkp tag=TAG20170925T145641 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=17 STAMP=955637365
input archived log thread=1 sequence=5 RECID=18 STAMP=955637511
input archived log thread=1 sequence=6 RECID=19 STAMP=955637569
input archived log thread=1 sequence=7 RECID=20 STAMP=955637800
channel ORA_DISK_1: starting piece 1 at 25-SEP-2017 14:56:48
channel ORA_DISK_2: finished piece 1 at 25-SEP-2017 14:56:48
piece handle=/u01/app/oracle/fast_recovery_area/ORADB1/backupset/2017_09_25/o1_mf_annnn_TAG20170925T145641_dwklykmf_.bkp tag=TAG20170925T145641 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: finished piece 1 at 25-SEP-2017 14:56:49
piece handle=/u01/app/oracle/fast_recovery_area/ORADB1/backupset/2017_09_25/o1_mf_annnn_TAG20170925T145641_dwklyrq7_.bkp tag=TAG20170925T145641 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 25-SEP-2017 14:56:49

Starting backup at 25-SEP-2017 14:56:49
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ORADB1/system01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ORADB1/users01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/ORADB1/example01.dbf
channel ORA_DISK_1: starting piece 1 at 25-SEP-2017 14:56:50
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/oradata/ORADB1/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ORADB1/undotbs01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/ORADB1/tbs_restore_01.dbf
channel ORA_DISK_2: starting piece 1 at 25-SEP-2017 14:56:50
channel ORA_DISK_2: finished piece 1 at 25-SEP-2017 14:57:45
piece handle=/u01/app/oracle/fast_recovery_area/ORADB1/backupset/2017_09_25/o1_mf_nnndf_TAG20170925T145649_dwklywhn_.bkp tag=TAG20170925T145649 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:55
channel ORA_DISK_1: finished piece 1 at 25-SEP-2017 14:58:05
piece handle=/u01/app/oracle/fast_recovery_area/ORADB1/backupset/2017_09_25/o1_mf_nnndf_TAG20170925T145649_dwklyt3t_.bkp tag=TAG20170925T145649 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
Finished backup at 25-SEP-2017 14:58:05

Starting backup at 25-SEP-2017 14:58:05
current log archived
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=21 STAMP=955637885
channel ORA_DISK_1: starting piece 1 at 25-SEP-2017 14:58:05
channel ORA_DISK_1: finished piece 1 at 25-SEP-2017 14:58:06
piece handle=/u01/app/oracle/fast_recovery_area/ORADB1/backupset/2017_09_25/o1_mf_annnn_TAG20170925T145805_dwkm15dy_.bkp tag=TAG20170925T145805 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 25-SEP-2017 14:58:06

Starting Control File and SPFILE Autobackup at 25-SEP-2017 14:58:06
piece handle=/u01/backup/rman/ctl_c-2294695355-20170925-01 comment=NONE
Finished Control File and SPFILE Autobackup at 25-SEP-2017 14:58:07

RMAN>


Add Datafile to new tablespace tbs_restore

SQL> ALTER TABLESPACE tbs_restore ADD DATAFILE '/u01/app/oracle/oradata/ORADB1/tbs_restore_02.dbf' SIZE 20M AUTOEXTEND ON;

Tablespace altered.

Simulate Failure by removing datafiles for tbs_resore tablespace from disk

[oracle@localhost ORADB1]$ ls -ltr tbs_restore*
-rw-r-----. 1 oracle oinstall 20979712 Sep 25 15:02 tbs_restore_01.dbf
-rw-r-----. 1 oracle oinstall 20979712 Sep 25 15:02 tbs_restore_02.dbf
[oracle@localhost ORADB1]$ rm tbs_restore*
[oracle@localhost ORADB1]$ ls -ltr tbs_restore*
ls: cannot access tbs_restore*: No such file or directory

Bring the tablespace offline.

SQL> alter tablespace tbs_restore offline ;
alter tablespace tbs_restore offline
*
ERROR at line 1:
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/u01/app/oracle/oradata/ORADB1/tbs_restore_01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Tablespace wont go offline as datafiles have been dropped. Make it offline using IMMEDIATE clause.

SQL> alter tablespace tbs_restore offline immediate;

Tablespace altered.

Restore datafile 6 (tbs_restore_01.dbf) which was backed up.

RMAN> restore datafile 6;

Starting restore at 25-SEP-2017 15:05:30
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=46 device type=DISK

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 00006 to /u01/app/oracle/oradata/ORADB1/tbs_restore_01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORADB1/backupset/2017_09_25/o1_mf_nnndf_TAG20170925T145649_dwklywhn_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORADB1/backupset/2017_09_25/o1_mf_nnndf_TAG20170925T145649_dwklywhn_.bkp tag=TAG20170925T145649
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 25-SEP-2017 15:05:32


Restore the tablespace tbs_restore.
You can see that datafile 7 which was not backed up has been restored.
First is preview session to see when RMAN is creating the datafile not backed up.
(Preview output is captured from different session, so timings are different.)
This RMAN feature was introduced in 10g and enhanced in 12c

RMAN> restore tablespace tbs_restore preview;

Starting restore at 25-SEP-2017 15:56:07
using channel ORA_DISK_1
using channel ORA_DISK_2

datafile 7 will be created automatically during restore operation

List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ --------------------
28      Full    81.22M     DISK        00:00:29     25-SEP-2017 15:45:39
        BP Key: 28   Status: AVAILABLE  Compressed: YES  Tag: TAG20170925T154538
        Piece Name: /u01/app/oracle/fast_recovery_area/ORADB1/backupset/2017_09_25/o1_mf_nnndf_TAG20170925T154538_dwkotbyx_.bkp
  List of Datafiles in backup set 28
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  6       Full 1298356    25-SEP-2017 15:45:38 /u01/app/oracle/oradata/ORADB1/tbs_restore_01.dbf

List of Archived Log Copies for database with db_unique_name ORADB1
=====================================================================

Key     Thrd Seq     S Low Time           
------- ---- ------- - --------------------
23      1    10      A 25-SEP-2017 15:45:29
        Name: /u01/app/oracle/oradata/ORADB1/archive/1_10_955400510.dbf

Media recovery start SCN is 1298356
Recovery must be done beyond SCN 1298686 to clear datafile fuzziness
Finished restore at 25-SEP-2017 15:56:07

RMAN>

RMAN> restore tablespace tbs_restore;

Starting restore at 25-SEP-2017 15:06:04
using channel ORA_DISK_1
using channel ORA_DISK_2

creating datafile file number=7 name=/u01/app/oracle/oradata/ORADB1/tbs_restore_02.dbf
skipping datafile 6; already restored to file /u01/app/oracle/oradata/ORADB1/tbs_restore_01.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 25-SEP-2017 15:06:04

Recover tablespace tbs_store.

RMAN> RECOVER TABLESPACE tbs_restore;

Starting recover at 25-SEP-2017 15:06:30
using channel ORA_DISK_1
using channel ORA_DISK_2

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 25-SEP-2017 15:06:31

RMAN>

Make the tablespace online.

SQL> alter tablespace tbs_restore online;

Tablespace altered.

Check and verify whether both datafiles are restored back.

SQL> select file_id, file_name ,tablespace_name,AUTOEXTENSIBLE,status  from dba_data_files order by file_id;

FILE_ID FILE_NAME                        TABLESPACE_NAME    AUT STATUS
-------- ------------------------------------------------- ------------------ --- ----
1  /u01/app/oracle/oradata/ORADB1/system01.dbf        SYSTEM      YES AVAILABLE
2  /u01/app/oracle/oradata/ORADB1/sysaux01.dbf        SYSAUX      YES AVAILABLE
3  /u01/app/oracle/oradata/ORADB1/undotbs01.dbf       UNDOTBS1    YES AVAILABLE
4  /u01/app/oracle/oradata/ORADB1/users01.dbf         USERS       YES AVAILABLE
5  /u01/app/oracle/oradata/ORADB1/example01.dbf       EXAMPLE     YES AVAILABLE
6  /u01/app/oracle/oradata/ORADB1/tbs_restore_01.dbf  TBS_RESTORE NO  AVAILABLE
7  /u01/app/oracle/oradata/ORADB1/tbs_restore_02.dbf  TBS_RESTORE YES AVAILABLE
Posted by at No comments:
Labels: , , , ,

Sunday, September 24, 2017

Oracle Backup and Restore Case Scenarios - Case Study 5

Oracle Backup and Restore Case Scenarios - Case Study 5

Case 5: Duplicating an Active Database without Backups

Goal: Create a database DUPDB1 by duplicating the active database ORADB1 on same HOST without taking any backup of source database.

Steps followed:
1.       Create password file for duplicate db
2.       Add entries for duplicate db in listener.ora file 
3.       Add entries for duplicate db in tnsnames.ora file 
4.       Create the directories which are required for the duplicate database.
5.       Create pfile with just one parameter db_name
6.       Start the duplicate database in nomount mode.
7.       Test connectivity to auxiliary and target instance from the Both Sides.
8.       Start up RMAN, and connect to the target database
9.       Connect to the duplicate database in auxiliary mode.
10.   Issue the duplicate target database command to start the database duplication process
11.   Remove the old pfile since it contains only one parameter db_name.
12.    Check and Verify the new duplicate database.

Source configuration
db name - ORADB1
log_archive_dest_1 - /u01/app/oracle/oradata/ORADB1/archive
admin directory - /u01/app/oracle/admin/ORADB1
log file location - /u01/app/oracle/oradata/ORADB1

Target configuration
db name - DUPDB1
log_archive_dest_1 - /u01/app/oracle/oradata/DUPDB1/archive
admin directory - /u01/app/oracle/admin/DUPDB1
log file location - /u01/app/oracle/oradata/DUPDB1

Create password file for duplicate db

[oracle@localhost ORADB1]$ cd $ORACLE_HOME/dbs
[oracle@localhost dbs]$ orapwd password=oracle file=orapwDUPDB1


Add following entries into listener.ora file.

Edit the listener.ora and add following lines:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = ORADB1)
    )
    (SID_DESC =
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = DUPDB1)
    )
  )

[oracle@localhost admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

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

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = ORADB1)
    )
    (SID_DESC =
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = DUPDB1)
    )
  )
ADR_BASE_LISTENER = /u01/app/oracle

Add the Following entry in the tnsnames.ora


ORADB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORADB1)
    )
  )

DUPDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DUPDB1)
    )
  )


Create the directories which are required for the duplicate database.

[oracle@localhost admin]$ mkdir -p /u01/app/oracle/oradata/DUPDB1/archive
[oracle@localhost admin]$ mkdir -p /u01/app/oracle/fast_recovery_area/DUPDB1
[oracle@localhost admin]$ mkdir -p /u01/app/oracle/admin/DUPDB1/pfile
[oracle@localhost ORADB1]$ mkdir -p /u01/app/oracle/admin/DUPDB1/adump
[oracle@localhost ORADB1]$ mkdir -p /u01/app/oracle/admin/DUPDB1/dpdump

Create pfile with just one parameter db_name

[oracle@localhost ORADB1]$ cd /u01/app/oracle/admin/DUPDB1/pfile
[oracle@localhost pfile]$ vi initDUPDB1.ora
[oracle@localhost pfile]$ cat initDUPDB1.ora db_name=DUPDB1

Start the duplicate database in nomount mode.

[oracle@localhost pfile]$ export ORACLE_SID=DUPDB1
[oracle@localhost pfile]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Sep 24 13:18:33 2017

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

Connected to an idle instance.

SQL> startup nomount pfile=/u01/app/oracle/admin/DUPDB1/pfile/initDUPDB1.ora;
ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size               2251816 bytes
Variable Size          159384536 bytes
Database Buffers        50331648 bytes
Redo Buffers             5189632 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Test connectivity to auxiliary and target instance.

[oracle@localhost pfile]$ sqlplus sys/oracle@ORADB1 as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Sep 24 13:20:28 2017

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn sys/oracle@DUPDB1 as sysdba
Connected.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost pfile]$

Start up RMAN, and connect to the target database
Connect to the duplicate database in auxiliary mode.

[oracle@localhost ORADB1]$ rman target sys/oracle@ORADB1 auxiliary sys/oracle@DUPDB1

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Sep 24 15:09:02 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORADB1 (DBID=2294695355)
connected to auxiliary database: DUPDB1 (not mounted)

Issue the duplicate target database command in run block to start the database duplication process.

RMAN> run {
duplicate target database to 'DUPDB1' from active database
SPFILE
    parameter_value_convert ('ORADB1','DUPDB1')
    set db_file_name_convert='/u01/app/oracle/oradata/ORADB1/','/u01/app/oracle/oradata/DUPDB1/'
    set log_file_name_convert='/u01/app/oracle/oradata/ORADB1/','/u01/app/oracle/oradata/DUPDB1/'
    set db_name='DUPDB1'
       set memory_target='400m'
    set log_archive_dest_1='location=/u01/app/oracle/oradata/DUPDB1/archive'
NOFILENAMECHECK
;
}
2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12>
Starting Duplicate Db at 24-SEP-2017 15:09:15
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=20 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/11.2.0/db_1/dbs/spfileORADB1.ora' auxiliary format
 '/u01/app/oracle/product/11.2.0/db_1/dbs/spfileDUPDB1.ora'   ;
   sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/db_1/dbs/spfileDUPDB1.ora''";
}
executing Memory Script

Starting backup at 24-SEP-2017 15:09:16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=40 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=45 device type=DISK
Finished backup at 24-SEP-2017 15:09:18

sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/db_1/dbs/spfileDUPDB1.ora''

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''DUPDB1'' comment=
 ''duplicate'' scope=spfile";
   sql clone "alter system set  audit_file_dest =
 ''/u01/app/oracle/admin/DUPDB1/adump'' comment=
 '''' scope=spfile";
   sql clone "alter system set  control_files =
 ''/u01/app/oracle/oradata/DUPDB1/control01.ctl'', ''/u01/app/oracle/fast_recovery_area/DUPDB1/control02.ctl'' comment=
 '''' scope=spfile";
   sql clone "alter system set  dispatchers =
 ''(PROTOCOL=TCP) (SERVICE=DUPDB1XDB)'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_file_name_convert =
 ''/u01/app/oracle/oradata/ORADB1/'', ''/u01/app/oracle/oradata/DUPDB1/'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_file_name_convert =
 ''/u01/app/oracle/oradata/ORADB1/'', ''/u01/app/oracle/oradata/DUPDB1/'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_name =
 ''DUPDB1'' comment=
 '''' scope=spfile";
   sql clone "alter system set  memory_target =
 400m comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_dest_1 =
 ''location=/u01/app/oracle/oradata/DUPDB1/archive'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''DUPDB1'' comment= ''duplicate'' scope=spfile

sql statement: alter system set  audit_file_dest =  ''/u01/app/oracle/admin/DUPDB1/adump'' comment= '''' scope=spfile

sql statement: alter system set  control_files =  ''/u01/app/oracle/oradata/DUPDB1/control01.ctl'', ''/u01/app/oracle/fast_recovery_area/DUPDB1/control02.ctl'' comment= '''' scope=spfile

sql statement: alter system set  dispatchers =  ''(PROTOCOL=TCP) (SERVICE=DUPDB1XDB)'' comment= '''' scope=spfile

sql statement: alter system set  db_file_name_convert =  ''/u01/app/oracle/oradata/ORADB1/'', ''/u01/app/oracle/oradata/DUPDB1/'' comment= '''' scope=spfile

sql statement: alter system set  log_file_name_convert =  ''/u01/app/oracle/oradata/ORADB1/'', ''/u01/app/oracle/oradata/DUPDB1/'' comment= '''' scope=spfile

sql statement: alter system set  db_name =  ''DUPDB1'' comment= '''' scope=spfile

sql statement: alter system set  memory_target =  400m comment= '''' scope=spfile

sql statement: alter system set  log_archive_dest_1 =  ''location=/u01/app/oracle/oradata/DUPDB1/archive'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     417546240 bytes

Fixed Size                     2253824 bytes
Variable Size                331353088 bytes
Database Buffers              79691776 bytes
Redo Buffers                   4247552 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''ORADB1'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''DUPDB1'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  '/u01/app/oracle/oradata/DUPDB1/control01.ctl';
   restore clone controlfile to  '/u01/app/oracle/fast_recovery_area/DUPDB1/control02.ctl' from
 '/u01/app/oracle/oradata/DUPDB1/control01.ctl';
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''ORADB1'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''DUPDB1'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     417546240 bytes

Fixed Size                     2253824 bytes
Variable Size                331353088 bytes
Database Buffers              79691776 bytes
Redo Buffers                   4247552 bytes

Starting backup at 24-SEP-2017 15:09:33
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_ORADB1.f tag=TAG20170924T150933 RECID=1 STAMP=955552174
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 24-SEP-2017 15:09:35

Starting restore at 24-SEP-2017 15:09:35
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=20 device type=DISK

channel ORA_AUX_DISK_2: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 24-SEP-2017 15:09:44

database mounted

contents of Memory Script:
{
   set newname for datafile  1 to
 "/u01/app/oracle/oradata/DUPDB1/system01.dbf";
   set newname for datafile  2 to
 "/u01/app/oracle/oradata/DUPDB1/sysaux01.dbf";
   set newname for datafile  3 to
 "/u01/app/oracle/oradata/DUPDB1/undotbs01.dbf";
   set newname for datafile  4 to
 "/u01/app/oracle/oradata/DUPDB1/users01.dbf";
   set newname for datafile  5 to
 "/u01/app/oracle/oradata/DUPDB1/example01.dbf";
   set newname for datafile  6 to
 "/u01/app/oracle/oradata/DUPDB1/tbs_restore01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
 "/u01/app/oracle/oradata/DUPDB1/system01.dbf"   datafile
 2 auxiliary format
 "/u01/app/oracle/oradata/DUPDB1/sysaux01.dbf"   datafile
 3 auxiliary format
 "/u01/app/oracle/oradata/DUPDB1/undotbs01.dbf"   datafile
 4 auxiliary format
 "/u01/app/oracle/oradata/DUPDB1/users01.dbf"   datafile
 5 auxiliary format
 "/u01/app/oracle/oradata/DUPDB1/example01.dbf"   datafile
 6 auxiliary format
 "/u01/app/oracle/oradata/DUPDB1/tbs_restore01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 24-SEP-2017 15:09:49
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/ORADB1/system01.dbf
channel ORA_DISK_2: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/ORADB1/sysaux01.dbf
output file name=/u01/app/oracle/oradata/DUPDB1/system01.dbf tag=TAG20170924T150949
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/ORADB1/example01.dbf
output file name=/u01/app/oracle/oradata/DUPDB1/sysaux01.dbf tag=TAG20170924T150949
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_2: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/ORADB1/undotbs01.dbf
output file name=/u01/app/oracle/oradata/DUPDB1/undotbs01.dbf tag=TAG20170924T150949
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_2: starting datafile copy
input datafile file number=00006 name=/u01/app/oracle/oradata/ORADB1/tbs_restore01.dbf
output file name=/u01/app/oracle/oradata/DUPDB1/tbs_restore01.dbf tag=TAG20170924T150949
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_2: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/ORADB1/users01.dbf
output file name=/u01/app/oracle/oradata/DUPDB1/users01.dbf tag=TAG20170924T150949
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/oradata/DUPDB1/example01.dbf tag=TAG20170924T150949
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:41
Finished backup at 24-SEP-2017 15:11:35

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "/u01/app/oracle/oradata/ORADB1/archive/1_3_955400510.dbf" auxiliary format
 "/u01/app/oracle/oradata/DUPDB1/archive/1_3_955400510.dbf"   ;
   catalog clone archivelog  "/u01/app/oracle/oradata/DUPDB1/archive/1_3_955400510.dbf";
   switch clone datafile all;
}
executing Memory Script

Starting backup at 24-SEP-2017 15:11:41
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=3 RECID=16 STAMP=955552300
output file name=/u01/app/oracle/oradata/DUPDB1/archive/1_3_955400510.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 24-SEP-2017 15:11:42

cataloged archived log
archived log file name=/u01/app/oracle/oradata/DUPDB1/archive/1_3_955400510.dbf RECID=16 STAMP=955552302

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=955552303 file name=/u01/app/oracle/oradata/DUPDB1/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=955552303 file name=/u01/app/oracle/oradata/DUPDB1/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=955552303 file name=/u01/app/oracle/oradata/DUPDB1/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=955552303 file name=/u01/app/oracle/oradata/DUPDB1/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=5 STAMP=955552303 file name=/u01/app/oracle/oradata/DUPDB1/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=6 STAMP=955552303 file name=/u01/app/oracle/oradata/DUPDB1/tbs_restore01.dbf

contents of Memory Script:
{
   set until scn  1267577;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 24-SEP-2017 15:11:43
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2

starting media recovery

archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/oradata/DUPDB1/archive/1_3_955400510.dbf
archived log file name=/u01/app/oracle/oradata/DUPDB1/archive/1_3_955400510.dbf thread=1 sequence=3
media recovery complete, elapsed time: 00:00:01
Finished recover at 24-SEP-2017 15:11:47
Oracle instance started

Total System Global Area     417546240 bytes

Fixed Size                     2253824 bytes
Variable Size                331353088 bytes
Database Buffers              79691776 bytes
Redo Buffers                   4247552 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''DUPDB1'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''DUPDB1'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     417546240 bytes

Fixed Size                     2253824 bytes
Variable Size                331353088 bytes
Database Buffers              79691776 bytes
Redo Buffers                   4247552 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUPDB1" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1 ( '/u01/app/oracle/oradata/DUPDB1/redo01.log' ) SIZE 50 M  REUSE,
  GROUP   2 ( '/u01/app/oracle/oradata/DUPDB1/redo02.log' ) SIZE 50 M  REUSE,
  GROUP   3 ( '/u01/app/oracle/oradata/DUPDB1/redo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '/u01/app/oracle/oradata/DUPDB1/system01.dbf'
 CHARACTER SET AL32UTF8


contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u01/app/oracle/oradata/DUPDB1/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/u01/app/oracle/oradata/DUPDB1/sysaux01.dbf",
 "/u01/app/oracle/oradata/DUPDB1/undotbs01.dbf",
 "/u01/app/oracle/oradata/DUPDB1/users01.dbf",
 "/u01/app/oracle/oradata/DUPDB1/example01.dbf",
 "/u01/app/oracle/oradata/DUPDB1/tbs_restore01.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/DUPDB1/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/DUPDB1/sysaux01.dbf RECID=1 STAMP=955552321
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/DUPDB1/undotbs01.dbf RECID=2 STAMP=955552321
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/DUPDB1/users01.dbf RECID=3 STAMP=955552321
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/DUPDB1/example01.dbf RECID=4 STAMP=955552321
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/DUPDB1/tbs_restore01.dbf RECID=5 STAMP=955552321

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=955552321 file name=/u01/app/oracle/oradata/DUPDB1/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=955552321 file name=/u01/app/oracle/oradata/DUPDB1/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=955552321 file name=/u01/app/oracle/oradata/DUPDB1/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=955552321 file name=/u01/app/oracle/oradata/DUPDB1/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=955552321 file name=/u01/app/oracle/oradata/DUPDB1/tbs_restore01.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 24-SEP-2017 15:12:17

RMAN>


Remove the old pfile

rm /u01/app/oracle/admin/DUPDB1/pfile/initDUPDB1.ora

Check and Verify the new duplicate database.

[oracle@localhost ORADB1]$ sqlplus sys/oracle@dupdb1 as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Sep 24 15:20:07 2017

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter spfile

NAME                            TYPE     VALUE
------------------------------------ ----------- ------------------------------
spfile                          string   /u01/app/oracle/product/11.2.0
                                          /db_1/dbs/spfileDUPDB1.ora

SQL> select name, open_mode, log_mode from v$database;

NAME     OPEN_MODE          LOG_MODE
--------- -------------------- ------------
DUPDB1   READ WRITE         ARCHIVELOG

SQL> select file_name ,bytes/1024/1024 "MB",tablespace_name,AUTOEXTENSIBLE,status  from dba_data_files;

FILE_NAME                                  MB TABLESPACE_NAME    AUT STATUS
---------------------------------------------- ---------- ------------------ --- ---------
/u01/app/oracle/oradata/DUPDB1/system01.dbf       760 SYSTEM            YES AVAILABLE
/u01/app/oracle/oradata/DUPDB1/sysaux01.dbf       590 SYSAUX            YES AVAILABLE
/u01/app/oracle/oradata/DUPDB1/undotbs01.dbf      110 UNDOTBS1                 YES AVAILABLE
/u01/app/oracle/oradata/DUPDB1/users01.dbf        5 USERS               YES AVAILABLE
/u01/app/oracle/oradata/DUPDB1/example01.dbf      346.25 EXAMPLE        YES AVAILABLE
/u01/app/oracle/oradata/DUPDB1/tbs_restore01.dbf  10 TBS_RESTORE        YES AVAILABLE

6 rows selected.

SQL> select member from v$logfile;

MEMBER
-------------------------------------------
/u01/app/oracle/oradata/DUPDB1/redo03.log
/u01/app/oracle/oradata/DUPDB1/redo02.log
/u01/app/oracle/oradata/DUPDB1/redo01.log

SQL> archive log list
Database log mode           Archive Mode
Automatic archival          Enabled
Archive destination         /u01/app/oracle/oradata/DUPDB1/archive
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence        1
SQL> show parameter recovery_file_des

NAME                         TYPE  VALUE
--------------------------- ------- ------------------------------
db_recovery_file_dest       string /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size      big integer 4182M
SQL>

Please Note down:

*      Both the source and destination databases must be known to Oracle Net.
*      The source database can be mounted or open.
*      If the source database is mounted, you must have shut it down cleanly prior to starting it up in mount mode.
*      If the source database is open, it must be running in archivelog mode.
*      You can continue to use the source database normally while the database duplication is going on.

*      There will be an overhead cost of CPU and network bandwidth consumption for sending the data.
Posted by at No comments:
Labels: , , ,
Subscribe to: Posts (Atom)