Saturday, January 21, 2012

Create duplicate database on same host

Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.

C:\Documents and Settings\Administrator>sqlplus

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Jan 21 02:30:56 2012

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

Enter user-name: sys /as sysdba
Enter password:

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

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 3376
Session ID: 132 Serial number: 2118


SQL> startup nomount;
ORACLE instance started.

Total System Global Area 431038464 bytes
Fixed Size 1333676 bytes
Variable Size 264242772 bytes
Database Buffers 159383552 bytes
Redo Buffers 6078464 bytes
SQL> alter database mount;

Database altered.

SQL> alter database archivelog
2 ;

Database altered.

SQL> alter database open;

Database altered.

SQL> list archivelog
SP2-0224: invalid starting line number
SQL> list archive log
SP2-0224: invalid starting line number
SQL> archivelog list
SP2-0734: unknown command beginning "archivelog..." - rest of line ignored.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4
SQL>



Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.

C:\Documents and Settings\Administrator>export oracle_sid=orcl
'export' is not recognized as an internal or external command,
operable program or batch file.

C:\Documents and Settings\Administrator>rman target /

Recovery Manager: Release 11.1.0.6.0 - Production on Sat Jan 21 02:38:33 2012

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

connected to target database: ORCL (DBID=1300829239)

Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.

C:\Documents and Settings\Administrator>export oracle_sid=orcl
'export' is not recognized as an internal or external command,
operable program or batch file.

C:\Documents and Settings\Administrator>rman target /

Recovery Manager: Release 11.1.0.6.0 - Production on Sat Jan 21 02:38:33 2012

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

connected to target database: ORCL (DBID=1300829239)

RMAN> backup database plus archivelog delete input
2> ;


Starting backup at 21-JAN-12
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=121 device type=DISK
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=4 RECID=2 STAMP=773116743
channel ORA_DISK_1: starting piece 1 at 21-JAN-12
channel ORA_DISK_1: finished piece 1 at 21-JAN-12
piece handle=C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2012_01_21\
O1_MF_ANNNN_TAG20120121T023904_7KO5GB4V_.BKP tag=TAG20120121T023904 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: deleting archived log(s)
archived log file name=C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\
2012_01_21\O1_MF_1_4_7KO5G3WB_.ARC RECID=2 STAMP=773116743
Finished backup at 21-JAN-12

Starting backup at 21-JAN-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=C:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01
.DBF
input datafile file number=00002 name=C:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01
.DBF
input datafile file number=00005 name=C:\APP\ADMINISTRATOR\ORADATA\ORCL\EXAMPLE0
1.DBF
input datafile file number=00003 name=C:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS0
1.DBF
input datafile file number=00004 name=C:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.
DBF
channel ORA_DISK_1: starting piece 1 at 21-JAN-12
channel ORA_DISK_1: finished piece 1 at 21-JAN-12
piece handle=C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2012_01_21\
O1_MF_NNNDF_TAG20120121T023914_7KO5GRWB_.BKP tag=TAG20120121T023914 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:08:36
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 21-JAN-12
channel ORA_DISK_1: finished piece 1 at 21-JAN-12
piece handle=C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2012_01_21\
O1_MF_NCSNF_TAG20120121T023914_7KO5YZL6_.BKP tag=TAG20120121T023914 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 21-JAN-12

Starting backup at 21-JAN-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=5 RECID=4 STAMP=773117292
channel ORA_DISK_1: starting piece 1 at 21-JAN-12
channel ORA_DISK_1: finished piece 1 at 21-JAN-12
piece handle=C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2012_01_21\
O1_MF_ANNNN_TAG20120121T024813_7KO5ZGCC_.BKP tag=TAG20120121T024813 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: deleting archived log(s)
archived log file name=C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\
2012_01_21\O1_MF_1_5_7KO5Z8DZ_.ARC RECID=4 STAMP=773117292
Finished backup at 21-JAN-12

RMAN> list backup
2> ;


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


BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1 7.44M DISK 00:00:03 21-JAN-12
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20120121T023904
Piece Name: C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2012
_01_21\O1_MF_ANNNN_TAG20120121T023904_7KO5GB4V_.BKP

List of Archived Logs in backup set 1
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 4 956121 21-JAN-12 959981 21-JAN-12

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 1.04G DISK 00:08:26 21-JAN-12
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20120121T023914
Piece Name: C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2012
_01_21\O1_MF_NNNDF_TAG20120121T023914_7KO5GRWB_.BKP
List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 959996 21-JAN-12 C:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.D
BF
2 Full 959996 21-JAN-12 C:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.D
BF
3 Full 959996 21-JAN-12 C:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.
DBF
4 Full 959996 21-JAN-12 C:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DB
F
5 Full 959996 21-JAN-12 C:\APP\ADMINISTRATOR\ORADATA\ORCL\EXAMPLE01.
DBF

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 9.36M DISK 00:00:10 21-JAN-12
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20120121T023914
Piece Name: C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2012
_01_21\O1_MF_NCSNF_TAG20120121T023914_7KO5YZL6_.BKP
SPFILE Included: Modification time: 21-JAN-12
SPFILE db_unique_name: ORCL
Control File Included: Ckp SCN: 960953 Ckp time: 21-JAN-12

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
4 4.31M DISK 00:00:05 21-JAN-12
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20120121T024813
Piece Name: C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2012
_01_21\O1_MF_ANNNN_TAG20120121T024813_7KO5ZGCC_.BKP

List of Archived Logs in backup set 4
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 5 959981 21-JAN-12 960978 21-JAN-12

RMAN>


back from sqlplus

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create pfile from spfile;

File created.

copy and rename pfile

C:\Documents and Settings\Administrator>copy C:\app\Administrator\product\11.1.0
\db_1\database\initorcl.ora C:\app\Administrator\product\11.1.0\db_1\database\in
ittest.ora
1 file(s) copied.


create password file
C:\Documents and Settings\Administrator>set oracle_sid=test

C:\Documents and Settings\Administrator>orapwd file=c:\app\Administrator\product
\11.1.0\db_1\database\orapwtest password=sys



C:\Documents and Settings\Administrator>oradim -new -sid test -usrpwd oracle

Instance created.
The Oracle test VSS Writer Service service is starting.
The Oracle test VSS Writer Service service was started successfully.


C:\Documents and Settings\Administrator>

C:\Documents and Settings\Administrator>set oracle_sid=test

C:\Documents and Settings\Administrator>sqlplus

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Jan 21 04:02:10 2012

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

Enter user-name: sys /as sysdba
Enter password:
Connected to an idle instance.

ORACLE instance started.

Total System Global Area 431038464 bytes
Fixed Size 1333676 bytes
Variable Size 255854164 bytes
Database Buffers 167772160 bytes
Redo Buffers 6078464 bytes
SQL>SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
test

C:\Documents and Settings\Administrator>RMAN TARGET / auxiliary sys/sys

Recovery Manager: Release 11.1.0.6.0 - Production on Sat Jan 21 05:40:06 2012

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

connected to target database: TEST (not mounted)
connected to auxiliary database: TEST (not mounted)

C:\Documents and Settings\Administrator>set oracle_sid=orcl

C:\Documents and Settings\Administrator>rman target / auxiliary sys/sys@test

Recovery Manager: Release 11.1.0.6.0 - Production on Sat Jan 21 08:04:38 2012

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

connected to target database: ORCL (DBID=1300829239)
connected to auxiliary database: TEST (not mounted)

RMAN> run
2> {
3> allocate auxiliary channel aux1 device type disk;
4> duplicate target database to 'test';
5> }

using target database control file instead of recovery catalog
allocated channel: aux1
channel aux1: SID=151 device type=DISK

Starting Duplicate Db at 21-JAN-12

contents of Memory Script:
{
set until scn 992917;
set newname for datafile 1 to
"C:\APP\ADMINISTRATOR\ORADATA\TEST\SYSTEM01.DBF";
set newname for datafile 2 to
"C:\APP\ADMINISTRATOR\ORADATA\TEST\SYSAUX01.DBF";
set newname for datafile 3 to
"C:\APP\ADMINISTRATOR\ORADATA\TEST\UNDOTBS01.DBF";
set newname for datafile 4 to
"C:\APP\ADMINISTRATOR\ORADATA\TEST\USERS01.DBF";
set newname for datafile 5 to
"C:\APP\ADMINISTRATOR\ORADATA\TEST\EXAMPLE01.DBF";
restore
clone database
;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 21-JAN-12

channel aux1: starting datafile backup set restore
channel aux1: specifying datafile(s) to restore from backup set
channel aux1: restoring datafile 00001 to C:\APP\ADMINISTRATOR\ORADATA\TEST\SYST
EM01.DBF
channel aux1: restoring datafile 00002 to C:\APP\ADMINISTRATOR\ORADATA\TEST\SYSA
UX01.DBF
channel aux1: restoring datafile 00003 to C:\APP\ADMINISTRATOR\ORADATA\TEST\UNDO
TBS01.DBF
channel aux1: restoring datafile 00004 to C:\APP\ADMINISTRATOR\ORADATA\TEST\USER
S01.DBF
channel aux1: restoring datafile 00005 to C:\APP\ADMINISTRATOR\ORADATA\TEST\EXAM
PLE01.DBF
channel aux1: reading from backup piece C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA
\ORCL\BACKUPSET\2012_01_21\O1_MF_NNNDF_TAG20120121T023914_7KO5GRWB_.BKP
channel aux1: piece handle=C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\BACKUPS
ET\2012_01_21\O1_MF_NNNDF_TAG20120121T023914_7KO5GRWB_.BKP tag=TAG20120121T02391
4
channel aux1: restored backup piece 1
channel aux1: restore complete, elapsed time: 00:10:56
Finished restore at 21-JAN-12
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TEST" RESETLOGS ARCHIVELOG

MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 50 M ,
GROUP 2 SIZE 50 M ,
GROUP 3 SIZE 50 M
DATAFILE
'C:\APP\ADMINISTRATOR\ORADATA\TEST\SYSTEM01.DBF'
CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=773136994 file name=C:\APP\ADMINISTRATOR\ORADA
TA\TEST\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=773136995 file name=C:\APP\ADMINISTRATOR\ORADA
TA\TEST\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=773136995 file name=C:\APP\ADMINISTRATOR\ORADA
TA\TEST\USERS01.DBF
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=773136996 file name=C:\APP\ADMINISTRATOR\ORADA
TA\TEST\EXAMPLE01.DBF

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

executing command: SET until clause

Starting recover at 21-JAN-12

starting media recovery

archived log for thread 1 with sequence 5 is already on disk as file C:\APP\ADMI
NISTRATOR\PRODUCT\11.1.0\DB_1\RDBMS\ARC00005_0773048512.001
archived log for thread 1 with sequence 6 is already on disk as file C:\APP\ADMI
NISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2012_01_21\O1_MF_1_6_7KON8HYM_.ARC

archived log file name=C:\APP\ADMINISTRATOR\PRODUCT\11.1.0\DB_1\RDBMS\ARC00005_0
773048512.001 thread=1 sequence=5
archived log file name=C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\
2012_01_21\O1_MF_1_6_7KON8HYM_.ARC thread=1 sequence=6
media recovery complete, elapsed time: 00:01:44
Finished recover at 21-JAN-12

contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount ;
}
executing Memory Script

database dismounted
Oracle instance shut down

RMAN>

Enter user-name: sys /as sysdba
Enter password:
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 431038464 bytes
Fixed Size 1333676 bytes
Variable Size 255854164 bytes
Database Buffers 167772160 bytes
Redo Buffers 6078464 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.

SQL>
SQL> select name from v$database;

NAME
---------
TEST

No comments:

Post a Comment