In sweet memories of my ever loving brother "kutty thambi " ARUN KUMAR

Wednesday, September 26, 2012

prvf 5300 failed to retrieve active version for crs on this node

We tried to install  11.2.0.2 oracle software version.When we ran the runInstaller it throws the
 prvf 5300 failed to retrieve active version for crs on this node error.
 11.2.0.3 ASM Grid already installed in server also the server has 11.1.0.7 version software and 11.1.0.7 databases using 11.2.0.3 ASM

Solution:-

1. Backup the files /etc/ocr.loc or /etc/oracle/ocr.loc and /etc/oracle/olr.loc, then rename them to some other names.
2. Retry the standalone (non-RAC) Database 11.2.0.2 installation.
3. After installation completed; restore/rename the backed up files to ocr.loc and olr.loc files in the /etc/ocr.loc or /etc/oracle/ocr.loc and /etc/oracle/olr.loc

Note:-
--No need to shutdown the existing databases and ASM while performing this operations.
--In some environment ocr.loc file is located in  /etc/ocr.loc or /etc/oracle/ocr.loc. It depends on the Operating System.

Problem we have faced at our environment:-


After renamed both the files we are unable to log in to existing databases available in that server.
We have not restored the original files ocr.loc and olr.loc files in original location after completing 11.2.0.2 oracle software installation ,so we have faced the below error;  
Note:- We came to know ; These 2 files are needed if we are using a grid,.
after renamed both the files to original name; we are able to log in to existing databases available in that server.

see the below error message:-
SQL> select status from v$datafile;
select status from v$datafile
*
ERROR at line 1:
ORA-00204: error in reading (block 1, # blocks 1) of control file
ORA-00202: control file: '+DG1/eagleb/controlfile/current.716.751453263'
ORA-15081: failed to submit an I/O operation to a disk

we have renamed the files ocr.loc and olr.loc again from backup. and the problem got resolved.
   
These 2 files are needed if we are using a grid,.

I really thank my colleague Krishna; We worked together in this problem.

Regards,
Rajesh Kumar Govindarajan

Thursday, May 31, 2012

Drop Database Command Demo

From 10g onwards, we have Drop Database command, that made a fast and efficient cleanup of all datafiles, redologs and control files in one command.


Drop database do require to mount the database in exclusive restricted mode.

The steps are as follows:

•shutdown abort;

•startup mount exclusive restrict;

•drop database;

Here is a Demo:-
 
$ . oraenv


ORACLE_SID = [oracle] ? miadev07

The Oracle base for ORACLE_HOME=/u01/app/oracle/product/10.2.0.4 is /u01/app/oracle/product/10.2.0.4

dev07: /home/oracle

$ sqlplus / as sysdba



SQL*Plus: Release 10.2.0.4.0 - Production on Wed May 30 18:46:37 2012



Copyright (c) 1982, 2007, Oracle. All Rights Reserved.



Connected to an idle instance.


SQL> startup mount exclusive restrict;

ORACLE instance started.



Total System Global Area 1073741824 bytes

Fixed Size 2089472 bytes

Variable Size 390073856 bytes

Database Buffers 675282944 bytes

Redo Buffers 6295552 bytes

Database mounted.

SQL> select name from v$datafile;



NAME

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

+DG1/miadev07/datafile/system.342.712614251

+DG1/miadev07/datafile/undotbs1.269.712587099

+DG1/miadev07/datafile/sysaux.311.712587103

+DG1/miadev07/datafile/users.315.712587107



SQL> select member from v$logfile;



MEMBER

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

+DG1/miadev07/onlinelog/group_3.351.712625873

+DG1/miadev07/onlinelog/group_2.350.712625871

+DG1/miadev07/onlinelog/group_1.349.712625871



SQL> select name from v$controlfile;



NAME

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

+DG1/miadev07/controlfile/current.348.712625853



SQL> drop database;



Database dropped.



Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit

Check Datafile/Redologs/Controlfile after drop database in ASM/File system. it will not available.
 
Regards,
Rajesh Kumar Govindarajan.


Monday, February 27, 2012

Step by Step document to Create Physical Standby Database from Active Database using RMAN

Step by step document to create a physical standby database using RMAN DUPLICATE FROM ACTIVE DATABASE command without shutting down the primary and using primary active database files (No need to take backup)

Database Name :- WHITE
Primary db_unique_name :- WHITE
standby db_unique_name :- BLACK

Primary Database Server Name :- DC1
Standby Database Server Name :- DC2

Steps:-
1.Make the necessary changes to the primary database.
a. Enable force logging.
b. Creating the password file if one does not exist.
c. Create standby redologs.
d. Modify the parameter file suitable for Dataguard.
e. Change the database to archivelog mode if it is in no archivelog mode.
2. Ensure that the sql*net connectivity is working fine.
----if not add an entry for database in TNSNAMES.ORA and LISTENER.ORA file for primary and standby database.
3. Create the standby database over the network using the active(primary) database files.

a.create a password file for standby database
The username is required to be SYS and the password needs to be the same on the Primary and Standby.
The password file name must match the instance name/SID used at the standby site, not the DB_NAME.
b. Create the initialization parameter file for the standby database (auxiliary database)
c. Create the necessary mount points or the folders for the database files
d. Run the standby creation ON STANDBY by connecting to primary as target database.

RMAN> run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;

duplicate target database for standby from active database
spfile
parameter_value_convert 'white','black'
set db_unique_name='black'
set db_file_name_convert='/white/','/black/'
set log_file_name_convert='/white/','/black/'
set control_files='+DATA/black/controlfile/control01.ctl'
set log_archive_max_processes='5'
set fal_client='black'
set fal_server='white'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(white,black)'
set log_archive_dest_1='service=white ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=white'
;
}

4. Check the log shipping and apply.

Demo:-


SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(white,black)';


SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=+DATA/archivelog/white/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=white';


SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=black LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=black';


SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;


SQL> alter system set FAL_SERVER=black;


SQL> alter system set FAL_CLIENT=white;


SQL> alter system set DB_FILE_NAME_CONVERT='+DATA/black/datafile/','+DATA/white/datafile/','+DATA/black/tempfile/','+DATA/white/tempfile/' scope=spfile;


SQL> alter system set LOG_FILE_NAME_CONVERT='+DATA/black/onlinelog/','+DATA/white/onlinelog/' scope=spfile;


SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=+DATA/archivelog/white/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=white';


SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=black LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=black';


SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;


SQL> alter system set FAL_SERVER=black;


SQL> alter system set FAL_CLIENT=white;


SQL> alter system set DB_FILE_NAME_CONVERT='+DATA/black/datafile/','+DATA/white/datafile/','+DATA/black/tempfile/','+DATA/white/tempfile/' scope=spfile;


SQL> alter system set LOG_FILE_NAME_CONVERT='+DATA/black/onlinelog/','+DATA/white/onlinelog/' scope=spfile;

System altered.

SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/11.2.0/db_1/dbs/arch
Oldest online log sequence 13
Current log sequence 15
SQL> select force_logging from v$database;

FOR
---
NO


SQL> Alter database force logging;


SQL> select force_logging from v$database;

FOR
---
YES


SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

SQL> select name,database_role,db_unique_name from v$database;

NAME DATABASE_ROLE DB_UNIQUE_NAME
--------- ---------------- ------------------------------
WHITE PRIMARY white

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/white/datafile/system.324.776321695
+DATA/white/datafile/sysaux.337.776321703
+DATA/white/datafile/undotbs1.336.776321707
+DATA/white/datafile/users.335.776321707

SQL> col member for a65
SQL> select a.member,b.bytes/1024/1024 from v$Logfile a,v$log b where a.group#=b.group#;

MEMBER
-----------------------------------------------------------------
B.BYTES/1024/1024
-----------------
+DATA/white/onlinelog/group_3.302.776321951
5

+DATA/white/onlinelog/group_2.322.776321951
5

+DATA/white/onlinelog/group_1.311.776321949
5


SQL> select name from v$tempfile;

NAME
---------------------------------------------------
+DATA/white/tempfile/temp.321.776321963


Adding standby redolog files.

SQL> alter database add standby logfile size 5m;

Database altered.

SQL> alter database add standby logfile size 5m;

Database altered.

SQL> alter database add standby logfile size 5m;

Database altered.

SQL> alter database add standby logfile size 5m;

Database altered.

SQL> col member for a56
SQL> select member,type from v$logfile;

MEMBER TYPE
-------------------------------------------------------- -------
+DATA/white/onlinelog/group_3.302.776321951 ONLINE
+DATA/white/onlinelog/group_2.322.776321951 ONLINE
+DATA/white/onlinelog/group_1.311.776321949 ONLINE
+DATA/white/onlinelog/group_4.358.776324285 STANDBY
+DATA/white/onlinelog/group_5.456.776324299 STANDBY
+DATA/white/onlinelog/group_6.331.776324299 STANDBY
+DATA/white/onlinelog/group_7.318.776324301 STANDBY

7 rows selected.

SQL>


follow the below steps, if your database is in no archivelog, or else no need to shutdown the database
========================================================

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1456349184 bytes
Fixed Size 1336568 bytes
Variable Size 855640840 bytes
Database Buffers 587202560 bytes
Redo Buffers 12169216 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> create pfile='/tmp/init.ora' from spfile;

File created.



=======================================================

add the following entry in the listener.ora file on primary database
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.2.101 )(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = white )
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = white)
)
)


==================================================
tnsnames.ora file on both machines
==================================================
BLACK =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dc2.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = black)
)
)



WHITE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dc1.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = white)
)
)

=============================================================

[oracle@dc1 admin]$ tnsping white

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 27-FEB-2012 05:46:29

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dc1.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = white)))
OK (50 msec)
[oracle@dc1 admin]$ tnsping black

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 27-FEB-2012 05:46:33

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dc2.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = black)))
OK (10 msec)
[oracle@dc1 admin]$

=========================================================

[oracle@dc1 dbs]$ scp orapwwhite dc2:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwblack
orapwwhite 100% 1536 1.5KB/s 00:00
[oracle@dc1 dbs]$

==========================================================

[oracle@dc1 dbs]$ scp /tmp/init.ora dc2:/u01/app/oracle/product/11.2.0/db_1/dbs/initblack.ora
init.ora 100% 1333 1.3KB/s 00:00
[oracle@dc1 dbs]$

SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/white/spfilewhite.ora
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +DATA/archivelog/white/
Oldest online log sequence 16
Next log sequence to archive 18
Current log sequence 18
SQL>

follow below steps on machine two for creating standby database

=================================================

[oracle@dc2 ~]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs/
[oracle@dc2 dbs]$ cat initblack.ora
white.__db_cache_size=587202560
white.__java_pool_size=16777216
white.__large_pool_size=16777216
white.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
white.__pga_aggregate_target=587202560
white.__sga_target=872415232
white.__shared_io_pool_size=0
white.__shared_pool_size=234881024
white.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/white/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+DATA/white/controlfile/current.310.776321931'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_name_convert='+DATA/black/datafile/','+DATA/white/datafile/','+DATA/black/tempfile/','+DATA/white/tempfile/'
*.db_name='white'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=whiteXDB)'
*.fal_client='WHITE'
*.fal_server='BLACK'
*.log_archive_config='DG_CONFIG=(white,black)'
*.log_archive_dest_1='LOCATION=+DATA/archivelog/white/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=white'
*.log_archive_dest_2='SERVICE=black LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=black'
*.log_archive_dest_state_1='ENABLE'
*.log_file_name_convert='+DATA/black/onlinelog/','+DATA/white/onlinelog/'
*.memory_target=1449132032
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
[oracle@dc2 dbs]$

===================================
after editing the pfile parameter required for standby database
=================================

black.__db_cache_size=587202560
black.__java_pool_size=16777216
black.__large_pool_size=16777216
black.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
black.__pga_aggregate_target=587202560
black.__sga_target=872415232
black.__shared_io_pool_size=0
black.__shared_pool_size=234881024
black.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/black/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+DATA/black/controlfile/current.310.776321931'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_name_convert='+DATA/white/datafile/','+DATA/black/datafile/','+DATA/white/tempfile/','+DATA/black/tempfile/'
*.db_name='white'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=blackXDB)'
*.fal_client='BLACK'
*.fal_server='WHITE'
*.log_archive_config='DG_CONFIG=(white,black)'
*.log_archive_dest_1='LOCATION=+DATA/archivelog/black/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=black'
*.log_archive_dest_2='SERVICE=white LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=white'
*.log_archive_dest_state_1='ENABLE'
*.log_file_name_convert='+DATA/white/onlinelog/','+DATA/black/onlinelog/'
*.memory_target=1449132032
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

==========================================
add an entry in the /etc/oratab file

black:/u01/app/oracle/product/11.2.0/db_1:N

add the following lines to the listener.ora file

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = black )
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = black)
)
)


add the below entries on both nodes tnsnames.ora file


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

BLACK =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dc2.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = black)
)
)

WHITE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dc1.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = white)
)
)

[oracle@dc2 admin]$ . oraenv
ORACLE_SID = [black] ? black
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@dc2 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 27 05:59:50 2012

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

Connected to an idle instance.

SQL> startup nomount
ORA-00304: requested INSTANCE_NUMBER is busy

to resolve this issue add db_unique_name='black' in the pfile of standby database.



ORA-09925: Unable to create audit trail file
Linux Error: 2: No such file or directory
Additional information: 9925

to resolve this issue create adump directory

[oracle@dc2 dbs]$ mkdir -p /u01/app/oracle/admin/black/adump

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1456349184 bytes
Fixed Size 1336568 bytes
Variable Size 855640840 bytes
Database Buffers 587202560 bytes
Redo Buffers 12169216 bytes
SQL>

SQL> conn sys/oracle@white as sysdba;
Connected.
SQL> conn sys/oracle@black as sysdba;
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

-- to resolve this issue need to check/add the below entries in listener.ora file.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = black )
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = black)
)
)


Warning: You are no longer connected to ORACLE.
SQL> conn sys/oracle@black as sysdba;
ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections

--- to resolve blocking new connections shutdown the database and startup nomount the standby database again will resolve it.

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

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

create standby database from active primary database using RMAN

[oracle@dc2 dbs]$ . oraenv
ORACLE_SID = [oracle] ? black
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@dc2 dbs]$ rman target sys/oracle@white auxiliary sys/oracle@black

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Feb 27 06:11:51 2012

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

connected to target database: WHITE (DBID=3671326283)
connected to auxiliary database: WHITE (not mounted)


RMAN> run {
allocate channel p1 type disk;
allocate channel p2 type disk;
allocate channel p3 type disk;
allocate channel p4 type disk;
allocate auxiliary channel stby type disk;

duplicate target database for standby from active database
spfile
parameter_value_convert 'white','black'
set db_unique_name='black'
set db_file_name_convert='/white/','/black/'
set log_file_name_convert='/white/','/black/'
set control_files='+DATA/black/controlfile/control01.ctl'
set log_archive_max_processes='5'
set fal_client='black'
set fal_server='white'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(white,black)'
;
}
2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21>
using target database control file instead of recovery catalog
allocated channel: p1
channel p1: SID=55 device type=DISK

allocated channel: p2
channel p2: SID=40 device type=DISK

allocated channel: p3
channel p3: SID=1 device type=DISK

allocated channel: p4
channel p4: SID=39 device type=DISK

allocated channel: stby
channel stby: SID=32 device type=DISK

Starting Duplicate Db at 27-FEB-12

contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwwhite' auxiliary format
'/u01/app/oracle/product/11.2.0/db_1/dbs/orapwblack' targetfile
'+DATA/white/spfilewhite.ora' auxiliary format
'/u01/app/oracle/product/11.2.0/db_1/dbs/spfileblack.ora' ;
sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/db_1/dbs/spfileblack.ora''";
}
executing Memory Script

Starting backup at 27-FEB-12
Finished backup at 27-FEB-12

sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/db_1/dbs/spfileblack.ora''

contents of Memory Script:
{
sql clone "alter system set audit_file_dest =
''/u01/app/oracle/admin/black/adump'' comment=
'''' scope=spfile";
sql clone "alter system set dispatchers =
''(PROTOCOL=TCP) (SERVICE=blackXDB)'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_1 =
''LOCATION=+DATA/archivelog/black/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=white'' comment=
'''' scope=spfile";
sql clone "alter system set db_unique_name =
''black'' comment=
'''' scope=spfile";
sql clone "alter system set db_file_name_convert =
''/white/'', ''/black/'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''/white/'', ''/black/'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''+DATA/black/controlfile/control01.ctl'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_max_processes =
5 comment=
'''' scope=spfile";
sql clone "alter system set fal_client =
''black'' comment=
'''' scope=spfile";
sql clone "alter system set fal_server =
''white'' comment=
'''' scope=spfile";
sql clone "alter system set standby_file_management =
''AUTO'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_config =
''dg_config=(white,black)'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

sql statement: alter system set audit_file_dest = ''/u01/app/oracle/admin/black/adump'' comment= '''' scope=spfile

sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=blackXDB)'' comment= '''' scope=spfile

sql statement: alter system set log_archive_dest_1 = ''LOCATION=+DATA/archivelog/black/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=white'' comment= '''' scope=spfile

sql statement: alter system set db_unique_name = ''black'' comment= '''' scope=spfile

sql statement: alter system set db_file_name_convert = ''/white/'', ''/black/'' comment= '''' scope=spfile

sql statement: alter system set log_file_name_convert = ''/white/'', ''/black/'' comment= '''' scope=spfile

sql statement: alter system set control_files = ''+DATA/black/controlfile/control01.ctl'' comment= '''' scope=spfile

sql statement: alter system set log_archive_max_processes = 5 comment= '''' scope=spfile

sql statement: alter system set fal_client = ''black'' comment= '''' scope=spfile

sql statement: alter system set fal_server = ''white'' comment= '''' scope=spfile

sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile

sql statement: alter system set log_archive_config = ''dg_config=(white,black)'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 1456349184 bytes

Fixed Size 1336568 bytes
Variable Size 855640840 bytes
Database Buffers 587202560 bytes
Redo Buffers 12169216 bytes
allocated channel: stby
channel stby: SID=29 device type=DISK

contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '+DATA/black/controlfile/control01.ctl';
}
executing Memory Script

Starting backup at 27-FEB-12
channel p1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_white.f tag=TAG20120227T061458 RECID=1 STAMP=776326500
channel p1: datafile copy complete, elapsed time: 00:00:08
Finished backup at 27-FEB-12

contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:
{
set newname for tempfile 1 to
"+data";
switch clone tempfile all;
set newname for datafile 1 to
"+data";
set newname for datafile 2 to
"+data";
set newname for datafile 3 to
"+data";
set newname for datafile 4 to
"+data";
backup as copy reuse
datafile 1 auxiliary format
"+data" datafile
2 auxiliary format
"+data" datafile
3 auxiliary format
"+data" datafile
4 auxiliary format
"+data" ;
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +data in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 27-FEB-12
channel p1: starting datafile copy
input datafile file number=00001 name=+DATA/white/datafile/system.324.776321695
channel p2: starting datafile copy
input datafile file number=00002 name=+DATA/white/datafile/sysaux.337.776321703
channel p3: starting datafile copy
input datafile file number=00003 name=+DATA/white/datafile/undotbs1.336.776321707
channel p4: starting datafile copy
input datafile file number=00004 name=+DATA/white/datafile/users.335.776321707
output file name=+DATA/black/datafile/undotbs1.266.776326483 tag=TAG20120227T061514
channel p3: datafile copy complete, elapsed time: 00:00:08
output file name=+DATA/black/datafile/users.514.776326483 tag=TAG20120227T061514
channel p4: datafile copy complete, elapsed time: 00:00:09
output file name=+DATA/black/datafile/sysaux.518.776326483 tag=TAG20120227T061514
channel p2: datafile copy complete, elapsed time: 00:01:19
output file name=+DATA/black/datafile/system.517.776326481 tag=TAG20120227T061514
channel p1: datafile copy complete, elapsed time: 00:01:40
Finished backup at 27-FEB-12

sql statement: alter system archive log current

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

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=776326581 file name=+DATA/black/datafile/system.517.776326481
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=776326581 file name=+DATA/black/datafile/sysaux.518.776326483
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=776326581 file name=+DATA/black/datafile/undotbs1.266.776326483
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=776326581 file name=+DATA/black/datafile/users.514.776326483
Finished Duplicate Db at 27-FEB-12
released channel: p1
released channel: p2
released channel: p3
released channel: p4
released channel: stby

RMAN> exit


Recovery Manager complete.
[oracle@dc2 dbs]$


SQL> conn / as sysdba
Connected.
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/black/datafile/system.517.776326481
+DATA/black/datafile/sysaux.518.776326483
+DATA/black/datafile/undotbs1.266.776326483
+DATA/black/datafile/users.514.776326483

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
+DATA

SQL> col member for a56
SQL> select member,type from v$logfile;

MEMBER TYPE
-------------------------------------------------------- -------
+DATA/black/onlinelog/group_3.587.776326583 ONLINE
+DATA/black/onlinelog/group_2.519.776326583 ONLINE
+DATA/black/onlinelog/group_1.516.776326583 ONLINE
+DATA/black/onlinelog/group_4.520.776326583 STANDBY
+DATA/black/onlinelog/group_5.522.776326583 STANDBY
+DATA/black/onlinelog/group_6.535.776326583 STANDBY
+DATA/black/onlinelog/group_7.534.776326583 STANDBY

7 rows selected.

SQL> select name,database_role,db_unique_name from v$database;

NAME DATABASE_ROLE DB_UNIQUE_NAME
--------- ---------------- ------------------------------
WHITE PHYSICAL STANDBY black

SQL>


SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination ?/dbs/arch
Oldest online log sequence 0
Next log sequence to archive 0
Current log sequence 0
SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination ?/dbs/arch
Oldest online log sequence 22
Next log sequence to archive 0
Current log sequence 23
SQL>


SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY



Regards,
Rajesh Kumar Govindarajan.

Sunday, January 22, 2012

Create single instance standby database for RAC Database

here i created a physical standby database stbydb for RAC database primdb instance names primdb1 and primdb2 , server names:- DC1 and DC2.

PRIMARY RAC DATABASE INSTANCE PRIMDB1 IN HOST MACHINE DC1
PRIMARY RAC DATABASE INSTANCE PRIMDB2 IN HOST MACHINE DC2

CREATING A STANDBY DATABASE IN SERVER DC2 WITH NAME STBYDB

ASSUMPTION:
Primary:

· The RAC Primary Database is in fully operating condition in Archivelog mode using ASM storage (shared location) as archivelog destination.
· The Primary Database is using ASM as Storage and OMFs.
· Listener LISTENER has already been created.

steps in primary database Servers:-
# Database must in Archivelog mode.
# Enable Force Logging.
# Create the Standby Redo logs.
# Create a password file for all instances( Password must same in all nodes).
# Update listener.ora file to include SID information on each node in cluster.
# Verify cluster Service is available.
# Verify TNSNAMES.ORA file.
# Modify init.ora Parameters For DataGuard Configuration.
# Create temporary directory to hold the RMAN backup of this database.
# Backup the Primary Database for Standby.
# copy the Primary Database backup to the standby database server to create standby database.

steps in Standby database server:-
# create required folders and directories.
# Modify the init.ora parameter on standby database.
# create a password file(Password must same as primary database instances)
# Update listener.ora file to include SID information
# Add required TNS entries for standby database on all servers.
# Verify TNSNAMES.ORA file.
# Create standby database.

[oracle@dc1 dbs]$ . oraenv
ORACLE_SID = [black] ? primdb1
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@dc1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 22 03:41:53 2012

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> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 16
Next log sequence to archive 17
Current log sequence 17
SQL> alter database force logging;

Database altered.

SQL> select instance_name,status from gv$instance;

INSTANCE_NAME STATUS
---------------- ------------
primdb1 OPEN
primdb2 OPEN

SQL> select name,db_unique_name,database_role from gv$database;

NAME DB_UNIQUE_NAME DATABASE_ROLE
--------- ------------------------------ ----------------
PRIMDB primdb PRIMARY
PRIMDB primdb PRIMARY

IN PRIMARY DATABASE(any one of the node)
=====================================================================================

alter system set log_file_name_convert='+DATA/stbydb,'+DATA/primdb' scope=spfile sid='*';
alter system set db_file_name_convert='+DATA/stbydb','+DATA/primdb' scope=spfile sid='*';
alter system set fal_client='primdb1' sid='primdb1';
alter system set fal_client='primdb2' sid='primdb2';
alter system set fal_server='stbydb' sid='*';
alter system set log_archive_config='dg_config=(primdb,stbydb)' sid='*';
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primdb' sid='*';
alter system set log_archive_dest_state_2='ENABLE' sid='*';
alter system set log_archive_dest_2='SERVICE=stbydb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stbydb' sid='*';
alter system set log_archive_dest_3='LOCATION=+DATA/primdb/STANDBYLOG VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=primdb' sid='*';
alter system set log_archive_dest_state_1='enable' sid='*';
alter system set log_archive_dest_state_3='enable' sid='*';
alter system set log_archive_max_processes=7 sid='*';
alter system set log_archive_min_succeed_dest=2 sid='*';
alter system set remote_login_passwordfile='EXCLUSIVE' sid='*';
alter system set standby_file_management='auto' sid='*';


primdb2.__db_cache_size=520093696
primdb1.__db_cache_size=520093696
primdb2.__java_pool_size=16777216
primdb1.__java_pool_size=16777216
primdb2.__large_pool_size=16777216
primdb1.__large_pool_size=16777216
primdb2.__pga_aggregate_target=587202560
primdb1.__pga_aggregate_target=587202560
primdb2.__sga_target=872415232
primdb1.__sga_target=872415232
primdb2.__shared_io_pool_size=0
primdb1.__shared_io_pool_size=0
primdb2.__shared_pool_size=301989888
primdb1.__shared_pool_size=301989888
primdb2.__streams_pool_size=0
primdb1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/primdb/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DATA/primdb/controlfile/current.466.773206191','+DATA/primdb/controlfile/current.465.773206191'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_name_convert='+DATA/stbydb','+DATA/primdb'
*.db_name='primdb'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primdbXDB)'
primdb1.fal_client='primdb1'
primdb2.fal_client='primdb2'
*.fal_server='stbydb'
primdb2.instance_number=2
primdb1.instance_number=1
*.log_archive_config='dg_config=(primdb,stbydb)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primdb'
*.log_archive_dest_2='SERVICE=stbydb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stbydb'
*.log_archive_dest_3='LOCATION=+DATA/primdb/STANDBYLOG VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=primdb'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_dest_state_3='enable'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=7
*.log_archive_min_succeed_dest=2
*.memory_target=1449132032
*.open_cursors=300
*.processes=150
*.remote_listener='dc-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sec_case_sensitive_logon=FALSE
*.standby_archive_dest='+DATA/primdb/STANDBYLOG'
*.standby_file_management='auto'
primdb1.thread=1
primdb2.thread=2
primdb2.undo_tablespace='UNDOTBS1'
primdb1.undo_tablespace='UNDOTBS2'

SQL> select force_logging from v$database;

FOR
---
YES

SQL> alter system set SEC_CASE_SENSITIVE_LOGON=FALSE scope=spfile sid='*';

System altered.



[oracle@dc1 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwprimdb1 password=oracle force=y entries=5 ignorecase=y
[oracle@dc1 dbs]$

[oracle@dc2 dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@dc2 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwprimdb2 password=oracle entries=5 force=y ignorecase=y

SQL> sho parameter password

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
SQL>

create standby logfile on primary database
=====================================================================================
SQL> col member for a56
SQL> select group#,member,type from v$logfile order by 1;

GROUP# MEMBER TYPE
---------- -------------------------------------------------------- -------
1 +DATA/primdb/onlinelog/group_1.468.773206197 ONLINE
1 +DATA/primdb/onlinelog/group_1.467.773206195 ONLINE
2 +DATA/primdb/onlinelog/group_2.470.773206197 ONLINE
2 +DATA/primdb/onlinelog/group_2.469.773206197 ONLINE
3 +DATA/primdb/onlinelog/group_3.473.773206427 ONLINE
3 +DATA/primdb/onlinelog/group_3.474.773206427 ONLINE
4 +DATA/primdb/onlinelog/group_4.475.773206429 ONLINE
4 +DATA/primdb/onlinelog/group_4.476.773206429 ONLINE

8 rows selected.


SQL> select group#,bytes from v$Log;

GROUP# BYTES
---------- ----------
1 5242880
2 5242880
3 5242880
4 5242880

SQL>
alter database add standby logfile thread 1
group 5 size 5M,
group 6 size 5M;

Database altered.

SQL> alter database add standby logfile thread 2
group 7 size 5M,
group 8 size 5M;

Database altered.


SQL> select group#,member ,type from v$Logfile order by 1;

GROUP# MEMBER TYPE
---------- -------------------------------------------------------- -------
1 +DATA/primdb/onlinelog/group_1.467.773206195 ONLINE
1 +DATA/primdb/onlinelog/group_1.468.773206197 ONLINE
2 +DATA/primdb/onlinelog/group_2.469.773206197 ONLINE
2 +DATA/primdb/onlinelog/group_2.470.773206197 ONLINE
3 +DATA/primdb/onlinelog/group_3.473.773206427 ONLINE
3 +DATA/primdb/onlinelog/group_3.474.773206427 ONLINE
4 +DATA/primdb/onlinelog/group_4.475.773206429 ONLINE
4 +DATA/primdb/onlinelog/group_4.476.773206429 ONLINE
5 +DATA/primdb/onlinelog/group_5.481.773209067 STANDBY
5 +DATA/primdb/onlinelog/group_5.482.773209067 STANDBY
6 +DATA/primdb/onlinelog/group_6.483.773209067 STANDBY
6 +DATA/primdb/onlinelog/group_6.484.773209069 STANDBY
7 +DATA/primdb/onlinelog/group_7.485.773209079 STANDBY
7 +DATA/primdb/onlinelog/group_7.486.773209079 STANDBY
8 +DATA/primdb/onlinelog/group_8.487.773209079 STANDBY
8 +DATA/primdb/onlinelog/group_8.488.773209081 STANDBY

16 rows selected.


create pfile for standby database
=====================================================================================
SQL> create pfile='$ORACLE_HOME/dbs/initstbydb.ora' from spfile;

File created.

SQL>

edit the required parameters

remove the red coloured lines for the standby database initstbydb.ora file
edit/change the blue coloured lines as per standby database required.

primdb2.__db_cache_size=520093696 primdb1.__db_cache_size=520093696 primdb2.__java_pool_size=16777216 primdb1.__java_pool_size=16777216 primdb2.__large_pool_size=16777216 primdb1.__large_pool_size=16777216 primdb2.__pga_aggregate_target=587202560 primdb1.__pga_aggregate_target=587202560 primdb2.__sga_target=872415232 primdb1.__sga_target=872415232 primdb2.__shared_io_pool_size=0 primdb1.__shared_io_pool_size=0 primdb2.__shared_pool_size=301989888 primdb1.__shared_pool_size=301989888 primdb2.__streams_pool_size=0 primdb1.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/primdb/adump' *.audit_trail='db' *.cluster_database=true *.compatible='11.2.0.0.0' *.control_files='+DATA/primdb/controlfile/current.466.773206191','+DATA/primdb/controlfile/current.465.773206191' *.db_block_size=8192 *.db_create_file_dest='+DATA' *.db_domain='' *.db_file_name_convert='+DATA/stbydb','+DATA/primdb' *.db_name='primdb' *.db_recovery_file_dest='+DATA' *.db_recovery_file_dest_size=4039114752 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=primdbXDB)' primdb1.fal_client='primdb1' primdb2.fal_client='primdb2' *.fal_server='stbydb' primdb2.instance_number=2 primdb1.instance_number=1 *.log_archive_config='dg_config=(primdb,stbydb)' *.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primdb' *.log_archive_dest_2='SERVICE=stbydb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stbydb' *.log_archive_dest_3='LOCATION=+DATA/primdb/STANDBYLOG VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=primdb' *.log_archive_dest_state_1='enable' *.log_archive_dest_state_2='defer' *.log_archive_dest_state_3='enable' *.log_archive_format='%t_%s_%r.dbf' *.log_archive_max_processes=7 *.log_archive_min_succeed_dest=2 *.memory_target=1449132032 *.open_cursors=300 *.processes=150 *.remote_listener='dc-scan:1521' *.remote_login_passwordfile='exclusive' *.sec_case_sensitive_logon=FALSE *.standby_file_management='auto' primdb1.thread=1 primdb2.thread=2 primdb2.undo_tablespace='UNDOTBS1' primdb1.undo_tablespace='UNDOTBS2'

*************************************************************************************
after editing the initstbydb.ora file
*************************************************************************************

db_cache_size=520093696 java_pool_size=16777216 large_pool_size=16777216 pga_aggregate_target=587202560 sga_target=872415232 shared_pool_size=301989888 streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/stbydb/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='+DATA/stbydb/controlfile/control01.ctl' *.db_block_size=8192 *.db_create_file_dest='+DATA' *.db_domain='' *.db_file_name_convert='+DATA/primdb/','+DATA/stbydb/' *.db_name='primdb' db_unique_name='stbydb' *.db_recovery_file_dest='+DATA' *.db_recovery_file_dest_size=4039114752 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=stbydbXDB)' fal_client='stbydb' *.fal_server='primdb' *.log_archive_config='dg_config=(primdb,stbydb)' *.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stbydb' *.log_archive_dest_2='SERVICE=primdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primdb' *.log_archive_dest_3='LOCATION=+DATA/stbydb/STANDBYLOG VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=stbydb' *.log_archive_dest_state_1='enable' *.log_archive_dest_state_2='defer' *.log_archive_dest_state_3='enable' *.log_archive_format='%t_%s_%r.dbf' *.log_archive_max_processes=7 *.log_archive_min_succeed_dest=2 *.open_cursors=300 *.processes=150 *.remote_listener='dc-scan:1521' *.remote_login_passwordfile='exclusive' *.standby_file_management='auto' undo_tablespace='UNDOTBS1' SEC_CASE_SENSITIVE_LOGON=FALSE
================================================================================

create required folder for stbydb database and also add an entry in the /etc/oratab for stbydb database ,
create a password file for stbydb database.(must have similar password like primary RAC database )
add required tns entries in the tnsnames.ora and listener entry in the listener.ora file
take a RMAN backup of target database and target database controlfile from any one of the rac node
and scp the backup to the standby database host machine to create standby database.

*************************************************************************************

[oracle@dc1 backup]$ pwd
/u01/app/oracle/backup
[oracle@dc1 backup]$ . oraenv
ORACLE_SID = [oracle] ? primdb1
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@dc1 backup]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Jan 22 04:37:01 2012

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

connected to target database: PRIMDB (DBID=447762734)

RMAN> configure channel device type disk format '/u01/app/oracle/backup/%U';

using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/app/oracle/backup/%U';
new RMAN configuration parameters are successfully stored

RMAN> backup as compressed backupset database plus archivelog;

RMAN> backup current controlfile for standby;

RMAN> backup archivelog all;


creating directories
=====================================================

[oracle@dc2 dbs]$ mkdir -p /u01/app/oracle/admin/primdb/adump
[oracle@dc2 dbs]$ mkdir -p /u01/app/oracle/backup

[oracle@dc2 dbs]$ . oraenv
ORACLE_SID = [+ASM2] ?
The Oracle base for ORACLE_HOME=/u01/app/11.2.0/grid is /u01/app/oracle
[oracle@dc2 dbs]$ asmcmd
ASMCMD> ls
DATA/
ASMCMD> cd data
ASMCMD> mkdir STBYDB
ASMCMD> cd STBYDB
ASMCMD> mkdir ARCHIVELOG CONTROLFILE DATAFILE ONLINELOG PARAMETERFILE STANDBYLOG TEMPFILE
ASMCMD> ls
ARCHIVELOG/
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
STANDBYLOG/
TEMPFILE/
ASMCMD>

create password file for standby database.
[oracle@dc2 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwstbydb password=oracle entries=5 ignorecase=y
[oracle@dc2 dbs]$

copy the backup to standby server.
[oracle@dc1 backup]$ ls -ltr
total 290584
-rw-r----- 1 oracle oinstall 4157952 Jan 22 04:38 01n1cg5n_1_1
-rw-r----- 1 oracle oinstall 259842048 Jan 22 04:40 02n1cg5t_1_1
-rw-r----- 1 oracle oinstall 1114112 Jan 22 04:40 03n1cg9g_1_1
-rw-r----- 1 oracle oinstall 157184 Jan 22 04:40 04n1cg9u_1_1
-rw-r----- 1 oracle oinstall 18546688 Jan 22 04:46 05n1cglv_1_1
-rw-r----- 1 oracle oinstall 13416448 Jan 22 04:47 06n1cgn8_1_1
[oracle@dc1 backup]$ scp * oracle@dc2:/u01/app/oracle/backup/
01n1cg5n_1_1 100% 4061KB 4.0MB/s 00:00
02n1cg5t_1_1 100% 248MB 4.3MB/s 00:58
03n1cg9g_1_1 100% 1088KB 1.1MB/s 00:00
04n1cg9u_1_1 100% 154KB 153.5KB/s 00:00
05n1cglv_1_1 100% 18MB 2.0MB/s 00:09
06n1cgn8_1_1 100% 13MB 2.6MB/s 00:05
[oracle@dc1 backup]$ pwd
/u01/app/oracle/backup
[oracle@dc1 backup]$


add an entry in the /etc/oratab file
vi /etc/oratab
stbydb:/u01/app/oracle/product/11.2.0/db_1:N


add tns entry in all nodes

example:-

[oracle@dc1 backup]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin/
[oracle@dc1 admin]$ vi tnsnames.ora
PRIMDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dc-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = primdb)
)
)

STBYDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dc-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stbydb)
)
)


[oracle@dc1 admin]$ tnsping primdb

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 22-JAN-2012 04:55:33

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dc-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primdb)))
OK (40 msec)
[oracle@dc1 admin]$ tnsping stbydb

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 22-JAN-2012 04:55:37

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dc-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = stbydb)))
OK (0 msec)
[oracle@dc1 admin]$


[oracle@dc2 admin]$ tnsping primdb

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 22-JAN-2012 04:56:52

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dc-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primdb)))
OK (0 msec)
[oracle@dc2 admin]$ tnsping stbydb

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 22-JAN-2012 04:56:56

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dc-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = stbydb)))
OK (10 msec)
[oracle@dc2 admin]$

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

start the standby database in nomount mode

[oracle@dc2 dbs]$ . oraenv
ORACLE_SID = [+ASM2] ? stbydb
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@dc2 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 22 05:00:18 2012

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

Connected to an idle instance.

SQL> startup nomount

[oracle@dc2 dbs]$ . oraenv
ORACLE_SID = [stbydb] ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@dc2 dbs]$ rman target sys/oracle@PRIMDB auxiliary /

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Jan 22 05:33:25 2012

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

connected to target database: PRIMDB (DBID=447762734)
connected to auxiliary database: PRIMDB (not mounted)

RMAN> duplicate target database for standby nofilenamecheck;

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

SQL>

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.
*************************************************************************************

alert log file inforamtion

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Using STANDBY_ARCHIVE_DEST parameter default value as +DATA/stbydb/standbylog
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=MEMORY;
Sun Jan 22 05:52:53 2012
alter database recover managed standby database disconnect from session
Attempt to start background Managed Standby Recovery process (stbydb)
Sun Jan 22 05:52:53 2012
MRP0 started with pid=29, OS id=5844
MRP0: Background Managed Standby Recovery process started (stbydb)
Serial Media Recovery started
Managed Standby Recovery not using Real Time Apply
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Waiting for thread 1 sequence 29
Completed: alter database recover managed standby database disconnect from session

Sun Jan 22 05:54:16 2012
alter database recover managed standby database cancel
Sun Jan 22 05:54:17 2012
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u01/app/oracle/diag/rdbms/stbydb/stbydb/trace/stbydb_mrp0_5844.trc:
ORA-16037: user requested cancel of managed recovery operation
Recovery interrupted!
Waiting for MRP0 pid 5844 to terminate
Errors in file /u01/app/oracle/diag/rdbms/stbydb/stbydb/trace/stbydb_mrp0_5844.trc:
ORA-16037: user requested cancel of managed recovery operation
MRP0: Background Media Recovery process shutdown (stbydb)
Managed Standby Recovery Canceled (stbydb)
Completed: alter database recover managed standby database cancel


ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
Attempt to start background Managed Standby Recovery process (stbydb)
Sun Jan 22 05:56:07 2012
MRP0 started with pid=29, OS id=6135
MRP0: Background Managed Standby Recovery process started (stbydb)
Serial Media Recovery started
Managed Standby Recovery starting Real Time Apply
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Waiting for thread 1 sequence 29
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION



Completed: ALTER DATABASE MOUNT
Errors in file /u01/app/oracle/diag/rdbms/stbydb/stbydb/trace/stbydb_arc1_6584.trc:
ORA-01017: invalid username/password; logon denied
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
------------------------------------------------------------
FAL[client, ARC1]: Error 16191 connecting to primdb for fetching gap sequence
Errors in file /u01/app/oracle/diag/rdbms/stbydb/stbydb/trace/stbydb_arc1_6584.trc:
ORA-16191: Primary log shipping client not logged on standby
Errors in file /u01/app/oracle/diag/rdbms/stbydb/stbydb/trace/stbydb_arc1_6584.trc:
ORA-16191: Primary log shipping client not logged on standby
ARC6: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE

work around:-
======================================

create password file again on both rac node database and also on standby database

[oracle@dc1 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwprimdb1 password=oracle ignorecase=y force=y entries=5
[oracle@dc2 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwprimdb2 password=oracle ignorecase=y force=y entries=5
[oracle@dc2 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwstbydb password=oracle ignorecase=y force=y entries=5

add the parameter on all databases
SEC_CASE_SENSITIVE_LOGON=FALSE
SQL> show parameter SEC_CASE_SENSITIVE_LOGON

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean FALSE


SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 0
Next log sequence to archive 0
Current log sequence 0

SQL> shu immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from 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
[oracle@dc2 dbs]$ vi initstbydb.ora
[oracle@dc2 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 22 05:59:26 2012

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 870457344 bytes
Fixed Size 1339964 bytes
Variable Size 335547844 bytes
Database Buffers 528482304 bytes
Redo Buffers 5087232 bytes
Database mounted.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 0
Next log sequence to archive 0
Current log sequence 0
SQL> show parameter SEC_CASE_SENSITIVE_LOGON=FALSE
SQL> show parameter SEC_CASE_SENSITIVE_LOGON

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean FALSE
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 0
Next log sequence to archive 0
Current log sequence 0
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 0
Next log sequence to archive 0
Current log sequence 0
SQL> shu immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 870457344 bytes
Fixed Size 1339964 bytes
Variable Size 335547844 bytes
Database Buffers 528482304 bytes
Redo Buffers 5087232 bytes
Database mounted.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 37
Next log sequence to archive 0
Current log sequence 38
SQL>


verification
=============================================

(on primary database)

SQL> create tablespace new datafile size 2m;



SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
UNDOTBS2
NEW

7 rows selected.


(on standby database)

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 39
Next log sequence to archive 0
Current log sequence 40
SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
UNDOTBS2

6 rows selected.

SQL> alter database recover managed standby database disconnect from session;

Database altered.


(on both RAC primary instances)

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.


SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 39
Next log sequence to archive 0
Current log sequence 40
SQL> alter database recover managed standby database cancel;

Database altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 39
Next log sequence to archive 0
Current log sequence 40
SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
UNDOTBS2
NEW

7 rows selected.

SQL> select name,db_unique_name,database_role from v$database;

NAME DB_UNIQUE_NAME DATABASE_ROLE
--------- ------------------------------ ----------------
PRIMDB stbydb PHYSICAL STANDBY

Thanks and Regards,
Rajesh Kumar Govindarajan.
references:-
http://www.oracledba.org/10g/dr/10gR2_dataguard_RAC_to_RAC.html
http://jhdba.wordpress.com/2008/04/11/how-not-to-set-up-remote-log-shipping-dataguard-and-streams/
http://www.dba-oracle.com/t_ora_16191_primary_log_shipping_client_not_logged_on_standby_tips.htm

Steps to Rename RAC instance name

Steps to Rename RAC instance name

In this demo, The RAC database name:- RACDB
I have renamed the database instance racdb1 to rac1db (HOSTNAME DC1)
and instance racdb2 to rac2db (HOSTNAME DC2)
1. take a backup of the initracdb1.ora and initracdb2.ora files.
[oracle@dc1 dbs]$ cp initracdb1.ora initracdb1.ora_bkp
[oracle@dc1 dbs]$ cat initracdb1.ora
SPFILE='+DATA/proddb/spfileracdb.ora'
2. create a new pfile from spfile
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/proddb/spfileracdb.ora
SQL> create pfile='$ORACLE_HOME/dbs/initrac2db.ora' from spfile;

File created.
3. Shutdown the instance on all nodes
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 
4. Rename or Edit the racdb1 and racdb2 to rac1db and rac2db respectively.

before changing the instance names in init.ora file
racdb2.__db_cache_size=708837376
racdb1.__db_cache_size=708837376
racdb2.__java_pool_size=4194304
racdb1.__java_pool_size=4194304
racdb2.__large_pool_size=4194304
racdb1.__large_pool_size=4194304
racdb1.__oracle_base='/u01/app/oracle'
racdb2.__oracle_base='/u01/app/oracle'
racdb2.__pga_aggregate_target=394264576
racdb1.__pga_aggregate_target=394264576
racdb2.__sga_target=1056964608
racdb1.__sga_target=1056964608
racdb2.__shared_io_pool_size=0
racdb1.__shared_io_pool_size=0
racdb2.__shared_pool_size=331350016
racdb1.__shared_pool_size=331350016
racdb2.__streams_pool_size=0
racdb1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/proddb/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DATA/proddb/controlfile/control01.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='racdb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racdbXDB)'
racdb2.instance_number=2
racdb1.instance_number=1
*.open_cursors=300
*.pga_aggregate_target=394264576
*.processes=150
*.remote_listener='dc-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sga_target=1053818880
racdb2.thread=2
racdb1.thread=1
racdb2.undo_tablespace='UNDOTBS2'
racdb1.undo_tablespace='UNDOTBS1'
after editing
rac2db.__db_cache_size=708837376
rac1db.__db_cache_size=708837376
rac2db.__java_pool_size=4194304
rac1db.__java_pool_size=4194304
rac2db.__large_pool_size=4194304
rac1db.__large_pool_size=4194304
rac1db.__oracle_base='/u01/app/oracle'
rac2db.__oracle_base='/u01/app/oracle'
rac2db.__pga_aggregate_target=394264576
rac1db.__pga_aggregate_target=394264576
rac2db.__sga_target=1056964608
rac1db.__sga_target=1056964608
rac2db.__shared_io_pool_size=0
rac1db.__shared_io_pool_size=0
rac2db.__shared_pool_size=331350016
rac1db.__shared_pool_size=331350016
rac2db.__streams_pool_size=0
rac1db.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/proddb/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DATA/proddb/controlfile/control01.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='racdb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racdbXDB)'
rac2db.instance_number=2
rac1db.instance_number=1
*.open_cursors=300
*.pga_aggregate_target=394264576
*.processes=150
*.remote_listener='dc-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sga_target=1053818880
rac2db.thread=2
rac1db.thread=1
rac2db.undo_tablespace='UNDOTBS2'
rac1db.undo_tablespace='UNDOTBS1'
5.create a new password file on all the nodes on new instance SID names.
[oracle@dc1 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwrac1db password=oracle
[oracle@dc1 dbs]$ 
[oracle@dc2 ~]$ orapwd file=$ORACLE_HOME/dbs/orapwrac2db password=oracle

6.Add an entry in the /etc/oratab file on all the nodes
example:-
on host machine DC1
vi /etc/oratab
rac1db:/u01/app/oracle/product/11.2.0/db_1:N
on host machine DC2
vi /etc/oratab
rac2db:/u01/app/oracle/product/11.2.0/db_1:N

7.Startup the database with new init.ora file and create a spfile using new pfile.
[oracle@dc1 dbs]$ . oraenv
ORACLE_SID = [rac2db] ? rac1db
[oracle@dc1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 21 06:05:54 2012

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1054593024 bytes
Fixed Size                  1341196 bytes
Variable Size             339740916 bytes
Database Buffers          708837376 bytes
Redo Buffers                4673536 bytes
SQL> create SPFILE='+DATA/proddb/spfileracdb.ora' from pfile;

File created.

SQL> shu immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> 
8.Edit/add the spfile location in the initrac1db.ora and initrac2db.ora file
example:-
[oracle@dc2 dbs]$ vi initrac2db.ora
SPFILE='+DATA/proddb/spfileracdb.ora'

[oracle@dc1 dbs]$ vi initrac2db.ora
SPFILE='+DATA/proddb/spfileracdb.ora'

9.remove the old instance name and add a new instance name using srvctl command.
[oracle@dc2 dbs]$ srvctl remove instance -i racdb1 -d racdb 
Remove instance from the database racdb? (y/[n]) y
[oracle@dc2 dbs]$ srvctl remove instance -i racdb2 -d racdb
Remove instance from the database racdb? (y/[n]) y

[oracle@dc2 dbs]$ srvctl add instance -i rac1db -d racdb -n dc1
[oracle@dc2 dbs]$ srvctl add instance -i rac2db -d racdb -n dc2
[oracle@dc2 dbs]$ srvctl start database -d racdb
10.Verify it.
SQL> select instance_name,status from gv$instance;

INSTANCE_NAME    STATUS
---------------- ------------
rac2db           OPEN
rac1db           OPEN

SQL> select name from v$database;

NAME
---------
RACDB

[oracle@dc2 dbs]$ srvctl config database -d racdb
Database unique name: racdb
Database name: 
Oracle home: /u01/app/oracle/product/11.2.0/db_1
Oracle user: oracle
Spfile: 
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: racdb
Database instances: rac1db,rac2db
Disk Groups: 
Services: 
Database is administrator managed

Thanks and Regards,
Rajesh Kumar Govindarajan.

Tuesday, January 10, 2012

Rename RAC Database

Here is the document to rename RAC database and next post we can see how to rename rac instance.

here is an example.

renamed RAC database proddb to racdb in servers dc1 and dc2.
SQL> select instance_name,host_name from gv$instance;

INSTANCE_NAME    HOST_NAME
---------------- -------------------------
proddb1          dc1.localdomain
proddb2          dc2.localdomain

SQL> select name from v$database;

NAME
---------
PRODDB

taken backup of pfile initproddb1.ora and initproddb2.ora on both nodes.
[oracle@dc1 dbs]$ cat initproddb1.ora
SPFILE='+DATA/proddb/spfileproddb.ora'
[oracle@dc1 dbs]$ cp initproddb1.ora initproddb1.ora_orig 
[oracle@dc1 dbs]$ 

[oracle@dc2 ~]$ cd $ORACLE_HOME/dbs
[oracle@dc2 dbs]$ cat initproddb2.ora 
SPFILE='+DATA/proddb/spfileproddb.ora'
[oracle@dc2 dbs]$ cp initproddb2.ora initproddb2.ora_bkp
[oracle@dc2 dbs]$ 
create pfile from spfile.
taken controlfile backup to rename rac database.
SQL> create pfile='/tmp/initproddb1.ora' from spfile;

File created.

SQL> alter database backup controlfile to trace;

Database altered.

SQL> --- or ----
SQL> 
SQL> alter database backup controlfile to trace as '/tmp/ctl_trc_bkp.txt';

Database altered.

stop the RAC database using srvctl command.
[oracle@dc1 ~]$ srvctl status database -d proddb
Instance proddb1 is running on node dc1
Instance proddb2 is running on node dc2
[oracle@dc1 ~]$ srvctl config database -d proddb
Database unique name: proddb
Database name: proddb
Oracle home: /u01/app/oracle/product/11.2.0/db_1
Oracle user: oracle
Spfile: +DATA/proddb/spfileproddb.ora
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: proddb
Database instances: proddb1,proddb2
Disk Groups: DATA
Services: 
Database is administrator managed
[oracle@dc1 ~]$ 

[oracle@dc1 ~]$ srvctl stop database -d proddb
[oracle@dc1 ~]$ srvctl status database -d proddb
Instance proddb1 is not running on node dc1
Instance proddb2 is not running on node dc2
[oracle@dc1 ~]$ 
edit/replace proddb to racdb in the newly created pfile
and also the controlfile name.
change the value of the parameter cluster_database to false.
racdb2.__db_cache_size=708837376
racdb1.__db_cache_size=708837376
racdb2.__java_pool_size=4194304
racdb1.__java_pool_size=4194304
racdb2.__large_pool_size=4194304
racdb1.__large_pool_size=4194304
racdb2.__pga_aggregate_target=394264576
racdb1.__pga_aggregate_target=394264576
racdb2.__sga_target=1056964608
racdb1.__sga_target=1056964608
racdb2.__shared_io_pool_size=0
racdb1.__shared_io_pool_size=0
racdb2.__shared_pool_size=331350016
racdb1.__shared_pool_size=331350016
racdb2.__streams_pool_size=0
racdb1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/proddb/adump'
*.audit_trail='db'
#*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DATA/proddb/controlfile/control01.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='racdb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racdbXDB)'
racdb2.instance_number=2
racdb1.instance_number=1
*.open_cursors=300
*.pga_aggregate_target=394264576
*.processes=150
*.remote_listener='dc-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sga_target=1053818880
racdb2.thread=2
racdb1.thread=1
racdb2.undo_tablespace='UNDOTBS2'
racdb1.undo_tablespace='UNDOTBS1'
startup the database in nomount mode with newly created pfile.
[oracle@dc1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 7 17:35:55 2012

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

Connected to an idle instance.

SQL> startup nomount pfile='/tmp/initproddb1.ora';
ORACLE instance started.

Total System Global Area 1054593024 bytes
Fixed Size                  1341196 bytes
Variable Size             276826356 bytes
Database Buffers          771751936 bytes
Redo Buffers                4673536 bytes
SQL> 

create a script using backup of controlfile trace to re-create controlfile(to rename database)  
CREATE CONTROLFILE SET DATABASE "RACDB" RESETLOGS  NOARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '+DATA/proddb/onlinelog/group_1.346.771671611'  SIZE 50M BLOCKSIZE 512,
GROUP 2 '+DATA/proddb/onlinelog/group_2.341.771671615'  SIZE 50M BLOCKSIZE 512
DATAFILE
'+DATA/proddb/datafile/system.256.771671395',
'+DATA/proddb/datafile/sysaux.282.771671401',
'+DATA/proddb/datafile/undotbs1.344.771671403',
'+DATA/proddb/datafile/users.343.771671403',
'+DATA/proddb/datafile/undotbs2.339.771671757'
CHARACTER SET WE8MSWIN1252
;

SQL> @'/tmp/create_ctl.sql';

Control file created.

SQL> sho parameter cluster_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     FALSE
cluster_database_instances           integer     1
cluster_interconnects                string
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
change the value of the parameter cluster_database to true and startup the database.
SQL> startup pfile='/tmp/initproddb1.ora';
ORACLE instance started.

Total System Global Area 1054593024 bytes
Fixed Size                  1341196 bytes
Variable Size             339740916 bytes
Database Buffers          708837376 bytes
Redo Buffers                4673536 bytes
Database mounted.
Database opened.

create new spfile using current pfile.
SQL> create SPFILE='+DATA/proddb/spfileracdb.ora' from pfile='/tmp/initproddb1.ora';

File created.

SQL> sho parameter cluster

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2
cluster_interconnects                string
SQL> 
SQL>ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 3,GROUP 4;

SQL> ALTER DATABASE OPEN RESETLOGS;

create a new init pfile in the dbs directory and copy and paste the spfile location.
[oracle@dc1 dbs]$ vi initracdb1.ora 
SPFILE='+DATA/proddb/spfileracdb.ora'
[oracle@dc1 dbs]$ scp initracdb1.ora dc2:$ORACLE_HOME/dbs/initracdb2.ora
initracdb1.ora                                100%   38     0.0KB/s   00:00    

create a new password file on both servers for database racdb.

[oracle@dc1 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwracdb1 password=oracle
[oracle@dc1 dbs]$ 

[oracle@dc2 ~]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@dc2 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwracdb2.ora password=oracle
[oracle@dc2 dbs]$ 

add an entry in the /etc/oratab file on both servers
[oracle@dc2 ~]$vi /etc/oratab
racdb2:/u01/app/oracle/product/11.2.0/db_1:N

[oracle@dc1 ~]$vi /etc/oratab
racdb1:/u01/app/oracle/product/11.2.0/db_1:N
[oracle@dc2 ~]$ . oraenv
ORACLE_SID = [oracle] ? racdb2
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@dc2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 8 23:36:03 2012

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1054593024 bytes
Fixed Size                  1341196 bytes
Variable Size             339740916 bytes
Database Buffers          708837376 bytes
Redo Buffers                4673536 bytes
Database mounted.
Database opened.
SQL> 

SQL> select name from v$database;

NAME
---------
RACDB

SQL> select instance_name,status from gv$Instance;

INSTANCE_NAME    STATUS
---------------- ------------
racdb1           OPEN
racdb2           OPEN

register the database with grid.

[oracle@dc1 dbs]$ srvctl status database -d racdb
PRCD-1120 : The resource for database racdb could not be found.
PRCR-1001 : Resource ora.racdb.db does not exist

[oracle@dc1 dbs]$ srvctl remove database -d proddb
Remove the database proddb? (y/[n]) y
[oracle@dc1 dbs]$ srvctl add database -d racdb -o $ORACLE_HOME
[oracle@dc1 dbs]$ srvctl add instance -d racdb -i racdb1 -n dc1
[oracle@dc1 dbs]$ srvctl add instance -d racdb -i racdb2 -n dc2

[oracle@dc1 dbs]$ srvctl status database -d racdb
Instance racdb1 is not running on node dc1
Instance racdb2 is not running on node dc2

[oracle@dc1 dbs]$ srvctl start database -d racdb
[oracle@dc1 dbs]$ srvctl status database -d racdb
Instance racdb1 is running on node dc1
Instance racdb2 is running on node dc2

finally, Change the instance names in the tnsnames.ora and listener.ora files.

regards,
Rajesh Kumar Govindarajan.
 
Share/Bookmark