-- Find which logs were applied in the last day SELECT SEQUENCE#, to_char(FIRST_TIME,'hh24:mi:ss dd/mm/yyyy'), to_char(NEXT_TIME,'hh24:mi:ss dd/mm/yyyy'),APPLIED FROM V$ARCHIVED_LOG where next_time>sysdate-1 ORDER BY SEQUENCE# ; -- Find last applied log select to_char(max(FIRST_TIME),'hh24:mi:ss dd/mm/yyyy') FROM V$ARCHIVED_LOG where applied='YES'; -- What are the managed standby processes doing? SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY; -- Are we on production or standby? SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE; -- Check for errors SELECT MESSAGE FROM V$DATAGUARD_STATUS; -- Check that the DB was openned correctly SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS; -- important lag statistics select * from v$dataguard_stats; -- co...
Shutting Down a Physical Standby Database Issue the following query to find out if the standby database is performing Redo Apply or real-time apply. If the MRP0 or MRP process exists, then the standby database is applying redo. SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY; If Redo Apply is running, cancel it as shown in the following example: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Shut down the standby database. SQL> SHUTDOWN IMMEDIATE; Start and mount the physical standby database: SQL> STARTUP MOUNT; Start Redo Apply or real-time apply: To start Redo Apply, issue the following statement: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; To start real-time apply, issue the following statement: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE; ALTER SESSION SET nls_date_format=’DD-MON-YYYY HH24:MI:SS’; select ...
Script – Find SQL being executed by a OS Process ID (PID) prompt "Please Enter The UNIX Process ID" set pagesize 50000 set linesize 30000 set long 500000 set head off select s.username su, substr(sa.sql_text,1,540) txt from v$process p, v$session s, v$sqlarea sa where p.addr=s.paddr and s.username is not null and s.sql_address=sa.address(+) and s.sql_hash_value=sa.hash_value(+) and spid=&SPID; script used to find out cpu used by this session SELECT substr(a.sid,1,5) "Sid", substr(a.process,1,7) "Process", nvl(a.USERNAME,'ORACLE PROC') "user", v.value "CPU used by this session" FROM v$statname s, v$sesstat v, v$session a WHERE s.name = 'CPU used by this session' and v.statistic#=s.statistic# AND v.sid = a.sid and v.value > 0 ORDER BY v.value DESC; Top 10 cpu heavy SQLs set pagesize 999 set linesize 150 col cpu_time format 9999999999 heading "Cputime" col sql_text f...
Comments
Post a Comment