Oracle DBACategory

Overview of New PULL method The original “push” process is based on image copies.With Oracle Database 12c, a “pull” (or restore) process is based on backup sets. A connection is first established with the source database. The auxiliary instance then retrieves the required database files from the source database as… Continue Reading Active RMAN duplicate clone 12c using section size and compress backupset

##Connecting RMAN############## rman TARGET SYS/target_pwd@target_str # connects in NOCATALOG mode rman TARGET / CATALOG rman/rman@rcat rman TARGET / CATALOG rman/rman@rcat AUXILIARY sys/aux_pwd@aux_str ##Create user and catalog in RMAN database ######## CREATE USER rman_dba IDENTIFIED BY rman_dba TEMPORARY TABLESPACE temp DEFAULT TABLESPACE rman_dba QUOTA UNLIMITED ON rman_dba; GRANT RECOVERY_CATALOG_OWNER TO rman_dba;… Continue Reading rman command reference

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… Continue Reading Database backup and restore after manual catalog RMAN backup

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.… Continue Reading ORACLE OPTIMIZER COST CALCULATIONS BASIC OVERVIEW

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 ———————————— ———–… Continue Reading Undo Advisor help to estimate the undo tablespace size and undo retention to avoid ORA-1555

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… Continue Reading How to check and implement best practice for Oracle Database 11g onwards

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 :… Continue Reading Duplicate database until Point in Time recover, using backup location from RAC to single instance

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… Continue Reading CDB and PDB concepts in Oracle 12c

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 12.1.0.2.161018 (OCT2016) Prepatch operation log file location: /u01/app/product/12.1.0/grid/cfgtoollogs/crsconfig/hapatch_2016-12-19_01-58-57PM.log… Continue Reading Step by step Upgrade cloud control from 13cR1 to 13cR2 (13.2.0.0.0)

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… Continue Reading Oracle time stamp showing wrong timezone connecting TNS remote