USER + PRIVILEGES + ROLES  ::



======== > Account Lock <===

select username,account_status from dba_users where account_status like '%LOCK%';

SQL > alter user XYZ account unlock;


 =====> Schema <============
 select owner, sum(bytes)/1024/1024/1024 schema_size_gig from dba_segments group by owner;

 select username from dba_users where username like '%XYZ%';

 alter session set current_schema=XYZ;

  select   sum(bytes)/1024/1024/1024 as size_in_gig, segment_type from  dba_segments where owner='XXX' group by segment_type;

 TABLE_NAME :
 ===========
 select TABLE_NAME ,STATUS,OWNER from dba_tables where OWNER='XXX'

 SQL> select TABLE_NAME ,STATUS,OWNER from dba_tables where table_name='XXX';

 * select OBJECT_NAME ,OWNER from dba_objects where OBJECT_NAME like '%LINK%' and owner='XXX'

  select segment_name,segment_type,bytes/1024/1024 MB from dba_segments where segment_type='TABLE' and segment_name='XXX';

 ============= USER CREATION + GRANT PRIVILIGES ===============

select username from dba_users where username like '%XXX%';
SQL> create user xxx identified by xxx;

SQL> grant create session to  xxxx;



>>>>>
    select role from dba_roles where role like '%READ_ONLY%';

>>>  select 'grant select on SHEMANAME.'||table_name|| '  to USERNAME;' from dba_tables where owner='XXXX';


======== USER MAP ==================

 select role from dba_roles where role like '%READ_ONLY%';

select * from DBA_ROLE_PRIVS where GRANTEE=upper('&user');
select grantee,table_name,privilege from dba_tab_privs where grantee='&rolename';

=========


The best way I believe is the take a spool of the grants and execute them once after the import is complete
==> ROLES
select 'grant '||PRIVILEGE||' on '||OWNER||'.'||TABLE_NAME||' to XXTEST;' from dba_tab_privs where GRANTEE='XXTEST' ;
 ===============================================

    DATA GUARD Handy:

************************************************************************************************************************************************************
primay :   SELECT MESSAGE FROM V$DATAGUARD_STATUS;


SELECT to_char(CURRENT_SCN) FROM V$DATABASE;

SQL> select name, open_mode, database_role from v$database;

select max(sequence#) from v$log_history;

Check redo received and applied on standby:
==========================================

SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;
SELECT SEQUENCE#, APPLIED ,COMPLETION_TIME ,STAMP FROM V$ARCHIVED_LOG where COMPLETION_TIME like '%06-JUL-18%';

SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG  WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG
GROUP BY THREAD#) ORDER BY 1;

select process,status,sequence# from v$managed_standby;

Identify the missing archive log file.

 SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;


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

———- ————–

         1             22

         2             14

         3             12
 ************************************************************************************************************************************************************

 Crosscheck this :
   
 
  Check redo received and applied on standby.
 
  SELECT ARCH.THREAD#  , ARCH.SEQUENCE#  , APPL.SEQUENCE#  , (ARCH.SEQUENCE# – APPL.SEQUENCE#)  FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;



Identify the missing archive log file.

 SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

col NAME for a70
select SEQUENCE#,THREAD#,STATUS,APPLIED,NAME from v$archived_log where APPLIED='YES' order by NAME;

col NAME for a70
select SEQUENCE#,THREAD#,STATUS,APPLIED,NAME from v$archived_log where APPLIED='NO';

col NAME for a70
select SEQUENCE#,THREAD#,STATUS,APPLIED,NAME from v$archived_log where APPLIED='YES' AND SEQUENCE# BETWEEN 80886 AND 81999;
use first query

 =========================
  set head off
set echo off
set feedback off
SELECT (ARCH.SEQUENCE# - APPL.SEQUENCE#) difference
FROM
(SELECT DISTINCT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH
,
(SELECT DISTINCT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;

===================  ARCH Difference query================================

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received",
APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
==========================================
errors

select status , error from v$archive_dest where des_id=2;

select process,client_process,status,thread#,sequence#,block#,blocks from v$managed_standby;


SQL> select process,client_process,status,thread#,sequence#,block#,blocks from v$managed_standby;

PROCESS   CLIENT_P STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- -------- ------------ ---------- ---------- ---------- ----------
ARCH      ARCH     CONNECTED             0          0          0          0
ARCH      ARCH     CONNECTED             0          0          0          0
ARCH      ARCH     CONNECTED             0          0          0          0
ARCH      ARCH     CONNECTED             0          0          0          0
MRP0      N/A      WAIT_FOR_LOG          1     169866          0          0
RFS       LGWR     RECEIVING             1     169990     242070          8
RFS       ARCH     IDLE                  0          0          0          0


SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG where APPLIED='NO' ;



alter database recover managed standby database cancel

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect from session
alter database recover managed standby database disconnect from session;

============  REAL TIME APPLY =======

select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

  select database_role , open_mode from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL> select name ,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
WFLPRD    READ ONLY WITH APPLY

SQL> select dest_id , recovery_mode from v$archive_dest_status where dest_id=1;

   DEST_ID RECOVERY_MODE
---------- -----------------------
         1 MANAGED




===========================