Multiplex Control Files In 11gR2 RAC/ASM.

1) Add location to the control_files parameter.

[oracle@rac1 ~]$ export ORACLE_SID=orcl1
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Aug 18 11:22:46 2013

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


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

SQL> show parameter control_files;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      +DATA/orcl/controlfile/current
                                                 .260.823159575, +FRA/orcl/cont
                                                 rolfile/current.256.823159577




SQL> alter system set control_files='+DATA/orcl/controlfile/current.260.823159575', 
'+FRA/orcl/controlfile/current.256.823159577', '+DATA', '+FRA' scope=spfile;

2) Stop the database and start it in nomount mode.

[oracle@rac1 ~]$ srvctl stop database -d orcl
[oracle@rac1 ~]$ srvctl start database -d orcl -o nomount


3) Create the control file using RMAN by pointing to an actual existing controlfile.

[oracle@rac1 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Aug 18 11:38:56 2013

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

connected to target database: ORCL (not mounted)


RMAN> restore controlfile from '+DATA/orcl/controlfile/current.260.823159575';

Starting restore at 18-AUG-2013 11:39:31
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 instance=orcl1 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA/orcl/controlfile/current.260.823159575
output file name=+FRA/orcl/controlfile/current.256.823159577
output file name=+DATA/orcl/controlfile/current.270.823779575
Finished restore at 18-AUG-2013 11:39:39

4) Stop the database and start it in normal mode.

[oracle@rac1 ~]$ srvctl stop database -d orcl
[oracle@rac1 ~]$ srvctl start database -d orcl


SQL> select open_mode,name from v$database;

OPEN_MODE            NAME
-------------------- ---------
READ WRITE           ORCL


+DATA/orcl/controlfile/current.260.823159575
+FRA/orcl/controlfile/current.256.823159577
+DATA/orcl/controlfile/current.269.823780197
+FRA/orcl/controlfile/current.271.823780199

Read more >>

11gR2 RAC (ASM) to Single Instance (NON-ASM) Cloning.

Source = 2 node rac cluster with asm  SID=dell
Target = single instance non asm      SID=dup(to be made)

Notes:

1) For cloning Rman active database duplication will be used,
   that means no backups are required(11gR2 feature). 

Other Methods which can be used for duplication using RMAN.

1.1) Duplication without connection to target(11gR2 Feature).
1.2) Backup and recreate database manually.
1.3) Rman duplication using backups.

2) Since source database is OMF managed target will be OMF managed as well.

   following parameters will be used to achieve that.

2.1) db_create_file_dest---------------->> This directory will contain data,redo & control files.
2.2) db_recovery_file_dest-------------->> This will contain Flash recover Area.

3) In case where source is NON-OMF managed use following parameters : -

3.1) control_files
3.2) db_file_name_convert
3.3) log_file_name_convert 


Source

1) Make sure rac database is in archive log mode.
2) Copy password file to target.

[oracle@rac1 dbs]$ scp orapwdell1 192.168.1.69:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwdup
oracle@192.168.1.69's password:
orapwdell1                                        100% 1536     1.5KB/s   00:00

3) make tnsnames.ora entry using "NETCA" as "Oracle" user
   so that a connection could be made to target.


Sample on both nodes:

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

DUP =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.69)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dup)
    )
  )

DELL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac-cluster-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dell.example.com)
    )
  )




   Copy the modified tnsnames.ora file to target.

[oracle@rac1 admin]$ scp tnsnames.ora 192.168.1.69:/u01/app/oracle/product/11.2.0/db_1/network/admin/
oracle@192.168.1.69's password:
tnsnames.ora                                                100%  533     0.5KB/s   00:00


4) Make pfile out of spfile and modify it for single instance
   cloning then copy it to target.


Original:

[oracle@rac1 oracle]$ cat initdup.ora

dell1.__db_cache_size=29360128
dell2.__db_cache_size=29360128
dell1.__java_pool_size=4194304
dell2.__java_pool_size=4194304
dell1.__large_pool_size=4194304
dell2.__large_pool_size=4194304
dell1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
dell1.__pga_aggregate_target=109051904
dell2.__pga_aggregate_target=109051904
dell1.__sga_target=155189248
dell2.__sga_target=155189248
dell1.__shared_io_pool_size=0
dell2.__shared_io_pool_size=0
dell1.__shared_pool_size=113246208
dell2.__shared_pool_size=113246208
dell1.__streams_pool_size=0
dell2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/dell/adump'
*.audit_trail='DB'
*.cluster_database=true
dell1.cluster_database=TRUE
*.compatible='11.2.0.0.0'
*.control_files='+DATA/dell/controlfile/current.261.800523723','+FRA/dell/controlfile/current.268.800523725'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain='example.com'
*.db_name='dell'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dellXDB)'
dell2.instance_number=2
dell1.instance_number=1
*.memory_target=262144000
*.open_cursors=300
*.processes=150
*.remote_listener='rac-cluster-scan:1521'
*.remote_login_passwordfile='exclusive'
dell2.thread=2
dell1.thread=1
dell1.undo_tablespace='UNDOTBS1'
dell2.undo_tablespace='UNDOTBS2'


Modified:

dup.__db_cache_size=29360128
dup.__java_pool_size=4194304
dup.__large_pool_size=4194304
dup.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
dup.__pga_aggregate_target=109051904
dup.__sga_target=155189248
dup.__shared_io_pool_size=0
dup.__shared_pool_size=113246208
dup.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/dup/adump'
*.audit_trail='DB'
*.compatible='11.2.0.0.0'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_domain='example.com'
*.db_name='dup'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dupXDB)'
*.memory_target=262144000
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='exclusive'
*.undo_tablespace='UNDOTBS1'


Target

1) Make ORACLE_SID entry in the bash profile before doing anything else.

----------------------------------snip------------------------------------
ORACLE_UNQNAME=orcl; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=dup; export ORACLE_SID
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
------------------------------------snip------------------------------------

2) Make a listener and register the "dup" database and start it using lsnrctl command.

Sample listener.

[oracle@newyork admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0
/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

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

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.69)(PORT = 1521))
  )

ADR_BASE_LISTENER = /u01/app/oracle


3) Make appropriate directory structures.

[oracle@newyork ~]$ mkdir -p /u01/app/oracle/admin/dup/adump
[oracle@newyork ~]$ mkdir -p /u01/app/oracle/oradata
[oracle@newyork ~]$ mkdir -p /u01/app/oracle/flash_recovery_area

4) Make appropriate entries in "/etc/resolv.conf" so a connection can
   be made through SCAN.

search example.com
nameserver 192.168.1.100

5) Do tnsping from both nodes to make sure both systems are reachable.

tnsping dell
tnsping dup

6) create spfile from pfile and start database in nomount stage.

[oracle@newyork ~]$ echo $ORACLE_SID
dup
[oracle@newyork ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue May 28 01:24:39 2013

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

Connected to an idle instance.

SQL> create spfile from pfile='/u01/initdup.ora';

File created.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  263639040 bytes
Fixed Size                  1335892 bytes
Variable Size             230690220 bytes
Database Buffers           29360128 bytes
Redo Buffers                2252800 bytes

7) Through RMAN do active database cloning.

[oracle@newyork ~]$ echo $ORACLE_SID
dup
[oracle@newyork ~]$ rman target sys/sys@dell nocatalog auxiliary sys/sys@dup

Recovery Manager: Release 11.2.0.1.0 - Production on Tue May 28 01:28:28 2013

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

connected to target database: DELL (DBID=3771264459)
using target database control file instead of recovery catalog
connected to auxiliary database: DUP (not mounted)

RMAN> duplicate database to dup
2> from active database
3> nofilenamecheck;

Starting Duplicate Db at 28-MAY-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  control_files = 
  ''/u01/app/oracle/oradata/DUP/controlfile/o1_mf_8t7gsyl1_.ctl'', ''/u01/app/oracle/
flash_recovery_area/DUP/controlfile/o1_mf_8t7gsyn9_.ctl'' comment=
 ''Set by RMAN'' scope=spfile";
   sql clone "alter system set  db_name = 
 ''DELL'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name = 
 ''DUP'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  '/u01/app/oracle/oradata/DUP/controlfile/
o1_mf_8t7gsyns_.ctl';
   restore clone controlfile to  '/u01/app/oracle/flash_recovery_area/DUP/controlfile/o1_mf_8t7gsyo3_.ctl' from 
 '/u01/app/oracle/oradata/DUP/controlfile/o1_mf_8t7gsyns_.ctl';
   sql clone "alter system set  control_files = 
  ''/u01/app/oracle/oradata/DUP/controlfile/o1_mf_8t7gsyns_.ctl'',
 ''/u01/app/oracle/flash_recovery_area/DUP/controlfile/o1_mf_8t7gsyo3_.ctl'' comment=
 ''Set by RMAN'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  control_files =  
 ''/u01/app/oracle/oradata/DUP/controlfile/o1_mf_8t7gsyl1_.ctl'',
 ''/u01/app/oracle/flash_recovery_area/DUP/controlfile/o1_mf_8t7gsyn9_.ctl''
 comment= ''Set by RMAN'' scope=spfile

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

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

Oracle instance shut down

Oracle instance started

Total System Global Area     263639040 bytes

Fixed Size                     1335892 bytes
Variable Size                230690220 bytes
Database Buffers              29360128 bytes
Redo Buffers                   2252800 bytes

Starting backup at 28-MAY-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 instance=dell2 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_dell2.f tag=TAG20130528T013011 RECID=2 STAMP=816571814
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 28-MAY-13

Starting restore at 28-MAY-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 28-MAY-13

sql statement: alter system set  control_files = 
  ''/u01/app/oracle/oradata/DUP/controlfile/o1_mf_8t7gsyns_.ctl'',
 ''/u01/app/oracle/flash_recovery_area/DUP/controlfile/o1_mf_8t7gsyo3_.ctl''
 comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     263639040 bytes

Fixed Size                     1335892 bytes
Variable Size                230690220 bytes
Database Buffers              29360128 bytes
Redo Buffers                   2252800 bytes

database mounted

contents of Memory Script:
{
   set newname for clone datafile  1 to new;
   set newname for clone datafile  2 to new;
   set newname for clone datafile  3 to new;
   set newname for clone datafile  4 to new;
   set newname for clone datafile  5 to new;
   set newname for clone datafile  6 to new;
   backup as copy reuse
   datafile  1 auxiliary format new
   datafile  2 auxiliary format new
   datafile  3 auxiliary format new
   datafile  4 auxiliary format new
   datafile  5 auxiliary format new
   datafile  6 auxiliary format new
   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 28-MAY-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/dell/datafile/system.267.800523427
output file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_system_0coanpe6_.dbf tag=TAG20130528T013045
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:39
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/dell/datafile/sysaux.263.800523439
output file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_sysaux_0doanphc_.dbf tag=TAG20130528T013045
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA/dell/datafile/example.266.800523445
output file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_example_0eoanpjf_.dbf tag=TAG20130528T013045
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:21
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/dell/datafile/undotbs1.265.800523447
output file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs1_0foanpk4_.dbf tag=TAG20130528T013045
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATA/dell/datafile/undotbs2.258.800523875
output file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs2_0goanpkc_.dbf tag=TAG20130528T013045
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:10
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/dell/datafile/users.262.800523447
output file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_users_0hoanpkm_.dbf tag=TAG20130528T013045
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
Finished backup at 28-MAY-13

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "+FRA/dell/archivelog/2013_05_28/thread_2_seq_3.270.816570261" auxiliary format 
 "/u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_2_3_%u_.arc"   archivelog like 
 "+FRA/dell/archivelog/2013_05_28/thread_1_seq_5.269.816572061" auxiliary format 
 "/u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_1_5_%u_.arc"   archivelog like 
 "+FRA/dell/archivelog/2013_05_28/thread_2_seq_4.267.816572061" auxiliary format 
 "/u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_2_4_%u_.arc"   ;
   catalog clone recovery area;
   switch clone datafile all;
}
executing Memory Script

Starting backup at 28-MAY-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=2 sequence=3 RECID=6 STAMP=816570261
output file name=/u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_2_3_0ioanpl7_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=5 RECID=7 STAMP=816572061
output file name=/u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_1_5_0joanpla_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log copy
input archived log thread=2 sequence=4 RECID=8 STAMP=816572062
output file name=/u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_2_4_0koanplb_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:02
Finished backup at 28-MAY-13

searching for all files in the recovery area

List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/flash_recovery_area/DUP/controlfile/o1_mf_8t7gsyn9_.ctl
File Name: /u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_2_3_0ioanpl7_.arc
File Name: /u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_2_4_0koanplb_.arc
File Name: /u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_1_5_0joanpla_.arc
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_2_3_0ioanpl7_.arc
File Name: /u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_2_4_0koanplb_.arc
File Name: /u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_1_5_0joanpla_.arc

List of Files Which Where Not Cataloged
=======================================
File Name: /u01/app/oracle/flash_recovery_area/DUP/controlfile/o1_mf_8t7gsyn9_.ctl
  RMAN-07517: Reason: The file header is corrupted

datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=816572080 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_system_0coanpe6_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=816572080 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_sysaux_0doanphc_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=816572080 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs1_0foanpk4_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=816572080 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_users_0hoanpkm_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=816572080 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_example_0eoanpjf_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=7 STAMP=816572080 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs2_0goanpkc_.dbf

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

executing command: SET until clause

Starting recover at 28-MAY-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK

starting media recovery

archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/flash_recovery_area/DUP/
archivelog/2013_05_28/o1_mf_1_5_0joanpla_.arc
archived log for thread 2 with sequence 4 is already on disk as file /u01/app/oracle/flash_recovery_area/DUP/
archivelog/2013_05_28/o1_mf_2_4_0koanplb_.arc
archived log file name=/u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_1_5_0joanpla_.arc
 thread=1 sequence=5
archived log file name=/u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_2_4_0koanplb_.arc
 thread=2 sequence=4
media recovery complete, elapsed time: 00:00:02
Finished recover at 28-MAY-13

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

Fixed Size                     1335892 bytes
Variable Size                230690220 bytes
Database Buffers              29360128 bytes
Redo Buffers                   2252800 bytes

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

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     263639040 bytes

Fixed Size                     1335892 bytes
Variable Size                230690220 bytes
Database Buffers              29360128 bytes
Redo Buffers                   2252800 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUP" RESETLOGS ARCHIVELOG 
  MAXLOGFILES    192
  MAXLOGMEMBERS      3
  MAXDATAFILES     1024
  MAXINSTANCES    32
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1  SIZE 50 M ,
  GROUP  2  SIZE 50 M 
 DATAFILE
  '/u01/app/oracle/oradata/DUP/datafile/o1_mf_system_0coanpe6_.dbf'
 CHARACTER SET WE8MSWIN1252

sql statement: ALTER DATABASE ADD LOGFILE 
  
  INSTANCE 'i2' 
  GROUP  3  SIZE 50 M ,
  GROUP  4  SIZE 50 M 

contents of Memory Script:
{
   set newname for clone tempfile  1 to new;
   switch clone tempfile all;
   catalog clone datafilecopy  "/u01/app/oracle/oradata/DUP/datafile/o1_mf_sysaux_0doanphc_.dbf", 
 "/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs1_0foanpk4_.dbf", 
 "/u01/app/oracle/oradata/DUP/datafile/o1_mf_users_0hoanpkm_.dbf", 
 "/u01/app/oracle/oradata/DUP/datafile/o1_mf_example_0eoanpjf_.dbf", 
 "/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs2_0goanpkc_.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/DUP/datafile/o1_mf_temp_%u_.tmp in control file

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_sysaux_0doanphc_.dbf RECID=1 STAMP=816572129
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs1_0foanpk4_.dbf RECID=2 STAMP=816572129
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_users_0hoanpkm_.dbf RECID=3 STAMP=816572129
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_example_0eoanpjf_.dbf RECID=4 STAMP=816572129
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs2_0goanpkc_.dbf RECID=5 STAMP=816572129

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=816572129 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_sysaux_0doanphc_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=816572129 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs1_0foanpk4_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=816572129 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_users_0hoanpkm_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=816572129 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_example_0eoanpjf_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=816572129 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs2_0goanpkc_.dbf

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

database opened
Finished Duplicate Db at 28-MAY-13

8) after cloning disable thread 2 and remove respective redo groups.

SQL> select thread#,status,enabled from v$thread;

   THREAD# STATUS ENABLED
---------- ------ --------
         1 OPEN   PUBLIC
         2 CLOSED PUBLIC

SQL> select group# from v$log where thread#=2;

    GROUP#
----------
         3
         4

SQL> alter database disable thread 2;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database drop logfile group 4;

Database altered.

SQL> select thread#, status, enabled from v$thread;

   THREAD# STATUS ENABLED
---------- ------ --------
         1 OPEN   PUBLIC


9) drop additional undo tablespaces

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
UNDOTBS2
EXAMPLE

7 rows selected.


SQL> drop tablespace UNDOTBS2 including contents and datafiles;

Tablespace dropped.



10) check status of tablespaces.

SQL> select tablespace_name,file_name,status from dba_temp_files;

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
STATUS
-------
TEMP
/u01/app/oracle/oradata/DUP/datafile/o1_mf_temp_8t7h4w47_.tmp
ONLINE



SQL> select tablespace_name,file_name,status from dba_data_files;

TABLESPACE_NAME
------------------------------
FILE_NAME
---------------------------------------------------------------------------------------------------
STATUS
---------
SYSTEM
/u01/app/oracle/oradata/DUP/datafile/o1_mf_system_0coanpe6_.dbf
AVAILABLE

SYSAUX
/u01/app/oracle/oradata/DUP/datafile/o1_mf_sysaux_0doanphc_.dbf
AVAILABLE

TABLESPACE_NAME
------------------------------
FILE_NAME
---------------------------------------------------------------------------------------------------
STATUS
---------

UNDOTBS1
/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs1_0foanpk4_.dbf
AVAILABLE

USERS
/u01/app/oracle/oradata/DUP/datafile/o1_mf_users_0hoanpkm_.dbf

TABLESPACE_NAME
------------------------------
FILE_NAME
---------------------------------------------------------------------------------------------------
STATUS
---------
AVAILABLE

EXAMPLE
/u01/app/oracle/oradata/DUP/datafile/o1_mf_example_0eoanpjf_.dbf
AVAILABLE



Cloning Finished.

Read more >>

General Performance Tuning Guidelines for Oracle RAC.

1) Rule of thumb - It is safe to assume that an application which
scales well in a single instance environment would scale well in
a RAC environment. Opposite of that is also true, If an application
has problems with scalability in a single instance environment,
Then it may not scale well in a RAC environment.

2) Speed up instance recovery. 

i) To speed up the instance recovery time use FAST_START_MTTR_TARGET.

 In a single instance environment instance start-up + crash recovery time
 is controlled by this parameter.

Read more >>

Collect Diagnostic Data in 11gR2 RAC Using DiagCollection.pl Script.

All the log files related to clusterware processes can be found in the "$GRID_HOME/logs"
directory. So, if there is some problem with the clusterware you could check
all the log files in the directory.


But mining data from all the log files is difficult. So, in order
to ease of this difficulty in 11gR2 you can use the "diagcollection.pl"
script to collect diagnostic data.

Run the diagcollection.pl script as the root user to collect diagnostic information 
from an Oracle Clusterware installation. The diagnostics provide additional information
so that Oracle Support Services can resolve problems. Run this script from the 
operating system prompt as follows, where CRS_home is the home directory of your Oracle 
Clusterware installation:

# Invoke this utility as the root user.
# CRS_home/bin/diagcollection.pl --collect

[root@rac1 ~]# /u01/app/11.2.0/grid/bin/diagcollection.pl --collect
Production Copyright 2004, 2008, Oracle.  All rights reserved
Cluster Ready Services (CRS) diagnostic collection tool
The following CRS diagnostic archives will be created in the local directory.
crsData_rac1_20130206_0017.tar.gz -> logs,traces and cores from CRS home. 
Note: core files will be packaged only with the --core option.
ocrData_rac1_20130206_0017.tar.gz -> ocrdump, ocrcheck etc
coreData_rac1_20130206_0017.tar.gz -> contents of CRS core files in text format

osData_rac1_20130206_0017.tar.gz -> logs from Operating System
Collecting crs data
/bin/tar: log/rac1/cssd/ocssd.log: file changed as we read it
/bin/tar: log/rac1/ctssd/octssd.log: file changed as we read it
Collecting OCR data
Collecting information from core files
No corefiles found
Collecting OS logs

[root@rac1 ~]# ll
total 14720
-rw------- 1 root root     2946 Aug 23 21:21 anaconda-ks.cfg
-rw-r--r-- 1 root root 14785218 Feb  6 00:17 crsData_rac1_20130206_0017.tar.gz
drwxr-xr-x 4 root root     4096 Feb  3 16:21 Desktop
-rw-r--r-- 1 root root    42129 Aug 23 21:20 install.log
-rw-r--r-- 1 root root     5259 Aug 23 21:20 install.log.syslog
-rw-r--r-- 1 root root    10960 Feb  6 00:18 ocrData_rac1_20130206_0017.tar.gz
-rw-r--r-- 1 root root   171884 Feb  6 00:18 osData_rac1_20130206_0017.tar.gz
-rwxr-x--- 1 root root      696 Jan 26 14:07 root.sh.racnode1.AFTER_INSTALL

To clean the collected data use the "--clean" option.

[root@rac1 ~]# /u01/app/11.2.0/grid/bin/diagcollection.pl --clean
Production Copyright 2004, 2008, Oracle.  All rights reserved
Cluster Ready Services (CRS) diagnostic collection tool
Cleaning up tar and gzip files
Done


[root@rac1 ~]# ll
total 88
-rw------- 1 root root  2946 Aug 23 21:21 anaconda-ks.cfg
drwxr-xr-x 5 root root  4096 Feb  6 00:19 cfgtoollogs
drwxr-xr-x 4 root root  4096 Feb  3 16:21 Desktop
drwxr-xr-x 2 root root  4096 Feb  6 00:19 install
-rw-r--r-- 1 root root 42129 Aug 23 21:20 install.log
-rw-r--r-- 1 root root  5259 Aug 23 21:20 install.log.syslog
drwxr-xr-x 3 root root  4096 Feb  6 00:19 log
-rwxr-x--- 1 root root   696 Jan 26 14:07 root.sh.racnode1.AFTER_INSTALL

Read more >>

Oracle RAC 11gR2 Policy Managed Database Creation using DBCA.

Policy managed databases depend upon server pools in order to work.


Starting from 11gR2, Oracle RAC has three kinds of server pools.

1) Free Server Pool - This pool contains servers which are not assigned to 
   any server pools.
2) Generic Server Pool - This pool contains Pre 11gR2 databases as well as administrator-managed
   databases. 
3) User Created pool - This server pool is created by the user and is used for running 
   policy managed databases.




I have a test three node cluster, Which will be used for creating 
policy managed database.

# Make sure that clusterware services are running on all the nodes
# before starting starting with the installation.

[root@rac1 ~]# su - grid
[grid@rac1 ~]$ crsctl check cluster -all
**************************************************************
rac1:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
rac2:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
rac3:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************

Also, make sure that you have appropriate servers in the free pool.

[grid@rac1 ~]$ crsctl status serverpool
NAME=Free
ACTIVE_SERVERS=rac1 rac2 rac3

NAME=Generic
ACTIVE_SERVERS=
 

In my test cluster i did not have servers in the free pool as they were being
used in the generic pool because of the administrator managed database that i had
created earlier.

So, i had to drop that database in order get servers in the free pool. 


Start with the database creation.

[root@rac1 ~]# xhost +
access control disabled, clients can connect from any host
[root@rac1 ~]# su - oracle
[oracle@rac1 ~]$ dbca










In the server pool creation step i have set the cardinality to "2"

this means that out of three nodes my database will run only on 2 nodes

at a time. 




















[grid@rac1 ~]$ crsctl status serverpool
NAME=Free
ACTIVE_SERVERS=rac1

NAME=Generic
ACTIVE_SERVERS=

NAME=ora.myserverpool
ACTIVE_SERVERS=rac2 rac3

[oracle@rac1 ~]$ srvctl status database -d dell
Instance dell_1 is running on node rac2
Instance dell_2 is running on node rac3

Read more >>

How to Password Protect Listener in 11g ?

As per Oracle Documentation from Oracle 11g Release 2 (11.2), the password feature is being deprecated.
This does not cause a loss of security because authentication is enforced through local operating
system authentication.

If remote administration of listener is required, then use one of the following methods to
connect to and administer the listener.

1) Connect to the host where listener is running using SSH or other secure method.
2) Use Oracle Enterprise Manager to adminster the listener.


Listener Name: prod

Log in to the listener control utility.

[oracle@canada ~]$ lsnrctl

#Select listener name.

LSNRCTL> set current_listener prod
Current Listener is prod

#Change password(press enter for old password).

LSNRCTL> change_password
Old password: 
New password: 
Reenter new password: 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=canada.example.com)(PORT=1521)))
Password changed for prod
The command completed successfully

#Save the configuration.

LSNRCTL> save_config
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=canada.example.com)(PORT=1521)))
Saved prod configuration parameters.
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Old Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.bak
The command completed successfully


In listener.ora file insert the following line.
LOCAL_OS_AUTHENTICATION_PROD=OFF

#Restart the listener.

[oracle@canada ~]$ lsnrctl

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 04-JAN-2013 11:43:43

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> set current_listener prod
Current Listener is prod

LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=canada.example.com)(PORT=1521)))
The command completed successfully

LSNRCTL> start
Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/canada/prod/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=canada.example.com)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=canada.example.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     prod
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                04-JAN-2013 11:43:59
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Password
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/canada/prod/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=canada.example.com)(PORT=1521)))
Services Summary...
Service "dell" has 1 instance(s).
  Instance "dell", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

#At this stage the password has been successfully implemented.
#Stopping a listener at this stage will result in an TNS error.

LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=canada.example.com)(PORT=1521)))
TNS-01169: The listener has not recognized the password


#To stop it set the password.

LSNRCTL> set password
Password: 
The command completed successfully

LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=canada.example.com)(PORT=1521)))
The command completed successfully

LSNRCTL> start
Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/canada/prod/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=canada.example.com)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=canada.example.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     prod
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                04-JAN-2013 11:44:51
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Password
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/canada/prod/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=canada.example.com)(PORT=1521)))
Services Summary...
Service "dell" has 1 instance(s).
  Instance "dell", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Read more >>

How to Configure DNS Server in RHEL 6?

Server IP Address: 192.168.1.121
Hostname: newyork.example.com

[root@newyork named]# vi /etc/sysconfig/network-scripts/ifcfg-eth0

Read more >>