Wednesday, December 28, 2011

ORA-28365: wallet is not open

ORA-28365: wallet is not open:


One should be careful in securing the key and i think data can not be viewed unless we have the right key and wallet is open with right key.

Please do check with oracle online documentation all the time... its good.


SQL> select * from tde_test;
select * from tde_test
*
ERROR at line 1:
ORA-28365: wallet is not open


SQL> conn sys as sysdba
Enter password:
Connected.
SQL> desc dba_wallets;
ERROR:
ORA-04043: object dba_wallets does not exist


SQL> desc dba_wallet_acls;
Name Null? Type
----------------------------------------- -------- ----------------------------

WALLET_PATH NOT NULL VARCHAR2(1000)
ACL VARCHAR2(4000)
ACLID NOT NULL RAW(16)

SQL> select * from dba_wallet_Acls;

no rows selected

SQL>
SQL> desc v$encryption_Wallet;
Name Null? Type
----------------------------------------- -------- ----------------------------

WRL_TYPE VARCHAR2(20)
WRL_PARAMETER VARCHAR2(4000)
STATUS VARCHAR2(18)

SQL> select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------

STATUS
------------------
file
F:\app\HOME\admin\orcl\encryption_wallet/
CLOSED


SQL> desc v$wallet;
Name Null? Type
----------------------------------------- -------- ----------------------------

CERT_ID VARCHAR2(52)
DN VARCHAR2(255)
SERIAL_NUM VARCHAR2(40)
ISSUER VARCHAR2(255)
KEYSIZE NUMBER
STATUS VARCHAR2(16)

SQL> select * from v$wallet;

no rows selected



SQL> alter system set wallet open identified by "wrongpassword";
alter system set wallet open identified by "1myPassword"
*
ERROR at line 1:
ORA-28353: failed to open wallet


SQL> alter system set wallet open identified by "correctpassword";

System altered.

SQL> conn test/test;
SQL> select * from tde_test;

ID DATA
---------- --------------------------------------------------
1 It is a secret and can not be shown unless wallet is open!

SQL>
SQL>


Please note: wallet should be open each time the db is restarted.
One can close the wallet with alter command : ALTER SYSTEM SET WALLET CLOSE;

DBA personal should remember the key or should keep the key safe...

-- Have a fun working in oracle technologies.. Cheers....

ORA-28365: wallet is not open

ORA-28365: wallet is not open:


One should be careful in securing the key and i think data can not be viewed unless we have the right key and wallet is open with right key.

Please do check with oracle online documentation all the time... its good.


SQL> select * from tde_test;
select * from tde_test
*
ERROR at line 1:
ORA-28365: wallet is not open


SQL> conn sys as sysdba
Enter password:
Connected.
SQL> desc dba_wallets;
ERROR:
ORA-04043: object dba_wallets does not exist


SQL> desc dba_wallet_acls;
Name Null? Type
----------------------------------------- -------- ----------------------------

WALLET_PATH NOT NULL VARCHAR2(1000)
ACL VARCHAR2(4000)
ACLID NOT NULL RAW(16)

SQL> select * from dba_wallet_Acls;

no rows selected

SQL>
SQL> desc v$encryption_Wallet;
Name Null? Type
----------------------------------------- -------- ----------------------------

WRL_TYPE VARCHAR2(20)
WRL_PARAMETER VARCHAR2(4000)
STATUS VARCHAR2(18)

SQL> select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------

STATUS
------------------
file
F:\app\HOME\admin\orcl\encryption_wallet/
CLOSED


SQL> desc v$wallet;
Name Null? Type
----------------------------------------- -------- ----------------------------

CERT_ID VARCHAR2(52)
DN VARCHAR2(255)
SERIAL_NUM VARCHAR2(40)
ISSUER VARCHAR2(255)
KEYSIZE NUMBER
STATUS VARCHAR2(16)

SQL> select * from v$wallet;

no rows selected



SQL> alter system set wallet open identified by "wrongpassword";
alter system set wallet open identified by "1myPassword"
*
ERROR at line 1:
ORA-28353: failed to open wallet


SQL> alter system set wallet open identified by "correctpassword";

System altered.

SQL> conn test/test;
SQL> select * from tde_test;

ID DATA
---------- --------------------------------------------------
1 It is a secret and can not be shown unless wallet is open!

SQL>
SQL>


Please note: wallet should be open each time the db is restarted.
One can close the wallet with alter command : ALTER SYSTEM SET WALLET CLOSE;

DBA personal should remember the key or should keep the key safe...

-- Have a fun working in oracle technologies.. Cheers....

Sunday, December 25, 2011

How to solve ORA-00845: MEMORY_TARGET not supported on this system having no root access

How to solve ORA-00845: MEMORY_TARGET not supported on this system having no root access

Please do check with oracle online documentation or metalink for further infor.. good to check this before we do anything.. this is my view..

I have got the the ORA-00845: MEMORY_TARGET not supported on this system

I Have no root access.

when i try to start the idle instance..
Oracle Version: 11.2.0.2.0
OS: Oracle Linux

 sqlplus "/as sysdba"

Connected to an idle instance.

SQL> startup
ORA-00845: MEMORY_TARGET not supported on this system
SQL> exit

I have no root access to run the following statements or have no grant to change /etc/fstab file..

umount tmpfs
mount -t tmpfs shmfs -o size=1500m /dev/shm

what i have done is that i checked if there are any oracle processes are running, my db is down anyway..

ps -elf |grep xxxttdb1 | grep ora_

Note down all the processes ID..
Kill -9 x,x1, x2 ... so on...

kill all the processes ids using kill command .. (db must or should be down before using kill.. if db is up.. one should not run kill command to kill the db processes...)

wait awhile....

the start the db..

sqlplus "/as sysdba"

..
..
Database opened.


Check the AMM configuration settings, sga, pga memory settings,..
calculate required memory_target values .. sga+greatest(pga_agg... max pga allocated) ... check with v$sgastat, v$pgastat..

alter system memory_max_Target=xG scope=spfile;
alter system memory_target=yG scope=spfile;

alter system sga=0
alter system pga=0

shut immediate;
startup;

it does not mean you should right away start AMM.. check from all aspects and do the as needed...


Good to have: I like to have a pfile all the time no matter whether db is running using spfile or pfile.. no matter what.. having a pfile backup always good..
if we have pfile, we can simply change parameter and start the db when there are issues.... having pfile backup always (db need not use pfile, always good to use spfile)a safe act...

In summary, have a sound full backup all the time..

more to come...

Have a fun .. working with oracle technologies and cheers..

How to solve ORA-00845: MEMORY_TARGET not supported on this system having no root access

How to solve ORA-00845: MEMORY_TARGET not supported on this system having no root access

Please do check with oracle online documentation or metalink for further infor.. good to check this before we do anything.. this is my view..

I have got the the ORA-00845: MEMORY_TARGET not supported on this system

I Have no root access.

when i try to start the idle instance..
Oracle Version: 11.2.0.2.0
OS: Oracle Linux

sqlplus "/as sysdba"

Connected to an idle instance.

SQL> startup
ORA-00845: MEMORY_TARGET not supported on this system
SQL> exit

I have no root access to run the following statements or have no grant to change /etc/fstab file..

umount tmpfs
mount -t tmpfs shmfs -o size=1500m /dev/shm

what i have done is that i checked if there are any oracle processes are running, my db is down anyway..

ps -elf |grep xxxttdb1 | grep ora_

Note down all the processes ID..
Kill -9 x,x1, x2 ... so on...

kill all the processes ids using kill command .. (db must or should be down before using kill.. if db is up.. one should not run kill command to kill the db processes...)

wait awhile....

the start the db..

sqlplus "/as sysdba"

..
..
Database opened.


Check the AMM configuration settings, sga, pga memory settings,..
calculate required memory_target values .. sga+greatest(pga_agg... max pga allocated) ... check with v$sgastat, v$pgastat..

alter system memory_max_Target=xG scope=spfile;
alter system memory_target=yG scope=spfile;

alter system sga=0
alter system pga=0

shut immediate;
startup;

it does not mean you should right away start AMM.. check from all aspects and do the as needed...


Good to have: I like to have a pfile all the time no matter whether db is running using spfile or pfile.. no matter what.. having a pfile backup always good..
if we have pfile, we can simply change parameter and start the db when there are issues.... having pfile backup always (db need not use pfile, always good to use spfile)a safe act...

In summary, have a sound full backup all the time..

more to come...

Have a fun .. working with oracle technologies and cheers..

Friday, December 23, 2011

CREATE DB FROM RMAN BACKUPS WITH SAME DBNAME ON SAME HOST OR REMOTE HOST

CREATE DB FROM RMAN BACKUPS WITH SAME DBNAME ON SAME HOST OR REMOTE HOST IN ORACLE 11G:

Please do check with oracle online documentation before we do any backup and recovery steps, I think it is good.. having complete failed backup of db/dbfiles storage would help us if something goes wrong in our recovery.. have a copy of it...


One should ensure that Database has valid RMAN full backup all the available......at least three places...

Problem: All the files are lost, including spfile, controlfile, dbf files, redo logs.. only we have good rman backup..

C:\Users\HOME>rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Dec 21 20:53:09 2011

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

connected to target database: TSTDEV01 (DBID=874354934)

RMAN> SHOW ALL;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name TSTDEV01 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
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 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 LOA
D TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'F:\APP\HOME\PRODUCT\11.2.0\DBHOME_1\DATA
BASE\SNCFTSTDEV01.ORA'; # default

set oracle_sid
C:\Users\HOME>set ORACLE_SID=TSTDEV01


Connect rman to take database backup
C:\Users\HOME>rman target /
connected to target database: TSTDEV01 (DBID=874354934)

3. take the database backup using one of the way shown below

run
{
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT 'd:\testdelete\dbf\%U';
backup database plus archivelog delete input;
}

or

run
{
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT 'd:\testdelete\dbf\%U';
backup database plus archivelog delete input;
backup current controlfile format 'd:\testdelete\dbf\control.back';
BACKUP SPFILE TO DESTINATION 'd:\testdelete\dbf';
BACKUP AS COPY CURRENT CONTROLFILE FORMAT 'd:\testdelete\dbf\control01.ctl';
}

Ensure that where control file, spfile backup is being made. it is important. I do like to run second run block no matter how many control files are created... good to safe...
Second run would help us when recovery disk location is lost.. otherwise first run block is okay...



---------------------- Restore database from RMAN backup starts from here....------------------

LOST DATABASE FILES (SPFILE, CONTROL FILES, DBF AND REDO LOGS ).. ONLY HAVE RMAN FULL BACKUP

1. Create instance
C:\Users\HOME>oradim -new -sid TSTDEV01 -intpwd oracle -startmode m
Instance created.

After creation of instance, one can use netmanager and netca to configure listener.ora and tnsnames.ora etc files... or one can go for manuall edit (ensure entries are made in right format)..
2. Add the following entry in listener.ora file

(SID_DESC =
(GLOBAL_DBNAME=TSTDEV01)
(SID_NAME = TSTDEV01)
(ORACLE_HOME = F:\app\HOME\product\11.2.0\dbhome_1)
)

3. Add the following entry into tnsnames.ora file

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



C:\Users\HOME>lsnrctl reload
C:\Users\HOME>lsnrclt status

Ensure instance is ready and accessible.

C:\Users\HOME>tnsping TSTDEV01

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 21-DEC-2
011 21:27:06

Copyright (c) 1997, 2010, Oracle. All rights reserved.

Used parameter files:
F:\app\HOME\product\11.2.0\dbhome_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhos
t)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TSTDEV01))
)
OK (20 msec)

C:\Users\HOME>

I see lsnrctl stop, lsnrctl start would help here .. if u can not able to login using sqlplus sys/pwd@dbname as sysdba..
C:\Users\HOME>SQLPLUS SYS/ORACLE@TSTDEV01 AS SYSDBA

SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 21 21:34:10 2011

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

Connected to an idle instance.

SQL>

4. Create the spfile from the rman backup

C:\Users\HOME>RMAN TARGET SYS/ORACLE@TSTDEV01

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Dec 21 21:35:03 2011

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

connected to target database (not started)

RMAN> set DBID=9945147239

executing command: SET DBID

RMAN> startup force nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'F:\APP\HOME\PRODUCT\11.2.0\DBHOME_1\DA
TABASE\INITTSTDEV01.ORA'

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

Total System Global Area 159019008 bytes

Fixed Size 1373264 bytes
Variable Size 75500464 bytes
Database Buffers 75497472 bytes
Redo Buffers 6647808 bytes

RMAN> restore spfile from 'F:\app\HOME\flash_recovery_area\TSTDEV01\AUTOBACKUP\2011_12_21\O1_MF_S_770504345_7H3YT5T9_.BKP';

Starting restore at 21-DEC-11
using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP F:\app\HOME\flash_recovery_
area\TSTDEV01\AUTOBACKUP\2011_12_21\O1_MF_S_770504345_7H3YT5T9_.BKP
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 21-DEC-11

RMAN>

Run the following command at command prompt to create pfile from spfile that is just created from rman backup
sql "create PFILE = ''D:\TESTDELETE\PFILE'' from SPFILE = ''F:\app\HOME\product\11.2.0\dbhome_1\database\SPFILETSTDEV01.ORA''";

RMAN> sql "create PFILE = ''D:\TESTDELETE\PFILE'' from SPFILE = ''F:\app\HOME\pr
oduct\11.2.0\dbhome_1\database\SPFILETSTDEV01.ORA''";

sql statement: create PFILE = ''D:\TESTDELETE\PFILE'' from SPFILE = ''F:\app\HOM
E\product\11.2.0\dbhome_1\database\SPFILETSTDEV01.ORA''

RMAN>exit

5. Create control file from RMAN backups

Shutdown the database and start with spfile (create in step 4)

C:\Users\HOME>SQLPLUS SYS/ORACLE@TSTDEV01 AS SYSDBA

SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 21 21:46:49 2011

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


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


ORACLE instance shut down.
SQL> startup nomount;
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_TSTDEV01'

remove the following line from pfile the one just created
*.local_listener='LISTENER_TSTDEV01'


SQL> startup nomount pfile='d:\testdelete\PFILE';
ORACLE instance started.

Total System Global Area 640286720 bytes
Fixed Size 1376492 bytes
Variable Size 197136148 bytes
Database Buffers 436207616 bytes
Redo Buffers 5566464 bytes
SQL>

6. Get the controle file from backup

Here are many ways to get the controlfile from RMAN backup.... shown three ways ( I dont know but some reason I backup control file many times..see the rman backup run script above).

 RMAN> restore controlfile from autobackup;

Starting restore at 23-DEC-11
using channel ORA_DISK_1

recovery area destination: F:\app\HOME\flash_recovery_area
database name (or database unique name) used for search: TSTDEV01
channel ORA_DISK_1: AUTOBACKUP F:\APP\HOME\FLASH_RECOVERY_AREA\TSTDEV01\AUTOBACK
UP\2011_12_22\O1_MF_S_770591689_7H6N3ML1_.BKP found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP F:\APP\HOME\FLASH_REC
OVERY_AREA\TSTDEV01\AUTOBACKUP\2011_12_22\O1_MF_S_770591689_7H6N3ML1_.BKP
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=F:\APP\HOME\ORADATA\TSTDEV01\CONTROL01.CTL
output file name=F:\APP\HOME\FLASH_RECOVERY_AREA\TSTDEV01\CONTROL02.CTL
Finished restore at 23-DEC-11

RMAN> restore controlfile from 'D:\TESTDELETE\dbf\control.back';

Starting restore at 23-DEC-11
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=F:\APP\HOME\ORADATA\TSTDEV01\CONTROL01.CTL
output file name=F:\APP\HOME\FLASH_RECOVERY_AREA\TSTDEV01\CONTROL02.CTL
Finished restore at 23-DEC-11

RMAN> restore controlfile from 'D:\TESTDELETE\dbf\control01.ctl';

Starting restore at 23-DEC-11
using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
output file name=F:\APP\HOME\ORADATA\TSTDEV01\CONTROL01.CTL
output file name=F:\APP\HOME\FLASH_RECOVERY_AREA\TSTDEV01\CONTROL02.CTL
Finished restore at 23-DEC-11

RMAN>
RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 23-DEC-11
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to F:\APP\HOME\ORADATA\TSTDEV01\SYS
TEM01.DBF
channel ORA_DISK_1: restoring datafile 00002 to F:\APP\HOME\ORADATA\TSTDEV01\SYS
AUX01.DBF
channel ORA_DISK_1: restoring datafile 00003 to F:\APP\HOME\ORADATA\TSTDEV01\UND
OTBS01.DBF
channel ORA_DISK_1: restoring datafile 00004 to F:\APP\HOME\ORADATA\TSTDEV01\USE
RS01.DBF
channel ORA_DISK_1: reading from backup piece D:\TESTDELETE\DBF\0KMUSIQ7_1_1
channel ORA_DISK_1: piece handle=D:\TESTDELETE\DBF\0KMUSIQ7_1_1 tag=TAG20111222T
211239
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:25
Finished restore at 23-DEC-11

RMAN> recover database;

Starting recover at 23-DEC-11
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=16
channel ORA_DISK_1: reading from backup piece D:\TESTDELETE\DBF\0LMUSITS_1_1
channel ORA_DISK_1: piece handle=D:\TESTDELETE\DBF\0LMUSITS_1_1 tag=TAG20111222T
211436
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=F:\APP\HOME\FLASH_RECOVERY_AREA\TSTDEV01\ARCHIVELOG\2011_
12_23\O1_MF_1_16_7H94R2H4_.ARC thread=1 sequence=16
channel default: deleting archived log(s)
archived log file name=F:\APP\HOME\FLASH_RECOVERY_AREA\TSTDEV01\ARCHIVELOG\2011_
12_23\O1_MF_1_16_7H94R2H4_.ARC RECID=16 STAMP=770674266
unable to find archived log
archived log thread=1 sequence=17
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/23/2011 20:11:10
RMAN-06054: media recovery requesting unknown archived log for thread 1 with seq
uence 17 and starting SCN of 958969

RMAN> recover database until logseq 17;

Starting recover at 23-DEC-11
using channel ORA_DISK_1

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

Finished recover at 23-DEC-11

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 12/23/2011 20:12:30
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


RMAN> alter database open resetlogs;

database opened

RMAN>

Note: Usually resetlogs used when incomplete recovery took place otherwise we should go for noresetlogs option.
RESETLOGS would initialize all the logs, reset the log sequence number from start and start a new incarnation of the database.


RMAN> exit


Recovery Manager complete.

C:\Users\HOME>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 23 20:22:23 2011

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


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

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
SQL> select name, open_mode from v$database;

NAME OPEN_MODE
--------- --------------------
TSTDEV01 READ WRITE

SQL>


Check everything and have a good backup once everything looks good.. Good to have backup at this moment.

.. Have a great fun in working in oracle technologies... Cheers....

CREATE DB FROM RMAN BACKUPS WITH SAME DBNAME ON SAME HOST OR REMOTE HOST

CREATE DB FROM RMAN BACKUPS WITH SAME DBNAME ON SAME HOST OR REMOTE HOST IN ORACLE 11G:

Please do check with oracle online documentation before we do any backup and recovery steps, I think it is good.. having complete failed backup of db/dbfiles storage would help us if something goes wrong in our recovery.. have a copy of it...


One should ensure that Database has valid RMAN full backup all the available......at least three places...

Problem: All the files are lost, including spfile, controlfile, dbf files, redo logs.. only we have good rman backup..

C:\Users\HOME>rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Dec 21 20:53:09 2011

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

connected to target database: TSTDEV01 (DBID=874354934)

RMAN> SHOW ALL;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name TSTDEV01 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
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 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 LOA
D TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'F:\APP\HOME\PRODUCT\11.2.0\DBHOME_1\DATA
BASE\SNCFTSTDEV01.ORA'; # default

set oracle_sid
C:\Users\HOME>set ORACLE_SID=TSTDEV01


Connect rman to take database backup
C:\Users\HOME>rman target /
connected to target database: TSTDEV01 (DBID=874354934)

3. take the database backup using one of the way shown below

run
{
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT 'd:\testdelete\dbf\%U';
backup database plus archivelog delete input;
}

or

run
{
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT 'd:\testdelete\dbf\%U';
backup database plus archivelog delete input;
backup current controlfile format 'd:\testdelete\dbf\control.back';
BACKUP SPFILE TO DESTINATION 'd:\testdelete\dbf';
BACKUP AS COPY CURRENT CONTROLFILE FORMAT 'd:\testdelete\dbf\control01.ctl';
}

Ensure that where control file, spfile backup is being made. it is important. I do like to run second run block no matter how many control files are created... good to safe...
Second run would help us when recovery disk location is lost.. otherwise first run block is okay...



---------------------- Restore database from RMAN backup starts from here....------------------

LOST DATABASE FILES (SPFILE, CONTROL FILES, DBF AND REDO LOGS ).. ONLY HAVE RMAN FULL BACKUP

1. Create instance
C:\Users\HOME>oradim -new -sid TSTDEV01 -intpwd oracle -startmode m
Instance created.

After creation of instance, one can use netmanager and netca to configure listener.ora and tnsnames.ora etc files... or one can go for manuall edit (ensure entries are made in right format)..
2. Add the following entry in listener.ora file

(SID_DESC =
(GLOBAL_DBNAME=TSTDEV01)
(SID_NAME = TSTDEV01)
(ORACLE_HOME = F:\app\HOME\product\11.2.0\dbhome_1)
)

3. Add the following entry into tnsnames.ora file

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



C:\Users\HOME>lsnrctl reload
C:\Users\HOME>lsnrclt status

Ensure instance is ready and accessible.

C:\Users\HOME>tnsping TSTDEV01

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 21-DEC-2
011 21:27:06

Copyright (c) 1997, 2010, Oracle. All rights reserved.

Used parameter files:
F:\app\HOME\product\11.2.0\dbhome_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhos
t)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TSTDEV01))
)
OK (20 msec)

C:\Users\HOME>

I see lsnrctl stop, lsnrctl start would help here .. if u can not able to login using sqlplus sys/pwd@dbname as sysdba..
C:\Users\HOME>SQLPLUS SYS/ORACLE@TSTDEV01 AS SYSDBA

SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 21 21:34:10 2011

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

Connected to an idle instance.

SQL>

4. Create the spfile from the rman backup

C:\Users\HOME>RMAN TARGET SYS/ORACLE@TSTDEV01

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Dec 21 21:35:03 2011

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

connected to target database (not started)

RMAN> set DBID=9945147239

executing command: SET DBID

RMAN> startup force nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'F:\APP\HOME\PRODUCT\11.2.0\DBHOME_1\DA
TABASE\INITTSTDEV01.ORA'

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

Total System Global Area 159019008 bytes

Fixed Size 1373264 bytes
Variable Size 75500464 bytes
Database Buffers 75497472 bytes
Redo Buffers 6647808 bytes

RMAN> restore spfile from 'F:\app\HOME\flash_recovery_area\TSTDEV01\AUTOBACKUP\2011_12_21\O1_MF_S_770504345_7H3YT5T9_.BKP';

Starting restore at 21-DEC-11
using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP F:\app\HOME\flash_recovery_
area\TSTDEV01\AUTOBACKUP\2011_12_21\O1_MF_S_770504345_7H3YT5T9_.BKP
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 21-DEC-11

RMAN>

Run the following command at command prompt to create pfile from spfile that is just created from rman backup
sql "create PFILE = ''D:\TESTDELETE\PFILE'' from SPFILE = ''F:\app\HOME\product\11.2.0\dbhome_1\database\SPFILETSTDEV01.ORA''";

RMAN> sql "create PFILE = ''D:\TESTDELETE\PFILE'' from SPFILE = ''F:\app\HOME\pr
oduct\11.2.0\dbhome_1\database\SPFILETSTDEV01.ORA''";

sql statement: create PFILE = ''D:\TESTDELETE\PFILE'' from SPFILE = ''F:\app\HOM
E\product\11.2.0\dbhome_1\database\SPFILETSTDEV01.ORA''

RMAN>exit

5. Create control file from RMAN backups

Shutdown the database and start with spfile (create in step 4)

C:\Users\HOME>SQLPLUS SYS/ORACLE@TSTDEV01 AS SYSDBA

SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 21 21:46:49 2011

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


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


ORACLE instance shut down.
SQL> startup nomount;
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_TSTDEV01'

remove the following line from pfile the one just created
*.local_listener='LISTENER_TSTDEV01'


SQL> startup nomount pfile='d:\testdelete\PFILE';
ORACLE instance started.

Total System Global Area 640286720 bytes
Fixed Size 1376492 bytes
Variable Size 197136148 bytes
Database Buffers 436207616 bytes
Redo Buffers 5566464 bytes
SQL>

6. Get the controle file from backup

Here are many ways to get the controlfile from RMAN backup.... shown three ways ( I dont know but some reason I backup control file many times..see the rman backup run script above).

RMAN> restore controlfile from autobackup;

Starting restore at 23-DEC-11
using channel ORA_DISK_1

recovery area destination: F:\app\HOME\flash_recovery_area
database name (or database unique name) used for search: TSTDEV01
channel ORA_DISK_1: AUTOBACKUP F:\APP\HOME\FLASH_RECOVERY_AREA\TSTDEV01\AUTOBACK
UP\2011_12_22\O1_MF_S_770591689_7H6N3ML1_.BKP found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP F:\APP\HOME\FLASH_REC
OVERY_AREA\TSTDEV01\AUTOBACKUP\2011_12_22\O1_MF_S_770591689_7H6N3ML1_.BKP
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=F:\APP\HOME\ORADATA\TSTDEV01\CONTROL01.CTL
output file name=F:\APP\HOME\FLASH_RECOVERY_AREA\TSTDEV01\CONTROL02.CTL
Finished restore at 23-DEC-11

RMAN> restore controlfile from 'D:\TESTDELETE\dbf\control.back';

Starting restore at 23-DEC-11
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=F:\APP\HOME\ORADATA\TSTDEV01\CONTROL01.CTL
output file name=F:\APP\HOME\FLASH_RECOVERY_AREA\TSTDEV01\CONTROL02.CTL
Finished restore at 23-DEC-11

RMAN> restore controlfile from 'D:\TESTDELETE\dbf\control01.ctl';

Starting restore at 23-DEC-11
using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
output file name=F:\APP\HOME\ORADATA\TSTDEV01\CONTROL01.CTL
output file name=F:\APP\HOME\FLASH_RECOVERY_AREA\TSTDEV01\CONTROL02.CTL
Finished restore at 23-DEC-11

RMAN>
RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 23-DEC-11
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to F:\APP\HOME\ORADATA\TSTDEV01\SYS
TEM01.DBF
channel ORA_DISK_1: restoring datafile 00002 to F:\APP\HOME\ORADATA\TSTDEV01\SYS
AUX01.DBF
channel ORA_DISK_1: restoring datafile 00003 to F:\APP\HOME\ORADATA\TSTDEV01\UND
OTBS01.DBF
channel ORA_DISK_1: restoring datafile 00004 to F:\APP\HOME\ORADATA\TSTDEV01\USE
RS01.DBF
channel ORA_DISK_1: reading from backup piece D:\TESTDELETE\DBF\0KMUSIQ7_1_1
channel ORA_DISK_1: piece handle=D:\TESTDELETE\DBF\0KMUSIQ7_1_1 tag=TAG20111222T
211239
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:25
Finished restore at 23-DEC-11

RMAN> recover database;

Starting recover at 23-DEC-11
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=16
channel ORA_DISK_1: reading from backup piece D:\TESTDELETE\DBF\0LMUSITS_1_1
channel ORA_DISK_1: piece handle=D:\TESTDELETE\DBF\0LMUSITS_1_1 tag=TAG20111222T
211436
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=F:\APP\HOME\FLASH_RECOVERY_AREA\TSTDEV01\ARCHIVELOG\2011_
12_23\O1_MF_1_16_7H94R2H4_.ARC thread=1 sequence=16
channel default: deleting archived log(s)
archived log file name=F:\APP\HOME\FLASH_RECOVERY_AREA\TSTDEV01\ARCHIVELOG\2011_
12_23\O1_MF_1_16_7H94R2H4_.ARC RECID=16 STAMP=770674266
unable to find archived log
archived log thread=1 sequence=17
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/23/2011 20:11:10
RMAN-06054: media recovery requesting unknown archived log for thread 1 with seq
uence 17 and starting SCN of 958969

RMAN> recover database until logseq 17;

Starting recover at 23-DEC-11
using channel ORA_DISK_1

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

Finished recover at 23-DEC-11

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 12/23/2011 20:12:30
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


RMAN> alter database open resetlogs;

database opened

RMAN>

Note: Usually resetlogs used when incomplete recovery took place otherwise we should go for noresetlogs option.
RESETLOGS would initialize all the logs, reset the log sequence number from start and start a new incarnation of the database.


RMAN> exit


Recovery Manager complete.

C:\Users\HOME>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 23 20:22:23 2011

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


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

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
SQL> select name, open_mode from v$database;

NAME OPEN_MODE
--------- --------------------
TSTDEV01 READ WRITE

SQL>


Check everything and have a good backup once everything looks good.. Good to have backup at this moment.

.. Have a great fun in working in oracle technologies... Cheers....

Tuesday, December 20, 2011

How to create duplicate oracle database from rman backups in oralce 11g

How to create duplicate oracle database from rman backups in oralce 11g
I think it is not advisable to clone database on same production primary database host to be safe side (excuse me if I am wrong, it does not mean we can not do clone on same host), however please check with oracle online documenation or my oraclesupport for further info if need arise.

I did clone the database from RMAN backups on same host.

Create instance for new oracle database: you my see earlier posts on how to create oracle instance

on target:\-intpwd password=oracle -startmode a -pfile D:\TESTDELETE\initclonedb1.ora
 orapwd file=F:\app\HOME\product\11.2.0\dbhome_1\database\PWDclonedb1.ora password=oracle entries=50
Add entry into listener.ora and tnsnames.ora file.
ensure tnsping clonedb1
ensure tnsping orcl
SET ORACLE_SID=CLONEDB1
lsnrctl reload -- incase required.
startup nomount pfile='D:\TESTDELETE\initclonedb1.ora'
SQL> startup nomount pfile='D:\TESTDELETE\initclonedb1.ora'
ORACLE instance started.

Total System Global Area 640286720 bytes
Fixed Size 1376492 bytes
Variable Size 314576660 bytes
Database Buffers 318767104 bytes
Redo Buffers 5566464 bytes

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
clonedb1

SQL>exit

on source db:
-------------
 SET ORACLE_SID=ORCL
rman target /
BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;
EXIT;
exit;
open cmd prompt;

 C:\Users\HOME>SET ORACLE_SID=ORCL

C:\Users\HOME>RMAN TARGET / AUXILIARY SYS/ORACLE@CLONEDB1

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Dec 21 05:06:20 2011

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

connected to target database: ORCL (DBID=1297854480)
connected to auxiliary database: CLONEDB1 (not mounted)

RMAN> run {
SET NEWNAME FOR DATAFILE 1 TO 'F:\app\HOME\oradata\clonedb1\SYSTEM01.DBF';
SET NEWNAME FOR DATAFILE 2 TO 'F:\app\HOME\oradata\clonedb1\SYSAUX01.DBF';
SET NEWNAME FOR DATAFILE 3 TO 'F:\app\HOME\oradata\clonedb1\UNDOTBS01.DBF';
SET NEWNAME FOR DATAFILE 4 TO 'F:\app\HOME\oradata\clonedb1\USERS01.DBF';
SET NEWNAME FOR DATAFILE 5 TO 'F:\app\HOME\oradata\clonedb1\EXAMPLE01.DBF';
SET NEWNAME FOR TEMPFILE 1 TO 'F:\app\HOME\oradata\clonedb1\TEMP01.DBF';
DUPLICATE DATABASE TO clonedb1
pfile 'D:\TESTDELETE\initclonedb1.ora'
BACKUP LOCATION 'F:\app\HOME\flash_recovery_area\orcl\'
LOGFILE GROUP 1 ('F:\app\HOME\oradata\clonedb1\REDO01.LOG') SIZE 60M REUSE,
GROUP 2 ('F:\app\HOME\oradata\clonedb1\REDO02.LOG.rdo') SIZE 60M REUSE,
GROUP 3 ('F:\app\HOME\oradata\clonedb1\REDO03.LOG') SIZE 60M REUSE;
}

How to create duplicate oracle database from rman backups in oralce 11g

How to create duplicate oracle database from rman backups in oralce 11g
I think it is not advisable to clone database on same production primary database host to be safe side (excuse me if I am wrong, it does not mean we can not do clone on same host), however please check with oracle online documenation or my oraclesupport for further info if need arise.

I did clone the database from RMAN backups on same host.

Create instance for new oracle database: you my see earlier posts on how to create oracle instance

on target:\-intpwd password=oracle -startmode a -pfile D:\TESTDELETE\initclonedb1.ora
orapwd file=F:\app\HOME\product\11.2.0\dbhome_1\database\PWDclonedb1.ora password=oracle entries=50
Add entry into listener.ora and tnsnames.ora file.
ensure tnsping clonedb1
ensure tnsping orcl
SET ORACLE_SID=CLONEDB1
lsnrctl reload -- incase required.
startup nomount pfile='D:\TESTDELETE\initclonedb1.ora'
SQL> startup nomount pfile='D:\TESTDELETE\initclonedb1.ora'
ORACLE instance started.

Total System Global Area 640286720 bytes
Fixed Size 1376492 bytes
Variable Size 314576660 bytes
Database Buffers 318767104 bytes
Redo Buffers 5566464 bytes

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
clonedb1

SQL>exit

on source db:
-------------
SET ORACLE_SID=ORCL
rman target /
BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;
EXIT;
exit;
open cmd prompt;

C:\Users\HOME>SET ORACLE_SID=ORCL

C:\Users\HOME>RMAN TARGET / AUXILIARY SYS/ORACLE@CLONEDB1

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Dec 21 05:06:20 2011

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

connected to target database: ORCL (DBID=1297854480)
connected to auxiliary database: CLONEDB1 (not mounted)

RMAN> run {
SET NEWNAME FOR DATAFILE 1 TO 'F:\app\HOME\oradata\clonedb1\SYSTEM01.DBF';
SET NEWNAME FOR DATAFILE 2 TO 'F:\app\HOME\oradata\clonedb1\SYSAUX01.DBF';
SET NEWNAME FOR DATAFILE 3 TO 'F:\app\HOME\oradata\clonedb1\UNDOTBS01.DBF';
SET NEWNAME FOR DATAFILE 4 TO 'F:\app\HOME\oradata\clonedb1\USERS01.DBF';
SET NEWNAME FOR DATAFILE 5 TO 'F:\app\HOME\oradata\clonedb1\EXAMPLE01.DBF';
SET NEWNAME FOR TEMPFILE 1 TO 'F:\app\HOME\oradata\clonedb1\TEMP01.DBF';
DUPLICATE DATABASE TO clonedb1
pfile 'D:\TESTDELETE\initclonedb1.ora'
BACKUP LOCATION 'F:\app\HOME\flash_recovery_area\orcl\'
LOGFILE GROUP 1 ('F:\app\HOME\oradata\clonedb1\REDO01.LOG') SIZE 60M REUSE,
GROUP 2 ('F:\app\HOME\oradata\clonedb1\REDO02.LOG.rdo') SIZE 60M REUSE,
GROUP 3 ('F:\app\HOME\oradata\clonedb1\REDO03.LOG') SIZE 60M REUSE;
}

ORA-19606: Cannot copy or restore to snapshot control file

How to solve ORA-19606: Cannot copy or restore to snapshot control file error:
----------------------------------------------------------------------------------

 RMAN> delete obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Control File Copy 5 18-DEC-11 F:\APP\HOME\PRODUCT\11.2.0\DBHOM
E_1\DATABASE\SNCFORCL.ORA

Do you really want to delete the above objects (enter YES or NO)? yes
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of delete command on ORA_DISK_1 channel at 12/21/2011 04:30:
18
ORA-19606: Cannot copy or restore to snapshot control file

RMAN>

RMAN> show all;

RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
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 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 LOA
D TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'F:\APP\HOME\PRODUCT\11.2.0\DBHOME_1\DATA
BASE\SNCFORCL.ORA'; # default

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'F:\app\HOME\product\11.2.0\dbhome_
1\database\SNCFORCL.ORA';

new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'F:\app\HOME\product\11.2.0\dbhome_1\data
base\SNCFORCL.ORA';
new RMAN configuration parameters are successfully stored

RMAN> show all;

RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
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 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 LOA
D TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'F:\app\HOME\product\11.2.0\dbhome_1\data
base\SNCFORCL.ORA';

RMAN> delete noprompt obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Control File Copy 5 18-DEC-11 F:\APP\HOME\PRODUCT\11.2.0\DBHOM
E_1\DATABASE\SNCFORCL.ORA
deleted control file copy
control file copy file name=F:\APP\HOME\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFORC
L.ORA RECID=5 STAMP=770204506
Deleted 1 objects


RMAN>

ORA-19606: Cannot copy or restore to snapshot control file

How to solve ORA-19606: Cannot copy or restore to snapshot control file error:
----------------------------------------------------------------------------------

RMAN> delete obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Control File Copy 5 18-DEC-11 F:\APP\HOME\PRODUCT\11.2.0\DBHOM
E_1\DATABASE\SNCFORCL.ORA

Do you really want to delete the above objects (enter YES or NO)? yes
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of delete command on ORA_DISK_1 channel at 12/21/2011 04:30:
18
ORA-19606: Cannot copy or restore to snapshot control file

RMAN>

RMAN> show all;

RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
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 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 LOA
D TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'F:\APP\HOME\PRODUCT\11.2.0\DBHOME_1\DATA
BASE\SNCFORCL.ORA'; # default

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'F:\app\HOME\product\11.2.0\dbhome_
1\database\SNCFORCL.ORA';

new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'F:\app\HOME\product\11.2.0\dbhome_1\data
base\SNCFORCL.ORA';
new RMAN configuration parameters are successfully stored

RMAN> show all;

RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
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 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 LOA
D TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'F:\app\HOME\product\11.2.0\dbhome_1\data
base\SNCFORCL.ORA';

RMAN> delete noprompt obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Control File Copy 5 18-DEC-11 F:\APP\HOME\PRODUCT\11.2.0\DBHOM
E_1\DATABASE\SNCFORCL.ORA
deleted control file copy
control file copy file name=F:\APP\HOME\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFORC
L.ORA RECID=5 STAMP=770204506
Deleted 1 objects


RMAN>

Sunday, December 18, 2011

HOW TO CREATE ORACLE INSTANCE IN WINDOWS 7

How to create oracle instance in windows 7:
----------------------------------------------
Add entry into listener.ora , the new db being created is clonedb1

listener.ora file content:
------------------------------

 # listener.ora Network Configuration File: F:\app\HOME\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = F:\app\HOME\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:F:\app\HOME\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
(SID_DESC =
(GLOBAL_DBNAME=CLONEDB1)
(SID_NAME = CLONEDB1)
(ORACLE_HOME = F:\app\HOME\product\11.2.0\dbhome_1)
)
(SID_DESC =
(GLOBAL_DBNAME=CLONET)
(SID_NAME = CLONET)
(ORACLE_HOME = F:\app\HOME\product\11.2.0\dbhome_1)
)

)

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

ADR_BASE_LISTENER = F:\app\HOME

2. Add entry into tnsnames.ora
tnsnames.ora:
-------------------

 # tnsnames.ora Network Configuration File: F:\app\HOME\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

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

ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)

LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


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


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

3. login into existing DB i.e. orcl

 create pfile='path/name.ora' from spfile;
exit;

open newly created pfile for db name change,

modify orcl with new db i.e. clonedb1 in pfile, save and exit..

ensure all the ../clonedb1/... directories are created in file system

4. create instance, reload listener and tests the newly created instance..

  C:\Users\HOME>lsnrctl status

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 18-DEC-2011 14:01
:20

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
32-bit Windows Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
32-bit Windows Error: 61: Unknown error

C:\Users\HOME>set ORACLE_SID=CLONEDB1

C:\Users\HOME>oradim -new -sid clonedb1 -intpwd password=oracle -startmode a -pf
ile D:\TESTDELETE\initclonedb1.ora
Instance created.

C:\Users\HOME>
C:\Users\HOME>orapwd file=F:\app\HOME\product\11.2.0\dbhome_1\database\PWDcloned
b1.ora password=oracle entries=50

OPW-00005: File with same name exists - please delete or rename

C:\Users\HOME>
C:\Users\HOME>lsnrctl start

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 18-DEC-2011 14:03
:17

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

Starting tnslsnr: please wait...

TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
System parameter file is F:\app\HOME\product\11.2.0\dbhome_1\network\admin\liste
ner.ora
Log messages written to f:\app\home\diag\tnslsnr\HOME-PC\listener\alert\log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521
ipc)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Produ
ction
Start Date 18-DEC-2011 14:03:23
Uptime 0 days 0 hr. 0 min. 7 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File F:\app\HOME\product\11.2.0\dbhome_1\network\admin\list
ener.ora
Listener Log File f:\app\home\diag\tnslsnr\HOME-PC\listener\alert\log.xm
l
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "CLONEDB1" has 1 instance(s).
Instance "CLONEDB1", status UNKNOWN, has 1 handler(s) for this service...
Service "CLONET" has 1 instance(s).
Instance "CLONET", status UNKNOWN, has 1 handler(s) for this service...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

C:\Users\HOME>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Sun Dec 18 14:03:48 2011

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

Connected to an idle instance.

SQL>
SQL> startup nomount pfile='D:\TESTDELETE\initclonedb1.ora'
ORACLE instance started.

Total System Global Area 640286720 bytes
Fixed Size 1376492 bytes
Variable Size 314576660 bytes
Database Buffers 318767104 bytes
Redo Buffers 5566464 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\HOME>lsnrctl reload

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 18-DEC-2011 14:04
:29

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully

C:\Users\HOME>lsnrctl status

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 18-DEC-2011 14:04
:33

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Produ
ction
Start Date 18-DEC-2011 14:03:23
Uptime 0 days 0 hr. 1 min. 14 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File F:\app\HOME\product\11.2.0\dbhome_1\network\admin\list
ener.ora
Listener Log File f:\app\home\diag\tnslsnr\HOME-PC\listener\alert\log.xm
l
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "CLONEDB1" has 1 instance(s).
Instance "CLONEDB1", status UNKNOWN, has 1 handler(s) for this service...
Service "CLONET" has 1 instance(s).
Instance "CLONET", status UNKNOWN, has 1 handler(s) for this service...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully


C:\Users\HOME>tnsping orcl

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 18-DEC-2
011 14:06:35

Copyright (c) 1997, 2010, Oracle. All rights reserved.

Used parameter files:
F:\app\HOME\product\11.2.0\dbhome_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhos
t)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (20 msec)

C:\Users\HOME>tnsping clonedb1

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 18-DEC-2
011 14:06:37

Copyright (c) 1997, 2010, Oracle. All rights reserved.

Used parameter files:
F:\app\HOME\product\11.2.0\dbhome_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhos
t)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = clonedb1))
)
OK (0 msec)

C:\Users\HOME>
C:\Users\HOME>
C:\Users\HOME>sqlplus sys@orcl as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Dec 18 14:06:55 2011

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

Enter password:

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

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

C:\Users\HOME>sqlplus sys@clonedb1 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Dec 18 14:07:07 2011

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

Enter password:

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

SQL>
SQL>

HOW TO CREATE ORACLE INSTANCE IN WINDOWS 7

How to create oracle instance in windows 7:
----------------------------------------------
Add entry into listener.ora , the new db being created is clonedb1

listener.ora file content:
------------------------------

# listener.ora Network Configuration File: F:\app\HOME\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = F:\app\HOME\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:F:\app\HOME\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
(SID_DESC =
(GLOBAL_DBNAME=CLONEDB1)
(SID_NAME = CLONEDB1)
(ORACLE_HOME = F:\app\HOME\product\11.2.0\dbhome_1)
)
(SID_DESC =
(GLOBAL_DBNAME=CLONET)
(SID_NAME = CLONET)
(ORACLE_HOME = F:\app\HOME\product\11.2.0\dbhome_1)
)

)

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

ADR_BASE_LISTENER = F:\app\HOME

2. Add entry into tnsnames.ora
tnsnames.ora:
-------------------

# tnsnames.ora Network Configuration File: F:\app\HOME\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

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

ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)

LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


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


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

3. login into existing DB i.e. orcl

create pfile='path/name.ora' from spfile;
exit;

open newly created pfile for db name change,

modify orcl with new db i.e. clonedb1 in pfile, save and exit..

ensure all the ../clonedb1/... directories are created in file system

4. create instance, reload listener and tests the newly created instance..

C:\Users\HOME>lsnrctl status

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 18-DEC-2011 14:01
:20

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
32-bit Windows Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
32-bit Windows Error: 61: Unknown error

C:\Users\HOME>set ORACLE_SID=CLONEDB1

C:\Users\HOME>oradim -new -sid clonedb1 -intpwd password=oracle -startmode a -pf
ile D:\TESTDELETE\initclonedb1.ora
Instance created.

C:\Users\HOME>
C:\Users\HOME>orapwd file=F:\app\HOME\product\11.2.0\dbhome_1\database\PWDcloned
b1.ora password=oracle entries=50

OPW-00005: File with same name exists - please delete or rename

C:\Users\HOME>
C:\Users\HOME>lsnrctl start

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 18-DEC-2011 14:03
:17

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

Starting tnslsnr: please wait...

TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
System parameter file is F:\app\HOME\product\11.2.0\dbhome_1\network\admin\liste
ner.ora
Log messages written to f:\app\home\diag\tnslsnr\HOME-PC\listener\alert\log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521
ipc)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Produ
ction
Start Date 18-DEC-2011 14:03:23
Uptime 0 days 0 hr. 0 min. 7 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File F:\app\HOME\product\11.2.0\dbhome_1\network\admin\list
ener.ora
Listener Log File f:\app\home\diag\tnslsnr\HOME-PC\listener\alert\log.xm
l
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "CLONEDB1" has 1 instance(s).
Instance "CLONEDB1", status UNKNOWN, has 1 handler(s) for this service...
Service "CLONET" has 1 instance(s).
Instance "CLONET", status UNKNOWN, has 1 handler(s) for this service...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

C:\Users\HOME>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Sun Dec 18 14:03:48 2011

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

Connected to an idle instance.

SQL>
SQL> startup nomount pfile='D:\TESTDELETE\initclonedb1.ora'
ORACLE instance started.

Total System Global Area 640286720 bytes
Fixed Size 1376492 bytes
Variable Size 314576660 bytes
Database Buffers 318767104 bytes
Redo Buffers 5566464 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\HOME>lsnrctl reload

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 18-DEC-2011 14:04
:29

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully

C:\Users\HOME>lsnrctl status

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 18-DEC-2011 14:04
:33

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Produ
ction
Start Date 18-DEC-2011 14:03:23
Uptime 0 days 0 hr. 1 min. 14 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File F:\app\HOME\product\11.2.0\dbhome_1\network\admin\list
ener.ora
Listener Log File f:\app\home\diag\tnslsnr\HOME-PC\listener\alert\log.xm
l
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "CLONEDB1" has 1 instance(s).
Instance "CLONEDB1", status UNKNOWN, has 1 handler(s) for this service...
Service "CLONET" has 1 instance(s).
Instance "CLONET", status UNKNOWN, has 1 handler(s) for this service...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully


C:\Users\HOME>tnsping orcl

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 18-DEC-2
011 14:06:35

Copyright (c) 1997, 2010, Oracle. All rights reserved.

Used parameter files:
F:\app\HOME\product\11.2.0\dbhome_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhos
t)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (20 msec)

C:\Users\HOME>tnsping clonedb1

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 18-DEC-2
011 14:06:37

Copyright (c) 1997, 2010, Oracle. All rights reserved.

Used parameter files:
F:\app\HOME\product\11.2.0\dbhome_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhos
t)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = clonedb1))
)
OK (0 msec)

C:\Users\HOME>
C:\Users\HOME>
C:\Users\HOME>sqlplus sys@orcl as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Dec 18 14:06:55 2011

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

Enter password:

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

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

C:\Users\HOME>sqlplus sys@clonedb1 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Dec 18 14:07:07 2011

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

Enter password:

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

SQL>
SQL>

Saturday, December 17, 2011

clone oracle database in 11g

clone oracle database in 11g:
------------------------------

Reading the http://docs.oracle.com/cd/E11882_01/backup.112/e10643/rcmsynta020.htm URL is advisable before one attempt to do cloning the oracle database. This is my view.

As you all know that there are two simplest methods to clone the db
1. active database duplication (source should be available)
2. backup based database duplication (using source backup files, connection to source db is optional)

The following method shows the first way of doing clone the database - active database duplication

Source db is in archivelog and have a valid backup (RMAN> backup database plus archivelog delete input;)
Sys password should be same on both source and target

Requirement is to clone the orcl database to destination clonedb1 db on same host on windows 7

1. Add entry into lsnrctl.ora and tnsnames.ora network config files.

Target database: clonedb1
lsnrctl.ora:
----------------

 (SID_DESC =
(GLOBAL_DBNAME=CLONEDB1)
(SID_NAME = CLONEDB1)
(ORACLE_HOME = F:\app\HOME\product\11.2.0\dbhome_1)
)

complete: lsnrctl.ora file

 # listener.ora Network Configuration File: F:\app\HOME\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = F:\app\HOME\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:F:\app\HOME\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
(SID_DESC =
(GLOBAL_DBNAME=CLONEDB1)
(SID_NAME = CLONEDB1)
(ORACLE_HOME = F:\app\HOME\product\11.2.0\dbhome_1)
)

)

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

ADR_BASE_LISTENER = F:\app\HOME




tnsnames.ora
----------------
 CLONEDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CLONEDB1)
)
)

tnsnmaes.ora file:

 # tnsnames.ora Network Configuration File: F:\app\HOME\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)

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


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


2. create service and start the service

C:\Users\HOME>
C:\Users\HOME>oradim -delete -sid clonedb1
Instance deleted.

C:\Users\HOME>oradim -new -sid clonedb1 -intpwd password=oracle -startmode a -pf
ile D:\TESTDELETE\initclonedb1.ora

OPW-00005: File with same name exists - please delete or rename
Instance created.

3. Create password file

C:\Users\HOME>orapwd file=F:\app\HOME\product\11.2.0\dbhome_1\database\PWDcloned
b1.ora password=oracle entries=50

4. Create pfile from source db

login soruce db:

C:\Users\HOME>SET ORACLE_SID=ORCL

C:\Users\HOME>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Sat Dec 17 22:32:01 2011

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


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

SQL>
SQL> create pfile='D:\TESTDELETE\initclonedb1.ora' from spfile;

File created.

5. Replace orcl in initclonedb1.ora file with clonedb1
find (orcl) and replace (clonedb1), do other changes as applicable.

initclonedb1.ora file contents are

clonedb1.__db_cache_size=369098752
clonedb1.__java_pool_size=4194304
clonedb1.__large_pool_size=4194304
clonedb1.__oracle_base='F:\app\HOME'#ORACLE_BASE set from environment
clonedb1.__pga_aggregate_target=427819008
clonedb1.__sga_target=641728512
clonedb1.__shared_io_pool_size=0
clonedb1.__shared_pool_size=255852544
clonedb1.__streams_pool_size=0
*.audit_file_dest='F:\app\HOME\admin\clonedb1\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='F:\app\HOME\oradata\clonedb1\control01.ctl','F:\app\HOME\flash_recovery_area\clonedb1\control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='clonedb1'
*.db_recovery_file_dest='F:\app\HOME\flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='F:\app\HOME'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=clonedb1XDB)'
*.memory_target=1069547520
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'


Note: create all directoires as shown in the initclonedb1.ora file -- folders can be empty but directory structure should be presented....


6. Start the clonedb1 instance

C:\Users\HOME>set ORACLE_SID=CLONEDB1

C:\Users\HOME>lsrnctl reload
'lsrnctl' is not recognized as an internal or external command,
operable program or batch file.

C:\Users\HOME>lsnrctl reload

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 17-DEC-2011 22:52
:04

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully

C:\Users\HOME>

C:\Users\HOME>SET ORACLE_SID=CLONEDB1

C:\Users\HOME>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Sat Dec 17 22:53:03 2011

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

Connected to an idle instance.

SQL> startup nomount pfile='D:\TESTDELETE\initclonedb1.ora'
ORACLE instance started.

Total System Global Area 640286720 bytes
Fixed Size 1376492 bytes
Variable Size 264245012 bytes
Database Buffers 369098752 bytes
Redo Buffers 5566464 bytes
SQL>

Check for the successful login
SQL> conn sys/oracle@clonedb1 as sysdba
Connected.
SQL>


7. Connect source db and target db via rman for db duplication
C:\Users\HOME>set ORACLE_SID=ORCL

alter system set db_recovery_file_dest_size=5G;

Ensure the above statement is run with sufficent disk space for db_recovery_File_Desk_size on both source and target db.

tnsping orcl
tnsping clonedb1
Ensure that the above both commands give valid output in source system... it means you are able to connect both databases and able to do successfull clone in upcoming steps...

C:\Users\HOME>rman target sys/oracle@orcl

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Dec 17 23:00:00 2011

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

connected to target database: ORCL (DBID=1297854480)

RMAN> connect auxiliary sys/oracle@clonedb1
connected to auxiliary database: CLONEDB1 (not mounted)
[
one can also connect rman like below
C:\Users\HOME>rman target sys/oracle@orcl auxiliary sys/oracle@clonedb1
]

SQL> select file#, name from v$datafile;
SQL> select file#, name from v$tempfile;

Change run command when you are cloning on same database to avoid conflcting with source db files. The directory structure should be the one that database files will be created on target cloned db on target system.

 RMAN> run {
2> SET NEWNAME FOR DATAFILE 1 TO 'F:\app\HOME\oradata\clonedb1\SYSTEM01.DBF';
3> SET NEWNAME FOR DATAFILE 2 TO 'F:\app\HOME\oradata\clonedb1\SYSAUX01.DBF';
4> SET NEWNAME FOR DATAFILE 3 TO 'F:\app\HOME\oradata\clonedb1\UNDOTBS01.DBF';
5> SET NEWNAME FOR DATAFILE 4 TO 'F:\app\HOME\oradata\clonedb1\USERS01.DBF';
6> SET NEWNAME FOR DATAFILE 5 TO 'F:\app\HOME\oradata\clonedb1\EXAMPLE01.DBF';
7> SET NEWNAME FOR TEMPFILE 1 TO 'F:\app\HOME\oradata\clonedb1\TEMP01.DBF';
8> duplicate target database to clonedb1 from active database;
9> }

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 Duplicate Db at 18-DEC-11
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 device type=DISK

contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

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

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 640286720 bytes

Fixed Size 1376492 bytes
Variable Size 268439316 bytes
Database Buffers 364904448 bytes
Redo Buffers 5566464 bytes

contents of Memory Script:
{
sql clone "alter system set db_name =
''ORCL'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''CLONEDB1'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
backup as copy current controlfile auxiliary format 'F:\APP\HOME\ORADATA\CLO
NEDB1\CONTROL01.CTL';
restore clone controlfile to 'F:\APP\HOME\FLASH_RECOVERY_AREA\CLONEDB1\CONTR
OL02.CTL' from
'F:\APP\HOME\ORADATA\CLONEDB1\CONTROL01.CTL';
alter clone database mount;
}
executing Memory Script

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

sql statement: alter system set db_unique_name = ''CLONEDB1'' comment= ''Modif
ied by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 640286720 bytes

Fixed Size 1376492 bytes
Variable Size 268439316 bytes
Database Buffers 364904448 bytes
Redo Buffers 5566464 bytes

Starting backup at 18-DEC-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=F:\APP\HOME\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFORCL.ORA tag=T
AG20111218T094144 RECID=5 STAMP=770204506
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 18-DEC-11

Starting restore at 18-DEC-11
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=133 device type=DISK

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 18-DEC-11

database mounted

contents of Memory Script:
{
set newname for datafile 1 to
"F:\app\HOME\oradata\clonedb1\SYSTEM01.DBF";
set newname for datafile 2 to
"F:\app\HOME\oradata\clonedb1\SYSAUX01.DBF";
set newname for datafile 3 to
"F:\app\HOME\oradata\clonedb1\UNDOTBS01.DBF";
set newname for datafile 4 to
"F:\app\HOME\oradata\clonedb1\USERS01.DBF";
set newname for datafile 5 to
"F:\app\HOME\oradata\clonedb1\EXAMPLE01.DBF";
backup as copy reuse
datafile 1 auxiliary format
"F:\app\HOME\oradata\clonedb1\SYSTEM01.DBF" datafile
2 auxiliary format
"F:\app\HOME\oradata\clonedb1\SYSAUX01.DBF" datafile
3 auxiliary format
"F:\app\HOME\oradata\clonedb1\UNDOTBS01.DBF" datafile
4 auxiliary format
"F:\app\HOME\oradata\clonedb1\USERS01.DBF" datafile
5 auxiliary format
"F:\app\HOME\oradata\clonedb1\EXAMPLE01.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

Starting backup at 18-DEC-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=F:\APP\HOME\ORADATA\ORCL\SYSTEM01.DBF
output file name=F:\APP\HOME\ORADATA\CLONEDB1\SYSTEM01.DBF tag=TAG20111218T09415
8
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=F:\APP\HOME\ORADATA\ORCL\SYSAUX01.DBF
output file name=F:\APP\HOME\ORADATA\CLONEDB1\SYSAUX01.DBF tag=TAG20111218T09415
8
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:56
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=F:\APP\HOME\ORADATA\ORCL\EXAMPLE01.DBF
output file name=F:\APP\HOME\ORADATA\CLONEDB1\EXAMPLE01.DBF tag=TAG20111218T0941
58
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=F:\APP\HOME\ORADATA\ORCL\UNDOTBS01.DBF
output file name=F:\APP\HOME\ORADATA\CLONEDB1\UNDOTBS01.DBF tag=TAG20111218T0941
58
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=F:\APP\HOME\ORADATA\ORCL\USERS01.DBF
output file name=F:\APP\HOME\ORADATA\CLONEDB1\USERS01.DBF tag=TAG20111218T094158

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 18-DEC-11

sql statement: alter system archive log current

contents of Memory Script:
{
backup as copy reuse
archivelog like "F:\APP\HOME\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2011_12_18\
O1_MF_1_29_7GTT5OFF_.ARC" auxiliary format
"F:\APP\HOME\FLASH_RECOVERY_AREA\CLONEDB1\ARCHIVELOG\2011_12_18\O1_MF_1_29_%U_.
ARC" ;
catalog clone recovery area;
switch clone datafile all;
}
executing Memory Script

Starting backup at 18-DEC-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=29 RECID=24 STAMP=770204687
output file name=F:\APP\HOME\FLASH_RECOVERY_AREA\CLONEDB1\ARCHIVELOG\2011_12_18\
O1_MF_1_29_ARCH_D-ORCL_ID-1297854480_S-29_T-1_A-770073748_0SMUGP0G_.ARC RECID=0
STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:03
Finished backup at 18-DEC-11

searching for all files in the recovery area

List of Files Unknown to the Database
=====================================
File Name: F:\APP\HOME\FLASH_RECOVERY_AREA\CLONEDB1\ARCHIVELOG\2011_12_18\O1_MF_
1_29_ARCH_D-ORCL_ID-1297854480_S-29_T-1_A-770073748_0SMUGP0G_.ARC
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: F:\APP\HOME\FLASH_RECOVERY_AREA\CLONEDB1\ARCHIVELOG\2011_12_18\O1_MF_
1_29_ARCH_D-ORCL_ID-1297854480_S-29_T-1_A-770073748_0SMUGP0G_.ARC

List of files in Recovery Area not managed by the database
==========================================================
File Name: F:\APP\HOME\FLASH_RECOVERY_AREA\CLONEDB1\CONTROL02.CTL
RMAN-07526: Reason: File is not an Oracle Managed File

number of files not managed by recovery area is 1, totaling 9.28MB

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=770204693 file name=F:\APP\HOME\ORADATA\CLONED
B1\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=770204693 file name=F:\APP\HOME\ORADATA\CLONED
B1\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=770204693 file name=F:\APP\HOME\ORADATA\CLONED
B1\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=770204693 file name=F:\APP\HOME\ORADATA\CLONED
B1\USERS01.DBF
datafile 5 switched to datafile copy
input datafile copy RECID=9 STAMP=770204694 file name=F:\APP\HOME\ORADATA\CLONED
B1\EXAMPLE01.DBF

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

executing command: SET until clause

Starting recover at 18-DEC-11
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 29 is already on disk as file F:\APP\HOM
E\FLASH_RECOVERY_AREA\CLONEDB1\ARCHIVELOG\2011_12_18\O1_MF_1_29_ARCH_D-ORCL_ID-1
297854480_S-29_T-1_A-770073748_0SMUGP0G_.ARC
archived log file name=F:\APP\HOME\FLASH_RECOVERY_AREA\CLONEDB1\ARCHIVELOG\2011_
12_18\O1_MF_1_29_ARCH_D-ORCL_ID-1297854480_S-29_T-1_A-770073748_0SMUGP0G_.ARC th
read=1 sequence=29
media recovery complete, elapsed time: 00:00:04
Finished recover at 18-DEC-11

contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set db_name =
''CLONEDB1'' 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

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 640286720 bytes

Fixed Size 1376492 bytes
Variable Size 268439316 bytes
Database Buffers 364904448 bytes
Redo Buffers 5566464 bytes

sql statement: alter system set db_name = ''CLONEDB1'' comment= ''Reset to ori
ginal 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 640286720 bytes

Fixed Size 1376492 bytes
Variable Size 268439316 bytes
Database Buffers 364904448 bytes
Redo Buffers 5566464 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CLONEDB1" RESETLOGS ARCHIV
ELOG
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
'F:\APP\HOME\ORADATA\CLONEDB1\SYSTEM01.DBF'
CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
set newname for tempfile 1 to
"F:\app\HOME\oradata\clonedb1\TEMP01.DBF";
switch clone tempfile all;
catalog clone datafilecopy "F:\APP\HOME\ORADATA\CLONEDB1\SYSAUX01.DBF",
"F:\APP\HOME\ORADATA\CLONEDB1\UNDOTBS01.DBF",
"F:\APP\HOME\ORADATA\CLONEDB1\USERS01.DBF",
"F:\APP\HOME\ORADATA\CLONEDB1\EXAMPLE01.DBF";
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to F:\app\HOME\oradata\clonedb1\TEMP01.DBF in control file

cataloged datafile copy
datafile copy file name=F:\APP\HOME\ORADATA\CLONEDB1\SYSAUX01.DBF RECID=1 STAMP=
770204922
cataloged datafile copy
datafile copy file name=F:\APP\HOME\ORADATA\CLONEDB1\UNDOTBS01.DBF RECID=2 STAMP
=770204922
cataloged datafile copy
datafile copy file name=F:\APP\HOME\ORADATA\CLONEDB1\USERS01.DBF RECID=3 STAMP=7
70204922
cataloged datafile copy
datafile copy file name=F:\APP\HOME\ORADATA\CLONEDB1\EXAMPLE01.DBF RECID=4 STAMP
=770204922

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=770204922 file name=F:\APP\HOME\ORADATA\CLONED
B1\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=770204922 file name=F:\APP\HOME\ORADATA\CLONED
B1\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=770204922 file name=F:\APP\HOME\ORADATA\CLONED
B1\USERS01.DBF
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=770204922 file name=F:\APP\HOME\ORADATA\CLONED
B1\EXAMPLE01.DBF

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

database opened
Finished Duplicate Db at 18-DEC-11

RMAN> }


8. login target system and test the newly cloned db

 C:\Users\HOME>SET ORACLE_SID=CLONEDB1

C:\Users\HOME>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Sun Dec 18 12:00:53 2011

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


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

SQL> select name, open_mode from v$database;

NAME OPEN_MODE
--------- --------------------
CLONEDB1 READ WRITE

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------

F:\APP\HOME\ORADATA\CLONEDB1\TEMP01.DBF

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------

F:\APP\HOME\ORADATA\CLONEDB1\CONTROL01.CTL
F:\APP\HOME\FLASH_RECOVERY_AREA\CLONEDB1\CONTROL02.CTL

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------

F:\APP\HOME\FLASH_RECOVERY_AREA\CLONEDB1\ONLINELOG\O1_MF_3_7GTTFNCF_.LOG
F:\APP\HOME\FLASH_RECOVERY_AREA\CLONEDB1\ONLINELOG\O1_MF_2_7GTTFJD3_.LOG
F:\APP\HOME\FLASH_RECOVERY_AREA\CLONEDB1\ONLINELOG\O1_MF_1_7GTTFF3Y_.LOG

SQL> show parameter recovery

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string F:\app\HOME\flash_recovery_are
a
db_recovery_file_dest_size big integer 3852M
recovery_parallelism integer 0
SQL>

After doing all these one can see that cloned db is similar to what we see in source db..

-- Have a fun in working in oracle Technologies-- Cheers