Oracle Database Administration

  Snapshots (take snaps before & after test): – exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;    AWR Report (@?/rdbms/admin/awrrpt.sql) – Workload Repository Report – Top events to start    ADDM report (@?/rdbms/admin/addmrpt.sql) – Oracle provided recommendations for further analysis    ASH Report (use particularly for concurrency issues) – @?/rdbms/admin/ashrpt.sql    SQR Report (With sqlid get the explain plan) – @?/rdbms/admin/awrsqrpt.sql tune sql @$ORACLE_HOME/rdbms/admin/sqltrpt.sql set linesize 200 col BEGIN_INTERVAL_TIME format a70 select * from (select snap_id,begin_interval_time from dba_hist_snapshot order by begin_interval_time desc) where rownum < 3; --------------------------------------------- Set pages 1000 Set lines 75 Select a.execution_end, b.type, b.impact, d.rank, d.type,  'Message           : '||b.message MESSAGE, 'Command To correct: '||c.command CO...

# Fixed objects stats  1. Check fixed object stats               select count(1) from tab_stats$;  2. Gather fixed objects stats            exec dbms_stats.gather_fixed_objects_stats; 3. Check fixed object stats                select count(1) from tab_stats$;  # Gather system stat   execute dbms_stats.gather_system_stats('Start'); -- few hour delay during high workload execute dbms_stats.gather_system_stats('Stop'); # Flush shared pool Alter system flush shared_pool;  Roll back if the change is not successful: 1. exec dbms_stats.delete_fixed_objects_stats(); 2. exec DBMS_STATS.DELETE_SYSTEM_STATS; --------------------------------------------------------------------------- http://www.dba-oracle.com/t_dbms_stats_gather_system_stats.htm The dbms_stats.gather_system_stats procedure is especially useful for multi-m...

Clone RDBMS RAC binaries in 12c in silent mode Copy binaries from  /h01/app/oracle/db/12.1.0.2_03  to /h01/app/oracle/db/12.1.0.2_02  /h01/app/oracle/db/12.1.0.2_03 > cp -Rp /h01/app/oracle/db/12.1.0.2_02 export ORACLE_HOME=/h01/app/oracle/db/12.1.0.2_02 export PATH=$ORACLE_HOME/bin:$PATH 2. Run the clone command in silent mode on both the DB hosts one after the other as “oracle” user perl  /h01/app/oracle/db/12.1.0.2_02/clone/bin/clone.pl -silent ORACLE_HOME="/h01/app/oracle/db/12.1.0.2_02" ORACLE_HOME_NAME="OraDB12Home2" ORACLE_BASE="/h01/app" '-O"CLUSTER_NODES={raj}"' '-O"LOCAL_NODE=raj"' oracle@raj (SID:DEMO) /h01/app/oracle/db/12.1.0.2_02 $ export ORACLE_HOME=/h01/app/oracle/db/12.1.0.2_02 oracle@raj (SID:DEMO) /h01/app/oracle/db/12.1.0.2_02 $ export PATH=$ORACLE_HOME/bin:$PATH oracle@raj (SID:DEMO) /h01/app/oracle/db/12.1.0.2_02 $ DES={raj}"' '-O"LOCAL_NODE=raj"'      ...