ADOP Patching Basics
Sunday, August 28, 2022
Tuesday, August 23, 2022
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
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
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
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
Sunday, August 14, 2022
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>
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>
Wednesday, August 10, 2022
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
Long Running Queries Troubleshoot
Long Running Queries Troubleshoot
To check the OSPD details:
To check the SID details:
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
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