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,
            a.Module as locked_Module,
            prior a.sid as LOCKER_SID,
            prior a."SERIAL#" as LOCKER_SERIAL,
            prior a.osuser as locker_osuser,
            prior a.schemaname as LOCKER_SCHEMANAME,
            prior a.machine as locker_mashine,
            prior a.program as locker_program,
            prior a.Action as locker_Action,
            prior a.Module as locker_Module,
            prior a.seconds_in_wait as LOCKER_SECONDS_IN_WAIT,
            a.sql_id as locked_sql_id,
            a.prev_sql_id as locked_prev_sql_id,
            prior a.sql_id as locker_sql_id,
            prior a.prev_sql_id as locker_prev_sql_id,
            prior a.sql_address,
            prior a.event as LOCKER_EVT,
            a.final_blocking_session,
            level lvl,
            prior a.status
        from
            gv$session a
        where
            a.blocking_session is not null
        and
            level>1
        connect by
            nocycle prior a.sid= a.blocking_session
        start with
            a.blocking_session is null
    )
    select
        t.LOCKED_WAIT_SEC as SEC_WAIT,
        t.LOCKED_EVT,
        'Sid: ' || t.locked_sid || chr(10) ||
            'Serial: ' || t.LOCKED_SERIAL || chr(10) ||
            'OS user: ' || t.locked_osuser  || chr(10) ||
            'Schema: ' || t.LOCKED_SCHEMANAME || chr(10)||
            'Machine: ' || t.locked_machine || chr(10) ||
            'Program: ' || locked_program || chr(10) ||
            'ACTION: ' || locked_Action || chr(10) ||
            'Module: ' || locked_Module || chr(10) || chr(10) ||
            'Curr sql: ' || t.locked_sql_id || chr(10)||
            SUBSTR((select s.sql_text from v$sql s where s.sql_id=t.locked_sql_id and s.loaded_versions=1  and rownum=1),0,250) || chr(10) || chr(10) ||
            'Prev sql: ' || t.locked_prev_sql_id || chr(10)||
            SUBSTR((select s.sql_text from v$sql s where s.sql_id=t.locked_prev_sql_id and s.loaded_versions=1 and rownum=1),0,250) LOCKED_USER,
        'Sid: ' || t.LOCKER_SID || chr(10) ||
            'Serial: ' || t.LOCKER_SERIAL || chr(10) ||
            'OS user: ' || t.locker_osuser  || chr(10) ||
            'Schema: ' || t.LOCKER_SCHEMANAME || chr(10)||
            'Machine: ' || t.locker_mashine || chr(10) ||
            'Program: ' || locker_program || chr(10) ||
            'Action: ' || locker_Action || chr(10) ||
            'Module: ' || locker_Module || chr(10) || chr(10) ||
            'Curr sql: ' || t.locker_sql_id || chr(10)||
            SUBSTR((select s.sql_text from v$sql s where s.sql_id=t.locker_sql_id and s.loaded_versions=1 and rownum=1),0,250) || chr(10) || chr(10) ||
            'Prev sql: ' || t.locker_prev_sql_id || chr(10)||
            SUBSTR((select s.sql_text from v$sql s where s.sql_id=t.locker_prev_sql_id and s.loaded_versions=1 and rownum=1),0,250) LOCKER_USER
    from
        t
    -- where locked_evt like 'enq%'
    order by
        lvl desc,decode(substr(locked_evt,1,3),'enq',0,'log',2,1), LOCKED_WAIT_SEC desc
    /


Tablespace usage

set lines 160
col name for a30
col file_name for a60
set pages 1000
select (select decode(extent_management,'LOCAL','*',' ')
          from dba_tablespaces where tablespace_name = b.tablespace_name) ||
nvl(b.tablespace_name,
             nvl(a.tablespace_name,'UNKOWN')) name,
       Mega_alloc Mega,
       Mega_alloc-nvl(Mega_free,0) used,
       nvl(Mega_free,0) free,
       ((Mega_alloc-nvl(Mega_free,0))/
                          Mega_alloc)*100 pct_used,
       nvl(largest,0) largest,
       nvl(Mega_max,Mega_alloc) Max_Size,
       decode( Mega_max, 0, 0, (Mega_alloc/Mega_max)*100) pct_max_used
from ( select sum(bytes)/1024/1024 Mega_free,
              max(bytes)/1024/1024 largest,
              tablespace_name
       from  sys.dba_free_space
       group by tablespace_name ) a,
     ( select sum(bytes)/1024/1024 Mega_alloc,
              sum(maxbytes)/1024/1024 Mega_max,
              tablespace_name
       from sys.dba_data_files
       group by tablespace_name
       union all
      select sum(bytes)/1024/1024 Mega_alloc,
              sum(maxbytes)/1024/1024 Mega_max,
              tablespace_name
       from sys.dba_temp_files
       group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
and a.tablespace_name='&tablespace_name' order by 5;

select file_name,bytes/1024/1024/1024,maxbytes/1024/1024/1024,AUTOEXTENSIBLE,status,INCREMENT_BY from dba_data_files where tablespace_name = '&NAME' order by 2 desc;

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          0
         2         69          69 04-DEC-2019 11:07:26          0

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
MOUNTED      RAJDBA           PHYSICAL STANDBY MOUNTED

SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1             75
         2             69

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /fra
db_recovery_file_dest_size           big integer 820G
SQL> alter database flashback_on on;
alter database flashback_on on
                            *
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE


SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active


SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

Once the standby database is mounted, convert the Physical standby database to snapshot standby


SQL> alter database convert to snapshot standby;

Database altered.

SQL> alter database open;

Database altered.

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN         RAJDBA           SNAPSHOT STANDBY READ WRITE

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
SET VERIFY OFF

COLUMN scn FOR 999,999,999,999,999
COLUMN Incar FOR 99
COLUMN name FOR A25
COLUMN storage_size FOR 999,999,999,999
COLUMN guarantee_flashback_database FOR A3

SELECT 
      database_incarnation# as Incar,
      scn,
      name,
      time,
      storage_size,
      guarantee_flashback_database
FROM 
      v$restore_point
ORDER BY 4
/
INCAR                  SCN NAME                      TIME                                                                            STORAGE_SIZE GUA
----- -------------------- ------------------------- --------------------------------------------------------------------------- ---------------- ---
    3    6,086,495,469,099 SNAPSHOT_STANDBY_REQUIRED 04-DEC-19 11.24.09.000000000 AM                                                  209,715,200 YES
                           _12/04/2019 11:24:09

SQL> select open_mode from v$database;
Press Return to Continue

OPEN_MODE
--------------------
READ WRITE

SQL> !hostname
rajhost

SQL>


Steps on converting back a snapshot standby database to physical standby database.



SQL> select open_mode from v$database;
Press Return to Continue

OPEN_MODE
--------------------
READ WRITE

SQL> !hostname
rajhost

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Press Return to Continue

Total System Global Area 1.0737E+10 bytes
Fixed Size                  3722144 bytes
Variable Size            1275069536 bytes
Database Buffers         9428795392 bytes
Redo Buffers               29831168 bytes
Database mounted.
SQL> alter database convert to physical standby;

Database altered.

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;
Press Return to Continue

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
MOUNTED      RAJDBA           PHYSICAL STANDBY MOUNTED

SQL>

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING ARCHIVED LOGFILE DISCONNECT;

Database altered.

SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;
Press Return to Continue

RECOVERY_MODE
-----------------------
MANAGED