Posts

Showing posts from 2019

blocking sessions and tabelspace

set pages 1000 set long 2000     set colsep '  |  '     set linesize 190     col LOCKED_EVT for a15     col SEC_WAIT for 99999999     col LOCKED_USER for a60     col LOCKER_USER for a60     with t as (         select             a.event as LOCKED_EVT,             a.wait_time_micro/1000000 as LOCKED_WAIT_SEC,             a.osuser as locked_osuser,             a.schemaname as LOCKED_SCHEMANAME,             a.machine as locked_machine,             a.sid as locked_sid,             a.serial# as LOCKED_SERIAL,             a.program as locked_program,             a.Action as locked_Action,     ...
Post a Comment
Read more

Snapshot Standby database and how to convert physical standby database to snapshot standby database (READ WRITE mode)

Snapshot Standby database and how to convert physical standby database to snapshot standby database (READ WRITE mode) SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; Session altered. SQL> SELECT   a.thread#,  b. last_seq, a.applied_seq, a. last_app_timestamp, b.last_seq-a.applied_seq   ARC_DIFF FROM (SELECT  thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp FROM gv$archived_log WHERE applied = 'YES' GROUP BY thread#) a,           (SELECT  thread#, MAX (sequence#) last_seq FROM gv$archived_log GROUP BY thread#) b WHERE a.thread# = b.thread#;    THREAD#   LAST_SEQ APPLIED_SEQ LAST_APP_TIMESTAMP     ARC_DIFF ---------- ---------- ----------- -------------------- ----------          1         75          75 04-DEC-2019 11:09:55          ...
Post a Comment
Read more

EM Query

  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...
1 comment
Read more

Collect statistics on fixed objects using DBMS_STATS.GATHER_FIXED_OBJECTS_STATS.

# 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...
Post a Comment
Read more

Clone RDBMS RAC binaries in 12c in silent mode - one node

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"'      ...
Post a Comment
Read more

A failover is typically used only when the Oracle primary database becomes unavailable

A failover is typically used only when the primary database becomes unavailable, and there is no possibility of restoring it to service within a reasonable period of time Performing a Failover to a Physical Standby Database – Step 01- Flush any unsent redo from the primary database to the target standby database If the primary database can be mounted, it may be possible to flush any unsent archived and current redo from the primary database to the standby database. If this operation is successful, a zero data loss failover is possible even if the primary database is not in a zero data loss data protection mode. Mount, but do not open primary database. If primary database cannot be mounted go to step 2. On Primary database ( if accessible during real disaster ) SQL> ALTER SYSTEM FLUSH REDO TO target_db_name; For target_db_name, specify the DB_UNIQUE_NAME of the standby database that is to receive the redo flushed from the primary database. This statement flushes any unsent...
Post a Comment
Read more