How to Clone an Active Database using RMAN - - On same Host

Active Database duplication on SAME HOST

Here, We will have the Same file structure on another Host too

In this article I will show you how to do Duplicate using Active Database duplication.

My Current DATABASE : NIK6

Another one will be     : NIK6_DUP  (Cloned)

Please Follow Below  steps to Duplicate a Production database on another host with different directory structure using Active database Duplication :-

1. Check that primary database is up and running in Archivelog mode :-

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

NAME      OPEN_MODE            LOG_MODE

--------- -------------------- ------------

NIK6     READ WRITE           ARCHIVELOG

2. Add the tns details of target and auxiliary in both server(Target, Auxiliary)

NIK6 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.20.12)(PORT = 1523))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = NIK6.db.com)

    )

  )

NIK6_DUP =

(DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.20.14)(PORT = 1523))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = NIK6.db.com)

    )

  )

3. Add the static entry of Auxiliary instance in listener.ora file

(SID_DESC =

      (GLOBAL_DBNAME = NIK6.db.com)

      (SID_NAME = TEST)

      (ORACLE_HOME = /opt/oracle/product/database/11.2.0.4)

     )

4. On Target host create pfile for Auxiliary Database :-

File Name : initNIK6_DUP.ora

*.audit_file_dest='/ora_backup/TEST/duplicate_test/NIK6/adump'

*.audit_trail='DB'

*.compatible='11.2.0.4.0'

*.control_files='/ora_backup/TEST/duplicate_test/NIK6/control01.ctl','/ora_backup/TEST/duplicate_test/NIK6/control02.ctl'

*.db_block_size=16384

*.db_file_name_convert='/ora_backup/TEST1/restore_test/NIK6/DATA','/ora_backup/TEST/duplicate_test/NIK6/DATA/'

*.log_file_name_convert='/ora_backup/TEST1/restore_test/NIK6/','/ora_backup/TEST/duplicate_test/NIK6/'

*.db_create_online_log_dest_1='/ora_backup/TEST/duplicate_test/NIK6/LOG/'

*.db_create_file_dest='/ora_backup/TEST/duplicate_test/NIK6/DATA/'

*.db_domain='mydb.domain'

*.db_name='NIK6'

*.db_recovery_file_dest='/ora_backup/TEST/duplicate_test/NIK6/FRA'

*.db_recovery_file_dest_size=65G

*.log_archive_dest_1='location=/ora_backup/TEST/duplicate_test/NIK6/FRA/'

*.diagnostic_dest='/ora_backup/TEST/duplicate_test/NIK6/diag'

*.log_archive_format='%t_%s_%r.dbf'

*.memory_target=1061607680

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

Above parameters in Bold has to be modified according to the auxiliary server.

NOW IN ANOTHER TERMINAL : -

5. Start the auxiliay Instance in nomount mode :-

==>sqlplus

SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 2 01:14:47 2014

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

Enter user-name: /as sysdba

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> startup force nomount pfile='/ora_backup/TEST/duplicate_test/initNIK6.ora'

ORACLE instance started.

Total System Global Area 1060585472 bytes

Fixed Size                  2260000 bytes

Variable Size             633340896 bytes

Database Buffers          402653184 bytes

Redo Buffers               22331392 bytes

6. Create spfile from pfile and start it again using spfile :-

SQL> create spfile from pfile='/ora_backup/TEST/duplicate_test/initNIK6.ora';

File created.

SQL> startup force nomount

ORACLE instance started.

Total System Global Area 1060585472 bytes

Fixed Size                  2260000 bytes

Variable Size             633340896 bytes

Database Buffers          402653184 bytes

Redo Buffers               22331392 bytes

7. Cross Check the below parameters in auxiliary instance :-

SQL> show parameter control_files

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

control_files                        string      /ora_backup/TEST/duplicate_test/NIK6/control01.ctl, /ora_ba

                                                 ckup/TEST/duplicate_test/NIK6/control02.ctl

SQL> show parameter db_file_name_convert

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_file_name_convert                 string      /ora_backup/TEST1/restore_test/NIK6/DATA,  /ora_backup/TEST/duplicate_test/NIK6/DATA/

SQL> show parameter log_file

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_file_name_convert                string      /ora_backup/TEST1/restore_test/NIK6/, /ora_backup/TEST/duplicate_test/NIK6/

We have to set above parameter when we do a duplicate database on another host with different file structure.

8. Start the RMAN duplicate command. If you are using a SPFILE for auxiliary instance then copy the SPFILE at the default location ($ORACLE_HOME/dbs) as while doing a duplicate RMAN will shut down the Auxiliary instance and start it again using SPFILE.

If you want to use a pfile then mention Pfile parameter in Duplicate command(optional) :-

SQL> ! rman target sys/Passw0rd@NIK6 auxiliary sys/Passw0rd@NIK6_DUP

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Dec 2 01:45:01 2014

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

connected to target database: NIK6 (DBID=2971225332)

connected to auxiliary database: NIK6 (not mounted)

RMAN >  duplicate database to NIK6_DUP

from active database

              pfile=/ora_backup/TEST/duplicate_test/initNIK6.ora;

RMAN> duplicate database to NIK6 from active database;

Starting Duplicate Db at 2014-12-02 01:45:55

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=145 device type=DISK

allocated channel: ORA_AUX_DISK_2

channel ORA_AUX_DISK_2: SID=151 device type=DISK

allocated channel: ORA_AUX_DISK_3

channel ORA_AUX_DISK_3: SID=157 device type=DISK

allocated channel: ORA_AUX_DISK_4

channel ORA_AUX_DISK_4: SID=163 device type=DISK

contents of Memory Script:

{

   sql clone "alter system set  db_name =

 ''NIK6'' comment=

 ''Modified by RMAN duplicate'' scope=spfile";

   sql clone "alter system set  db_unique_name =

 ''NIK6'' comment=

 ''Modified by RMAN duplicate'' scope=spfile";

   shutdown clone immediate;

   startup clone force nomount

   backup as copy current controlfile auxiliary format  '/ora_backup/TEST/duplicate_test/NIK6/control01.ctl';

   restore clone controlfile to  '/ora_backup/TEST/duplicate_test/NIK6/control02.ctl' from

 '/ora_backup/TEST/duplicate_test/NIK6/control01.ctl';

   alter clone database mount;

}

executing Memory Script

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

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

Oracle instance shut down

Oracle instance started

Total System Global Area    1060585472 bytes

Fixed Size                     2260000 bytes

Variable Size                633340896 bytes

Database Buffers             402653184 bytes

Redo Buffers                  22331392 bytes

Starting backup at 2014-12-02 01:53:16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=199 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=217 device type=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: SID=223 device type=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: SID=229 device type=DISK

channel ORA_DISK_1: starting datafile copy

copying current control file

output file name=/opt/oracle/product/database/11.2.0.4/dbs/snapcf_NIK6.f tag=TAG20141202T015327 RECID=22 STAMP=865216412

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

Finished backup at 2014-12-02 01:53:35

Starting restore at 2014-12-02 01:53:35

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=139 device type=DISK

allocated channel: ORA_AUX_DISK_2

channel ORA_AUX_DISK_2: SID=151 device type=DISK

allocated channel: ORA_AUX_DISK_3

channel ORA_AUX_DISK_3: SID=157 device type=DISK

allocated channel: ORA_AUX_DISK_4

channel ORA_AUX_DISK_4: SID=163 device type=DISK

channel ORA_AUX_DISK_2: skipped, AUTOBACKUP already found

channel ORA_AUX_DISK_3: skipped, AUTOBACKUP already found

channel ORA_AUX_DISK_4: skipped, AUTOBACKUP already found

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 2014-12-02 01:53:54

database mounted

contents of Memory Script:

{

   set newname for datafile  1 to

 "/ora_backup/TEST/duplicate_test/NIK6/DATA//system01.dbf";

   set newname for datafile  2 to

 "/ora_backup/TEST/duplicate_test/NIK6/DATA//sysaux01.dbf";

   set newname for datafile  3 to

 "/ora_backup/TEST/duplicate_test/NIK6/DATA//undotbs1.dbf";

   set newname for datafile  4 to

 "/ora_backup/TEST/duplicate_test/NIK6/DATA//undotbs2.dbf";

   set newname for datafile  5 to

 "/ora_backup/TEST/duplicate_test/NIK6/DATA//users_1.dbf";

   set newname for datafile  6 to

 "/ora_backup/TEST/duplicate_test/NIK6/DATA//ts_aud01.dbf";

   set newname for datafile  7 to

 "/ora_backup/TEST/duplicate_test/NIK6/DATA//DATA_data01.dbf";

   set newname for datafile  8 to

 "/ora_backup/TEST/duplicate_test/NIK6/DATA//DATA_idx01.dbf";

   set newname for datafile  9 to

 "/ora_backup/TEST/duplicate_test/NIK6/DATA//DATA_arch01.dbf";

   backup as copy reuse

   datafile  1 auxiliary format

 "/ora_backup/TEST/duplicate_test/NIK6/DATA//system01.dbf"   datafile

 2 auxiliary format

 "/ora_backup/TEST/duplicate_test/NIK6/DATA//sysaux01.dbf"   datafile

 3 auxiliary format

 "/ora_backup/TEST/duplicate_test/NIK6/DATA//undotbs1.dbf"   datafile

 4 auxiliary format

 "/ora_backup/TEST/duplicate_test/NIK6/DATA//undotbs2.dbf"   datafile

 5 auxiliary format

 "/ora_backup/TEST/duplicate_test/NIK6/DATA//users_1.dbf"   datafile

 6 auxiliary format

 "/ora_backup/TEST/duplicate_test/NIK6/DATA//ts_aud01.dbf"   datafile

 7 auxiliary format

 "/ora_backup/TEST/duplicate_test/NIK6/DATA//DATA_data01.dbf"   datafile

 8 auxiliary format

 "/ora_backup/TEST/duplicate_test/NIK6/DATA//DATA_idx01.dbf"   datafile

 9 auxiliary format

 "/ora_backup/TEST/duplicate_test/NIK6/DATA//DATA_arch01.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

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 2014-12-02 01:54:41

using channel ORA_DISK_1

using channel ORA_DISK_2

using channel ORA_DISK_3

using channel ORA_DISK_4

channel ORA_DISK_1: starting datafile copy

input datafile file number=00006 name=/ora_backup/TEST1/restore_test/NIK6/DATA/ts_aud01.dbf

channel ORA_DISK_2: starting datafile copy

input datafile file number=00007 name=/ora_backup/TEST1/restore_test/NIK6/DATA/DATA_data01.dbf

channel ORA_DISK_3: starting datafile copy

input datafile file number=00008 name=/ora_backup/TEST1/restore_test/NIK6/DATA/DATA_idx01.dbf

channel ORA_DISK_4: starting datafile copy

input datafile file number=00002 name=/ora_backup/TEST1/restore_test/NIK6/DATA/sysaux01.dbf

output file name=/ora_backup/TEST/duplicate_test/NIK6/DATA/sysaux01.dbf tag=TAG20141202T015442

channel ORA_DISK_4: datafile copy complete, elapsed time: 00:02:05

channel ORA_DISK_4: starting datafile copy

input datafile file number=00001 name=/ora_backup/TEST1/restore_test/NIK6/DATA/system01.dbf

output file name=/ora_backup/TEST/duplicate_test/NIK6/DATA/DATA_data01.dbf tag=TAG20141202T015442

channel ORA_DISK_2: datafile copy complete, elapsed time: 00:02:38

channel ORA_DISK_2: starting datafile copy

input datafile file number=00003 name=/ora_backup/TEST1/restore_test/NIK6/DATA/undotbs1.dbf

output file name=/ora_backup/TEST/duplicate_test/NIK6/DATA/DATA_idx01.dbf tag=TAG20141202T015442

channel ORA_DISK_3: datafile copy complete, elapsed time: 00:02:37

channel ORA_DISK_3: starting datafile copy

input datafile file number=00004 name=/ora_backup/TEST1/restore_test/NIK6/DATA/undotbs2.dbf

output file name=/ora_backup/TEST/duplicate_test/NIK6/DATA/undotbs2.dbf tag=TAG20141202T015442

channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:45

channel ORA_DISK_3: starting datafile copy

input datafile file number=00005 name=/ora_backup/TEST1/restore_test/NIK6/DATA/users_1.dbf

output file name=/ora_backup/TEST/duplicate_test/NIK6/DATA/users_1.dbf tag=TAG20141202T015442

channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:15

channel ORA_DISK_3: starting datafile copy

input datafile file number=00009 name=/ora_backup/TEST1/restore_test/NIK6/DATA/DATA_arch01.dbf

output file name=/ora_backup/TEST/duplicate_test/NIK6/DATA/undotbs1.dbf tag=TAG20141202T015442

channel ORA_DISK_2: datafile copy complete, elapsed time: 00:01:07

output file name=/ora_backup/TEST/duplicate_test/NIK6/DATA/system01.dbf tag=TAG20141202T015442

channel ORA_DISK_4: datafile copy complete, elapsed time: 00:01:36

output file name=/ora_backup/TEST/duplicate_test/NIK6/DATA/DATA_arch01.dbf tag=TAG20141202T015442

channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:07

output file name=/ora_backup/TEST/duplicate_test/NIK6/DATA/ts_aud01.dbf tag=TAG20141202T015442

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:07:33

Finished backup at 2014-12-02 02:02:19

sql statement: alter system archive log current

contents of Memory Script:

{

   backup as copy reuse

   archivelog like  "/ora_backup/TEST1/restore_test/NIK6/FRA/1_5_864800713.dbf" auxiliary format

 "/ora_backup/TEST/duplicate_test/NIK6/FRA/1_5_864800713.dbf"   ;

   catalog clone archivelog  "/ora_backup/TEST/duplicate_test/NIK6/FRA/1_5_864800713.dbf";

   switch clone datafile all;

}

executing Memory Script

Starting backup at 2014-12-02 02:07:52

using channel ORA_DISK_1

using channel ORA_DISK_2

using channel ORA_DISK_3

using channel ORA_DISK_4

channel ORA_DISK_1: starting archived log copy

input archived log thread=1 sequence=5 RECID=17241 STAMP=865216960

output file name=/ora_backup/TEST/duplicate_test/NIK6/FRA/1_5_864800713.dbf RECID=0 STAMP=0

channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:25

Finished backup at 2014-12-02 02:08:22

cataloged archived log

archived log file name=/ora_backup/TEST/duplicate_test/NIK6/FRA/1_5_864800713.dbf RECID=17241 STAMP=865217303

datafile 1 switched to datafile copy

input datafile copy RECID=22 STAMP=865217305 file name=/ora_backup/TEST/duplicate_test/NIK6/DATA/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=23 STAMP=865217306 file name=/ora_backup/TEST/duplicate_test/NIK6/DATA/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=24 STAMP=865217308 file name=/ora_backup/TEST/duplicate_test/NIK6/DATA/undotbs1.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=25 STAMP=865217309 file name=/ora_backup/TEST/duplicate_test/NIK6/DATA/undotbs2.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=26 STAMP=865217310 file name=/ora_backup/TEST/duplicate_test/NIK6/DATA/users_1.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=27 STAMP=865217312 file name=/ora_backup/TEST/duplicate_test/NIK6/DATA/ts_aud01.dbf

datafile 7 switched to datafile copy

input datafile copy RECID=28 STAMP=865217313 file name=/ora_backup/TEST/duplicate_test/NIK6/DATA/DATA_data01.dbf

datafile 8 switched to datafile copy

input datafile copy RECID=29 STAMP=865217314 file name=/ora_backup/TEST/duplicate_test/NIK6/DATA/DATA_idx01.dbf

datafile 9 switched to datafile copy

input datafile copy RECID=30 STAMP=865217316 file name=/ora_backup/TEST/duplicate_test/NIK6/DATA/DATA_arch01.dbf

contents of Memory Script:

{

   set until scn  4943275;

   recover

   clone database

    delete archivelog

   ;

}

executing Memory Script

executing command: SET until clause

Starting recover at 2014-12-02 02:08:49

using channel ORA_AUX_DISK_1

using channel ORA_AUX_DISK_2

using channel ORA_AUX_DISK_3

using channel ORA_AUX_DISK_4

starting media recovery

archived log for thread 1 with sequence 5 is already on disk as file /ora_backup/TEST/duplicate_test/NIK6/FRA/1_5_864800713.dbf

archived log file name=/ora_backup/TEST/duplicate_test/NIK6/FRA/1_5_864800713.dbf thread=1 sequence=5

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

Finished recover at 2014-12-02 02:10:32

Oracle instance started

Total System Global Area    1060585472 bytes

Fixed Size                     2260000 bytes

Variable Size                633340896 bytes

Database Buffers             402653184 bytes

Redo Buffers                  22331392 bytes

contents of Memory Script:

{

   sql clone "alter system set  db_name =

 ''NIK6'' 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 =  ''NIK6'' 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    1060585472 bytes

Fixed Size                     2260000 bytes

Variable Size                633340896 bytes

Database Buffers             402653184 bytes

Redo Buffers                  22331392 bytes

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "NIK6" RESETLOGS ARCHIVELOG

  MAXLOGFILES    192

  MAXLOGMEMBERS      3

  MAXDATAFILES     1024

  MAXINSTANCES    32

  MAXLOGHISTORY     4672

 LOGFILE

  GROUP   1 ( '/ora_backup/TEST/duplicate_test/NIK6/DATA/group_1.1335.859503481', '/ora_backup/TEST/duplicate_test/NIK6/DATA/group_1.283.859503479' ) SIZE 256 M  REUSE,

  GROUP   2 ( '/ora_backup/TEST/duplicate_test/NIK6/DATA/group_2.284.859503481', '/ora_backup/TEST/duplicate_test/NIK6/DATA/group_2.1429.859503481' ) SIZE 256 M  REUSE,

  GROUP  10 ( '/ora_backup/TEST/duplicate_test/NIK6/DATA/group_10.298.859929363', '/ora_backup/TEST/duplicate_test/NIK6/DATA/group_10.1258.859929365' ) SIZE 256 M  REUSE

 DATAFILE

  '/ora_backup/TEST/duplicate_test/NIK6/DATA/system01.dbf'

 CHARACTER SET AL32UTF8

sql statement: ALTER DATABASE ADD LOGFILE

  INSTANCE 'i2'

  GROUP   3 ( '/ora_backup/TEST/duplicate_test/NIK6/DATA/group_3.291.859504009', '/ora_backup/TEST/duplicate_test/NIK6/DATA/group_3.1303.859504009' ) SIZE 256 M  REUSE,

  GROUP   4 ( '/ora_backup/TEST/duplicate_test/NIK6/DATA/group_4.292.859504011', '/ora_backup/TEST/duplicate_test/NIK6/DATA/group_4.1246.859504011' ) SIZE 256 M  REUSE,

  GROUP  20 ( '/ora_backup/TEST/duplicate_test/NIK6/DATA/group_20.858.859929393', '/ora_backup/TEST/duplicate_test/NIK6/DATA/group_20.299.859929393' ) SIZE 256 M  REUSE

contents of Memory Script:

{

   set newname for tempfile  1 to

 "/ora_backup/TEST/duplicate_test/NIK6/DATA//NIK6/datafile/o1_mf_temp_b446vnwf_.tmp";

   switch clone tempfile all;

   catalog clone datafilecopy  "/ora_backup/TEST/duplicate_test/NIK6/DATA/sysaux01.dbf",

 "/ora_backup/TEST/duplicate_test/NIK6/DATA/undotbs1.dbf",

 "/ora_backup/TEST/duplicate_test/NIK6/DATA/undotbs2.dbf",

 "/ora_backup/TEST/duplicate_test/NIK6/DATA/users_1.dbf",

 "/ora_backup/TEST/duplicate_test/NIK6/DATA/ts_aud01.dbf",

 "/ora_backup/TEST/duplicate_test/NIK6/DATA/DATA_data01.dbf",

 "/ora_backup/TEST/duplicate_test/NIK6/DATA/DATA_idx01.dbf",

 "/ora_backup/TEST/duplicate_test/NIK6/DATA/DATA_arch01.dbf";

   switch clone datafile all;

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /ora_backup/TEST/duplicate_test/NIK6/DATA//NIK6/datafile/o1_mf_temp_b446vnwf_.tmp in control file

cataloged datafile copy

datafile copy file name=/ora_backup/TEST/duplicate_test/NIK6/DATA/sysaux01.dbf RECID=1 STAMP=865217830

cataloged datafile copy

datafile copy file name=/ora_backup/TEST/duplicate_test/NIK6/DATA/undotbs1.dbf RECID=2 STAMP=865217830

cataloged datafile copy

datafile copy file name=/ora_backup/TEST/duplicate_test/NIK6/DATA/undotbs2.dbf RECID=3 STAMP=865217831

cataloged datafile copy

datafile copy file name=/ora_backup/TEST/duplicate_test/NIK6/DATA/users_1.dbf RECID=4 STAMP=865217831

cataloged datafile copy

datafile copy file name=/ora_backup/TEST/duplicate_test/NIK6/DATA/ts_aud01.dbf RECID=5 STAMP=865217832

cataloged datafile copy

datafile copy file name=/ora_backup/TEST/duplicate_test/NIK6/DATA/DATA_data01.dbf RECID=6 STAMP=865217832

cataloged datafile copy

datafile copy file name=/ora_backup/TEST/duplicate_test/NIK6/DATA/DATA_idx01.dbf RECID=7 STAMP=865217833

cataloged datafile copy

datafile copy file name=/ora_backup/TEST/duplicate_test/NIK6/DATA/DATA_arch01.dbf RECID=8 STAMP=865217833

datafile 2 switched to datafile copy

input datafile copy RECID=1 STAMP=865217830 file name=/ora_backup/TEST/duplicate_test/NIK6/DATA/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=2 STAMP=865217830 file name=/ora_backup/TEST/duplicate_test/NIK6/DATA/undotbs1.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=3 STAMP=865217831 file name=/ora_backup/TEST/duplicate_test/NIK6/DATA/undotbs2.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=4 STAMP=865217831 file name=/ora_backup/TEST/duplicate_test/NIK6/DATA/users_1.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=5 STAMP=865217832 file name=/ora_backup/TEST/duplicate_test/NIK6/DATA/ts_aud01.dbf

datafile 7 switched to datafile copy

input datafile copy RECID=6 STAMP=865217832 file name=/ora_backup/TEST/duplicate_test/NIK6/DATA/DATA_data01.dbf

datafile 8 switched to datafile copy

input datafile copy RECID=7 STAMP=865217833 file name=/ora_backup/TEST/duplicate_test/NIK6/DATA/DATA_idx01.dbf

datafile 9 switched to datafile copy

input datafile copy RECID=8 STAMP=865217833 file name=/ora_backup/TEST/duplicate_test/NIK6/DATA/DATA_arch01.dbf

Reenabling controlfile options for auxiliary database

Executing: alter database force logging

contents of Memory Script:

{

   Alter clone database open resetlogs;

}

executing Memory Script

database opened

Finished Duplicate Db at 2014-12-02 02:19:53

Database Duplicated using Active Database duplication on another host with different file structure .
 If we want to duplicate database on same host then we have to follow the same above process. One thing we have to keep in mind while duplicating on same host , DB name of Auxiliary instance has to be different  from target.
 

Recovery from complete loss of all online redo log files using RMAN:

Database name and version

SQL> select instance_name,version from v$instance;

INSTANCE_NAME    VERSION

---------------- -----------------

opsdba           10.2.0.2.0

SQL> select member from v$Logfile;

MEMBER

-------------------------------

/u02/ORACLE/opsdba/redo03.log

/u02/ORACLE/opsdba/redo02.log

/u02/ORACLE/opsdba/redo01.log

opsdba:/u02/ORACLE/opsdba>rm redo*.log

If one or all of the online redo logfiles are delete then the database hangs and in the alert log file we can see the following error message:

Tue Jan 30 00:47:19 2007

ARC1: Failed to archive thread 1 sequence 93 (0)

Tue Jan 30 00:47:24 2007

Errors in file /opt/oracle/admin/opsdba/bdump/opsdba_arc0_32722.trc:

ORA-00313: open failed for members of log group 2 of thread 1

ORA-00312: online log 2 thread 1: '/u02/ORACLE/opsdba/redo02.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

The file is missing at the operating system level. Using RMAN we can recover from this error by restoring the database from the backup and recovering to the last available archived redo logfile. From the error message in the log file we can get the last archived file in our case it is sequence 92 as the error shows that it fails to archive the log file sequence 93.

SQL> select * from v$Log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM

---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------

         1          1         95   52428800          1 NO  CURRENT                3203078 30-JAN-07

         2          1         93   52428800          1 NO  INACTIVE               3202983 30-JAN-07

         3          1         94   52428800          1 NO  INACTIVE               3203074 30-JAN-07

At the operating system also we can find the last archived logfile:

opsdba:/u02/ORACLE/opsdba/arch> ls –lrt

total 54824

-rw-r-----    1 oracle   dba        714240 Jan 29 16:02 arch_1_90_613129285.dbf

-rw-r-----    1 oracle   dba      46281216 Jan 30 00:37 arch_1_91_613129285.dbf

-rw-r-----    1 oracle   dba         11264 Jan 30 00:41 arch_1_92_613129285.dbf

Shutdown the database

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

Mount the database

SQL> startup mount;

ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size                  2069680 bytes

Variable Size              92277584 bytes

Database Buffers           67108864 bytes

Redo Buffers                6316032 bytes

Database mounted.

Using RMAN connect to the target database:

opsdba:/u02/ORACLE/opsdba>rman target /

Recovery Manager: Release 10.2.0.2.0 - Production on Tue Jan 30 00:53:21 2007

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

connected to target database: OPSDBA (DBID=1493612009, not open)

RMAN> run {

2> set until sequence 93;

3> restore database;

4> recover database;

5>  alter database open resetlogs;

6> }

executing command: SET until clause

Starting restore at 30-JAN-07

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

allocated channel: ORA_SBT_TAPE_1

channel ORA_SBT_TAPE_1: sid=155 devtype=SBT_TAPE

channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.2.4.0

channel ORA_SBT_TAPE_1: starting datafile backupset restore

channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to /u02/ORACLE/opsdba/system01.dbf

restoring datafile 00002 to /u02/ORACLE/opsdba/undotbs01.dbf

restoring datafile 00003 to /u02/ORACLE/opsdba/sysaux01.dbf

restoring datafile 00004 to /u02/ORACLE/opsdba/users01.dbf

restoring datafile 00005 to /u02/ORACLE/opsdba/users02.dbf

restoring datafile 00006 to /u02/ORACLE/opsdba/users03.dbf

restoring datafile 00007 to /u02/ORACLE/opsdba/users05.dbf

restoring datafile 00008 to /u02/ORACLE/opsdba/users06.dbf

restoring datafile 00009 to /u02/ORACLE/opsdba/users07.dbf

restoring datafile 00010 to /u02/ORACLE/opsdba/users04.dbf

restoring datafile 00011 to /u02/ORACLE/opsdba/drtbs1.dbf

restoring datafile 00012 to /u02/ORACLE/opsdba/drtbs2.dbf

restoring datafile 00013 to /tmp/undonew.dbf

channel ORA_SBT_TAPE_1: reading from backup piece 5mi8ornj_1_1

channel ORA_SBT_TAPE_1: restored backup piece 1

piece handle=5mi8ornj_1_1 tag=TAG20070130T004019

channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:01:06

Finished restore at 30-JAN-07

Starting recover at 30-JAN-07

using channel ORA_DISK_1

using channel ORA_SBT_TAPE_1

starting media recovery

archive log thread 1 sequence 92 is already on disk as file /u02/ORACLE/opsdba/arch/arch_1_92_613129285.dbf

archive log filename=/u02/ORACLE/opsdba/arch/arch_1_92_613129285.dbf thread=1 sequence=92

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

Finished recover at 30-JAN-07

database opened

RMAN>exit

The recovery process creates the online redo logfiles at the operating system level also.

opsdba:/u02/ORACLE/opsdba>ls -lrt redo*

-rw-r-----    1 oracle   dba      52429312 Jan 30 01:00 redo03.log

-rw-r-----    1 oracle   dba      52429312 Jan 30 01:00 redo02.log

-rw-r-----    1 oracle   dba      52429312 Jan 30 01:00 redo01.log

Since we have done an incomplete recover with open resetlogs, we should take a fresh complete backup of the database.

NOTE: Please make sure you remove all the old archived logfiles from the archived area.

What is Force LOGGING in Oracle

In FORCE LOGGING mode, Oracle Database logs all changes in the database except changes in temporary tablespaces and temporary segments. This setting takes precedence over and is independent of any NOLOGGING or FORCE LOGGING settings you specify for individual tablespaces and any NOLOGGING settings you specify for individual database objects.

 If you specify FORCE LOGGING, Oracle Database waits for all ongoing unlogged operations to finish.

 ALTER DATABASE FORCE LOGGING;       //enable

 ALTER DATABASE NO FORCE LOGGING;                                //disable

 SELECT FORCE_LOGGING FROM V$DATABASE;                 //check

 RESETLOGS | NORESETLOGS  This clause determines whether Oracle Database resets the current log sequence number to 1, archives any unarchived logs (including the current log), and discards any redo information that was not applied during  recovery, ensuring that it will never be applied.

  Oracle Database uses NORESETLOGS automatically except in the following specific situations, which require a setting for this clause:

  1. You must specify RESETLOGS:

   – After performing incomplete media recovery or media recovery using a backup controlfile

   – After a previous OPEN RESETLOGS operation that did not complete

   – After a FLASHBACK DATABASE operation

  2. If a created controlfile is mounted, then you must specify RESETLOGS if the online logs are lost, or you must  specify NORESETLOGS if they are not lost.

 While there are several types of problem that can halt the normal operation of an Oracle database or affect database I/O operations, only two typically require DBA intervention and media recovery: media failure and user errors.

Other failures may require DBA intervention to restart the database (after an instance failure) or allocate more disk space (after statement failure due to, for instance, a full datafile) but these situations will not generally cause data loss or require recovery from backup.

1. User Error

   User errors occur when, either due to an error in application logic or a manual mis-step, data in your database is changed or deleted incorrectly. Data loss due to user error includes such missteps as dropping important tables or deleting or changing the contents of a table. While user training and careful management of privileges can prevent most user errors, your backup strategy determines how gracefully you recover the lost data when user error does cause data loss.

2. Media Failure

   A media failure is the failure of a read or write of a disk file required to run the database, due to a physical problem with the disk such as a head crash. Any database file can be vulnerable to a media failure. The appropriate recovery technique following a media failure depends on the files affected and the types of backup available.

Errors and Failures Without Requiring Recovery from Backup

1. Process Failure

   PMON will automatically perform the process recovery and rollback all involved transactions.

2. Program Failure

   It occurs when data violate the contrains, or data do not meet the data types, and so on, Oracle will automatically raise ORA- errors and Oracle will rollback the transactions automatically, or will wait for program's determination.

3. Instance Crash

   When an instance is shutdown abort or abnormally, SMON will perform recovery automatically

(roll forward + open the database [uses can access the instance/database at this time],

+  roll back the uncommitted transactions)         when the instance starts up.

Oracle 11gR2 - Patching Oracle using oPatch

The following steps uses the patch set 10094017 as an example. This patch set fixes issues in both Oracle home and Grid home.

 

The following Environment variables are assumed  –

 

CRS_HOME = /app/oracle/grid/11.2/grid

RDBMS_HOME = /app/oracle/oracle/11.2/db

 

 

Prerequisites 

 

1.                   Make sure you have access as oracle,grid and root user.

2.                   Make sure you have the right amount of free space.

            Free space required is 5.1GB. To free up space on grid home and oracle home, two places to look at are

 

Ø                /app/oracle/grid/11.2/grid/.patch_storage.

This directory contains files created as backup everytime a patch is installed.

 

To free up space in this directory, issue the following command –

$opatch util cleanup <patch number with timestamp>

              that command will usually free up more than 2GB of space.

 

 

Ø  Log directories for the grid background processes.

 

Some of the files locations are -

              /app/oracle/grid/11.2/grid/log/diag/tnslsnr/node1/listener_scan/alert

              /app/oracle/grid/11.2/grid/log/thehostnamehere/cssd

              /app/oracle/grid/11.2/grid/log/ thehostnamehere /crsd

 

3.                   Check if you have the latest version of the opatch.

            It is recommended for the patch to be atleast the same version as Oracle currently installed.

           Current version of Oracle is 11.2.0.1.0. So the opatch is recommended to be atleast 11.2.0.1.0

 

       4.         Check if patch inventory is accessible

 

        Issue the following commands –

 

        $opatch lsinventory -detail -oh /app/oracle/grid/11.2/grid

        $opatch lsinventory -detail -oh /app/oracle/oracle/11.2/db

 

     5.         Check if the patch being installed does not conflict with a previously installed patch.

      $ opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./

 

Installation steps

 

 Important first step : Read the README.TXT file that is included in the oPatch zip file.

 

      Place opatch in all the oracle and grid homes in all nodes.

      To achieve that, perform the following steps -

1.           Download zipped opatch file from oracle website.

2.           Copy the zipped opatch file to the following locations on all nodes where you plan to execute opatch –

/app/oracle/grid/11.2/grid

/app/oracle/oracle/11.2/db

3.           Unzip the zipped opatch file in the above directories.

This will result in the following new directories

/app/oracle/grid/11.2/grid/OPatch

/app/oracle/oracle/11.2/db/OPatch

This is the final installed opatch . You do not have to do anything more.

 

This specific patch should be applied as a rolling upgrade. All of the following steps should be followed for each node. Do not patch two nodes at once.

 

      1. Verify that the Oracle Inventory is properly configured.

         $ opatch lsinventory -detail -oh <CRS_HOME>

         $ opatch lsinventory -detail -oh <RDBMS_HOME>

         This command completes instantly

 

      2.relocate service for inst1 instance. relocated the services service1, service2 and service3

         This command takes about 30 seconds to complete.

 

       3.shut down the CRS managed resources running from DB home

          Issue the following command -

         $ <RDBMS_HOME>/bin/srvctl stop home -o <RDBMS_HOME> -s <status file location> -n  

             <node_name>#

         note the status file is created by the process

         This command completes in 4 minutes.

   

     4.Execute the following command as root to unlock protected files.

        $ <CRS_HOME>/crs/install/rootcrs.pl –unlock

        This command completes in 3 minutes.

 

       5. Save the RDBMS home configuration settings

        To achieve that,execute the following command –

        $ custom/server/10094017/custom/scripts/prepatch.sh -dbhome <RDBMS_HOME>

        This command completes instantly.

 

       6. Patch the CRS home files

       As the Oracle Clusterware (CRS) software owner, from the directory where the patch was unzipped;

       $ opatch napply -local -oh <CRS_HOME> -id 10094017

       This command finishes in ~17-20 minutes.

 

       7. Configure the CRS HOME

 

       After opatch completes, some configuration settings need to be applied to the patched files.

      To change owner and permissions as root execute the following;

      $ chmod +w <CRS_HOME>/log/<nodename>/agent

      $ chmod +w <CRS_HOME>/log/<nodename>/agent/crsd

      This command finishes instantly.

 

      8. Configure the RDBMS HOME

      After opatch completes, some configuration settings need to be applied to the patched files.

      As the  RDBMS software owner execute the following –

     $ server/10094017/custom/scripts/postpatch.sh -dbhome <RDBMS_HOME>

     This command finishes instantly.

 

     9. Security settings need to be restored on the CRS Home. This script will also restart the CRS daemons. Invoke this script as root.

     $ <CRS_HOME>/crs/install/rootcrs.pl -patch

     This command finishes in 3 minutes.

 

     $ <DB_HOME>/bin/srvctl start home -o <DB_HOME> -s <status file location> -n <node name>

     This command finishes in 4 minutes.

 

    10. On success you can determine whether the patch has been installed by using the following command -

     $ opatch lsinventory -detail -oh <CRS_HOME>

     $ opatch lsinventory -detail -oh <RDBMS_HOME>

 

 

This completes the patching of Oracle version 11.2.0.1

 

What can I learn right now in one minute that will be useful for the rest of my life?

For windows user only-

Use Window Button + Down Key to minimize the currently active window
Use Window Button + Up Key to maximize the currently active window
Use Window Button + Left Key to align the currently active window on the left and Right key to align on the right
Use Window Button + M to minimize all open windows.
'Windows key + D' to return directly to the desktop, minimizing all the open windows.(Credit Abhishek Naik)
Use CTRL+SHIFT+ESC to open task manager
Use Window key + L to lock the system.
Use Window key + # to open the application pinned on the taskbar, where # is the position of the icon Starting from 1.
Use Shift + Right click and then select open command prompt here to open the command prompt with the current directory set
Use Shift + Right click on any item to see extra options.



Download Video Without Signups

Some sites don't allow to download their video content without signing in or do not have facility to download at all.

So without using any extension or downloader, here is how to do this..

Goto that page.
Press ctrl+shift+i or right click and select Inspect Element.
Select the Network tab and refresh the page & play the video to get content status.
You can sort the content by TYPE (tab), to easily get the video link at the bottom under the NAME(tab).
Double click the video link. Voila, your download will start.
Works fine with many sites. Enjoy :)

Edit #1: For those who don't know, to download YouTube videos just add "ss" before 'y' in www.youtube.com.
Example: To download www.youtube.com/watch?v=ONi**mhDvRo
do, www.ssyoutube.com/watch?v=ONi**mhDvRo and go..

Edit #2:
"ssyoutube" redirects you to a third party website "savefromnet" that might serve malware/virus (very rare cases).  I have used this method and have not experienced any trouble so far. Take precautions!