11GR2: Creating Physical Standby on ASM usign Active Database Duplicate

You can follow official documents:
e10642.pdf: Backup and Recovery User's Guide  page:24-1 "Duplicating a Database"
e10700.pdf: Data Guard Concepts and Administration  page: F-4 "Using the DUPLICATE Command to Create a Standby Database"
e10643.pdf: "Backup and Recovery Reference "

Prepare Auxiliary database

#Create Password File for the Auxiliary Instance
$orapwd file=$ORACLE_HOME/dbs/orapwMYSID password=xxxx entries=5

#Create a static listener (single-instance)

LISTENER=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS = (PROTOCOL = TCP)(HOST = myserver)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = myserverip)(PORT = 1521)(IP = FIRST))
))
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /oracle_home_path)
(SID_NAME = MYSID)
))

#Create necessary folders and prepare paths for new database:
ORACLE_BASE
ORACLE_BASE\DBNAME
ORACLE_BASE\DBNAME\adump
ORACLE_BASE\DBNAME\dpdump
ORACLE_BASE\DBNAME\hdump
ORACLE_BASE\DBNAME\pfile
ORACLE_BASE\DBNAME\scripts

Normally documents say that you need to create a pfile including only db_name parameter at Auxiliary site. Then you can start instance with nomount mode and Auxiliary configuration is done. However, at later steps you may have troupble with rman dublicate command including SPFILE configuration parameters (like me):
 
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
SPFILE
....
SET "db_unique_name"="..."
SET DB_CREATE_FILE_DEST "+ASMDISK";

An other choice, you can manually create Auxiliary spfile and set all spfile parameters here instead of rman duplicate command. Later we will use only command to create standby....
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
Here is my previously created pfile at Auxiliary database:

MYDBNAME.__db_cache_size=XXXXXXXX
MYDBNAME.__java_pool_size=XXXXXXXX
MYDBNAME.__large_pool_size=XXXXXXXX
MYDBNAME.__oracle_base='/oracle_base_path'#ORACLE_BASE set from environment
MYDBNAME.__pga_aggregate_target=XXXXXXXX
MYDBNAME.__sga_target=XXXXXXXX
MYDBNAME.__shared_io_pool_size=0
MYDBNAME.__shared_pool_size=XXXXXXXX
MYDBNAME.__streams_pool_size=XXXXXXXX
*.audit_file_dest='/adump_path'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/db_ctrl_path/ctrl1.ctl','/db_ctrl_path/ctrl2.ctl'
*.db_block_size=XXXXXXXX
*.db_create_file_dest='+ASMDISK'
*.db_domain=''
*.db_file_multiblock_read_count=XXXXXXXX
*.db_name='MYDBNAME'
*.db_recovery_file_dest='+ASMRECODISK'
*.db_recovery_file_dest_size=XXXXXXXX
*.db_unique_name='MYDBNAME'
*.diagnostic_dest='/oracle_base_path'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=MYDBNAME2XDB)'
*.memory_max_target=XXXXXXXX
*.memory_target=XXXXXXXX
*.open_cursors=XXXXXXXX
*.processes=XXXXXXXX
*.remote_login_passwordfile='EXCLUSIVE'
*.result_cache_max_size=XXXXXXXX
*.service_names='MYDBNAME',...
*.sessions=XXXXXXXX
*.sort_area_size=XXXXXXXX
*.undo_retention=XXXXXXXX
*.undo_tablespace='MYUNDOTS'

Note: I dont suggest using "db_file_name_convert" or "log_file_name_convert" parameters. If you use them both with db_create_file_dest parameter, then datafiles and logfiles are created at paths "{db_create_file_dest}/{db_unique_name}/DATAFILE", "{db_create_file_dest}/{db_unique_name}/ONLINELOG" and aliases are created at paths "{db_file_name_convert}", "{log_file_name_convert}".
Note: Because db_unique_name is used as a template parameter for asm paths, chose it wisely. Then It will be difficult to move them and rename in controlfile. I suggest use DBNAME as unique name. However both primary and standby can not have same db_unique_name so decide what to set which. If you decide to change db_unique_name of the primary, it resuires restart...

When your pfile is ready, first startup instance with nomount option using this pfile and then create spfile. Then shutdown, startup again with nomount option, this time using spfile.

Prepare Source database and run RMAN DUPLICATE

Before beginning duplication, force database logging:

ALTER DATABASE FORCE LOGGING;

You need to configure sql net connections for both source and auxiliary databases. Here is the tnsnames.ora:

MYDBNAMEPROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = prodhost)(PORT = 1521))
(CONNECT_DATA = (SID = MYSID))
))

MYDBNAMESTBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = stbyhost)(PORT = 1521))
(CONNECT_DATA =(SERVER = DEDICATED)(SID = MYSID))
)

You should be able to connect both instance with sys as sysdba through sql.net:

sqlplus /nolog
SQL> connect SYS@MYDBNAMEPROD as sysdba
SQL> connect SYS@MYDBNAMESTBY as sysdba

If you could not connect, you should correct this before proceeding.

Now connect rman and start duplicate:

$rman
RMAN> CONNECT TARGET SYS@MYDBNAMEPROD
target database Password:
connected to target database: MYDBNAME (DBID=XXXXXX)

RMAN> CONNECT AUXILIARY SYS@MYDBNAMESTBY
auxiliary database Password:
connected to auxiliary database: MYDBNAME (not mounted)

RMAN> DUPLICATE TARGET DATABASE
2> FOR STANDBY
3> FROM ACTIVE DATABASE
4> ;
....

Note: If datafiles are created with aliases and you decide to remove aliases, dont use "rm" command of asmcmd. It will remove both alias and file itself. Instead, use "rmalias". I removed a file accidently by removing alias with rm command and could not find a way to get that single datafile from source directly into auxillary asm. If you have a filesystem drive at auxillary big enough to hold that datafile, then there seems a way...

Prepare Dataguard Configuration

Put standby control files into ASM:

First of all, prepare paths for controlfiles. If necessary, create asm directories.

SQL> shutdown immediate

ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount

ORACLE instance started.
Total System Global Area 2.6724E+10 bytes
Fixed Size 2219408 bytes
Variable Size 1.2885E+10 bytes
Database Buffers 1.3824E+10 bytes
Redo Buffers 12148736 bytes
SQL> exit

$rman target /

RMAN> restore controlfile to '+ASMDISK' from '/db_ctrl_path/ctrl1.ctl';
RMAN> restore controlfile to '+ASMRECODISK' from '/db_ctrl_path/ctrl2.ctl';

Check that new control files are created at paths:
+ASMDISK/MYDBNAME/CONTROLFILE
+ASMRECODISK/MYDBNAME/CONTROLFILE


Go to asm and find out newly created control files:
+ASMDISK/MYDBNAME/CONTROLFILE/current.XXX.XXXXXXXXX

+ASMRECODISK/MYDBNAME/CONTROLFILE/current.XXX.XXXXXXXXX

Then update control_files and mount database:

sqlplus '/ AS SYSDBA' 
SQL> alter system set control_files='+ASMDISK/MYDBNAME/CONTROLFILE/current.XXX.XXXXXXXXX',
'+ASMRECODISK/MYDBNAME/CONTROLFILE/current.XXX.XXXXXXXXX' scope=spfile;
SQL> startup mount


Follow this procedure to setup dataguard:
http://erkanorclog.blogspot.com/2008/03/setting-up-dataguard-configuration-for.html

Configure dynamic (local) listener:

You may want to register standby with local listener :

ALTER SYSTEM SET LOCAL_LISTENER='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mystbyhost)(PORT=1521))))' scope=both;

Check and see if local listener registration is ok. Change "MYDBNAMESTBY" tns at primary side to use this new listener configuration. If it is ok and you see dataguard is working fine then you can unset static listener configuration created previously.

Using Sql loader (sqlldr.exe)

Here is an example of using sql loader to load data from flat file to oracle table. In order to solve input-client time format conflicts, time format for timestamp columns specified manually in parameter file.

C:\>set NLS_LANG=TURKISH_TURKEY.TR8MSWIN1254
C:\>sqlldr myuser@mydb control=loader.ctl

loader.ctl:
load data

infile 'myinputfile'
into table MYSCHEMA.MYTABLE
fields terminated by "\t" OPTIONALLY ENCLOSED BY '"'
(COLUMN1, TIMESTAMP_COLUMN TIMESTAMP "DDMONYYYY:HH24:MI:SS", COLUMN2, COLUMN3...)

How to change DBNAME & SID without changing DBID

This procedure does not require resetlogs so previously taken backups will be valid after this operation. However database restart is needed at some points so maintenance with downtime should be scheduled for this operation.

You may follow “Changing Only the Database Name”  topic at official document:

http://download.oracle.com/docs/cd/B14117_01/server.101/b10825/dbnewid.htm#i1004734

$cd $ORACLE_HOME/dbs
#backup old initora file
$mv initMYOLDSID.ora initMYOLDSID.ora.org  

#create pfile from spfile
$sqlplus '/ AS SYSDBA'
SQL> create pfile from spfile;
SQL> exit

#replace all old dbname parameters with new dbname in pfile. Becareful, if you change some path variables, make sure that new path (including new dbname) also exists on system. If not, create manually.
$vi initMYOLDSID.ora
vi=> :%s/MYOLDDBNAME/MYNEWDBNAME/g

vi=> :wq

#now database goes offline...
$sqlplus '/ AS SYSDBA'
SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP MOUNT
SQL> exit

#change dbname using nid. Output is similar to fallowing...
$nid TARGET=/ DBNAME=MYNEWDBNAME SETNAME=YES
DBNEWID: Release 11.2.0.1.0 - Production on ....


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

Connected to database MYOLDDBNAME (DBID=XXXXXXXXX)

Connected to server version 11.2.0

Control Files in database:
/db_ctrl_path/ctrl1.ctl
/db_ctrl_path/ctrl2.ctl

Change database name of database MYOLDDBNAME to MYNEWDBNAME? (Y/[N]) => Y

Proceeding with operation
Changing database name from MYOLDDBNAME to MYNEWDBNAME
Control File /db_ctrl_path/ctrl1.ctl - modified
Control File /db_ctrl_path/ctrl2.ctl - modified
Datafile /db_data_path/file1.db - wrote new name
Datafile /db_data_path/file2.db - wrote new name
Datafile /db_data_path/file3.db - wrote new name
...
Control File /db_ctrl_path/ctrl1.ctl - wrote new name
Control File /db_ctrl_path/ctrl2.ctl - wrote new name
Instance shut down

Database name changed to MYNEWDBNAME.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.

#Change ORACLE_SID environment variable
$vi $HOME/.profile
vi=> change ORACLE_SID from MYOLDSID to MYNEWSID
$. $HOME/.profile

$env|grep SID
ORACLE_SID=MYNEWSID

#Create new password file and rename pfile..
$orapwd file=$ORACLE_HOME/dbs/orapwMYNEWSID password=xxxx entries=5

$mv initMYOLDSID.ora initMYNEWSID.ora

#Start database with new pfile and create spfile.
$sqlplus '/ AS SYSDBA'
SQL> startup pfile=oracle_home_path/dbs/initMYNEWSID.ora
ORACLE instance started.
…
SQL> select name from v$database;
NAME
---------
MYNEWDBNAME

SQL> create spfile from pfile;

#Note that database is opened using pfile so no spfile operation will be allowed. You should restart database again without pfile parameter to open it with spfile.

ODI: connection to repository failed

If connection string for the repository database has been changed, you may face following error while connecting to repository altough you have changed url parameter of client tool:

java.sql.SQLException: Io Exception: The Network Adapter could not establish the connection
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:274)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:328)
at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:361)
at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:151)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:595)
at com.sunopsis.sql.SnpsConnection.u(SnpsConnection.java)
at com.sunopsis.sql.SnpsConnection.c(SnpsConnection.java)
at com.sunopsis.sql.h.run(h.java)

This is because, altough tool connects to repository successfully, it retrieves repository connection string from repository database and reconnects using it (or tries to open a new connection with it and fails). As a result, you can not connect and correct repository connection parameter.

I solved this problem by manually logging into repository database and updating repository connection  parameter:

select TXT from snp_mtxt_part where i_txt=( select I_TXT_JAVA_URL from SNP_CONNECT where CON_NAME='MY_PROD_WORK_REP_NAME')

update snp_mtxt_part set TXT='{correct repository connection string}' where i_txt=....;

Creating New 11gr2 database on existing 11gr2 RAC

It is assumed that 11gr2 cluster has been installed. ASM is configured and disks are prepared for new database.

1-) Create new OS user for new database. New user should be in the same group with grid's OS user (Using different os users for databases is optional, not a must)

2-) Set ssh trust between the nodes for the newly created os user:

From each node, logged in as new user:


mkdir ~/.ssh
chmod 755 ~/.ssh
/usr/bin/ssh-keygen -t rsa
/usr/bin/ssh-keygen -t dsa

Example:
$ mkdir ~/.ssh
$ chmod 755 ~/.ssh
$ /usr/bin/ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (.../.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:

Your identification has been saved in .../.ssh/id_rsa.
Your public key has been saved in .../.ssh/id_rsa.pub.
The key fingerprint is:
XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX os_user@my.domain.com

$ /usr/bin/ssh-keygen -t dsa
Generating public/private dsa key pair.
Enter file in which to save the key (.../.ssh/id_dsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:

Your identification has been saved in .../.ssh/id_dsa.
Your public key has been saved in .../.ssh/id_dsa.pub.
 The key fingerprint is:
XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX os_user@my.domain.com

Append all public keys to authorized_keys of all nodes:

FIRST NODE

cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys
ssh os_user@node2 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
ssh os_user@node2 cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys
chmod 644 ~/.ssh/authorized_keys

SECOND NODE

cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys
ssh os_user@node1 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
ssh os_user@node1 cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys
chmod 644 ~/.ssh/authorized_keys

ssh command run at previous step will register each node's ssh identity to the known_hosts file at other node (confirmation appeared when you run ssh first). However, every node should also has a know_hosts entry for itself. In order to do that, run following command at each node and answer yes at confirmation.

ssh os_user@nodeitself ls

Finally, check that each node does a ssh ls to orher node without requiring any password or confirmation.

ssh os_user@othernode ls
ssh os_user@nodeitself ls


3-) edit user profile and set following variables:
 
ORACLE_HOME

RDBMS_HOME
ASM_HOME
CRS_HOME
ORACLE_BASE
PATH
ORACLE_SID
LD_LIBRARY_PATH
4-) Check permisions of ORACLE HOME and BASE so new user has write permission on these (on both nodes)
5-) Set new environments paramaters on both nodes.
6-) Start Installer and install software only... Create database? maybe next time...
 

System Wide Stats Gathering

It is recommended to run gather statistics reqularly, especially after upgrades or running catalog scripts:

exec DBMS_STATS.GATHER_DICTIONARY_STATS();

exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS();


Also it is recommended to gather hardware based statistics after database first created or a hardware maintenance has been done

exec DBMS_STATS.gather_system_stats('start');
exec DBMS_STATS.gather_system_stats('stop');

You may query aux_stats$ system view to see system stat results:


select * from aux_stats$

Oracle 11GR2 SCAN, ORA-12502: TNS:listener received no CONNECT_DATA from client

While local_listener parameter of RAC database was something like this:

(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=node-vip)(PORT=1521))))

connecting to database using SCAN listener gave following error (after a short period of hang time)

ORA-12502: TNS:listener received no CONNECT_DATA from client

After setting local_listener as:

(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=node-vip.mycompany.com)(PORT=1521))))

problem resolved...

Oracle 11gR2 RAC and crs_stat

In Oracle 11gR2 RAC cluster, crs_stat does not show database instance (node,online,offline) information any more. In order to get this information, you should use srvctl commands...

example: mydb2 is offline at mynode2

$crs_stat -t   (no information at output)
Name Type Target State Host
------------------------------------------------------------
ora.DATA.dg ora....up.type ONLINE ONLINE mynode1
ora.DATA10G.dg ora....up.type ONLINE ONLINE mynode1
ora....ER.lsnr ora....er.type ONLINE ONLINE mynode1
ora....N1.lsnr ora....er.type ONLINE ONLINE mynode2
ora....N2.lsnr ora....er.type ONLINE ONLINE mynode1
ora....N3.lsnr ora....er.type ONLINE ONLINE mynode1
ora....TING.dg ora....up.type ONLINE ONLINE mynode1
ora.RECO.dg ora....up.type ONLINE ONLINE mynode1
ora.asm ora.asm.type ONLINE ONLINE mynode1
ora.eons ora.eons.type ONLINE ONLINE mynode1
ora....SM1.asm application ONLINE ONLINE mynode1
ora....A1.lsnr application ONLINE ONLINE mynode1
ora....ca1.gsd application OFFLINE OFFLINE
ora....ca1.ons application ONLINE ONLINE mynode1
ora....ca1.vip ora....t1.type ONLINE ONLINE mynode1
ora....SM2.asm application ONLINE ONLINE mynode2
ora....A2.lsnr application ONLINE ONLINE mynode2
ora....ca2.gsd application OFFLINE OFFLINE
ora....ca2.ons application ONLINE ONLINE mynode2
ora....ca2.vip ora....t1.type ONLINE ONLINE mynode2
ora.gsd ora.gsd.type OFFLINE OFFLINE
ora....network ora....rk.type ONLINE ONLINE mynode1
ora.oc4j ora.oc4j.type OFFLINE OFFLINE
ora.ons ora.ons.type ONLINE ONLINE mynode1
ora.mydb.db ora....se.type ONLINE ONLINE mynode1
ora.scan1.vip ora....ip.type ONLINE ONLINE mynode2
ora.scan2.vip ora....ip.type ONLINE ONLINE mynode1
ora.scan3.vip ora....ip.type ONLINE ONLINE mynode1


$srvctl status database -d mydb
Instance mydb1 is running on node mynode1
Instance mydb2 is not running on node mynode2