Database backup and restore after manual catalog RMAN backup

Database backup and restore after manual catalog RMAN backup 1.Please take backup of primary database ORCL rman target / nocatalog log=/tmp/rman_bkp.log << EOF1 run { backup as compressed backupset database format ‘/opt/app/oratest1/bkp/ORCL_%U’; backup as compressed backupset archivelog all format ‘/opt/app/oratest1/bkp/ORCL_ARCH_%U’; } exit; EOF1 Please copy it in backup location to /opt/app/oratest1/bkp 2.Please start nomount RCATT database. 3.Please create controlfile from […]


Cost Calculation for Full table Scan How FTS cost depends on system statistics Gather statistics manually on load: The following command will start to gather system statistics. SQL> execute DBMS_STATS.GATHER_SYSTEM_STATS (‘START’); Please wait for system warm up and stop gather system statistics after work load. This is called workload statistics. The MBRC will be calculated automatically depending on no. of […]

Undo Advisor help to estimate the undo tablespace size and undo retention to avoid ORA-1555

Prepare by: Nurullah Sharif Scope: Undo Advisor   Undo Advisor help to estimate the undo tablespace size and also advise of undo retention. SQL> @db NAME      OPEN_MODE ——— ——————– COLLPROD  READ WRITE   Undo retention is 900 sec which 15 min SQL> sho parameter undo NAME                                 TYPE        VALUE ———————————— ———– —————————— undo_management                      string      AUTO undo_retention                       integer     900 undo_tablespace                      string      UNDOTBS2 […]

How to check and implement best practice for Oracle Database 11g onwards

Author: Saibal Ghosh About author: I am an Oracle professional with twenty plus years of experience and have deep knowledge and understanding of various facets of Oracle technology including basic Oracle Database Administration, Performance Tuning, Real Application Clusters, Data Guard and Maximum Availability Architecture. I also work a lot in the Oracle Database Cloud space, and I believe that the […]

Duplicate database until Point in Time recover, using backup location from RAC to single instance

Prepare by:  Nurullah Sharif Scope: Point in Time Recovery   Duplicate database until Point in Time recover, using backup location. #PointInTimeRecovery #DuplicateDatabase #RestoreDatabaseUsingBackupLocation   We are using full backup of 28-11-2017 and archivelog backup of 29-11-2017   Step 1: Take full backup and archive log backup. On target database : Rman target / run { backup as compressed BACKUPSET incremental […]

CDB and PDB concepts in Oracle 12c

Oracle introduced PDB from 12c to minimize following drawbacks in 11g   In 11g,sometimes we need to create numerous databases in single host which resulted below side effects and eventually slowed down whole system. 1.Too many background processes. 2.High shared memory. 3.Many copies of oracle metadata. Now We will delve into more deeper what Oracle 12c PDB concepts bring to […]

Index creation progress in oracle

———-Progress index creation————– set lines 200 col “Index Operation” for a60 select sess.sid as “Session ID”, sql.sql_text as “Index Operation”, longops.totalwork, longops.sofar, longops.elapsed_seconds/60 as “Runtime Mins”, longops.time_remaining/60 as “ETA Mins” from v$session sess, v$sql sql, v$session_longops longops where sess.sid=longops.sid and sess.sql_address = sql.address and sess.sql_address = longops.sql_address and sess.status = ‘ACTIVE’ and longops.totalwork > longops.sofar and sess.sid not in ( […]

Step by step Upgrade cloud control from 13cR1 to 13cR2 (

Prerequisites for Upgrading to Enterprise Manager Cloud Control 13c Release 2 1.First latest PSU patch needs to be applied in GRID and ORACLE home. In my case latest patch of that time was as below Patch 24412235: GRID INFRASTRUCTURE PATCH SET UPDATE (OCT2016) Prepatch operation log file location: /u01/app/product/12.1.0/grid/cfgtoollogs/crsconfig/hapatch_2016-12-19_01-58-57PM.log CRS service brought down successfully on home /u01/app/product/12.1.0/gridStart applying binary […]

Oracle time stamp showing wrong timezone connecting TNS remote

Time stamp problem:- sqlplus system/****@ORCL SQL>alter session set nls_date_format=’dd-mm-yyyy hh:mi:ss’; SQL>select instance_name from v$instance; SQL>select   sysdate,   current_timestamp,   systimestamp,   localtimestamp from   dual; The above query will show different time . How to Fix:- 1.1                               Stop the apps and DB cluster/HAS. su – root cd /orasw/app/grid/product/11.2.0/grid/bin ./crsctl stop crs –f su – grid cd /orasw/app/grid/product/11.2.0/grid/bin ./crsctl stop […]

PostgreSQL Database Backup to S3 and Restore from S3 using simple shell script

Following topic will discuss how to Backup and Restore PostgreSQL Databases directly in S3. This topic will help you to leverage AWS S3 -IA storage to use as Backup Storage for any number of PostgreSQL databases running under an instance. This topic will cover PostgreSQL Instance running on EC2 or on-premises servers. Step 1:- Create a new mount point or directory […]