Commands to find and Remove old logs from Oracle RAC environment (reclaim filesystem space)

cd $ORACLE_BASE  or $GRID_BASE

find . -name "evmlog.*" -mtime +15 -exec rm -f  {} \;

find . -name "+ASM*.trc*" -mtime +15 -exec ls -ltr  {} \;
find . -name "evmlog.*" -mtime +15 -exec rm  -f {} \;

find . -name "*.xml" -mtime +10 -exec ls -ltr  {} \;

find . -name "ora_*.trc*" -mtime +15 -exec rm -f {} \;

find . -name "+ASM_ora*.aud*" -mtime +15 -exec rm -f{} \;

Command to list files by their size :

ls -l | sort -k 5 -rn

Changing Apps Password in Oracle EBS R 12.1.3

1. Shut down application services with adstpall.sh script 

   sh adstpall.sh apps/hirbzlcc

   sh adopmnctl.sh stop

2. FNDCPASS apps/hirbzlcc 0 Y system/wnEV6wTCza SYSTEM APPLSYS ftfgrufz

3. run autoconfig on dbtier than run it on  appstier with new password

(run autoconfig.sh on each apps node & db node)

4. Start up all application services with  adstrtal.sh 

sh adstrtal.sh apps/ujbehpiw 

sh adopmnctl.sh start

Oracle EBS 12.1.3 Instance Cloning

Cloning Steps on the Target node after  running adpreclone.pl on source node (db Tier & AppsTier)

i.e

adpreclone.pl dbTier

adpreclone.pl appsTier


scp -r erpdbtuat@server01:/erpbintuat/erptuat/ /u01  

or

scp -r erpdbtuat@DBserver01:/erpbintuat/erptuat/prod_apps.tar.gz  /u01

(Note : rename the base directory from erpuat to erptest i.e to instance name you are copying)

cd /u0l/ERPTEST/db/11.2.0.4/appsutil/clone/bin

perl adcfgclone.pl dbTechStack

sqlplus "/as sysdba"

startup nomount pfile='$ORACLE_HOME/dbs/initERPTEST.ora'

copy the the rman backups to the destination filesystem (if its in accessible shared location then no need to copy)

rman auxiliary /
run {
allocate AUXILIARY channel c0 type disk;
allocate AUXILIARY channel c1 type disk;
allocate AUXILIARY channel c2 type disk;
allocate AUXILIARY channel c3 type disk;
allocate AUXILIARY channel c4 type disk;
allocate AUXILIARY channel c5 type disk;
allocate AUXILIARY channel c6 type disk;
allocate AUXILIARY channel c7 type disk;
duplicate target database to "ERPTEST" BACKUP LOCATION '/stage/backup/' NOFILENAMECHECK;
RELEASE CHANNEL c0;
RELEASE CHANNEL c1;
RELEASE CHANNEL c2;
RELEASE CHANNEL c3;
RELEASE CHANNEL c4;
RELEASE CHANNEL c5;
RELEASE CHANNEL c6;
RELEASE CHANNEL c7;
}

Take care of post db activities like password resets ,re-pointing db links and purging tables can be done now else these can also be done after Apps Tier Cloning Completes

Run Autoconfig $ORACLE_HOME/appsutil/scripts/instance_name/adautocfg.sh
==================================================================
Apps Tier

scp -r erpappstuat@appsserver01:/erpbintuat/erptuat/ /u01

 or 

untar tar -zxvf  prod_apps.tar.gz     (remove the Apps Tier Dir Structure under  /u01)

cd /u02/ERPTEST/apps/apps_st/comn/clone/bin

perl adcfgclone.pl appsTier

(Note: Rename  Instance specific Directory mv erptuat_server01 ERPTEST_server02)

cd  $IAS_ORACLE_HOME/certs/Apache/cwallet.sso .

mv cwallet.sso cwallet.sso_bkp

Perform post Clone activities like updating the workflow notificaon mailer address to Test/clone environment as per your requirement (you could leave it blank as well)

============================================
Trouble Shooting Login Page issues (When Login page goes Blank after cloning)

rm -rf $ORA_CONFIG_HOME/10.1.3/j2ee/oacore/persistence/*
rm -rf $ORA_CONFIG_HOME/10.1.3/j2ee/oafm/persistence/*
rm -rf $ORA_CONFIG_HOME/10.1.3/j2ee/forms/persistence/*
perl $FND_TOP/patch/115/bin/ojspCompile.pl --compile --flush -p 2

Migrating & Upgrading Oracle 11G to 12c Database

Migrating & Upgrading Oracle 11.2.0.4 to 12c Database

Copy the the Oracle Home from "tar -cvf oh.tar $ORACLE_HOME "  to new server

 export ORACLE_HOME=/u01/data/db/11.2.0.4
 export ORACLE_BASE=/u01/data/
 export PATH=$ORACLE_HOME/bin:$PATH
 export ORACLE_SID=PROD


./runInstaller -silent -clone ORACLE_BASE="/u01/data/" ORACLE_HOME="/u01/data/db/11.2.0.4" ORACLE_HOME_NAME="OraHome1"

 ./relink all

writing relink log to: /u01/data/db/11.2.0.4/install/relink.log


---./runInstaller -silent -attachHome -invPtrLoc /etc/oraInst.loc \ ORACLE_HOME="/u01/data/db/11.2.0.4" ORACLE_HOME_NAME="OraHome1"

orapwd file=$ORACLE_HOME/dbs/orapwPROD password=Welcome1 force=y

Take Rman backup of Source Database

rman target /


run

{
CONFIGURE CONTROLFILE AUTOBACKUP ON;
allocate channel c1 type disk format '/stage/Backup/%U';
allocate channel c2 type disk format '/stage/Backup/%U';
allocate channel c3 type disk format '/stage/Backup/%U';
allocate channel c4 type disk format '/stage/Backup/%U';
sql 'alter system archive log current';
BACKUP AS COMPRESSED BACKUPSET DATABASE;
sql 'alter system archive log current';
BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG ALL;
backup CURRENT CONTROLFILE format '/stage/Backup/cntrl_%s_%p_%t';
RELEASE CHANNEL c1;
RELEASE CHANNEL c2;
RELEASE CHANNEL c3;
RELEASE CHANNEL c4;
}


Clone DB from Source Backup

Bring up the instance in nomount

sqlplus "/as sysdba"
startup nomount pfile='/u01/data/db/11.2.0.4/dbs/initPROD.ora'
(Edit the Control file .archive log paths accordingly to the the new 12c Home / Mount points)

Connect to Auxiliary instance to clone the DB from RMAN backup

rman auxiliary sys/Welcome1


run {
allocate AUXILIARY channel c0 type disk;
allocate AUXILIARY channel c1 type disk;
allocate AUXILIARY channel c2 type disk;
allocate AUXILIARY channel c3 type disk;
allocate AUXILIARY channel c4 type disk;
allocate AUXILIARY channel c5 type disk;
allocate AUXILIARY channel c6 type disk;
allocate AUXILIARY channel c7 type disk;
duplicate target database to "PROD" BACKUP LOCATION '/stage/Backup/' NOFILENAMECHECK;
RELEASE CHANNEL c0;
RELEASE CHANNEL c1;
RELEASE CHANNEL c2;
RELEASE CHANNEL c3;
RELEASE CHANNEL c4;
RELEASE CHANNEL c5;
RELEASE CHANNEL c6;
RELEASE CHANNEL c7;
}


Install the 12c Binaries under /u01/oracle12c/  by invoking ./runInstaller  from Install directory

Set the ORACLE_HOME and PATH variables pointing to newly installed ORACLE 12C Binaries
export ORACLE_HOME=/u01/oracle12c/dbhome/product/12.1.0
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=PROD

Got to cd $ORACLE_HOME/bin  and invoke  ./dbua

How to Sync Oracle Standby Database (DataGuard) when the archive gap is very large

SQL> select DATABASE_ROLE from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL> select current_scn from v$database;

    CURRENT_SCN
-----------------------
10180880784901

QL> alter database recover managed standby database cancel;

Database altered.



rman target /
run
{
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/stage/prod/Backup_30May/%F';
allocate channel c1 type disk format '/stage/prod/Backup/%U';
allocate channel c2 type disk format '/stage/prod/Backup/%U';
allocate channel c3 type disk format '/stage/prod/Backup/%U';
allocate channel c4 type disk format '/stage/prod/Backup/%U';
allocate channel c5 type disk format '/stage/prod/Backup/%U';
allocate channel c6 type disk format '/stage/prod/Backup/%U';
allocate channel c7 type disk format '/stage/prod/Backup/%U';
backup incremental from scn 10180880784901 database;
}


alter database create standby controlfile as '/stage/prod/Backup/for_standby.ctl'

SQL> startup nomount

replace the previous controlfile with this new one created at primary , and Bring the database to MOUNT state

alter database mount standby database;

rman target /

RMAN> catalog start with '/stage/prod/Backup/';


recover database;

Again Check the SCN’s in primary and standby to make sure that both are in SYNc:

SQL> select current_scn from v$database;

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


Option B


rman target sys/passwd@PROD auxiliary /
run
{
ALLOCATE AUXILIARY CHANNEL CH01 TYPE disk format '/stage/prod/Backup/%U';
ALLOCATE AUXILIARY CHANNEL CH02 TYPE disk format '/stage/prod/Backup/%U';
DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK;
}



SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;



ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Query to check which session is using TEMPORARY Tablepace

select  su.username , ses.sid ,ses.serial# , su.tablespace , ceil((su.blocks * dt.block_size) / 1048576) MB
from v$sort_usage su ,dba_tablespaces dt , v$session ses where   su.tablespace = dt.tablespace_name and su.session_addr = ses.saddr order by MB desc