Saturday, October 9, 2010

Golden Gate on ASM

Links to this post
I  also wrote latest post  Goldengate on ASM / RAC  , which is step by step configuration of GG on Grid Infrastructure.


Create the parameter file for the Primary Extract group

Its setup for 2 Node RAC , THREADS will be 2.
ggsci>>ADD EXTRACT E_X, TRANLOG, THREADS 2, BEGIN NOW
ggsci>>ADD EXTTRAIL /u01/ggate/TRAIL/ts, EXTRACT E_X

ggsci>>edit params ./GLOBALS
GGSCHEMA GGS
CHECKPOINTTABLE GGS.CKPT

ggsci>>DBLOGIN USERID ggs, PASSWORD test
ADD CHECKPOINTTABLE GGS.CKPT

sqlplus / as sysdba
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER SYSTEM SWITCH LOGFILE;

Configure tnsentry as +ASM alias and password file.

ggsci>>edit params E_X
SETENV (ORACLE_SID=abc)
USERID ggsuser, PASSWORD ggs_tmp1
EXTTRAIL /u01/ggate/TRAIL/ts
DISCARDFILE ./dirout/E_X.dsc, APPEND, MEGABYTES 10
TRANLOGOPTIONS ASMUSER SYS@+ASM, ASMPASSWORD system
--GETTRUNCATES
DYNAMICRESOLUTION
--TRANLOGOPTIONS FETCHCHAINEDUPDATES
ddl include mapped objname xxx.*;
TABLE xxx.*;
TABLEEXCLUDE xxx.USER_ACTIVITY_TRACE;

Create the parameter file for the Data pump group

ggsci>>ADD EXTRACT P_x,EXTTRAILSOURCE /u01/ggate/TRAIL/ts
ggsci>>ADD RMTTRAIL /u01/ggate/TRAIL/rs, EXTRACT P_x

ggsci>>edit params p_x
userid ggs, password test
rmthost 192.168.1.72, mgrport 7809
rmttrail /u01/ggate/TRAIL/rs
PASSTHRU
--GETTRUNCATES
DYNAMICRESOLUTION
ddl include mapped objname xxx.*;
TABLE xxx.*;

===================================================
ON TARGET SYSTEM
===================================================
Create the parameter file for the replicate group

ggsci>>EDIT PARAMS ./GLOBALS
GGSCHEMA GGS
CHECKPOINTTABLE GGS.CKPT

ggsci>>DBLOGIN USERID ggs, PASSWORD test
ADD CHECKPOINTTABLE GGS.CKPT

sqlplus / as sysdba

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER SYSTEM SWITCH LOGFILE;

ggsci>>ADD REPLICAT R_x,EXTTRAIL /u01/ggate/TRAIL/rs, CHECKPOINTTABLE GGS.CKPT

ggsci>>EDIT PARAMS r_x
REPLICAT R_x
ASSUMETARGETDEFS
userid ggs, password test
DDL
MAP xxx.*, TARGET xxx.*;


ON SOURCE
START EXTRACT E_X
START EXTRACT P_x

ggsci>>info all

make sure every process is running

ON TARGET SYSTEM

START REPLICAT R_x
STATUS REPLICAT R_x

make sure every process is running

---------------------
The best Drill down
---------------------

tail -f ggserr.log

Monday, September 20, 2010

Upgrade ASM and database from 10g to 11g

Links to this post

The upgrade will fall in three phase

1) Install 11g software.

2) Upgrade ASM

3) Upgrade database.


phase 1) Install 11g software.



























phase 2) upgrade ASM

Oracle cluster Synchronization Services (CSS) needs to be upgraded as follow:

Connect as root user and execute:

export ORACLE_HOME=/u01/app/oracle/product/11.1/db_1

Where: $ORACLE_HOME is your new 11g ASM Oracle Home.

$ORACLE_HOME/bin/localconfig reset





Startup the ASM instance using the old ASM Oracle Home e.g. 10.1.0.5.



From another graphical session, set the environment variables

(ORACLE_HOME, PATH, etc.) pointing to your new ASM Oracle Home 11g

Execute the DBUA from the new 11g ASM Oracle Home:

$ORACLE_HOME/bin/dbua

Where: $ORACLE_HOME is the new ASM Oracle Home





DBUA will show 2 options:

=)> upgrade a database

=)> upgrade ASM instance.

Select [upgrade ASM instance] option.

The DBUA will upgrade your ASM instance to the latest release automatically.

























Finally, please startup the databases that are using ASM as

storage option.

Note: The databases will not be affected with the ASM upgrade.

The databases will remain in the original 10g version only since they do not share the same Oracle Home.

START ASM INSTANCE FROM NEW 11G ORACLE_HOME note:it wil be already started


PHASE 3) UPGRADE DATABASE.


START DATABASE from old ORACLE_HOME 10.2.0.1

$ORACLE_HOME/rdbms/admin/utlu111i.sql

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Sep 4 18:32:16 2010

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

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> @/u01/app/oracle/product/11.1/db_1/rdbms/admin/utlu111i.sql

Oracle Database 11.1 Pre-Upgrade Information Tool 09-04-2010 18:35:20

.

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

Database:

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

--> name: PRODASM

--> version: 10.2.0.1.0

--> compatible: 10.2.0.1.0

--> blocksize: 8192

--> platform: Linux IA (32-bit)

--> timezone file: V2

.

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

Tablespaces: [make adjustments in the current environment]

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

--> SYSTEM tablespace is adequate for the upgrade.

.... minimum required size: 721 MB

.... AUTOEXTEND additional space required: 241 MB

--> UNDOTBS1 tablespace is adequate for the upgrade.

.... minimum required size: 472 MB

.... AUTOEXTEND additional space required: 447 MB

--> SYSAUX tablespace is adequate for the upgrade.

.... minimum required size: 410 MB

.... AUTOEXTEND additional space required: 180 MB

--> TEMP tablespace is adequate for the upgrade.

.... minimum required size: 61 MB

.... AUTOEXTEND additional space required: 41 MB

.

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

Update Parameters: [Update Oracle Database 11.1 init.ora or spfile]

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

WARNING: --> "sga_target" needs to be increased to at least 336 MB

.

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

Renamed Parameters: [Update Oracle Database 11.1 init.ora or spfile]

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

-- No renamed parameters found. No changes are required.

.

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

Obsolete/Deprecated Parameters: [Update Oracle Database 11.1 init.ora or spfile]

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

--> "background_dump_dest" replaced by "diagnostic_dest"

--> "user_dump_dest" replaced by "diagnostic_dest"

--> "core_dump_dest" replaced by "diagnostic_dest"

.

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

Components: [The following database components will be upgraded or installed]

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

--> Oracle Catalog Views [upgrade] VALID

--> Oracle Packages and Types [upgrade] VALID

--> JServer JAVA Virtual Machine [upgrade] VALID

--> Oracle XDK for Java [upgrade] VALID

--> Oracle Workspace Manager [upgrade] VALID

--> OLAP Analytic Workspace [upgrade] VALID

--> OLAP Catalog [upgrade] VALID

--> EM Repository [upgrade] VALID

--> Oracle Text [upgrade] VALID

--> Oracle XML Database [upgrade] VALID

--> Oracle Java Packages [upgrade] VALID

--> Oracle interMedia [upgrade] VALID

--> Spatial [upgrade] VALID

--> Data Mining [upgrade] VALID

--> Expression Filter [upgrade] VALID

--> Rule Manager [upgrade] VALID

--> Oracle OLAP API [upgrade] VALID

.

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

Miscellaneous Warnings

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

WARNING: --> Database is using an old timezone file version.

.... Patch the 10.2.0.1.0 database to timezone file version 4

.... BEFORE upgrading the database. Re-run utlu111i.sql after

.... patching the database to record the new timezone file version.

WARNING: --> Database contains stale optimizer statistics.

.... Refer to the 11g Upgrade Guide for instructions to update

.... statistics prior to upgrading the database.

.... Component Schemas with stale statistics:

.... SYS

WARNING: --> Database contains schemas with objects dependent on network

packages.

.... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.

.... USER SYSMAN has dependent objects.

WARNING: --> EM Database Control Repository exists in the database.

.... Direct downgrade of EM Database Control is not supported. Refer to the

.... 11g Upgrade Guide for instructions to save the EM data prior to upgrade.

.

PL/SQL procedure successfully completed.

NOTE: TO AVOID BELOW ERROR APPLY PATCH 5632264

WARNING: --> Database is using an old timezone file version.

.... Patch the 10.2.0.1.0 database to timezone file version 4

.... BEFORE upgrading the database. Re-run utlu111i.sql after

.... patching the database to record the new timezone file version.

if patch cant apply do as below

The manual process is:

a) download the correct 5632264 patch for your base version and platform.

b) unzip the 6672979 patch

c) make a backup of the 2 *.dat files in your $ORACLE_HOME/oracore/zoneinfo

directory and removed them (Databases may be running but if possible stop them to avoid any file locks)

d) copy the 2 *.dat files and the readme.txt from the unzipped patch 5632264/files/oracore/zoneinfo directory to your $ORACLE_HOME/oracore/zoneinfo directory (Databases may be running but if possible stop them to avoid any file locks).

e) stop all databases using this $ORACLE_HOME and after all databases are stopped start them again

startup database

SQL> select * from v$timezone_file;

FILENAME VERSION

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

timezlrg.dat 4


EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS');

PL/SQL procedure successfully completed.

create pfile from spfile;

copy pfile from old home to new 11g home

cp initPRODASM.ora /u01/app/oracle/product/11.1/db_1/dbs

cp orapwPRODASM /u01/app/oracle/product/11.1/db_1/dbs

copy init+ASM.ora if its not there on new 11g home/dbs

change init parameters in new file

export new 11g path

export ORACLE_SID=PRODASM

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.1/db_1

#export ORA_CRS_HOME=$ORACLE_BASE/product/10.2.0/crs_1

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export PATH=$ORACLE_HOME/bin:$ORA_CRS_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin

update /etc/oratab

#PRODASM:/u01/app/oracle/product/10.2.0/db_1:N

PRODASM:/u01/app/oracle/product/11.1/db_1:N

+ASM:/u01/app/oracle/product/11.1/db_1:N

export parameters

PRDASM-> cp initPRODASM.ora /u01/app/oracle/product/11.1/db_1/dbs

PRDASM-> cp orapwPRODASM /u01/app/oracle/product/11.1/db_1/dbs

PRDASM-> export ORACLE_HOME=$ORACLE_BASE/product/11.1/db_1

PRDASM-> export ORACLE_SID=PRODASM

PRDASM-> RS_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin <

PRDASM-> which sqlplus

/u01/app/oracle/product/11.1/db_1/bin/sqlplus

PRDASM-> sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Sep 4 19:49:23 2010

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

Connected to an idle instance.

SQL> startup upgrade

ORA-32004: obsolete and/or deprecated parameter(s) specified

ORACLE instance started.

Total System Global Area 188313600 bytes

Fixed Size 1298780 bytes

Variable Size 71306916 bytes

Database Buffers 113246208 bytes

Redo Buffers 2461696 bytes

Database mounted.

Database opened.

19) run catupgrd.sql

SQL> @/u01/app/oracle/product/11.1/db_1/rdbms/admin/catupgrd.sql

check if any component or database object is not a invalid

Monday, August 23, 2010

oracle search: ASM CLONING

Links to this post
oracle search: ASM CLONING: "Links to this post"

GOLDEN GATE LAG MONITORING

Links to this post
Hi

There are two script i have created
1) Linux
2) solaris and other platform.

First one i just wrote with modified logic and less processing using files.
Second script is specially for other Unix OS ,due to tac command does not work there.

Please inform me on jonyjt@gmail.com , if you find problem regarding it.


1 script

#!/bin/ksh
###############################
# monitor gg process
###############################
export ORACLE_HOME=/u00/app/oracle/product/10.2.0/DB
export GG_HOME=/ORAGG/product/11.2.1/gghome10
export PATH=$GG_HOME:$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$GG_HOME:$LD_LIBRARY_PATH
export LOGFILE=/tmp/testoutput.log
export DT=`date +%d/%m/%Y\ %k:%M:%S`
export BOX=`hostname -s`
export COLUMNS=130
#echo "############################################################"
#echo `date +%d/%m/%Y\ %k:%M:%S`
################################
echo $DT
rm -rf /tmp/testoutput.log
###############################
#check if process has abended.
${GG_HOME}/ggsci <<!| while read;do printf '%s\n' ;egrep -i '(EXTRACT|REPLICAT)'|tr ":" " " | tr -s '[:space:]' | awk '{if ($2=="ABENDED" || $5>=05 || $7 >=05 ) {print $1" "$3" is "$2" with lag of " $4"HR:"$5"MI and checkpoint of "$7"HR:"$8"MI" "\n">> "/tmp/testoutput.log"} else {print "No lag for"$3 > "/tmp/nolag.log"} }' ;  done
info all
exit
!
###############################
if [ -f ${LOGFILE} ]; then
        x=$( grep -c ABENDED /tmp/testoutput.log )
        y=$( grep -c RUNNING  /tmp/testoutput.log )
        if [ $x -gt 0 ]; then
                echo "please refer below logs" >> $LOGFILE
                echo "##################################################################" >> $LOGFILE
                tac ${GG_HOME}/ggserr.log | grep -m 1 -A 2 -B 2 ABEND >> $LOGFILE
                mailx -s "Alert GG Abended on $BOX" fistname.lastname@company.com < $LOGFILE
        fi
        if [ $y -gt 0 ]; then
                ##echo "process running "
                mailx -s "Lag Found on $BOX" fistname.lastname@company.com < $LOGFILE
        fi
else
        echo "NO LAG FOUND"
fi
###################################
2 script It was created on Solaris platform for ksh shell. ggs_lag.ksh is main script which calls ggs.ksh , you can also merge both into one script also.
#!/bin/ksh
############################################
# Name: ggs_lag.ksh                             #
# PURPOSE: TO MONITOR LAG OF GOLDEN GATE        #
# NOTE: THIS SCRIPT CALLS ggs.ksh               #
# THIS SCRIPT NOTIFY IF LAG IS MORE THEN 30 MIN #
# ONLY FOR FOR EXT AND PMP PROCESS GROUP #
###########################################
export GGATE=/opt/oracle/u01/app/oracle/ggs
alias gate='clear;cd $GGATE;./ggsci'
export PATH=/opt/oracle/u01/app/oracle/ggs:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/opt/oracle/u01/app/oracle/ggs
LOGDIR=/export/home/oracle/dba_scripts/ggs/logs
EMAILFile=$LOGDIR/ggs_email.log
BOX=$(uname -a | awk '{print $2}')

##########################################################################
# RUNNING SCRIPT TO GET GOLDEN GATE INFORMATION #
##########################################################################

/export/home/oracle/dba_scripts/ggs/ggs.ksh > $LOGDIR/ggs_1.log

#to check when script was running

echo "script ggsksh completed from ggs_lag at `date`" >> /tmp/ggs_check.log

##################################################################################
## FORMATING INFORMATION: change cut -d":" -f 1,4 TO cut -d":" -f 1,2 ##
## to getinformation about lag instead of checkpoint ##
## this command grep only EXT_ and PMP_ if you need more pattern ##
## if you need more pattern to be greped please add to '(EXT_|PMP_|pattern)' ##
##################################################################################


cat $LOGDIR/ggs_1.log|egrep -i '(EXT_|PMP_)'|cut -d":" -f 1,2,4| tr ":" " "|tr -s '[:space:]'|cut -d" " -f1,2,3,4,5,6 > $LOGDIR/ggs_2.log

# uncomment below command if you want to get lag and checkpoint both information #

#cat $LOGDIR/ggs_1.log|egrep -i '(EXT_|PMP_|DART)'|cut -d":" -f 1,2,4| tr ":" " "|tr -s '[:space:]'|cut -d" " -f1,2,3,4,5,6 > $LOGDIR/ggs_2.log

# uncomment below command if you want to get lag information about running process #

#cat $LOGDIR/ggs_1.log|grep RUNNING|cut -d":" -f 1,2,4| tr ":" " "|tr -s '[:space:]'|cut -d" " -f1,2,3,4,5,6 >
$LOGDIR/ggs_2.log

##########################################################################
## CHECKING FOR LAG MORE THEN 30 MIN FOR ABENDED PROCESS ##
##########################################################################

##########################################################################
## CHECKING FOR LAG MORE THEN 30 MIN FOR ABENDED PROCESS ##
##########################################################################


awk '{if ( $4 > 00 || $5 >=30 ) {print $1 " " $3 " HAS LAG of " $4" hour " $5 " min -- at -- " d "\n"} else {print "NO LAG FOR " $3 " " d >> "/tmp/ggs_lag_fine.log" }}' d="$(date)" $LOGDIR/ggs_2.log > $LOGDIR/ggs_email.log

# uncomment below command if you want to get lag and checkpoint both information #

#awk '{if ($4 >=30 || $5>=30 ) {print $1 " " $3 " has lag of "$4" min with checkpoint of "$5" min -- at -- " d "\n"} else {print "NO LAG FOR " $3 " "d > "/tmp/ggs_lag_fine.log" }}' d="$(date)" $LOGDIR/ggs_2.log > $LOGDIR/ggs_email.log

##########################################################
## SENDING EMAIL IF ERRORS ARE IN LOGFILE ###
##########################################################

if [ -s $EMAILFile ]; then
#echo "ERRORS FOUND"
mailx -s "GG LAG FOUND ON: $BOX" your.email@gmail.com team.email@gmail.com < $EMAILFile else cat /dev/null > $EMAILFile
#echo "ERRORS NOT FOUND"
fi

################# SCRIPT END ######################
This is ggs.ksh which calls by above ggs_lag.ksh
#!/bin/ksh
#########################################
#Name: ggs.ksh #
#THIS SCRIPT WILL CALLED BY ggs_lag.ksh #
#########################################
#alias gate='clear;cd $GGATE;./ggsci'
echo "ggsksh started `date`" >> /tmp/ggs_check.log
export GGATE=/opt/oracle/u01/app/oracle/ggs
alias gate='clear;cd $GGATE;ggsci'
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/opt/oracle/u01/app/oracle/ggs
export PATH=/opt/oracle/u01/app/oracle/ggs:$PATH
#cd $GGATE
/opt/oracle/u01/app/oracle/ggs/ggsci <<>
info all
exit
EOF
echo "ggsksh completed at `date` " >> /tmp/ggs_check.log

Monday, August 16, 2010

ASM CLONING

Links to this post
------ON HOST

==================================================================================================
ENABLE BLOCK CHANGE TRACKING
===================================================================================================

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

==================================================================================================
CHECK EACH LOCATION OF FILE ON HOST (PRODASM)
===================================================================================================

select MEMBER from v$logfile;
select NAME from v$datafile;
select NAME from v$tempfile;

+DG1/prodasm/datafile/users.259.727034915
+DG1/prodasm/datafile/sysaux.257.727034911
+DG1/prodasm/datafile/undotbs1.258.727034913
+DG1/prodasm/datafile/system.256.727034907
+DG1/prodasm/controlfile/current.260.727035019

==================================================================================================
PUT DATABASE IN BEGIN BACKUPMODE
===================================================================================================

SQL> alter database begin backup;

Database altered.

==================================================================================================
ON HOST (PRODASM) CONVERT ALL DBFILE + CONTROLFILE ASM FILE TO FILESYSTEMS ( DONT CONVERT TEMPFILE)
===================================================================================================

----------- CAN NOT CONVERT TEMPFILE......

run {
copy current controlfile to '/u01/copy/controlfile.ctl';
convert datafile '+DG1/prodasm/datafile/users.259.727034915' format '/u01/copy/users.dbf';
convert datafile '+DG1/prodasm/datafile/sysaux.257.727034911' format '/u01/copy/sysaux.dbf';
convert datafile '+DG1/prodasm/datafile/undotbs1.258.727034913' format '/u01/copy/undotbs1.dbf';
convert datafile '+DG1/prodasm/datafile/system.256.727034907' format '/u01/copy/system.dbf';
convert datafile '+DG1/DG1/prodasm/tempfile/temp.264.727035059' format '/u01/copy/tempfile.dbf';
}

----------------------OUTPUT--------------------------------
run {
copy current controlfile to '/u01/copy/controlfile.ctl';
convert datafile '+DG1/prodasm/datafile/users.259.727034915' format '/u01/copy/users.dbf';
RMAN> 2> 3> 4> convert datafile '+DG1/prodasm/datafile/sysaux.257.727034911' format '/u01/copy/sysaux.dbf';
5> convert datafile '+DG1/prodasm/datafile/undotbs1.258.727034913' format '/u01/copy/undotbs1.dbf';
convert datafile '+DG1/prodasm/datafile/system.256.727034907' format '/u01/copy/system.dbf';
6> 7> }

Starting backup at 15-AUG-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=/u01/copy/controlfile.ctl tag=TAG20100815T204830 recid=26 stamp=727130911
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 15-AUG-10

Starting backup at 15-AUG-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=+DG1/prodasm/datafile/users.259.727034915
converted datafile=/u01/copy/users.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:01:10
Finished backup at 15-AUG-10

Starting backup at 15-AUG-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=+DG1/prodasm/datafile/sysaux.257.727034911
converted datafile=/u01/copy/sysaux.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:16
Finished backup at 15-AUG-10

Starting backup at 15-AUG-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=+DG1/prodasm/datafile/undotbs1.258.727034913
converted datafile=/u01/copy/undotbs1.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:04
Finished backup at 15-AUG-10

Starting backup at 15-AUG-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=+DG1/prodasm/datafile/system.256.727034907
converted datafile=/u01/copy/system.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:26
Finished backup at 15-AUG-10

RMAN> exit


===================================================================================
COPY EACH CONVERTEDFILE + INITFILE TO REMOTE SERVER FILESYSTEM OF PRODASM
===================================================================================
---ON HOST
copy each file to remote destination plus initfile

scp * DEVASM:/u01/copy

PRDASM-> scp * DEVASM:/u01/copy
controlfile.ctl 100% 7088KB 3.5MB/s 00:02
sysaux.dbf 100% 230MB 4.3MB/s 00:53
system.dbf 100% 480MB 4.7MB/s 01:42
undotbs1.dbf 100% 25MB 5.0MB/s 00:05
users.dbf 100% 5128KB 5.0MB/s 00:01


===================================================================================
ON REMOTE SERVER(DEVASM) CHANGE INIT FILE AND GIVE CONTROLFILE LOCATION THERE..
===================================================================================

PRODASM.__db_cache_size=121634816
PRODASM.__java_pool_size=4194304
PRODASM.__large_pool_size=4194304
PRODASM.__shared_pool_size=54525952
PRODASM.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/PRODASM/adump'
*.background_dump_dest='/u01/app/oracle/admin/PRODASM/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/copy/controlfile.ctl' ---------------> changed
*.core_dump_dest='/u01/app/oracle/admin/PRODASM/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DG2'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_file_name_convert='+DG1','+DG2'
*.db_name='PRODASM'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=PRODASMXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=+DG2/DEVASM/ARCH'
*.open_cursors=300
*.pga_aggregate_target=62914560
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=188743680
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/PRODASM/udump'

=======================================================================================
ON REMOTE SERVER(DEVASM) STARTUP DATABASE IN NOMOUNT MODE WITH HELP OF PROD INITFILE
=======================================================================================

DEVASM-> echo $ORACLE_SID
DEVASM
DEVASM->

SQL> startup mount pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initPRODASM.ora';
ORACLE instance started.

Total System Global Area 188743680 bytes
Fixed Size 1218436 bytes
Variable Size 62916732 bytes
Database Buffers 121634816 bytes
Redo Buffers 2973696 bytes
Database mounted.

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

=======================================================================================
ON REMOTE SERVER(DEVASM) : CONVERT FILE AGAIN BACK TO REMOTE ASM (DEVASM)
=======================================================================================
devasm
run {
convert datafile '/u01/copy/undotbs1.dbf' format '+DG2';
convert datafile '/u01/copy/system.dbf' format '+DG2';
convert datafile '/u01/copy/users.dbf' format '+DG2';
convert datafile '/u01/copy/sysaux.dbf' format '+DG2';
}

--------------------------OUTPUT------------------------------------------
DEVASM-> rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Sun Aug 15 20:59:20 2010

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

connected to target database: PRODASM (DBID=3528672201, not open)

RMAN> run {
convert datafile '/u01/copy/undotbs1.dbf' format '+DG2';
convert datafile '/u01/copy/system.dbf' format '+DG2';
convert datafile '/u01/copy/users.dbf' format '+DG2';
convert datafile '/u01/copy/sysaux.dbf' format '+DG2';
}2> 3> 4> 5> 6>

Starting backup at 15-AUG-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input filename=/u01/copy/undotbs1.dbf
converted datafile=+DG2/prodasm/datafile/undotbs1.273.727131573
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:09
Finished backup at 15-AUG-10

Starting backup at 15-AUG-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=/u01/copy/system.dbf
converted datafile=+DG2/prodasm/datafile/system.274.727131575
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:02:12
Finished backup at 15-AUG-10

Starting backup at 15-AUG-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=/u01/copy/users.dbf
converted datafile=+DG2/prodasm/datafile/users.275.727131711
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:08
Finished backup at 15-AUG-10

Starting backup at 15-AUG-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=/u01/copy/sysaux.dbf
converted datafile=+DG2/prodasm/datafile/sysaux.276.727131715
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:25
Finished backup at 15-AUG-10

=======================================================================================
ON REMOTE SERVER(DEVASM) : CHANGE NAME OF DATAFILES IN CONTROLFILE
=======================================================================================

SQL> select MEMBER from v$logfile;
SQL> select NAME from v$datafile;
SQL> select NAME from v$tempfile;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

ALTER DATABASE RENAME FILE '+DG1/prodasm/datafile/users.259.727034915' to '+DG2/prodasm/datafile/users.275.727131711';
ALTER DATABASE RENAME FILE '+DG1/prodasm/datafile/sysaux.257.727034911' to '+DG2/prodasm/datafile/sysaux.276.727131715';
ALTER DATABASE RENAME FILE '+DG1/prodasm/datafile/undotbs1.258.727034913' to '+DG2/prodasm/datafile/undotbs1.273.727131573';
ALTER DATABASE RENAME FILE '+DG1/prodasm/datafile/system.256.727034907' to '+DG2/prodasm/datafile/system.274.727131575';


SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

=======================================================================================
ON REMOTE SERVER(DEVASM) : DISABLE BLOCK CHANGE TRACKING
=======================================================================================

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-19751: could not create the change tracking file
ORA-19750: change tracking file:
'+DG1/prodasm/changetracking/ctf.267.727101715'
ORA-17502: ksfdcre:1 Failed to create file
+DG1/prodasm/changetracking/ctf.267.727101715
ORA-17501: logical block size 4294967295 is invalid
ORA-17503: ksfdopn:2 Failed to open file
+DG1/prodasm/changetracking/ctf.267.727101715
ORA-15001: diskgroup "DG1" does not exist or is not mounted
ORA-15001: diskgroup "DG1" does not exist or is not mounted


SQL> alter database disable block change tracking;

Database altered.

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '+DG2/prodasm/datafile/system.274.727131575'

=======================================================================================
ON REMOTE SERVER(DEVASM) : SHUTDOWN IMMEDIATE;
=======================================================================================

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

=======================================================================================
ON REMOTE SERVER(DEVASM) : MOUNT DATABASE AND OPEN IT NORMALLY
=======================================================================================

SQL> startup mount;
ORACLE instance started.

Total System Global Area 188743680 bytes
Fixed Size 1218436 bytes
Variable Size 62916732 bytes
Database Buffers 121634816 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> alter database open;

Database altered.

=======================================================================================
ON REMOTE SERVER(DEVASM) : CONTROLFILE AND SPFILE CREATED AUTOMATICALLY
=======================================================================================

SQL> show parameter CONTROL


NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DG2/devasm/controlfile/current.260.727044801
SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DG2/devasm/spfiledevasm.ora

Wednesday, June 2, 2010

METALINK NOTES

Links to this post
=============================================
METALINK NOTE
=============================================

Note 458122.1 Installing and Configuring Csscan in 8i and 9i (Database Character Set Scanner)
211909.1 ORA-7445 related information.
144808.1 for more information and limits of using BYTE and CHAR semantics. This case will be added to this Note.

NOTE: The example shown above assumes that you only have one
Note 458122.1 Installing and Configuring Csscan in 8i and 9i (Database Character Set Scanner)
Note 745809.1 Installing and configuring Csscan in 10g and 11g (Database Character Set Scanner)
Note 444701.1 Csscan output explained
SQL> --Note we need to create all indexes
Make a note of the interface name (eth1 in the following example), then
186981.1 Oracle Application Server with Oracle E-Business Suite Release 11i

Physical Standby
*****************
Note:180031.1 Creating a DATA Guard physical standby
Note:214071.1 Creating a DATA Guard physical standby with DATA Guard Manager
Note:232649.1 Configuring gap resolution
Note:232240.1 Performing a switchover
Note:227196.1 Performing a failover
Note:187242.1 Applying Patchsets with Physical Standby in Place

Logical Standby
****************
Note:186150.1 Creating a logical standby
Note:214071.1 Creating a logical standby with DATA Guard Manager
Note:232240.1 Performing a switchover
Note:227196.1 Performing a failover
Note:233261.1 Tuning Log Apply Services
Note:215020.1 Troubleshooting Logical Standbys
Note:210989.1 Applying Patchsets with Logical Standby in Place
Note:233519.1 Known Issues with Logical Standby

Dataguard General Information
*****************************
Note:205637.1 Configuring Transparent Application Failover with DATA Guard
Note:233509.1 DATA Guard Frequently Asked Questions
Note:225633.1 Using SSH with 9i DATA Guard
Note:233425.1 Top DATA Guard Bugs
Note:219344.1 Usage, Benefits and Limitations of Standby RedoLogs
Note:201669.1 Setup and maintenance of DATA Guard Broker using DGMGRL
Note:203326.1 DATA Guard 9i Log Transportation on RAC
Note:239100.1 DATA Guard Protection Modes Explained

Dataguard Configuration Best Practices
**************************************
Note:240874.1 Primary Site and Network Configuration Best Practices
Note:240875.1 9i Media Recovery Best Practices

--------------RAC NOTE

Real Application Clusters(RAC)
*******************************
Note 338348.1
259301.1 CRS and 10g Real Application Clusters
178683.1 Tracing GSD, SRVCTL, GSDCTL, and SRVCONFIG
239998.1 10g RAC How to Clean Up After a Failed CRS Install
188135.1 Documentation Index for Real Application Clusters
268937.1 Repairing or Restoring an Inconsistent OCR in RAC
279793.1 How to Restore a Lost Voting Disk in 10g
292776.1 10g RAC Lessons Learned
452924.1 How to Prepare Storage for ASM,
239998.1 remove a failed CRS install
334567.1 Pre-Install checks for 10gR2 RDBMS (10.2.x) - SUN Solaris Platforms Refer to Doc ID:
181503.1 Real Application Clusters Whitepapers (OTN)
280209.1 10g RAC Performance Best Practices (INTERNAL ONLY)
302806.1 IBM General Parallel File System (GPFS) and Oracle RAC on AIX 5L and IBM eServer pSeries
270512.1 Adding a Node to a 10g RAC Cluster
268937.1 Repairing or Restoring an Inconsistent OCR in RAC
279793.1 How to Restore a Lost Voting Disk in 10g
292776.1 10g RAC Lessons Learned
137288.1 Manual Database Creation in Oracle9i (Single Instance and RAC)
292776.1 10g RAC Lessons Learned
280216.1 10g RAC Reference (INTERNAL ONLY)
269320.1 Removing a Node from a 10g RAC Cluster
226561.1 9iRAC Tuning Best Practices (INTERNAL ONLY)
220178.1 Installing and setting up ocfs on Linux - Basic Guide
208375.1 How To Convert A Single Instance Database To RAC In A Cluster File System Configuration
255359.1 Automatic Storage Management (ASM) and Oracle Cluster File System (OCFS) in Oracle10g
341963.1 10gR2 RAC Best Practices (INTERNAL ONLY)
273015.1 Migrating to RAC using DATA Guard
329530.1 Using Redhat Global File System (GFS) as shared storage for RAC
270901.1 How to Dynamically Add a New Node to an Existing 9.2.0 RAC Cluster
203326.1 DATA Guard 9i Log Transportation on RAC
169539.1 A Short Description of HA Options Available in 9i
160120.1 Oracle Real Application Clusters on Sun Cluster v3
226569.1 9iRAC Most Common Performance Problem Areas (INTERNAL ONLY)
251578.1 Step-By-Step Upgrade of Oracle Cluster File System (OCFS v1) on Linux
247135.1 How to Implement Load Balancing With RAC Configured System Using JDBC
139436.1 Understanding 9i Real Application Clusters Cache Fusion
285358.1 Creating a Logical Standby from a RAC Primary Using a Hot Backup
222288.1 9i Rel 2 RAC Running on IBM’s General Parallel File System
226567.1 9iRAC Related Init.ora Parameters (INTERNAL ONLY)
210889.1 RAC Installation with a NetApp Filer in Red Hat Linux Environment
341965.1 10gR2 RAC Reference (INTERNAL ONLY)
341969.1 10gR2 RAC OS Best Practices (INTERNAL ONLY)
226566.1 9iRAC Related Latches (INTERNAL ONLY)
220970.1 RAC: Frequently Asked Questions
268202.1 Dynamic node addition in a Linux cluster
285455.1 HOW TO MAKE AN EXCLUSIVE INSTANCE AVAILABLE ON MULTIPLE CLUSTER NODES.
332257.1 Using Oracle Clusterware with Vendor Clusterware FAQ
245079.1 Steps to clone a 11i RAC environment to a non-RAC environment
235158.1 How To Enable/Disbale Archive Log Mode on Oracle9i Real Application Cluster
210022.1 How To Add A New Instance To The Existing Two Nodes RAC Database Manually
317516.1 Adding and Deleting a Cluster Node on 10gR2 / Linux
271685.1 How to Run Autoconfig for RAC Environment on Apps Tier Only
278816.1 How to Setup Parallel Concurrent Processing using Shared APPL_TOP for RAC Environment
334459.1 How to change hostname in RAC environment
250378.1 Migrating Applications 11i to use Oracle9i RAC (Real Application Clusters).
295998.1 How to solve corruptions on OCFS file system
345081.1 How to Rename a RAC Database in a 10g Real Application Clusters Environment
312051.1 How To Remove Ocfs From Linux Box.
203226.1 : RAC Survival Kit: Real Application Clusters Troubleshooting
289690.1 : Data Gathering for Troubleshooting RAC and CRS issues

********************************************************
471165.1 Additional steps to install 10gR2 RAC on IBM zSeries Based Linux (SLES10)
407086.1 USING CLONING IN CRS/RAC WINDOWS ENVIRONMENTS TO ADD A NODE
414163.1 10gR2 RAC Install issues on Oracle EL5 or RHEL5 or SLES10 (VIPCA Failures)
467753.1 Veritas clusterware 5.0 not recognized by Oracle due to the fact that Veritas
467176.1 RAC: Installing RDBMS Oracle Home Hangs The Oui
466975.1 Step to remove node from Cluster when the node crashes due to OS or H/w
330358.1 CRS 10g R2 Diagnostic Collection Guide
401132.1 How to install Oracle Clusterware with shared storage on block devices
392207.1 CSSD Startup fails with NSerr (12532,12560) transport:(502,0,0) during Install
333166.1 CSSD Startup Fails with NSerr (12546,12560) transport:(516,0,0) During install
330929.1 CRS Stack Fails to Start After Reboot ORA-29702 CRS-0184
463255.1 Enable trace for gsd issues on 10gR2 RAC
338924.1 CLUVFY Fails With Error: Could not find a suitable set of interfaces for VIPs
462616.1 Reconfiguring the CSS disktimeout of 10gR2 Clusterware for Proper LUN Failover
461884.1 How To Disable Fatal Mode Oprocd On HP-UX Itanium 10gR2
404474.1 Status of Certification of Oracle Clusterware with HACMP 5.3 & 5.4
329530.1 Using Redhat Global File System (GFS) as shared storage for RAC
458324.1 Increased 'Log File Sync' waits in 10gR2
341214.1 How To clean up after a Failed (or successful) Oracle Clusterware Installation
454638.1 srvctl command failed - An unexpected exception has been detected in native
276434.1 Modifying the VIP or VIP Hostname of a 10g Oracle Clusterware Node
383123.1 PRKP-1001 CRS-215 srvctl Can not Start 2nd Instance
358620.1 How To Recreate Voting And OCR Disk In 10gR1/2 RAC
200346.1 RAC: Frequently Asked Questions
220970.1 RAC: Frequently Asked Questions
269320.1 Removing a Node from a 10g RAC Cluster
430266.1 How to install 10gR2 and 9iR2 on the same node with different UDLM requirement
283684.1 How to Change Interconnect/Public Interface IP Subnet in a 10g Cluster
391790.1 Unable To Connect To Cluster Manager Ora-29701
294430.1 CSS Timeout Computation in RAC 10g (10g Release 1 and 10g Release 2)
316583.1 VIPCA FAILS COMPLAINING THAT INTERFACE IS NOT PUBLIC
416868.1 CDMP DIRECTORIES AND TRW FILES ON RAC
414177.1 Executing root.sh errors with "Failed To Upg Oracle Cluster Registry Config
390483.1 DRM - Dynamic Resource management
390880.1 OCR Corruption after Adding/Removing voting disk to a cluster when CRS stack
309542.1 How to start/stop the 10g CRS ClusterWare
396643.1 CVU HAS INCORRECT ORA_CRS_HOME VARIABLE AFTER APPLYING CRS BUNDLE II
387205.1 The 10.1.0.4 DB Cannot Start With 10.2.0.2.0 CRS And ASM
270512.1 Adding a Node to a 10g RAC Cluster
395156.1 Startup (mount) of 2nd RAC instance fails with ORA-00600 [kccsbck_first]
363777.1 How to Completely Remove a Service so that its Service_id Can Be Reused
391112.1 Database Resource Manager Spins Lmon To 100% Of Cpu
365530.1 Permissions not set correctly after 10gR2 installation
357808.1 Diagnosability for CRS / EVM / RACG
284752.1 10g RAC: Steps To Increase CSS Misscount, Reboottime and Disktimeout
332180.1 ASMCMD - ASM command line utility
371434.1 Using Openfiler iSCSI with an Oracle database
338047.1 cluvfy ERROR: Unable to retrieve database release version
183408.1 Raw Devices and Cluster Filesystems With Real Application Clusters
367564.1 Server Reboots When Rolling Upgrading CRS(10gr1 -> 10gr2)
358545.1 Root.sh is failing with CORE dumps, during CRS installation
343092.1 How to setup Linux md devices for CRS and ASM
295871.1 How to verify if CRS install is Valid
331934.1 RAC Single Instance (ASM) startup fails with ORA-27300/ORA-27301/ORA-27302
341974.1 10gR2 RAC Scheduling and Process Prioritization
341971.1 10gR2 RAC GES Statistics
341969.1 10gR2 RAC OS Best Practices
341965.1 10gR2 RAC Reference
341963.1 10gR2 RAC Best Practices
313540.1 Manually running cvu to verify stages during a CRS/RAC installation
331168.1 Oracle Clusterware consolidated logging in 10gR2
339710.1 Abnormal Program Termination When Installing 10gR2 on RHAS 4.0
339383.1 CSSD FAILURE DOES NOT REBOOT THE NODE
337937.1 Step By Step - 10gR2 RAC with ASM install on Linux(x86) - Demo
810663.1 11.1.0.X CRS Bundle Patch Information
438049.1 How To Find RDBMS patchsets on My Oracle Support
338706.1 Oracle Clusterware (formerly CRS) Rolling Upgrades
6890831_111070_Linux-x86.zip

********************************************
performance and tunning
********************************************

6890831
842884.1 - How To Understand AWR Report / Statspack Report
744143.1 - Tuning performance on eBusiness suite (section "How to interpret AWR or Statspack report ?")
872733.1 - Transport AWR Data
280209.1 10g RAC Performance Best Practices
810663.1
762526.1 - What do OS stats section of AWR report really tell us? \
884046.1 Understand each field of AWR
153788.1 ORA-600/ORA-7445 Error Look-up Tool [ID ]
https://updates.oracle.com/Orion/QuickLinks/process_form?type=nonapps

CLONING
*********
216664.1 FAQ: Cloning Oracle Applications Release 11i
230672.1 Cloning Oracle Applications Release 11i with Rapid Clone
135792.1 Cloning Oracle Applications Release 11i

Discoverer
************
139516.1 Discoverer 4i with Oracle Applications 11i
257798.1 Discoverer 10g (9.0.4) with Oracle Applications 11i
139516.1 Installation of Discoverer 4i

AutoConfig
************
165195.1 Using AutoConfig to Manage System Configurations with Oracle Applications 11i
218089.1 Autoconfig FAQ

PORTAL
*******
228516.1 How to copy (export/import) Portal database schemas of IAS 9.0.2 to another database
330391.1 How to copy (export/import) Portal database schemas of IAS 10.1.2 to another database

UPGRADES
*********
125767.1 Upgrading Devloper6i with Oracle Applications 11i
216550.1 RDBMS upgrade to 9.2.0
161779.1 Upgradation of HTTP Server
212005.1 Upgrade Oracle Applications to 11.5.8
139863.1 Self Servie Framework Upgrade
112867.1 Express Server & OFA upgrade
124606.1 Jinitiator upgrade
130091.1 JDK upgrade to 1.3
130091.1 Upgrading Oracle Applications 11i to use JDK 1.3
144069.1 Upgrading to Workflow 2.6 with Oracle Applications 11i
159657.1 Complete Upgrade Checklist for Manual Upgrades from 8.X / 9.0.1 to Oracle9i

EXPORT / IMPORT
***************

Note 230627.1 - 9i Export/Import Process for Oracle Applications Release 11i
Note 331221.1 - 10g Export/Import Process for Oracle Applications Release 11i
Note 362205.1 - 10g Release 2 Export/Import Process for Oracle Applications Release 11i
Note 277650.1 - How to Use Export and Import when Transferring DATA Across Platforms or Acros...
Note 243304.1 - 10g: Transportable Tablespaces Across Different Platforms
Note 341733.1 - Export/Import DataPump Parameters INCLUDE and EXCLUDE - How to Load and Unload..
336014.1 - How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ?
155477.1 - on improving importing speed

Streams
************
Note 784021.1 Managing Streams from Oracle Enterprise Manager 10g Release 5 Grid Control
Note 460950.1 How To Monitor Oracle Streams Using OEM

create logical standby

Links to this post

TO STOP ARCHIVAL GAP

# Start the standby databases and listeners before starting the primary database.
# Shut down the primary database before shutting down the standby database

* Creation of Logical Standby database (oracle 10gr2)

PRIMARY --> STANDBY

***** Preparing the Primary Database for Standby Database Creation *****

1. Enable Forced Logging

SQL> ALTER DATABASE FORCE LOGGING;

2. Configure a Standby Redo Log

- Ensure log file sizes are identical on the primary and standby databases.
- Determine the appropriate number of standby redo log file groups.
- Verify related database parameters and settings.
- Create standby redo log file groups.
SQL> select MEMBER from v$logfile;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5
('/oravl04/oradata/PRIMARY/redo_gE_m01.dbf',
'/oravl04/oradata/PRIMARY/redo_gE_m02.dbf') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6
('/oravl04/oradata/PRIMARY/redo_gF_m01.dbf',
'/oravl04/oradata/PRIMARY/redo_gF_m02.dbf') SIZE 50M;

- Verify the standby redo log file groups were created.

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

3. Set Primary Database Initialization Parameters

#############################################################################################################
# Instance configured as Primary database (Standby STANDBY )
#############################################################################################################


##########################################
# Standby Parameters #
##########################################

DB_UNIQUE_NAME = PRIMARY
LOG_ARCHIVE_CONFIG = 'DG_CONFIG=(PRIMARY,STANDBY)'
LOG_ARCHIVE_DEST_1 = 'LOCATION=/oravl01/oracle/adm/PRIMARY/arc/local_arc VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMARY'
LOG_ARCHIVE_DEST_2 = 'SERVICE=STANDBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY'
LOG_ARCHIVE_DEST_STATE_1 = ENABLE
LOG_ARCHIVE_DEST_STATE_2 = ENABLE
REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE
LOG_ARCHIVE_FORMAT = archPRIMARY_%s_%t_%r.dbf
LOG_ARCHIVE_MAX_PROCESSES = 30

Note : Make sure that database is not in Shared server mode.

4. Enable Archiving / create password file / create control file for standby DB (Primary)

SQL> SHUTDOWN IMMEDIATE;
SQL> EXIT

* Create a Password File
orapwd file=$ORACLE_HOME/dbs/orapwPRIMARY PASSWORD=system ENTRIES=10 force=y


SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;

* Create a Control File for Standby Database
QL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/oravl01/oracle/STANDBY.ctl';

SQL> ALTER DATABASE OPEN;


* Check datafiles/logfiles status

SQL> select MEMBER from v$logfile;
SQL> select NAME from v$datafile;
SQL> select NAME from v$tempfile;


***** Step-by-Step Instructions for Creating a Physical Standby Database *****

5. Run Cre_OFA.sh to create OFA for new DB instance.

6. Create a Backup Copy of the Primary Database Datafiles ( Primary )

SQL> alter database begin backup;

<-- Copy All datafiles/redo log files/arch files/cnt file to new DB location -->

SQL> alter database end backup;
-- create new Archive locations

mkdir /oravl99/ORACLE/PRIMARY/arc/local_arc
mkdir /oravl99/ORACLE/PRIMARY/arc/standby_arc


7. Prepare an Initialization Parameter File for the Standby Database

##############################################################################
# Logical standby database instance for PRIMARY (Primary DB instance)
##############################################################################

##########################################
# Standby Parameters #
##########################################

DB_UNIQUE_NAME =STANDBY
LOG_ARCHIVE_CONFIG ='DG_CONFIG=(PRIMARY,STANDBY)'
DB_FILE_NAME_CONVERT ='PRIMARY','STANDBY'
LOG_FILE_NAME_CONVERT ='/oravl04/oradata/PRIMARY/','/oravl04/oradata/STANDBY/'
LOG_ARCHIVE_FORMAT =archPRIMARY_%s_%t_%r.dbf
LOG_ARCHIVE_DEST_1 ='LOCATION=/oravl01/oracle/adm/STANDBY/arc/local_arc VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STANDBY'
LOG_ARCHIVE_DEST_2 ='SERVICE=PRIMARY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=PRIMARY'
LOG_ARCHIVE_DEST_3 ='LOCATION=/oravl01/oracle/adm/STANDBY/arc/standby_arc VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLES) DB_UNIQUE_NAME=STANDBY'
LOG_ARCHIVE_DEST_STATE_1 =ENABLE
LOG_ARCHIVE_DEST_STATE_2 =ENABLE
LOG_ARCHIVE_DEST_STATE_3 =ENABLE

REMOTE_LOGIN_PASSWORDFILE =EXCLUSIVE
STANDBY_FILE_MANAGEMENT = AUTO
FAL_SERVER = PRIMARY
FAL_CLIENT = STANDBY

8. Set Up the Environment to Support the Standby Database
- Create a password file.
orapwd file=$ORACLE_HOME/dbs/orapwSTANDBY PASSWORD=system ENTRIES=10 force=y
- Configure listeners for the primary and standby databases.
% lsnrctl stop
% lsnrctl start


9. Start the Physical Standby Database
set db_name=PRIMARY in init file

SQL> STARTUP MOUNT;

- Rename file location in new Control file (If any)

* Check datafiles/logfiles status

SQL> select MEMBER from v$logfile;
SQL> select NAME from v$datafile;
SQL> select NAME from v$tempfile;
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

SQL> ALTER DATABASE RENAME FILE '/oravl04/oradata/STANDBY/system_1.dbf' to '/oravl02/oradata/STANDBY/system_1.dbf';
SQL> ALTER DATABASE RENAME FILE '/oravl04/oradata/STANDBY/undotbs_1.dbf' to '/oravl02/oradata/STANDBY/undotbs_1.dbf';
SQL> ALTER DATABASE RENAME FILE '/oravl04/oradata/STANDBY/sysaux_1.dbf' to '/oravl02/oradata/STANDBY/sysaux_1.dbf';
SQL> ALTER DATABASE RENAME FILE '/oravl04/oradata/STANDBY/users_1.dbf' to '/oravl02/oradata/STANDBY/users_1.dbf';
SQL> ALTER DATABASE RENAME FILE '/oravl04/oradata/STANDBY/pool_data_1.dbf' to '/oravl02/oradata/STANDBY/pool_data_1.dbf';
SQL> ALTER DATABASE RENAME FILE '/oravl04/oradata/STANDBY/pool_ix_1.dbf' to '/oravl02/oradata/STANDBY/pool_ix_1.dbf';
SQL> ALTER DATABASE RENAME FILE '/oravl04/oradata/STANDBY/tools_1.dbf' to '/oravl02/oradata/STANDBY/tools_1.dbf';
SQL> ALTER DATABASE RENAME FILE '/oravl04/oradata/STANDBY/temp_1.dbf' to '/oravl02/oradata/STANDBY/temp_1.dbf';

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

- Start Redo Apply, On the standby database, issue the following command to start Redo Apply
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

11. Test archival operations to the physical standby database - run on primary database

SQL> ALTER SYSTEM SWITCH LOGFILE;

12. Verify the Physical Standby Database Is Performing Properly

- Identify the existing archived redo log files. - On the standby database
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;


- Force a log switch to archive the current online redo log file - On the primary database
SQL> ALTER SYSTEM SWITCH LOGFILE;


* Set ENV time format
SQL> ALTER SESSION set nls_date_FORMAT = 'DD-MON-YY HH:MI:SS';


- Verify the new redo data was archived on the standby database. - On the standby database
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;


- Verify new archived redo log files were applied. - On the standby database
SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;


*************************************************************************************************************************************************
PHYSICAL STANDBY DATABASE IS READY
*************************************************************************************************************************************************

* Creating a Logical Standby Database


13. Stop Redo Apply on the Physical Standby Database

- To stop Redo Apply, issue the following statement on the physical standby database
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;


14. Prepare the Primary Database to Support a Logical Standby Database


- Prepare the Primary Database for Role Transitions
update init file (include a LOG_ARCHIVE_DEST_3 destination on the primary database)

- Primary
alter system set LOG_ARCHIVE_DEST_3='LOCATION=/oravl01/oracle/adm/PRIMARY/arc/standby_arc VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=PRIMARY';

- Standby

alter system set LOG_ARCHIVE_DEST_3='LOCATION=/oravl01/oracle/adm/STANDBY/arc/standby_arc VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLES) DB_UNIQUE_NAME=STANDBY';

- Primary Init

LOG_ARCHIVE_DEST_3='LOCATION=/oravl01/oracle/adm/PRIMARY/arc/standby_arc VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=PRIMARY'
LOG_ARCHIVE_DEST_STATE_3 = ENABLE

15. Build a Dictionary in the Redo Data - on Primay database

SQL> EXECUTE DBMS_LOGSTDBY.BUILD;


16. Transition to a Logical Standby Database

- Convert to a Logical Standby Database

SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY STANDBY;

* Note : Got following error which is not the error, you may refer alert log. DB must be renamed using NID utility.

*************************
ERROR at line 1:
ORA-16254: change db_name to STANDBY in the client-side parameter file (pfile)

ORA-16254: change db_name to string in the client-side parameter file (pfile)
Cause: An ALTER DATABASE RECOVER TO LOGICAL STANDBY new-dbname command was successfully executed without a server parameter file (spfile).
Action: The client-side parameter file must be edited so that db_name is set to the given name before mounting the database again
*************************
* Note : if command goes in indefinite mode then then run following command from another session,
(Optional)
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
18. Adjust Initialization Parameters for the Logical Standby Database

SQL> SHUTDOWN;

- Create a New Password File

orapwd file=$ORACLE_HOME/dbs/orapwSTANDBY_NEW PASSWORD=sys4get ENTRIES=10 force=y

DB_NAME = STANDBY
LOG_ARCHIVE_DEST_1 = 'LOCATION=/oravl01/oracle/adm/STANDBY/arc VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STANDBY'
LOG_ARCHIVE_DEST_2 = 'SERVICE=PRIMARY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMARY'
LOG_ARCHIVE_DEST_3 = 'LOCATION=/oravl01/oracle/adm/STANDBY/arc/standby_arc VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=STANDBY'
LOG_ARCHIVE_DEST_STATE_1 = ENABLE
LOG_ARCHIVE_DEST_STATE_2 = ENABLE
LOG_ARCHIVE_DEST_STATE_3 = ENABLE

SQL> STARTUP MOUNT;

19. Open the Logical Standby Database


SQL> ALTER DATABASE OPEN RESETLOGS;


20. Issue the following statement to begin applying redo data to the logical standby database

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Verify Logical database
SQL> desc dba_logstdby_progress
SQL> select APPLIED_SCN,READ_SCN,NEWEST_SCN from dba_logstdby_progress;

* Optional steps -->


21. Verify the Logical Standby Database Is Performing Properly

- Monitoring Log File Archival Information

a. Determine the status of redo log files.- on primary database

SQL> SELECT THREAD#, SEQUENCE#, ARCHIVED, STATUS FROM V$LOG;

b. Determine the most recent archived redo log file. - on primay database

SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#;

c. Determine the most recent archived redo log file at each destination.- on primary database

SQL> SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ# FROM V$ARCHIVE_DEST_STATUS WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE';

d. Find out if archived redo log files have been received. - on primary database
SQL> SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1)
LOCAL WHERE LOCAL.SEQUENCE# NOT IN (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND THREAD# = LOCAL.THREAD#);


22. Applying Redo Data to Logical Standby Databases


- Starting SQL Apply
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;


To start redo apply immediate
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;


- Stopping SQL Apply on a Logical Standby Database

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;

To stop immediate redo apply
SQL> ALTER DATABASE ABORT LOGICAL STANDBY APPLY;
*************************************************************************************************************************************************
LOGICAL STANDBY DATABASE IS READY
*************************************************************************************************************************************************


select APPLIED_SCN,READ_SCN,NEWEST_SCN from dba_logstdby_progress;

select SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE#,DICT_BEGIN,DICT_END from DBA_LOGSTDBY_LOG;

select * from DBA_LOGSTDBY_PROGRESS;

Alter database register logical logfile '/oravl99/ORACLE/STANDBY/arc/standby_arc/archSTANDBY_49_1_654631187.dbf';



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


column Archive_dest format a50
column Error format a10

set linesize 100
set pagesize 10000



desc dba_logstdby_log;

*std by

select SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE# from dba_logstdby_log;

* primary

select SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE# from v$archived_log;

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

FORCE LOGGING Option

The database or tablespaces in the database should be put into FORCE LOGGING mode before creating the backup for the standby database. Either a database or all of its tablespaces should be put into this mode but not both.

The following statement will put a tablespace in FORCE LOGGING mode:

ALTER TABLESPACE FORCE LOGGING;

The FORCE LOGGING mode can be cancelled at the database level using the following statement:

ALTER DATABASE NO FORCE LOGGING;

The FORCE LOGGING mode can be cancelled at the tablespace level using the following statement:

ALTER TABLESPACE NO FORCE LOGGING;

Temporary tablespaces and temporary segments have no effect during FORCE LOGGING mode because these objects do not generate any redo. Undo tablespaces are in FORCE LOGGING mode by default, so they cannot be put into FORCE LOGGING mode. Oracle will generate an error if an attempt is made to put a temporary tablespace or undo tablespace into FORCE LOGGING mode.
==========================================
trouble shooting
==========================================

Check the alert logs on both sides.

1. run on primary to detect failures :-

select destination, status, fail_date, valid_now
from v$archive_dest
where dest_name in ('LOG_ARCHIVE_DEST_1','LOG_ARCHIVE_DEST_2','LOG_ARCHIVE_DEST_3','LOG_ARCHIVE_DEST_4')
and (status 'VALID' or VALID_NOW 'YES');

2. run on standby to get exact position of rollforward :-

select thread#, to_char(snapshot_time,'dd-mon-yyyy:hh24:mi'),
to_char(applied_time,'dd-mon-yyyy:hh24:mi'),
to_char(newest_time,'dd-mon-yyyy:hh24:mi') from V$STANDBY_APPLY_SNAPSHOT;

====================================================================================
DATA GUARD
====================================================================================

SYS@dg AS SYSDBA> ALTER SYSTEM SET DG_BROKER_START=TRUE;

System altered.

ON PRIMARY

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/sys
Connected.
DGMGRL> CREATE CONFIGURATION 'FAILOVER' AS PRIMARY DATABASE IS 'BG' CONNECT IDENTIFIER IS 'BG';
Configuration "NAME" created with primary database "DG"
DGMGRL>

DGMGRL> ADD DATABASE 'BG' AS CONNECT IDENTIFIER IS 'BG' MAINTAINED AS PHYSICAL;
Database "BG" added

====================================================================================
SWITCH OVER
====================================================================================
http://www.orafaq.com/node/2078
http://www.fadalti.com/oracle/database/How%20to%20create%20a%20%20logical_standby_database.htm
primary

1)
KILL ALL SESSION;
ALTER SYSTEM SWITCH LOGFILE;
SYS@dg AS SYSDBA> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

2)ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

IMMIDEATLY ISSUE ON PRIMARY

3)
SYS@dg AS SYSDBA> shutdown immediate;
SYS@dg AS SYSDBA> startup mount;

4) ON STANDBY AFTER STEP TO 2 COMPLETE

SYS@bg AS SYSDBA> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SYS@bg AS SYSDBA> startup

5)SWITCH LOGFILE ON FORMER STANDBY MEANS CURRENT PRIMARY

ALTERY SYSTEM SWITCH LOGFILE;

6) VARIFY BY

ARCHIVE LOG LIST ON BOTH DATABASES

7) ON FORMER PRIMARY MEANS CURRENT STANDBY

SYS@dg AS SYSDBA> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

----------------------------------references-----------------------------------------------

http://www.orafaq.com/node/2078
http://www.fadalti.com/oracle/database/How%20to%20create%20a%20%20logical_standby_database.htm