generate AWR automatic

hmm so , there was a performance problem in my prod server,

which pushed me to create a baseline of whole month and we never had it. To create that i needed AWR report of every day , every hour which made it even hard for me to take snapshot of it.

To create AWR manually needs a lot of times and obviously LABOR.

so i created my favorite script which is derived from BASH shell , where i had aix 5.1 and never had bash 3.x , which dont allowed `seq` in for loop.

so i wrote in (ksh). which is as below.

#!/bin/ksh
if [ $# != 4 ]
then
echo "Syntax awr_generation.sh dbid instanceId startsnapid endsnapid"
exit 1
fi
l_dbid=$1
l_instid=$2
l_start_snapid=$3
let l_end_snapid=$4-1
i=${l_start_snapid}
until [[ $i = $l_end_snapid ]];
do
let l_next_snapid=$i+1;
l_awr_log_file="/home/oracle/awr/awrrpt_${2}_${i}_${l_next_snapid}.log"
sqlplus -s / as sysdba << EOF set head off set pages 0 set lines 132 set echo off set feedback off spool $l_awr_log_file SELECT output FROM TABLE (dbms_workload_repository.awr_report_html
($l_dbid,$l_instid,$i,$l_next_snapid)
);
spool off
EOF
let i=i+1
done

Golden Gate on ASM

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

oracle search: Upgrade ASM and database from 10g to 11g

oracle search: Upgrade ASM and database from 10g to 11g

Upgrade ASM and database from 10g to 11g

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

oracle search: ASM CLONING

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

GOLDEN GATE LAG MONITORING

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

ASM CLONING

------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