ADOP Patching Basics

ADOP Patching Basics

Full database export & import using datapump in Oracle

Full database export & import using datapump in Oracle

Step.1

Create a directory for export in source side.
create directory EXP_DIR as '/u01/dpdump_prod/EXP_DIR';

Step.2

Get the tablespace DDL metadata in source side.

set heading off;
set echo off;
set lines 1000 pages 40000
set long 999999
spool ddl_tablespace.sql
select dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) from dba_tablespaces tb;

Step.3

Run expdp command.

expdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=INDPROD_$(date +"%Y%m%d")exp.dmp logfile=INDPROD$(date +"%Y%m%d")_exp.log FULL=Y exclude=statistics

Step.4

Install oracle database binaries and create database.

Step.5

Create the directory for impdp in target side.
create or replace directory IMP_DIR as '/u01/dpdump_prod/IMP_DIR';

Step.6

Create the missing tablespaces in target side.

Please refer to spool ddl_tablespace.sql and create the tablespaces.

Step.7

Run impdp command.

impdp \'/ as sysdba\' dumpfile=INDPROD_20220823exp.dmp logfile=-INDPROD$(date +"%Y%m%d")_imp.log directory=DATA_PUMP_DIR full=y

Step.8

Run utlrp.sql to compile invalid objects in target side.
sql> @$ORACLE_HOME/rdbms/admin/utlrp.sql

Script to take Daily Full Backups using RMAN In Oracle

Script to take Daily Full Backups using RMAN In Oracle

Need to create required directories:

mkdir -p /u03/scripts

mkdir -p /u03/rman_backup


Actual Script:

[oratest@mqmhrd1 rman_backup]$ cd scripts/

vi rman_backup.sh

. /home/oratest/MQMGEMTEST.env
today="$(date --date '0 days ago' '+%d_%b_%Y')";
BKUP_DIR=/u03/rman_backup/
BKUP_LOG=$BKUP_DIR/logs/"$ORACLE_SID"_Full_hot_backup_"$today".log
mkdir -p $BKUP_DIR/$today/hot
mkdir -p $BKUP_DIR/$today/cfile
mkdir -p $BKUP_DIR/$today/alogbkp
rman target / log=$BKUP_LOG<<EOF
run{
allocate channel d1 type disk;
allocate channel d2 type disk;
crosscheck backup;
crosscheck archivelog all;
delete noprompt obsolete;
delete noprompt expired backup;
delete noprompt expired backup of archivelog all;
sql 'alter system archive log current';
BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 0 format '$BKUP_DIR/$today/hot/%d-fullhot-%T-%t-%s-%p.bkp' filesperset 8 DATABASE plus archivelog format '$BKUP_DIR/$today/alogbkp/%d-alogs-fha-%T-%t-%s-%p.archbkp' filesperset 8;
delete noprompt obsolete;
backup current controlfile FORMAT='$BKUP_DIR/$today/cfile/%d-CTRL-%U.ctrl';
delete noprompt archivelog all completed before 'sysdate-4';
release channel d1;
release channel d2;
}
EOF

## Logfile deletion
find /u03/rman_backup/logs/*.log -mtime +2 -print -exec rm {} \;

## Empty Folder deletion
find /u03/rman_backup/ -type d -empty -delete
exit

Crontab:

[oratest@mqmhrd1 rman_backup]$ crontab -l
40 17 * * * /bin/sh /u03/rman_backup/scripts/rman_backup.sh
[oratest@mqmhrd1 rman_backup]$


RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name MQMTEST are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/snapcf_MQMTEST.f'; # default
RMAN>

Migrate Oracle Database Workload To Cloud Re-host (Lift and Shift), Re-platform, Refactor

Migrate Oracle Database Workload To Cloud  Re-host (Lift and Shift), Re-platform, Refactor

When Migrating database and application to cloud, it is very important to keep in mind the business goals, application and database capabilities and cost of migration.

There are different strategies for different application and database migrations goals.

Re-host (Lift and Shift) - Oracle on EC2

-Same administration experience as on-premise   

-Full Control over the environment 

-All feature is available 

-All version is supported 

Re-platform - Oracle on RDS

-Optimized Architecture

-Database Install and Maintenance

-Automated Patching & Upgrade 

-Automated Backup 

-High Availability 

-OS Patching & Maintenance 

-Scaling

Refactor (Rearchitect) - Adopt Cloud Native Services

-Amazon Aurora

-Amazon Redshift

-PostgreSQL

-Other database engine 

-Eliminate Oracle Licensing Cost 

Oracle Database Migration Tools & Techniques

Oracle Database Migration Tools & Techniques

Oracle Native Migrations Tools

• Data Pump
• Transportable Tablespaces
• Full Transportable Export/Import
• Data Guard
• Incremental Backups
• Oracle GoldenGate
• RMAN

AWS Native Migration Tools

• Database Migration Service (DMS)
• CloudEndure
• AWS Application Migration Service

Ebs 12.2 Features / Components / Tools

Ebs 12.2 Features / Components / Tools

 

Long Running Queries Troubleshoot

 Long Running Queries Troubleshoot

To check the OSPD details:

Long_running

To check the SID details:

SID_Info

How to Identify SID based on OSPID in Oracle

How to Identify SID based on OSPID in Oracle

Get the overall process output using TOP command.


Verify the OSPID:

ps -ef|grep 28335
mqmprod 28501 28071 0 14:04:46 pts/1 0:00 grep 28335
mqmprod 28335 1 6 14:00:10 ? 4:06 oracleQPROD (LOCAL=NO)


set lines 200 pages 500
col sid format 99999
col username format a30
col osuser format a15
select p.spid,s.sid, s.serial#,s.username, s.osuser from gv$session s, gv$process p where s.paddr= p.addr and p.spid='&spid' order by p.spid;

==> 28335

Script To Delete Archivelogs In Oracle

Script To Delete Archivelogs In Oracle

Create necessary directories as per the requirement.

mkdir -p /home/oratest/rman_scripts
mkdir -p /home/oratest/rman_scripts/logs

cd /home/oratest/rman_scripts

vi RunDeleteArchive.sh

export ORACLE_SID=MQMTEST
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin:$PATH

rman target / log=/home/oratest/rman_scripts/logs/rman.log <<EOF

run{
crosscheck archivelog all;
delete noprompt archivelog until time 'sysdate -2';
crosscheck archivelog all;
}
EOF
exit

Crontab:

[oratest@MQMPROD rman_scripts]$ crontab -l
# Delete the archived redo log files on daily
00 6,12,18,23 * * * /home/oratest/rman_scripts/RunDeleteArchive.sh


File content:

[oratest@MQMPROD rman_scripts]$ cat /home/oratest/rman_scripts/RunDeleteArchive.sh
export ORACLE_SID=MQMTEST
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin:$PATH

rman target / log=/home/oratest/rman_scripts/logs/rman.log <<EOF
run{
crosscheck archivelog all;
delete noprompt archivelog until time 'sysdate -2';
crosscheck archivelog all;
}
EOF
exit