Saturday, March 31, 2012

Creating single instance standby from 2 node RAC in AIX 6.1

Step1: Create the requires pfiles for both primary & standby

Sample primary Init file
===============================
T24TRAIN1.__db_cache_size=1342177280
T24TRAIN2.__db_cache_size=1358954496
T24TRAIN1.__java_pool_size=16777216
T24TRAIN2.__java_pool_size=16777216
T24TRAIN1.__large_pool_size=16777216
T24TRAIN2.__large_pool_size=16777216
T24TRAIN1.__pga_aggregate_target=1677721600
T24TRAIN2.__pga_aggregate_target=1677721600
T24TRAIN1.__sga_target=2516582400
T24TRAIN2.__sga_target=2516582400
T24TRAIN1.__shared_io_pool_size=0
T24TRAIN2.__shared_io_pool_size=0
T24TRAIN1.__shared_pool_size=1107296256
T24TRAIN2.__shared_pool_size=1090519040
T24TRAIN1.__streams_pool_size=0
T24TRAIN2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/T24TRAIN/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DATA/t24train/controlfile/current.273.778262941','+DATA/t24train/controlfile/current.272.778262941'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_recovery_file_dest_size=319815680000
*.db_recovery_file_dest='+ARCHIVE'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=T24TRAINXDB)'
T24TRAIN1.instance_number=1
T24TRAIN2.instance_number=2
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=4194304000
*.open_cursors=300
*.processes=5000
*.remote_listener='t24db_scan.cbe.local:1521'
*.remote_login_passwordfile='exclusive'
*.sessions=5505
T24TRAIN2.thread=2
T24TRAIN1.thread=1
T24TRAIN2.undo_tablespace='UNDOTBS2'
T24TRAIN1.undo_tablespace='UNDOTBS1'
#parameters spcific to Standby database
*.db_name='T24TRAIN'
*.DB_UNIQUE_NAME='T24TRAIN'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(T24TRAIN,T24TRAINSBY)'
*.LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=T24TRAIN'
*.LOG_ARCHIVE_DEST_2='SERVICE=T24TRAINSBY ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=T24TRAINSBY'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
*.LOG_ARCHIVE_MAX_PROCESSES=8
*.FAL_SERVER=T24TRAINSBY
*.DB_FILE_NAME_CONVERT='+DATA/T24TRAINSBY/,'+DATA/T24TRAIN/'
*.LOG_FILE_NAME_CONVERT='+DATA/T24TRAINSBY/','+DATA/T24TRAIN/'
*.STANDBY_FILE_MANAGEMENT=AUTO

Sample standby INIT file
===================
audit_file_dest='/u01/app/oracle/admin/T24TRAINSBY/adump'
audit_trail='db'
compatible='11.2.0.0.0'
control_files='+DATA/T24TRAINSBY/CONTROLFILE/control01.ctl','+DATA/T24TRAINSBY/CONTROLFILE/control02.ctl'
db_block_size=8192
db_create_file_dest='+DATA'
db_domain=''
db_recovery_file_dest_size=319815680000
db_recovery_file_dest='+ARCHIVE'
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=T24TRAINXDB)'
log_archive_format='%t_%s_%r.dbf'
memory_target=4194304000
open_cursors=300
processes=5000
remote_login_passwordfile='exclusive'
sessions=5505
#parameters spcific to Standby database
db_name='T24TRAIN'
DB_UNIQUE_NAME='T24TRAINSBY'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(T24TRAIN,T24TRAINSBY)'
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=T24TRAINSBY'
LOG_ARCHIVE_DEST_2='SERVICE=T24TRAIN ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=T24TRAIN'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=8
FAL_SERVER=T24TRAIN
DB_FILE_NAME_CONVERT='+DATA/T24TRAIN','+DATA/T24TRAINSBY/'
LOG_FILE_NAME_CONVERT='+DATA/T24TRAIN','+DATA/T24TRAINSBY/'
STANDBY_FILE_MANAGEMENT=AUTO

Step2: Start primary with the modified pfile if it is ASM stored pfile

SQL> shutdown immediate
SQL>exit
SQL>export ORACLE_SID=<primary_db_instance_name>
SQL>sqlplus / as sysdba
SQL>startup nomount pfile='/tmp/<modified_pfile>.ora'
SQL>create spfile='<ASM_PATH>' from pfile='/tmp/<modified_pfile>.ora'
SQL> exit

<convert pfile to spfile on ASM storage>
go to $ORACLE_HOME/dbs/  find the corresponding init.ora file for primary DB, crosscheck the contents with correct ASM path for spfile or modify it.

Step3:
Create necessary standby redo log files. e.g. in our env we have 2 threads each with 2 groups , so we added 2 standby redo log threads each with 3 groups

First create necessary ASM directories to hold the standby logs
e.g. +DATA/T24TRAIN/STANDBYLOG

then make standby file management manual on both nodes
alter system set standby_file_management=manual scope=both sid='*';

alter database add standby logfile thread 1 group 15 '+DATA/T24TRAIN/STANDBYLOG/standby_group_15.log' size 500M;
alter database add standby logfile thread 1 group 16 '+DATA/T24TRAIN/STANDBYLOG/standby_group_16.log' size 500M;
alter database add standby logfile thread 1 group 17 '+DATA/T24TRAIN/STANDBYLOG/standby_group_17.log' size 500M;
alter database add standby logfile thread 2 group 18 '+DATA/T24TRAIN/STANDBYLOG/standby_group_18.log' size 500M;
alter database add standby logfile thread 2 group 19 '+DATA/T24TRAIN/STANDBYLOG/standby_group_19.log' size 500M;
alter database add standby logfile thread 2 group 20 '+DATA/T24TRAIN/STANDBYLOG/standby_group_20.log' size 500M;

again make standby file management auto
alter system set standby_file_management=auto scope=both sid='*';

Step4: make appropriate tnsnames.ora entries on both RAC primary nodes & standby server


Step5: Make sure fal_server parameter can be connected from either side , this is important as it is used for redolog transfer

e.g. in primary FAL_SERVER=t24trainsby
so from primary server
prompt> sqlplus sys/password@t24trainsby as sysdba <should be able to connect>
          
in standby FAL_SERVER=t24train
prompt> sqlplus sys/password@t24train as sysdba<should be able to connect>

important: for our particular case we had used SCAN for primary so make sure SCAN also should be able to resolve from standby site.

Step6: make backup of primary site

 create required direcory to hold backup in local file system


mkdir /u01/backup/
rman target /
run
{
     sql "alter system switch logfile";
     allocate channel ch1 type disk format '/u01/backup/Primary_bkp_for_stndby_%U';
     backup database;
     backup current controlfile for standby;
     sql "alter system archive log current";
}

scp the backup pieces to standby server
scp /u01/backup/Primary_bkp_for_stndby* mailto:oracle@%3Cstandby_server%3E:/u01/backup/

Step7: Also make password file common on both RAC nodes & standby server
e.g.
[login to primary & standby DBs & make sec_case_sensitive_logins=FALSE]
unix_prompt> cd $ORACLE_HOME/dbs
           > <delete or backup any existing passwd files>
           > orapw file=<> entries=5 ignorecase=y password=<> 

Step8:Start the standby DB in nomount state using the modified pfile & create spfile from it.

prompt> export ORACLE_SID=t24trainsby
prompt> sqlplus / as sysdba
 sql> startup nomount pfile='<PATH>'
 sql> create spfile='<ASM_PATH>' from pfile='<PATH>'
 sql> exit

Step 8: from stanby server 

$ rman target sys@T24TRAIN1 auxiliary /
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Mar 26 08:41:48 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
target database Password:
connected to target database: T24TRAIN (DBID=3199606683)
connected to auxiliary database: T24TRAIN (not mounted)
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK;
Starting Duplicate Db at 26-MAR-12
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=5293 device type=DISK
contents of Memory Script:
{
   restore clone standby controlfile;
}
executing Memory Script
Starting restore at 26-MAR-12
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/backup/Primary_bkp_for_stndby_03n6hill_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/backup/Primary_bkp_for_stndby_03n6hill_1_1 tag=TAG20120322T191253
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=+DATA/t24trainsby/controlfile/control01.ctl
output file name=+DATA/t24trainsby/controlfile/control02.ctl
Finished restore at 26-MAR-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";
   set newname for datafile  5 to
 "+data";
   set newname for datafile  6 to
 "+data";
   set newname for datafile  7 to
 "+data";
   restore
   clone database
   ;
}
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
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 26-MAR-12
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +data
channel ORA_AUX_DISK_1: restoring datafile 00002 to +data
channel ORA_AUX_DISK_1: restoring datafile 00003 to +data
channel ORA_AUX_DISK_1: restoring datafile 00004 to +data
channel ORA_AUX_DISK_1: restoring datafile 00005 to +data
channel ORA_AUX_DISK_1: restoring datafile 00006 to +data
channel ORA_AUX_DISK_1: restoring datafile 00007 to +data
channel ORA_AUX_DISK_1: reading from backup piece /u01/backup/Primary_bkp_for_stndby_01n6hil2_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/backup/Primary_bkp_for_stndby_01n6hil2_1_1 tag=TAG20120322T191233
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:46
Finished restore at 26-MAR-12
contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=778927491 file name=+DATA/t24trainsby/datafile/system.260.778927447
datafile 2 switched to datafile copy
input datafile copy RECID=9 STAMP=778927491 file name=+DATA/t24trainsby/datafile/sysaux.261.778927447
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=778927491 file name=+DATA/t24trainsby/datafile/undotbs1.262.778927447
datafile 4 switched to datafile copy
input datafile copy RECID=11 STAMP=778927491 file name=+DATA/t24trainsby/datafile/users.264.778927447
datafile 5 switched to datafile copy
input datafile copy RECID=12 STAMP=778927491 file name=+DATA/t24trainsby/datafile/undotbs2.263.778927447
datafile 6 switched to datafile copy
input datafile copy RECID=13 STAMP=778927491 file name=+DATA/t24trainsby/datafile/t24distdata.258.778927447
datafile 7 switched to datafile copy
input datafile copy RECID=14 STAMP=778927491 file name=+DATA/t24trainsby/datafile/t24distindex.259.778927447
Finished Duplicate Db at 26-MAR-12
RMAN> exit
 
Posted by at 2 comments:

Issues in Installation of 2 node RAC on AIX 6.1

The main issues faced are only discussed here

Issues1: While running root.sh script it fails with "Error: Connection Timeout"
reason:   due to incorrect ULIMIT setting for users in AIX.

Issue2:  Accidentally override the System disks for ASM.
reason: before using a RAW disk for ASM make sure it has correct reserve_policy & ownership changed to grid:oinstall & permission correctly given

Issue3: While Database creation ASM diskgroups doesn't show up.
reason: the file permission of $GRID_HOME/bin/oracle should be 6571 so as root user change permission of the executable
root> chmod 6571 $GRID_HOME/bin/oracle

Issue4: After database creation users not able to connect by tns entry.
reason: Change the permission of $ORACLE_HOME/bin/oracle to 6571 as root user
root> chmod 6571 $ORACLE_HOME/bin/oracle




Posted by at No comments:
Subscribe to: Posts (Atom)