exec dbms_metadata.set_transform_param( dbms_metadata.session_transform,’SQLTERMINATOR’, TRUE); select dbms_metadata.get_ddl( object_type, object_name, owner ) from all_objects where object_type NOT LIKE ‘%PARTITION’ AND object_type NOT LIKE ‘%BODY’ AND upper(object_name) LIKE upper(CASE WHEN INSTR(‘&1′,’.’) > 0 THEN SUBSTR(‘&1’,INSTR(‘&1′,’.’)+1) ELSE ‘&1’ END ) AND owner LIKE CASE WHEN INSTR(‘&1′,’.’) > 0 THEN UPPER(SUBSTR(‘&1’,1,INSTR(‘&1′,’.’)-1)) ELSE user END /
Monthly Archives: July 2017
Displays information on all long operations
COLUMN sid FORMAT 999 COLUMN serial# FORMAT 9999999 COLUMN machine FORMAT A30 COLUMN progress_pct FORMAT 99999999.00 COLUMN elapsed FORMAT A10 COLUMN remaining FORMAT A10 SELECT s.sid, s.serial#, s.machine, ROUND(sl.elapsed_seconds/60) || ‘:’ || MOD(sl.elapsed_seconds,60) elapsed, ROUND(sl.time_remaining/60) || ‘:’ || MOD(sl.time_remaining,60) remaining, ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct FROM v$session s, v$session_longops sl WHERE s.sid = sl.sid AND s.serial# = […]
Lists all locked objects for whole RAC
COLUMN owner FORMAT A20 COLUMN username FORMAT A20 COLUMN object_owner FORMAT A20 COLUMN object_name FORMAT A30 COLUMN locked_mode FORMAT A15 SELECT b.inst_id, b.session_id AS sid, NVL(b.oracle_username, ‘(oracle)’) AS username, a.owner AS object_owner, a.object_name, Decode(b.locked_mode, 0, ‘None’, 1, ‘Null (NULL)’, 2, ‘Row-S (SS)’, 3, ‘Row-X (SX)’, 4, ‘Share (S)’, 5, ‘S/Row-X (SSX)’, 6, ‘Exclusive (X)’, b.locked_mode) […]
FIND THE ARCHIVE LAG BETWEEN PRIMARY AND STANDBY
select LOG_ARCHIVED-LOG_APPLIED “LOG_GAP” from (SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED=’YES’), (SELECT MAX(SEQUENCE#) LOG_APPLIED FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED=’YES’);
LAST SEQUENCE RECEIVED AND LAST SEQUENCE APPLIED
SQL> SELECT al.thrd “Thread”, almax “Last Seq Received”, lhmax “Last Seq Applied” FROM (select thread# thrd, MAX(sequence#) almax FROM v$archived_log WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) al, (SELECT thread# thrd, MAX(sequence#) lhmax FROM v$log_history WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) lh WHERE al.thrd = lh.thrd; Thread Last Seq Received Last Seq […]
CHECK THE MESSAGES/ERRORS IN STANDBY DATABASE
set pagesize 2000 set lines 2000 col MESSAGE for a90 select message,timestamp from V$DATAGUARD_STATUS where timestamp > sysdate – 1/6; MESSAGE TIMESTAMP —————————————————————————————— ——— RFS[48]: No standby redo logfiles created 05-AUG-15 Media Recovery Log /uv1010/arch/MRSX/arch_MRSX_779539386_1_49481.log 05-AUG-15 Media Recovery Waiting for thread 1 sequence 49482 (in transit) 05-AUG-15 RFS[48]: No standby redo logfiles created 05-AUG-15 Media […]
CHECK THE NUMBER OF ARCHIVES GETTING GENERATING ON HOURLY BASIS:
SELECT TO_CHAR(TRUNC(FIRST_TIME),’Mon DD’) “DG Date”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’00’,1,0)),’9999′) “12AM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’01’,1,0)),’9999′) “01AM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’02’,1,0)),’9999′) “02AM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’03’,1,0)),’9999′) “03AM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’04’,1,0)),’9999′) “04AM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’05’,1,0)),’9999′) “05AM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’06’,1,0)),’9999′) “06AM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’07’,1,0)),’9999′) “07AM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’08’,1,0)),’9999′) “08AM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’09’,1,0)),’9999′) “09AM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’10’,1,0)),’9999′) “10AM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’11’,1,0)),’9999′) “11AM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’12’,1,0)),’9999′) “12PM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’13’,1,0)),’9999′) “1PM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’14’,1,0)),’9999′) “2PM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’15’,1,0)),’9999′) “3PM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’16’,1,0)),’9999′) “4PM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’17’,1,0)),’9999′) “5PM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’18’,1,0)),’9999′) “6PM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’19’,1,0)),’9999′) “7PM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’20’,1,0)),’9999′) “8PM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’21’,1,0)),’9999′) “9PM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’22’,1,0)),’9999′) “10PM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’23’,1,0)),’9999′) “11PM” FROM V$LOG_HISTORY […]
Rolling Forward Standby Database When Archives Missing In Primary In 12c
There can be situation , in dataguard setup, where archive logs are missing from primary , before it was being shipped to standby . Till 11g , we can make standby db sync with primary by taking an incremental backup from primary and apply the same in standby . But with 12c , a new […]
Convert Physical Standby To Snapshot Standby Database
What is snapshot standby: Snapshot standby is a feature in Oracle 11g that allows doing a read-write operation on the standby database. i. e we can convert the physical standby database to snapshot standby. On that, we can do all types of testing or can be used as a development database (which is an exact […]
How to apply PSU patch on standby database
To apply psu patch on physical standby database setup, 1st patch need to be applied on standby, and then on primary. Below are the steps. Download patch from oracle support. Check the database_role for both dbs PRIMARY> select database_role from v$database; DATABASE_ROLE —————- PRIMARY STANDBY> select database_role from v$database; SDATABASE_ROLE —————- PHYSICAL STANDBY 2.Run below […]