ORACLE DATABASE ADMINISTRATOR: 2012-09-23

Thursday, September 27, 2012

Active database Duplication using RMAN 11gR2

Using "Active database Duplication" method Incomplete-recovery "NOT" possible ( SET UNTIL time/sequence/cancel). Because this duplication method only based on the logs are archived in the target database location . Even content in the online redologs from the target database. So we no need to take the backup of target database . but target database must be in archive log mode

if the database size in TB's with network connection between database enabled. we can use this method.

target database a.k.a (primary/source database)
auxiliary database a.k.a (destination/clone database)

Target database name: delta
Auxiliary database name: delclon

Overview:

1. Copy the password file from Target database to auxiliary database location
2. Create the pfile from target database for Auxilary database( if target has spfile create pfile from spfile)
3. Configure the linster & tnsname on both target database and auxiliary database
4. Create the required directory structure in Auxillary db location (based on pfile )
5. Open the database in mount mode using modified pfile
6. Use rman command, RMAN will perform restore and recover the database.
$rman target sys/oracle@<target_dbname>
RMAN> connect auxiliary sys/oracle@<auxiliary_dbname>
(or)
rman target sys/oracle@<target_dbname> auxiliary sys/oracle@<auxiliary_dbname>
5.RMAN>duplicate target database to <auxiliary_dbname> from active database nofilenamecheck;

After "Active database Duplication"
1.Auxiliary DB has unique DBID
2.Auxiliary DB has Copy of data files & archived log files from target database
3.Recreate the new control files for auxiliary database
4.Recreates the online redo log files.
5.Restart the auxiliary instance.
6.Open the database with RESETLOGS.
7. enable the database in archivelog mode

STEP 1: Copy the password file from Target database to auxiliary database location

[oracle@nyc dbs]$ scp orapwdelta oracle@192.168.168.2:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwdelclon
oracle@192.168.168.2's password:
orapwdelta                                                                                 100% 1536     1.5KB/s   00:00
[oracle@nyc dbs]$

STEP 2: Create the pfile from target database for Auxilary database( if target has spfile create pfile from spfile)

From Target database location

SQL> conn /as sysdba
Connected.
SQL> show parameter spfile;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0/db_1/dbs/spfiledelta.ora

SQL> create pfile='/tmp/initdelclon.ora' from spfile;
File created.

[oracle@nyc dbs]$ cd /tmp
[oracle@nyc tmp]$ scp initdelclon.ora oracle@192.168.168.2:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
oracle@192.168.168.2's password:
initdelclon.ora                                                                              100% 1478     1.4KB/s   00:00
[oracle@nyc tmp]$

From Auxiliary database location

[oracle@sfo dbs]$ vi initdelclon.ora
delta.__db_cache_size=335544320
delta.__java_pool_size=16777216
delta.__large_pool_size=16777216
delta.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
delta.__pga_aggregate_target=469762048
delta.__sga_target=771751936
delta.__shared_io_pool_size=0
delta.__shared_pool_size=369098752
delta.__streams_pool_size=16777216
*.audit_file_dest='/u01/app/oracle/admin/delta/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/delta//delta/control01.ctl','/u01/app/oracle/flash_recovery_area/delta/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/u01/app/oradata/deccan/','/u01/app/oracle/oradata/delta/delta'
*.db_name='delta'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=deltaXDB)'
*.fal_client='DELTA'
*.fal_server='DECCAN'
*.log_archive_config='dg_config=(delta, deccan)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/delta/archive'
*.log_archive_dest_2='service=deccan valid_for=(online_logfile,primary_role) db_unique_name=deccan'
*.log_archive_dest_state_2='defer'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='/u01/app/oradata/deccan/','/u01/app/oracle/oradata/delta/delta'
*.memory_target=1234173952
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='auto'
*.undo_tablespace='UNDOTBS1'

edited intidelclon.ora file

[oracle@sfo dbs]$ cat initdelclon.ora
*.audit_file_dest='/u01/app/oracle/admin/delclon/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/delclon/delclon/control01.ctl','/u01/app/oracle/flash_recovery_area/delclon/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/u01/app/oracle/oradata/delta/delta','/u01/app/oracle/oradata/delclon/delclon'
*.db_name='delclon'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=delclonXDB)'
*.fal_client='DELCLON'
*.fal_server='DELTA'
*.log_archive_config='dg_config=(delclon, delta)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/delclon/archive'
*.log_archive_dest_2='service=deccan valid_for=(online_logfile,primary_role) db_unique_name=delta'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='/u01/app/oracle/oradata/delta/delta','/u01/app/oracle/oradata/delclon/delclon'
*.memory_target=1234173952
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='auto'
*.undo_tablespace='UNDOTBS1'

STEP 3: Configure the linster & tnsname on both target database and auxiliary database

Listener.ora (following lines need to added in listerner.ora) (In TARGET db location)

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = delta)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = delta)
    )
)

[oracle@nyc ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 27-SEP-2012 03:29:50

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                27-SEP-2012 02:38:23
Uptime                    0 days 0 hr. 51 min. 32 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/nyc/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=nyc.saravanan.com)(PORT=1521)))
Services Summary...
Service "delta" has 2 instance(s).
Instance "delta", status UNKNOWN, has 1 handler(s) for this service...
Instance "delta", status READY, has 1 handler(s) for this service...
Service "deltaXDB" has 1 instance(s).
Instance "delta", status READY, has 1 handler(s) for this service...
The command completed successfully

tnsnames.ora(following lines need to added in tnsnames.ora) (In TARGET db location)

DELCLON =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = sfo.saravanan.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = delclon)
    )
)

[oracle@nyc ~]$ tnsping delclon
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 26-SEP-2012 22:21:26
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = sfo.saravanan.com)(PORT = 1521))) (CONNECT_DATA = (SID = delcon)))
OK (50 msec)

Listener.ora (following lines need to added in listerner.ora) (In Auxillary db location)

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = delclon)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = delclon)
    )
)

[oracle@sfo ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 26-SEP-2012 18:57:48
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                26-SEP-2012 18:57:33
Uptime                    0 days 0 hr. 0 min. 15 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/sfo/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sfo.saravanan.com)(PORT=1521)))
Services Summary...
Service "delclon" has 1 instance(s).
Instance "delclon", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

tnsnames.ora(following lines need to added in tnsnames.ora (In Auxillary db location)

DELTA=
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = nyc.saravanan.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = delta)
    )
)

DELCLON =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = sfo.saravanan.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = delclon)
    )
)
[oracle@sfo ~]$ tnsping delta
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 26-SEP-2012 19:33:25
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = nyc.saravanan.com)(PORT = 1521))) (CONNECT_DATA = (SID = delta)))
OK (60 msec)

[oracle@sfo ~]$ tnsping delclon
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 26-SEP-2012 22:21:26
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = sfo.saravanan.com)(PORT = 1521))) (CONNECT_DATA = (SID = delclon)))
OK (50 msec)

STEP 4: Create the required directory structure in Auxillary db location

create the nessary directory structure based on the init file.
mkdir -p /u01/app/oracle/admin/delclon/adump
mkdir -p /u01/app/oracle/oradata/delclon/delclon
mkdir -p /u01/app/oracle/flash_recovery_area/delclon
mkdir -p /u01/app/oracle/oradata/delclon/delclon
mkdir -p /u01/app/oracle/oradata/delclon/archive

STEP 6. Open the database in mount mode using modified pfile

In Auxillary database location add entry in /etc/oratab

[oracle@sfo flash_recovery_area]$ . oraenv
ORACLE_SID = [11gR2_NEW] ? delclon
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle

[oracle@sfo dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 26 22:43:52 2012

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

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1235959808 bytes
Fixed Size                  2212896 bytes
Variable Size             889195488 bytes
Database Buffers          335544320 bytes
Redo Buffers                9007104 bytes
SQL>

Check the network work connection between two servers(Target & Auxillary)

SQL> conn sys/oracle@delta as sysdba;
Connected.
SQL> conn sys/oracle@delclon as sysdba;
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Warning: You are no longer connected to ORACLE.

[NOTE: To resolve above issue oracle blocking new connections so shutdown the Auxiliary database "delclon" and startup in nomount stage the standby database again will resolve TNS issue iautomatically.]

SQL> shut immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1235959808 bytes
Fixed Size                  2212896 bytes
Variable Size             889195488 bytes
Database Buffers          335544320 bytes
Redo Buffers                9007104 bytes
SQL>

SQL> conn sys/oracle@delta as sysdba;
Connected.
SQL> conn sys/oracle@delclon as sysdba;
Connected.

STEP 6. Use rman command, RMAN will perform restore and recover the database

$rman target sys/oracle@delta

[oracle@sfo ~]$ rman target sys/oracle@delta

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Sep 27 03:28:18 2012

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

connected to target database: DELTA (DBID=3056476564)

RMAN> connect auxiliary sys/oracle@delclon
.
.
.

RMAN>duplicate target database to clonedb from active database nofilenamecheck;
database opened
Finished Duplicate delclon at 27-SEP-12

Verify the status of auxiliary database status

SQL> select name,open_mode from v$database;

NAME             OPEN_MODE
---------            ----------
DELCLON       READ WRITE

Wednesday, September 26, 2012

Disater recovery using rman with same directory structure

Disater recovery implemented to validate the backups or to recover all datafiles, control file, redolog file, spfile if they are lost

Source Database name: jatway
Clone Database name: jatway (using the source database backup with same directory structure)

Step 1: Taking backup from the source database "jatway"

[oracle@sfo ~]$ . oraenv
ORACLE_SID = [11gR2_NEW] ? jatway
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle

[oracle@sfo ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 25 16:04:10 2012

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1235959808 bytes
Fixed Size                  2212896 bytes
Variable Size            1056967648 bytes
Database Buffers          167772160 bytes
Redo Buffers                9007104 bytes
Database mounted.
Database opened.

SQL> select name from v$database;

NAME
---------
JATWAY

Step 2: Make a note of the directory stucture from the source database. This same directory structure needs to be built in the new

server or clone location for this example.

(Note: If there is no chance to create the directory structure in the new server, we can also use "link method" to maintain the same

structure of the clone database)

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/flash_recovery
                                                 _area
db_recovery_file_dest_size           big integer 3882M

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/jetway/jatway/system01.dbf
/u01/app/oracle/oradata/jetway/jatway/sysaux01.dbf
/u01/app/oracle/oradata/jetway/jatway/undotbs01.dbf
/u01/app/oracle/oradata/jetway/jatway/users01.dbf
/u01/app/oracle/oradata/jetway/jatway/samtbs01.dbf

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/jetway/jatway/redo03.log
/u01/app/oracle/oradata/jetway/jatway/redo02.log
/u01/app/oracle/oradata/jetway/jatway/redo01.log
/u01/app/oracle/oradata/jetway/jatway/redo04.log
/u01/app/oracle/oradata/jetway/jatway/redo05.log
/u01/app/oracle/oradata/jetway/jatway/redo06.log
/u01/app/oracle/oradata/jetway/jatway/redo07.log

7 rows selected.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/jetway/jatway/control01.ctl
/u01/app/oracle/flash_recovery_area/jatway/control02.ctl

find the location of tracefile

SQL>show parameter dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest                 string      /u01/app/oracle/diag/rdbms/jat
                                                 way/jatway/trace
core_dump_dest                       string      /u01/app/oracle/diag/rdbms/jat
                                                 way/jatway/cdump
user_dump_dest                       string      /u01/app/oracle/diag/rdbms/jat
                                                 way/jatway/trace

SQL> show parameter dump_destaudit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/jatway/a
                                                 dump

SQL> show parameter diagnostic

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest                      string      /u01/app/oracle

Step 3: Log into the user Tom and create table name "c" and insert value into it

SQL>conn tom/tom
connected

SQL> create table c(id number);

Table created.

SQL> insert into c values(4);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.
..

SQL> select count(1) from c;

COUNT(1)
----------
         5

SQL> commit;
Commit complete.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data
Mining and Real Application Testing options

Step 4: Backup the source database using RMAN

[oracle@sfo ~]$ . oraenv
ORACLE_SID = [jatway] ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle

[oracle@sfo ~]$ rman target=/

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Sep 25 17:01:47 2012

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

connected to target database: JATWAY (DBID=3196377116)

a) Configure the backup location where the backup needs to be stored

RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/u01/app/oracle/oradata/jetway/jatway/backup/%U';

old RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/u01/app/oracle/oradata/jetway/jatway/backup/%U';
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/u01/app/oracle/oradata/jetway/jatway/backup/%U';
new RMAN configuration parameters are successfully stored

b) Enable the control file autobackup so that it will backup the control file as well spfile in the backup piece

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP on;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

c) Verify the configuration settings of the RMAN

RMAN> show all;

RMAN configuration parameters for database with db_unique_name JATWAY are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/u01/app/oracle/oradata/jetway/jatway/backup/%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_jatway.f'; # default

d) Backup the whole database with archivelog

RMAN> backup database plus archivelog;

Starting backup at 25-SEP-12
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=72 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=30 RECID=29 STAMP=794580386
input archived log thread=1 sequence=31 RECID=30 STAMP=794580651
input archived log thread=1 sequence=32 RECID=31 STAMP=794581003
input archived log thread=1 sequence=33 RECID=32 STAMP=794581473
input archived log thread=1 sequence=34 RECID=33 STAMP=794593512
input archived log thread=1 sequence=35 RECID=34 STAMP=794593515
input archived log thread=1 sequence=36 RECID=35 STAMP=794595461
input archived log thread=1 sequence=37 RECID=36 STAMP=794595464
input archived log thread=1 sequence=38 RECID=37 STAMP=794620809
input archived log thread=1 sequence=39 RECID=38 STAMP=794649631
input archived log thread=1 sequence=40 RECID=39 STAMP=794671245
input archived log thread=1 sequence=41 RECID=40 STAMP=794689121
input archived log thread=1 sequence=42 RECID=41 STAMP=794912453
input archived log thread=1 sequence=43 RECID=42 STAMP=794921073
input archived log thread=1 sequence=44 RECID=43 STAMP=794933728
input archived log thread=1 sequence=45 RECID=44 STAMP=794933729
input archived log thread=1 sequence=46 RECID=45 STAMP=794937869
input archived log thread=1 sequence=47 RECID=46 STAMP=794937872
input archived log thread=1 sequence=48 RECID=47 STAMP=794941697
channel ORA_DISK_1: starting piece 1 at 25-SEP-12
channel ORA_DISK_1: finished piece 1 at 25-SEP-12
piece handle=/u01/app/oracle/oradata/jetway/jatway/backup/11nm3m83_1_1 tag=TAG20120925T170818 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 25-SEP-12

Starting backup at 25-SEP-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/jetway/jatway/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/jetway/jatway/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/jetway/jatway/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/jetway/jatway/samtbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/jetway/jatway/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-SEP-12
channel ORA_DISK_1: finished piece 1 at 25-SEP-12
piece handle=/u01/app/oracle/oradata/jetway/jatway/backup/12nm3m96_1_1 tag=TAG20120925T170854 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:16
Finished backup at 25-SEP-12

Starting backup at 25-SEP-12
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=49 RECID=48 STAMP=794941810
channel ORA_DISK_1: starting piece 1 at 25-SEP-12
channel ORA_DISK_1: finished piece 1 at 25-SEP-12
piece handle=/u01/app/oracle/oradata/jetway/jatway/backup/13nm3mbj_1_1 tag=TAG20120925T171011 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 25-SEP-12

Starting Control File and SPFILE Autobackup at 25-SEP-12
piece handle=/u01/app/oracle/flash_recovery_area/JATWAY/autobackup/2012_09_25/o1_mf_s_794941814_864kz6qs_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-SEP-12

Step 5: Now the backups are stored in "/u01/app/oracle/oradata/jetway/jatway/backup/". Zip and TAR the backup folder and move across

the new server (Imagine that these backups now reside in the storage medium. To perform disaster recovery, the backup needs to be

brought from storage to the new server)

[oracle@sfo jatway]$ tar -cvzf backup.tar.gz backup
backup/
backup/o1_mf_s_794941814_864kz6qs_.bkp
backup/11nm3m83_1_1
backup/13nm3mbj_1_1
backup/12nm3m96_1_1

[oracle@sfo jatway]$ scp backup.tar.gz oracle@192.168.168.1:/u01/app/oracle/oradata/
oracle@192.168.168.1's password:
backup.tar.gz

                                                                    100% 271MB 22.6MB/s   00:12
[oracle@sfo jatway]$

Step 6: This is the new server (Imagine that the RMAN backup has been downloaded from the storage medium to the new server). Extract

the zipped file.

[oracle@nyc oradata]$ mkdir jatway

[oracle@nyc jatway]$ tar -xvzf backup.tar.gz
backup/
backup/o1_mf_s_794941814_864kz6qs_.bkp
backup/11nm3m83_1_1
backup/13nm3mbj_1_1
backup/12nm3m96_1_1

All the steps below need to be performed in the new server (clone location)

Step 7: Create the required directory structure based on the new server location. Because in this example we are following the same

directory structure as the source database environment

(Based on the information collected from step 1, we have creating the directory structure in the new server)

[oracle@nyc backup]$ mkdir -p /u01/app/oracle/oradata/jetway/jatway/
[oracle@nyc backup]$ mkdir -p /u01/app/oracle/diag/rdbms/jatway/jatway/trace
[oracle@nyc backup]$ mkdir -p /u01/app/oracle/diag/rdbms/jatway/jatway/cdump
[oracle@nyc backup]$ mkdir -p /u01/app/oracle/diag/rdbms/jatway/jatway/trace
[oracle@nyc backup]$ mkdir -p /u01/app/oracle/admin/jatway/adump
[oracle@nyc backup]$ mkdir -p /u01/app/oracle/flash_recovery_area/jatway/

Step 8: Restore and recover the database from the RMAN backup.

Now we will perform disaster recovery with same name of the database and directory structure of source database.

In the new server, adding the database name entry in /etc/oratab

Add database entry in the /etc/oratab

[oracle@nyc jatway]$ vi /etc/oratab
jatway:/u01/app/oracle/product/11.2.0/db_1:N

[oracle@nyc jatway]$ export ORACLE_SID=jatway
[oracle@nyc jatway]$ rman target=/

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Sep 25 23:17:30 2012

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

connected to target database (not started)

Step 9: Login to the RMAN prompt and perform "fake nomount"

The below command performs fake nomount of the database

RMAN> startup nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/db_1/dbs/initjatway.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area     158662656 bytes

Fixed Size                     2211448 bytes
Variable Size                 88080776 bytes
Database Buffers              62914560 bytes
Redo Buffers                   5455872 bytes

Step 10: The below command creates the spfile from the backup taken using rman.
(same backup piece "o1_mf_s_794941814_864kz6qs_.bkp" will be used to restore spfile as well as control file)

RMAN> restore spfile from '/u01/app/oracle/oradata/jatway/backup/o1_mf_s_794941814_864kz6qs_.bkp';

Starting restore at 25-SEP-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/oradata/jatway/backup/o1_mf_s_794941814_864kz6qs_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 25-SEP-12

RMAN> shutdown immediate;

(Note: Once the spfile restore successfully completed, we can get the copy of the spfile in $ORACLE_HOME/dbs)

[oracle@nyc backup]$ cd $ORACLE_HOME/dbs
[oracle@nyc dbs]$ ls
spfilejatway.ora

Step 11: Bring the database to actual nomount stage

RMAN> startup nomount;

Oracle instance started

Total System Global Area    1235959808 bytes

Fixed Size                     2212896 bytes
Variable Size               1056967648 bytes
Database Buffers             167772160 bytes
Redo Buffers                   9007104 bytes

Step 12: Create the control file using the same backup piece which was used to recover spfile, since this backup has control file as

well as spfile information .

RMAN> restore controlfile from '/u01/app/oracle/oradata/jatway/backup/o1_mf_s_794941814_864kz6qs_.bkp';

Starting restore at 26-SEP-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output file name=/u01/app/oracle/oradata/jetway/jatway/control01.ctl
output file name=/u01/app/oracle/flash_recovery_area/jatway/control02.ctl
Finished restore at 26-SEP-12

Step 13: Mount the database with force option.

RMAN> startup force mount;

Oracle instance started
database mounted

Total System Global Area    1235959808 bytes

Fixed Size                     2212896 bytes
Variable Size               1056967648 bytes
Database Buffers             167772160 bytes
Redo Buffers                   9007104 bytes

Step 14: When we try to "restore" the database it will "throw an error" because the backup piece information is not updated in RMAN

catalog.

RMAN> restore database;

Starting restore at 26-SEP-12
Starting implicit crosscheck backup at 26-SEP-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 26-SEP-12

Starting implicit crosscheck copy at 26-SEP-12
using channel ORA_DISK_1
Finished implicit crosscheck copy at 26-SEP-12

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 09/26/2012 00:50:27
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

restore command fails("no files are catalog") because rman not able to find backup file using rman repository

Step 15: The below command helps the RMAN to understand the backup piece information in the specified location and the backup piece

information will be cataloged. We need to confirm ("YES") inorder to allow the files to be cataloged.

RMAN> catalog start with '/u01/app/oracle/oradata/jatway/backup';

searching for all files that match the pattern /u01/app/oracle/oradata/jatway/backup

List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/oradata/jatway/backup.tar.gz
File Name: /u01/app/oracle/oradata/jatway/backup/o1_mf_s_794941814_864kz6qs_.bkp
File Name: /u01/app/oracle/oradata/jatway/backup/11nm3m83_1_1
File Name: /u01/app/oracle/oradata/jatway/backup/13nm3mbj_1_1
File Name: /u01/app/oracle/oradata/jatway/backup/12nm3m96_1_1
File Name: /u01/app/oracle/oradata/jatway/backup/exit

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/oradata/jatway/backup/o1_mf_s_794941814_864kz6qs_.bkp
File Name: /u01/app/oracle/oradata/jatway/backup/11nm3m83_1_1
File Name: /u01/app/oracle/oradata/jatway/backup/13nm3mbj_1_1
File Name: /u01/app/oracle/oradata/jatway/backup/12nm3m96_1_1

Step 16: Now rman backup pieces are already cataloged and so the "restore database" command will work without error.
and recover database.

RMAN>recover database;

RMAN>recover database;

(Note: Sometimes, after applying all archivelog files, the command looks for archivelogs that are not yet generated and so it will

throw an error. This error can be ignored)

Step 17: Open the database with resetlog and check the values in the database

RMAN>alter database open resetlog;
database opened

check the values in the database.

SQL>conn tom/tom
connected

SQL> select count(1) from c;

COUNT(1)
----------
         5

OR

We can use diaster recovery script instead of manually performing "Step 9 to Step 17"

(Disaser recovery script)

run
{
startup nomount;
restore spfile from '/u01/app/oracle/oradata/jatway/backup/o1_mf_s_794941814_864kz6qs_.bkp';
startup force nomount;
restore controlfile from '/u01/app/oracle/oradata/jatway/backup/o1_mf_s_794941814_864kz6qs_.bkp';
startup force mount;
catalog start with 'catalog start with '/u01/app/oracle/oradata/jatway/backup';
restore database;
recover database;
}

just copy and paste the line in rman prompt: it will perform disaster recovery by itself.

RMAN>startup nomount;
restore spfile from '/u01/app/oracle/oradata/jatway/backup/o1_mf_s_794941814_864kz6qs_.bkp';
startup force nomount;
restore controlfile from '/u01/app/oracle/oradata/jatway/backup/o1_mf_s_794941814_864kz6qs_.bkp';
startup force mount;
catalog start with 'catalog start with '/u01/app/oracle/oradata/jatway/backup';
restore database;
recover database;

To exclude a particular tablespace from full database backup.

To exclude a particular tablespace from full database backup.

In most of the environment we know few tableapces zero data change happned in test environment.
In a such environment tablespace data is not crucial. then we can avoid particular tablespace backup from
RMAN full backup.

note: the following command may override the backup policy

step1: Exclude the particular tablespace in rman level.

RMAN> CONFIGURE EXCLUDE FOR TABLESPACE SAMTBS1;

using target database control file instead of recovery catalog
Tablespace SAMTBS1 will be excluded from future whole database backups
new RMAN configuration parameters are successfully stored

step2: Then need to perform the default full backup. so now rman will backup all tablespace in
the database except the tablespace mentioned in step 1

RMAN> BACKUP DATABASE;

the below command help to view excludes tablespace with rman

To list the excludes tablespace

RMAN> show exclude;

RMAN configuration parameters for database with db_unique_name JATWAY are:
CONFIGURE EXCLUDE FOR TABLESPACE 'SAMTBS1';

Command to skip multiple tablespaces blog to same database.

RMAN> CONFIGURE EXCLUDE FOR TABLESPACE SAMTBS2;
RMAN> CONFIGURE EXCLUDE FOR TABLESPACE SAMTBS3;

Here tablespace SAMTBS2,SAMTBS3 will be excluded from backup.

Command to override (or) clear the Excluded tablespace in RMAN

(a)Command for Override the excluded tablespaces

You can override this exclusion feature by explicitly issuing keyword NOEXCLUDE in order to take whole database backup.

RMAN>BACKUP DATABASE NOEXCLUDE;

(b)Command for Clear the Excluded SAMTBS1 tablespace

RMAN>CONFIGURE EXCLUDE FOR TABLESPACE SAMTBS1 CLEAR;

Command to backup READONLY and OFFLINE tablespaces

RMAN>BACKUP DATABASE SKIP READONLY, SKIP OFFLINE;

Tuesday, September 25, 2012

Cloning Single instance oracle home

Cloning the single instance oracle home (This method require -Sudo privilage on both servers)

here I am going to clone the oracle home to the new server which has same hardware configuration as the source server)

1. login as root user

$su -
password:

#echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/db_1

#cd /u01/app/oracle/product/11.2.0/db_1

2.check the present directory disk usage size
(Note: we need to move the size across the network and makwe sure we have space in the destination location)

du -sh

3. Then copy the directory without changing the permission of the file
(note : "-rp " option help to copy the witout changing the permission of file or folder)
cp -rp

cd /u01/app/oracle/product/11.2.0/

11.2.0] cp -rp db_1 clonedb_1

tar -zvCf clonedb_1.tar.gz

scp -rp clonedb_1 oracle@192.168.168.1:/u01/app/oracle/product/11.2.0/db_home

4.In the new server create required directory structure

$ mkdir -p /u01/app/oracle/product/11.2.0/dbhome_1
$ export ORACLE_BASE=/u01/app
$ export ORACLE_HOME=$ORACLE_BASE/oracle/product/11.2.0/dbhome_1
$ cd $ORACLE_HOME/

5. In the clone location unzip tar file what
dbhome_1]$ cp ~/orahome11gR2_lin32.tar.gz .

dbhome_1]$ gunzip clonedb_1.tar.gz

dbhome_1]$ tar -xvf clonedb_1.tar

dbhome_1]$su -

# tar -xvf clonedb_1

# exit
$ cd /u01/app/oracle/product/11.2.0/clonedb_1/clone/bin

6. Now we need to provide the oracle base location. it is going to create the "oracle inventory location"
and run the perl script in clone/bin location

11.2.0/clonedb_1/clone/bin]$ perl clone.pl ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 ORACLE_HOME_NAME=11gR2_NEW

by the same time tail the log when the the location mentioned after using the above command

in another terminal
$tail-f /u01/app/oraInventory/logs/cloneAction2012-09-02_10-22.0PM.log
.
.
.
.
.
.
.

The cloning of 11gR2_NEW was sucessesful.
(Note when you get this message in log file cole preocess is sucessful)

7. run the root script ( to change the permission of the files and folder)

$su -

# /u01/app/oracle/product/11.2.0/clonedb_1/root.sh

8. then check the entry of the database in the /etc/oratab location

and we can check the oracle home location by checking the xml file in the inventory location

cd /u01/app/oraInventory/ContentsXML

cat inventory.xml

then change the listener entry

Hot Backup clone without using RMAN

I wish to create details steps " how to clone database without using RMAN in hotbackup mode"

SQL> conn tom/tom
Connected.
SQL> select * from tab;

A                              TABLE
B                              TABLE
SQL> select * from b;

         8
         8
         8
         8
         8
SQL>

1. Make sure the database is in archive log mode
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/oradata/jetway/jatway/arch
Oldest online log sequence     40
Next log sequence to archive   42
Current log sequence           42

2. Make a note of the current archive log change number
Because the restored files will require recovery, some archive logs will be needed.
This applies even if you are not intending to put the cloned database into archive log mode.
Work out which will be the first required log by running the following query on the source database.
Make a note of the change number that is returned:

SQL> select max(first_change#) Latest_chng from v$archived_log;

LATEST_CHNG
-----------
    1134000

3. View the current location of the data files

SQL> Select tablespace_name, file_name from dba_data_files order by 1;
SAMTBS1                        /u01/app/oracle/oradata/jetway/jatway/samtbs01.dbf
SYSAUX                         /u01/app/oracle/oradata/jetway/jatway/sysaux01.dbf
SYSTEM                         /u01/app/oracle/oradata/jetway/jatway/system01.dbf
UNDOTBS1                       /u01/app/oracle/oradata/jetway/jatway/undotbs01.dbf
USERS                          /u01/app/oracle/oradata/jetway/jatway/users01.db

4. Check whether the data files are in backup mode or not

SQL> SELECT t.name, d.file# as, b.status FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b WHERE d.TS#=t.TS# AND b.FILE#=d.FILE#;

NAME                                FILE# STATUS
------------------------------ ---------- ------------------
SYSTEM                                  1 NOT ACTIVE
SYSAUX                                  2 NOT ACTIVE
UNDOTBS1                                3 NOT ACTIVE
USERS                                   4 NOT ACTIVE
SAMTBS1                                 5 NOT ACTIVE

The above result shows that the data files are not in backup mode

5. Generate a script to bring the data file in backup mode using the following command:

SQL> !pwd
/home/oracle

[oracle@sfo ~]$ cd /home/oracle/
[oracle@sfo ~]$ vi enable_hotbackup_mode.sql

set lines 999 pages 999
set verify off
set feedback off
set heading off

spool begin_backup.sql

select 'alter tablespace ' || tablespace_name || ' begin backup;' tsb_backup_mode
from    dba_tablespaces
where   contents != 'TEMPORARY'
order by tablespace_name
/
spool off

SQL> @enable_hotbackup_mode.sql

alter tablespace SAMTBS1 begin backup;
alter tablespace SYSAUX begin backup;
alter tablespace SYSTEM begin backup;
alter tablespace UNDOTBS1 begin backup;
alter tablespace USERS begin backup;

(or) -- if you do not want a particular tablespace in backup mode,
the entire database can be brought to backup mode using the following command

SQL>alter database begin backup;

SQL> SELECT t.name, d.file# as, b.status FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b WHERE d.TS#=t.TS# AND b.FILE#=d.FILE#;

SYSTEM                                  1 ACTIVE
SYSAUX                                  2 ACTIVE
UNDOTBS1                                3 ACTIVE
USERS                                   4 ACTIVE
SAMTBS1                                 5 ACTIVE

The above result shows all the tablespaces with backup mode enabled

6. Check the database using server parameter file

SQL> show parameter spfile;

spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /dbhome_1/dbs/spfilejatway.ora

7. The below steps helps to bring individual tablespaces out of backup mode

SQL> alter tablespace SAMTBS1 end backup;

a)

SQL> alter tablespace SAMTBS1 end backup;
SQL> SELECT t.name, d.file# as, b.status FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b WHERE d.TS#=t.TS# AND b.FILE#=d.FILE#;

SYSTEM                                  1 ACTIVE
SYSAUX                                  2 ACTIVE
UNDOTBS1                                3 ACTIVE
USERS                                   4 ACTIVE
SAMTBS1                                 5 NOT ACTIVE

b)

SQL> alter tablespace SYSTEM end backup;
SQL> SELECT t.name, d.file# as, b.status FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b WHERE d.TS#=t.TS# AND b.FILE#=d.FILE#;

SYSTEM                                  1 NOT ACTIVE
SYSAUX                                  2 ACTIVE
UNDOTBS1                                3 ACTIVE
USERS                                   4 ACTIVE
SAMTBS1                                 5 NOT ACTIVE

c)

SQL> alter tablespace SYSAUX end backup;
SQL> SELECT t.name, d.file# as, b.status FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b WHERE d.TS#=t.TS# AND b.FILE#=d.FILE#;

SYSTEM                                  1 NOT ACTIVE
SYSAUX                                  2 NOT ACTIVE
UNDOTBS1                                3 ACTIVE
USERS                                   4 ACTIVE
SAMTBS1                                 5 NOT ACTIVE

d)

SQL> alter tablespace UNDOTBS1 end backup;
SQL> SELECT t.name, d.file# as, b.status FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b WHERE d.TS#=t.TS# AND b.FILE#=d.FILE#;

SYSTEM                                  1 NOT ACTIVE
SYSAUX                                  2 NOT ACTIVE
UNDOTBS1                                3 NOT ACTIVE
USERS                                   4 ACTIVE
SAMTBS1                                 5 NOT ACTIVE

e)

SQL> alter tablespace USERS end backup;
SQL> SELECT t.name, d.file# as, b.status FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b WHERE d.TS#=t.TS# AND b.FILE#=d.FILE#;

SYSTEM                                  1 NOT ACTIVE
SYSAUX                                  2 NOT ACTIVE
UNDOTBS1                                3 NOT ACTIVE
USERS                                   4 NOT ACTIVE
SAMTBS1                                 5 NOT ACTIVE

8. The following query can also be used to find whether the tablespaces is in backup mode or not

SQL> Select * from v$backup;

         1 NOT ACTIVE            1179330 25-SEP-12
         2 NOT ACTIVE            1179330 25-SEP-12
         3 NOT ACTIVE            1179330 25-SEP-12
         4 NOT ACTIVE            1179330 25-SEP-12
         5 NOT ACTIVE            1179330 25-SEP-12

9. Copy the following tablespaces to the clone location (except the temp tablespace)
I am moving all the files to the "backup" folder

SQL> Select tablespace_name, file_name from dba_data_files order by 1;
SAMTBS1                        /u01/app/oracle/oradata/jetway/jatway/samtbs01.dbf
SYSAUX                         /u01/app/oracle/oradata/jetway/jatway/sysaux01.dbf
SYSTEM                         /u01/app/oracle/oradata/jetway/jatway/system01.dbf
UNDOTBS1                       /u01/app/oracle/oradata/jetway/jatway/undotbs01.dbf
USERS                          /u01/app/oracle/oradata/jetway/jatway/users01.db

[oracle@sfo backup]$ pwd
/u01/app/oracle/oradata/jetway/jatway/backup
[oracle@sfo backup]$ ls
initclohot.ora samtbs01.dbf sysaux01.dbf system01.dbf undotbs01.dbf users01.dbf

10. Create the TAR file of the copied tablesapces. To avoid future errors I am creating another
copy of the tablespace backup folder and moving across the clone location

[oracle@sfo jatway]$ cp -r backup backup01
[oracle@sfo jatway]$ ls
arch backup backup01 backupinitclohot.ora control01.ctl redo01.log redo02.log redo03.log redo04.log redo05.log redo06.log redo07.log samtbs01.dbf sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
[oracle@sfo jatway]$ tar -cvzf backup.tar.gz backup01
backup01/
backup01/system01.dbf
backup01/control01.ora
backup01/sysaux01.dbf
backup01/initclohot.ora
backup01/samtbs01.dbf
backup01/undotbs01.dbf
backup01/users01.dbf
backup01/1_41_793069279.dbf
[oracle@sfo jatway]$

[oracle@sfo jatway]$ ls
arch    backup01              backup.tar.gz redo01.log redo03.log redo05.log redo07.log    sysaux01.dbf temp01.dbf     users01.dbf
backup backupinitclohot.ora control01.ctl redo02.log redo04.log redo06.log samtbs01.dbf system01.dbf undotbs01.dbf
[oracle@sfo jatway]$ scp backup.tar.gz oracle@192.168.168.1:/u01/app/oracle/oradata

oracle@192.168.168.1's password:

backup.tar.gz                   100% 245MB 17.5MB/s   00:14
                                                                                                                                                                           100% 245MB 17.5MB/s   00:14

11. move the password file for the clone database across network

[oracle@sfo jatway]$

[oracle@sfo jatway]$ cd $ORACLE_HOME/dbs
[oracle@sfo dbs]$ ls orapw*
orapwdeccan orapwdelta orapwjatway
[oracle@sfo dbs]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/dbs
[oracle@sfo dbs]$ scp orapwjatway oracle@192.168.168.1:/u01/app/oracle/product/11.2.0/db_1/dbs
oracle@192.168.168.1's password:
orapwjatway                                 100% 1536     1.5KB/s   00:00                                                                                                                                                                             100% 1536     1.5KB/s   00:00
[oracle@sfo dbs]$

[oracle@sfo backup]$

12. Create a backup control file for the clone location and move across the clone location.
SQL> show parameter control

control_file_record_keep_time        integer     7
control_files                        string      /u01/app/oracle/oradata/jetway
                                                 /jatway/control01.ctl, /u01/ap
                                                 p/oracle/flash_recovery_area/j
                                                 atway/control02.ctl
control_management_pack_access       string      DIAGNOSTIC+TUNING

SQL>Alter database backup controlfile to trace as '/u01/app/oracle/oradata/jetway/jatway/backup/control01.ora';

[oracle@sfo backup]$ pwd
/u01/app/oracle/oradata/jetway/jatway/backup
[oracle@sfo backup]$ ls
control01.ora initclohot.ora samtbs01.dbf sysaux01.dbf system01.dbf undotbs01.dbf users01.dbf
[oracle@sfo backup]$

oracle@sfo dbs]$ scp control01.ora oracle@192.168.168.1:/u01/app/oracle/oradata/backup/control01.ora
oracle@192.168.168.1's password:
control01.ora                                 100% 1536     1.5KB/s   00:00

13. Copy archive logs for clone database
It is only necessary to copy archive logs created during the time the source database was in backup mode.
Begin by archiving the current redo: floow the step 2 to get archive log current.

SQL>alter system archive log current;

select name from v$archived_log where first_change# >= &change_no
order by name
/

Enter value for change_no: 1134000

/u01/app/oracle/oradata/jetway/jatway/arch/1_41_793069279.dbf

14. In the clone database location (name of the clone database is "clohot") and UNTAR the zip file

[oracle@nyc oradata]$cd /u01/app/oracle/oradata
[oracle@nyc oradata]$ ls
backup.tar.gz
[oracle@nyc oradata]$ ls
backup.tar.gz

[oracle@nyc oradata]$ tar -xvzf backup.tar.gz
backup01/
backup01/system01.dbf
backup01/control01.ora
backup01/sysaux01.dbf
backup01/initclohot.ora
backup01/samtbs01.dbf
backup01/undotbs01.dbf
backup01/users01.dbf
backup01/1_41_793069279.dbf

[oracle@nyc oradata]$

[oracle@nyc oradata]$ mv backup01 clohot/
[oracle@nyc oradata]$ ls -lrt
total 250916
drwxr-xr-x 4 oracle oinstall      4096 Sep 2 23:29 delta
drwxr-xr-x 3 oracle oinstall      4096 Sep 9 05:48 deccan
-rw-r--r-- 1 oracle oinstall 256665891 Sep 25 05:11 backup.tar.gz
drwxr-xr-x 4 oracle oinstall      4096 Sep 25 09:35 clohot

[oracle@nyc oradata]$ cd clohot/
[oracle@nyc clohot]$ ls -lrt
total 8
drwxr-xr-x 2 oracle oinstall 4096 Sep 25 05:06 backup01
drwxr-xr-x 2 oracle oinstall 4096 Sep 25 09:33 arch
[oracle@nyc clohot]$

15. Prepare the init parameter file for clone database("clohot")

Actul init parameter file
*.audit_file_dest='/u01/app/oracle/admin/jatway/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/jetway/jatway/control01.ctl','/u01/app/oracle/flash_recovery_area/jatway/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/u01/app/oracle/oradata/deccan/','/u01/app/oracle/oradata/jetway/jatway'
*.db_name='jatway'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.db_unique_name='jatway'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=jatwayXDB)'
*.fal_client='delta'
*.fal_server='deccan'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(jatway,deccan)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/oradata/jetway/jatway/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=jatway'
*.LOG_ARCHIVE_DEST_2='SERVICE=deccan LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=deccan'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.log_file_name_convert='/u01/app/oracle/oradata/deccan/','/u01/app/oracle/oradata/jetway/jatway'
*.memory_target=1234173952
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='auto'
*.undo_tablespace='UNDOTBS1'
~
(note: My source database has dataguard so i am just database cone with archivelog and flashback enabled)

create the required dicectory as mentined in the parameter file
[oracle@nyc backup01]$ mkdir -p /u01/app/oracle/admin/clohot/adump
[oracle@nyc backup01]$ mkdir -p /u01/app/oracle/oradata/clohot

16. Modify the parameter as per the clone database base directory setup and add entry in /etc/oratab

[oracle@nyc backup01]$ cat initclohot.ora
*.audit_file_dest='/u01/app/oracle/admin/clohot/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/clohot/control01.ctl'
*.db_block_size=8192
*.db_domain=''
#*.db_file_name_convert='/u01/app/oracle/oradata/deccan/','/u01/app/oracle/oradata/jetway/jatway'
*.db_name='clohot'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
#*.db_unique_name='jatway'
*.diagnostic_dest='/u01/app/oracle'
#*.dispatchers='(PROTOCOL=TCP) (SERVICE=jatwayXDB)'
#*.fal_client='delta'
#*.fal_server='deccan'
#*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(jatway,deccan)'
#*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/oradata/jetway/jatway/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=jatway'
#*.LOG_ARCHIVE_DEST_2='SERVICE=deccan LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=deccan'
#*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
#*.log_archive_dest_state_2='ENABLE'
#*.log_archive_format='%t_%s_%r.dbf'
#*.LOG_ARCHIVE_MAX_PROCESSES=30
#*.log_file_name_convert='/u01/app/oracle/oradata/deccan/','/u01/app/oracle/oradata/jetway/jatway'
*.memory_target=1234173952
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
#*.standby_file_management='auto'
*.undo_tablespace='UNDOTBS1'
~
(Note: change the db_domain, db_unique_name,)

[oracle@nyc clohot]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/db_1

17. Add entry in /etc/oratab

[oracle@nyc dbs]$ vi /etc/oratab
clohot:/u01/app/oracle/product/11.2.0/db_1:N

[oracle@nyc flash_recovery_area]$ . oraenv
ORACLE_SID = [oracle] ? clohot
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@nyc flash_recovery_area]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 25 10:27:57 2012

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

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1235959808 bytes
Fixed Size                  2212896 bytes
Variable Size             738200544 bytes
Database Buffers          486539264 bytes
Redo Buffers                9007104 bytes
SQL>

18. Create the control file script.

actual control file look like this---

[oracle@nyc clohot]$ cat control01.ora
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
-- DB_UNIQUE_NAME="jatway"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE'
-- LOG_ARCHIVE_CONFIG='DG_CONFIG=("deccan")'
-- LOG_ARCHIVE_MAX_PROCESSES=30
-- STANDBY_FILE_MANAGEMENT=auto
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=delta
-- FAL_SERVER=deccan
--
-- LOG_ARCHIVE_DEST_2='SERVICE=deccan'
-- LOG_ARCHIVE_DEST_2='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_2='LGWR NOAFFIRM NOEXPEDITE NOVERIFY ASYNC=61440'
-- LOG_ARCHIVE_DEST_2='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_2='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED'
-- LOG_ARCHIVE_DEST_2='DB_UNIQUE_NAME=deccan'
-- LOG_ARCHIVE_DEST_2='VALID_FOR=(STANDBY_LOGFILE,STANDBY_ROLE)'
-- LOG_ARCHIVE_DEST_STATE_2=ENABLE
--
-- LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/oradata/jetway/jatway/arch'
-- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED'
-- LOG_ARCHIVE_DEST_1='DB_UNIQUE_NAME=jatway'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE

--
-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script file, edited as necessary, and executed when there is a
-- need to re-create the control file.
--
--     Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.

-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "JATWAY" NORESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/jetway/jatway/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/jetway/jatway/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/jetway/jatway/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
--   GROUP 4 '/u01/app/oracle/oradata/jetway/jatway/redo04.log' SIZE 50M BLOCKSIZE 512,
--   GROUP 5 '/u01/app/oracle/oradata/jetway/jatway/redo05.log' SIZE 50M BLOCKSIZE 512,
--   GROUP 6 '/u01/app/oracle/oradata/jetway/jatway/redo06.log' SIZE 50M BLOCKSIZE 512,
--   GROUP 7 '/u01/app/oracle/oradata/jetway/jatway/redo07.log' SIZE 50M BLOCKSIZE 512
DATAFILE
'/u01/app/oracle/oradata/jetway/jatway/system01.dbf',
'/u01/app/oracle/oradata/jetway/jatway/sysaux01.dbf',
'/u01/app/oracle/oradata/jetway/jatway/undotbs01.dbf',
'/u01/app/oracle/oradata/jetway/jatway/users01.dbf',
'/u01/app/oracle/oradata/jetway/jatway/samtbs01.dbf'
CHARACTER SET WE8MSWIN1252
;

-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE DISK FORMAT   ''/u01/app/oracle/oradata/jetway/jatway/backup/%U''');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/oradata/jetway/jatway/arch/1_1_694916203.dbf';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/oradata/jetway/jatway/arch/1_1_793069279.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE

-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;

-- Database can now be opened normally.
ALTER DATABASE OPEN;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/jetway/jatway/temp01.dbf'
     SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
--
--
--
----------------------------------------------------------
-- The following script can be used on the standby database
-- to re-populate entries for a standby controlfile created
-- on the primary and copied to the standby site.
----------------------------------------------------------
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/jetway/jatway/redo04.log'
SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/jetway/jatway/redo05.log'
SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/jetway/jatway/redo06.log'
SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/jetway/jatway/redo07.log'
SIZE 50M BLOCKSIZE 512 REUSE;
--     Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.

-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "JATWAY" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/jetway/jatway/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/jetway/jatway/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/jetway/jatway/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE

DATAFILE
'/u01/app/oracle/oradata/jetway/jatway/system01.dbf',
'/u01/app/oracle/oradata/jetway/jatway/sysaux01.dbf',
'/u01/app/oracle/oradata/jetway/jatway/undotbs01.dbf',
'/u01/app/oracle/oradata/jetway/jatway/users01.dbf',
'/u01/app/oracle/oradata/jetway/jatway/samtbs01.dbf'
CHARACTER SET WE8MSWIN1252
;

-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE DISK FORMAT   ''/u01/app/oracle/oradata/jetway/jatway/backup/%U''');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/oradata/jetway/jatway/arch/1_1_694916203.dbf';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/oradata/jetway/jatway/arch/1_1_793069279.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE

-- Create log files for threads other than thread one.
;

-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/jetway/jatway/temp01.dbf'
     SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
--
--
--
----------------------------------------------------------
-- The following script can be used on the standby database
-- to re-populate entries for a standby controlfile created
-- on the primary and copied to the standby site.
----------------------------------------------------------
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/jetway/jatway/redo04.log'
SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/jetway/jatway/redo05.log'
SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/jetway/jatway/redo06.log'
SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/jetway/jatway/redo07.log'
SIZE 50M BLOCKSIZE 512 REUSE;

19. using following points you can create the control file script
The file will require extensive editing before it can be used. Using your favourite editor make the following alterations:

    Remove any lines that start with '#', '--',

    Remove any blank lines in the 'CREATE CONTROLFILE' section and 'RECOVER DATABASE USING BACKUP CONTROLFILE' and 'ALTER DATABASE OPEN RESETLOGS;'

    Make a copy of the 'ALTER TABLESPACE TEMP...' lines, and then remove them from the file.
          Make sure that you hang onto the command, it will be used later.

   The word 'REUSE' needs to be changed to 'SET' and 'NORESETLOGS' changed to 'RESETLOGS'

    The database name needs setting to the new database name ("CLOHOT") (if it is being changed).

    Decide whether the database will be put into archivelog mode or not. IF YES USE "ARCHIVELOG", IF NO USE "NOARCHIVELOG"

    If the file paths are being changed, alter the file to reflect the changes.

20. changes are made as per the requirement

[oracle@nyc clohot]$ cat control01.ora
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "CLOHOT" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/clohot/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/clohot/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/clohot/redo03.log' SIZE 50M BLOCKSIZE 512
DATAFILE
'/u01/app/oracle/oradata/clohot/system01.dbf',
'/u01/app/oracle/oradata/clohot/sysaux01.dbf',
'/u01/app/oracle/oradata/clohot/undotbs01.dbf',
'/u01/app/oracle/oradata/clohot/users01.dbf',
'/u01/app/oracle/oradata/clohot/samtbs01.dbf'
CHARACTER SET WE8MSWIN1252
;

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> @/u01/app/oracle/oradata/clohot/contro1_create.sql
ORACLE instance started.

Total System Global Area 1235959808 bytes
Fixed Size                  2212896 bytes
Variable Size             738200544 bytes
Database Buffers          486539264 bytes
Redo Buffers                9007104 bytes

Control file created

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

21. now apply the archivelog to perfornm the recovery.

SQL> recover database using backup controlfile until cancel;
(note: The above show the suggested archive log so you need to give full path of the arhivelog)

ORA-00279: change 1179330 generated at 09/25/2012 04:35:11 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/CLOHOT/archivelog/2012_09_25/o1_mf_1_42_%u_.arc
ORA-00280: change 1179330 for thread 1 is in sequence #42

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto

SQL> RECOVER DATABASE using backup controlfile until cancel;
ORA-00279: change 1179330 generated at 09/25/2012 04:35:11 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/CLOHOT/archivelog/2012_09_25/1_42_793069279.dbf
ORA-00280: change 1179330 for thread 1 is in sequence #42

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/flash_recovery_area/CLOHOT/archivelog/2012_09_25/1_42_793069279.dbf
ORA-00279: change 1196618 generated at 09/25/2012 09:00:51 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/CLOHOT/archivelog/2012_09_25/o1_mf_1_43_%u_.arc
ORA-00280: change 1196618 for thread 1 is in sequence #43
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/CLOHOT/archivelog/2012_09_25/1_42_793069279.dbf'
no longer needed for this recovery
(note: archivelog "1_42*" applied now it required "1_43*"

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

SQL> RECOVER DATABASE using backup controlfile until cancel;
ORA-00279: change 1196618 generated at 09/25/2012 09:00:51 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/CLOHOT/archivelog/2012_09_25/o1_mf_1_43_%u_.arc
ORA-00280: change 1196618 for thread 1 is in sequence #43

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

22. Now open the database with resetlog.
(if archivelog not applied preperly it thow throw the error when open the database
Re: ORA-01195: online backup of file 1 needs more recovery to be consistent )

SQL> alter database open resetlogs;

Database altered.

SQL> select status from v$instance;

STATUS
------------
OPEN

23. create spfile from pfile and shutdown and startup the database toapply the change.

SQL> create spfile from pfile;

File created.

24. create the temporary table for the clone database

CREATE TEMPORARY TABLESPACE temp01 TEMPFILE '/u01/app/oracle/oradata/clohot/temp_01.tmp' SIZE 20M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

SQL> select NAME, TS# from v$TEMPFILE ;

NAME                                                         TS#
--------------------------------------------------------------------------------

/u01/app/oracle/oradata/clohot/temp_01.tmp                    7

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp01;

Database altered.

SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

TABLESPACE_NAME                BYTES_USED BYTES_FREE
------------------------------ ---------- ----------
TEMP01                            4194304   16777216

SQL>shutdown immdiate

25. change the database ID (DBID) if we clone tha database using rman it will change the DBID automatically.
due to the manual clone we need to change the DBID using "nid utility"

SQL> startup mount
ORACLE instance started.

Total System Global Area 1235959808 bytes
Fixed Size                  2212896 bytes
Variable Size             738200544 bytes
Database Buffers          486539264 bytes
Redo Buffers                9007104 bytes
Database mounted.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@nyc flash_recovery_area]$ nid target=/

DBNEWID: Release 11.2.0.1.0 - Production on Tue Sep 25 12:16:05 2012

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

Connected to database CLOHOT (DBID=3196377116)

Connected to server version 11.2.0

Control Files in database:
    /u01/app/oracle/oradata/clohot/control01.ctl

Change database ID of database CLOHOT? (Y/[N]) => y (note: here we have to give to give "Y" to change the DBID)

Proceeding with operation
Changing database ID from 3196377116 to 2915369990
    Control File /u01/app/oracle/oradata/clohot/control01.ctl - modified
    Datafile /u01/app/oracle/oradata/clohot/system01.db - dbid changed
    Datafile /u01/app/oracle/oradata/clohot/sysaux01.db - dbid changed
    Datafile /u01/app/oracle/oradata/clohot/undotbs01.db - dbid changed
    Datafile /u01/app/oracle/oradata/clohot/users01.db - dbid changed
    Datafile /u01/app/oracle/oradata/clohot/samtbs01.db - dbid changed
    Datafile /u01/app/oracle/oradata/clohot/temp_01.tm - dbid changed
    Control File /u01/app/oracle/oradata/clohot/control01.ctl - dbid changed
    Instance shut down

Database ID for database CLOHOT changed to 2915369990.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.

26. Finally mount and open the database with resetlog option

SQL>startup mount
SQL>alter database open resetlogs
Database altered.

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> show parameter spfile;

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

SQL> select max(first_change#) Latest_chng from v$archived_log;

LATEST_CHNG
-----------
(not showing any value)

SQL> conn tom/tom
Connected.

SQL> select * from tab;
A                              TABLE
B                              TABLE

SQL> select * from b;

         8
         8
         8
         8
         8
SQL>

27. Configure the listener and tns as per the requirement

ORACLE DATABASE ADMINISTRATOR

Search