DBA Jobs details

set markup html on spool on 
spool scheduler_check_norun.html 
set echo on 
alter session set nls_timestamp_tz_format='DD-MON-RR HH.MI.SS AM TZR'; 
alter session set nls_date_format='DD-MON-RR HH.MI.SS AM'; 
show parameter job_queue_processes 
select * from dba_scheduler_jobs; 
select * from dba_scheduler_windows; 
select * from dba_scheduler_window_details where log_date >= sysdate-10 order by log_date desc; 
select * from dba_scheduler_window_log order by log_date desc; 
select * from dba_scheduler_window_groups; 
select * from dba_scheduler_job_run_details where log_date >= sysdate-10 order by log_date desc; 
select * from dba_scheduler_job_log where log_date >= sysdate-10 order by log_date desc; 
spool off 
set markup html off 

ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD'; 
ALTER SESSION SET NLS_DATE_FORMAT ='DD-MM-YYYY HH24:MI:SS'; 
set pagesize 9999 
spool /tmp/dba_autotask_client.html 
set markup html on 
select * from DBA_AUTOTASK_CLIENT; 
select * from DBA_AUTOTASK_CLIENT_HISTORY; 
select * from DBA_AUTOTASK_CLIENT_JOB; 
select * from DBA_AUTOTASK_JOB_HISTORY order by JOB_START_TIME; 
select * from DBA_AUTOTASK_OPERATION; 
select * from DBA_AUTOTASK_SCHEDULE order by START_TIME; 
select * from DBA_AUTOTASK_TASK; 
select * from DBA_AUTOTASK_WINDOW_CLIENTS; 
select * from DBA_AUTOTASK_WINDOW_HISTORY order by WINDOW_START_TIME; 
select * from dba_scheduler_windows; 
select * from dba_scheduler_window_groups; 
select * from dba_scheduler_job_run_details order by ACTUAL_START_DATE; 
select * from DBA_SCHEDULER_JOB_LOG; 
SELECT program_name, program_action, enabled FROM dba_scheduler_programs; 
spool off

Truncate Audit Trail Table AUD$

###Take the export###

exp aud_backup/audit file=/local/oracle/UATWCS7/aud_backup.dmp log=aud_backup.log tables=SYS.AUD$ buffer=5000000

log==>
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Export done in US7ASCII character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
Current user changed to SYS
. . exporting table                           AUD$  929290860 rows exported
EXP-00091: Exporting questionable statistics.

##
NAME                          GBYTES       USED       FREE   PCT_USED    LARGEST   MAX_SIZE PCT_MAX_USED EXTENT_MAN SEGMEN
------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ------------ ---------- ------
SYSTEM                        161.82     159.12        2.7      98.33       1536        157       101.35 LOCAL      MANUAL

##

col owner for a6
col segment_name for a50
select * from
(select owner,segment_name||'~'||partition_name segment_name,bytes/(1024*1024) size_m
from dba_segments
where tablespace_name = 'SYSTEM' ORDER BY BLOCKS desc) where rownum < 11;

OWNER  SEGMENT_NAME                                           SIZE_M
------ -------------------------------------------------- ----------
SYS    AUD$~                                                  160929  ==========>>>>>THIS
SYS    FGA_LOG$~                                                 984
SYS    IDL_UB1$~                                                 256
SYS    SYS_LOB0000000394C00013$$~                                 96
SYS    SOURCE$~                                                   80
SYS    SYS_LOB0000403209C00045$$~                                 71
SYS    C_OBJ#_INTCOL#~                                            37
SYS    IDL_UB2$~                                                  32
SYS    C_TOID_VERSION#~                                           24
SYS    SYS_LOB0000001067C00003$$~                                 20

###script####

#!/bin/bash
export ORACLE_SID=ABC
export ORACLE_HOME=/app/oracle/product/11.2.0/db
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/lib:$PATH
sqlplus "/as sysdba"<<-EOF
set echo on;
set serveroutput on;
set lines 200 pages 200
set time on;
set timing on;
set feedback on
spool TRUNCATE_SYS_AUD.log
Show user;
show parameter db_name
show parameter instance_number
SELECT NAME "DB_NAME", LOG_MODE, OPEN_MODE, RESETLOGS_TIME  FROM V\$DATABASE;
alter session enable parallel ddl;
alter session force parallel ddl parallel 20;
alter session set db_file_multiblock_read_count = 256;
alter session set "_px_max_granules_per_slave"=3000;
alter session set "_px_index_sampling"=3000;
alter session set "_px_dynamic_sample_size"=3000;
ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600;
alter session set parallel_force_local=TRUE;
truncate table aud$;
spool off
EOF
exit

##LOG===>

18:48:51 SQL> alter session enable parallel ddl;

Session altered.

Elapsed: 00:00:00.00
18:48:51 SQL> alter session force parallel ddl parallel 20;

Session altered.

Elapsed: 00:00:00.00
18:48:51 SQL> alter session set db_file_multiblock_read_count = 256;

Session altered.

Elapsed: 00:00:00.01
18:48:51 SQL> alter session set "_px_max_granules_per_slave"=3000;

Session altered.

Elapsed: 00:00:00.00
18:48:51 SQL> alter session set "_px_index_sampling"=3000;

Session altered.

Elapsed: 00:00:00.00
18:48:51 SQL> alter session set "_px_dynamic_sample_size"=3000;

Session altered.

Elapsed: 00:00:00.00
18:48:51 SQL> ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600;

Session altered.

Elapsed: 00:00:00.01
18:48:51 SQL> alter session set parallel_force_local=TRUE;

Session altered.

Elapsed: 00:00:00.00
18:48:51 SQL> truncate table aud$;

Table truncated.

Elapsed: 00:00:02.55
18:48:54 SQL> spool off

###
col owner for a6
col segment_name for a50
select * from
(select owner,segment_name||'~'||partition_name segment_name,bytes/(1024*1024) size_m
from dba_segments
where tablespace_name = 'SYSTEM' ORDER BY BLOCKS desc) where rownum < 11;


OWNER  SEGMENT_NAME                                           SIZE_M
------ -------------------------------------------------- ----------
SYS    FGA_LOG$~                                                 984
SYS    IDL_UB1$~                                                 256
SYS    SYS_LOB0000000394C00013$$~                                 96
SYS    SOURCE$~                                                   80
SYS    SYS_LOB0000403209C00045$$~                                 71
SYS    C_OBJ#_INTCOL#~                                            37
SYS    IDL_UB2$~                                                  32
SYS    C_TOID_VERSION#~                                           24
SYS    C_OBJ#~                                                    20
SYS    SYS_LOB0000001067C00003$$~                                 20

10 rows selected.

#

NAME                          GBYTES       USED       FREE   PCT_USED    LARGEST   MAX_SIZE PCT_MAX_USED EXTENT_MAN SEGMEN

------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ------------ ---------- ------

SYSTEM                        161.82       1.96     159.86       1.21       3968        157         1.25 LOCAL      MANUAL

#

How to Truncate, Delete, or Purge Rows from the Audit Trail Table AUD$ (Doc ID 73408.1)

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=240046292784578&id=73408.1&_afrWindowMode=0&_adf.ctrl-state=12uh2plej6_4

Insufficient SRLs reported by DGMGRL VALIDATE DATABASE-12c

DGMGRL> validate database 'STG_PROD';

  Database Role:    Primary database

  Ready for Switchover:  Yes

DGMGRL> validate database 'stg_prod_dg';

  Database Role:     Physical standby database
  Primary Database:  STG_PROD

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    STG_PROD:     On
    stg_prod_dg:  Off

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (STG_PROD)              (stg_prod_dg)
    1         11                      5                       Insufficient SRLs   ======>>>>>>THIS

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (stg_prod_dg)           (STG_PROD)
    1         11                      5                       Insufficient SRLs  ======>>>>>>THIS



This is because if you see the thread#, it is showing as 0 which is incorrect. it has to be 1 ( stand alone DB)


SQL> set pages 200
SQL> select thread#, group#, sequence#, status, bytes from v$standby_log;

   THREAD#     GROUP#  SEQUENCE# STATUS          BYTES
---------- ---------- ---------- ---------- ----------
         1         12          0 UNASSIGNED  419430400
         1         13         54 ACTIVE      419430400
         1         14          0 UNASSIGNED  419430400
         1         15          0 UNASSIGNED  419430400
         1         16          0 UNASSIGNED  419430400
         0         17          0 UNASSIGNED  419430400
         0         18          0 UNASSIGNED  419430400
         0         19          0 UNASSIGNED  419430400
         0         20          0 UNASSIGNED  419430400
         0         21          0 UNASSIGNED  419430400
         0         22          0 UNASSIGNED  419430400
         0         23          0 UNASSIGNED  419430400

###

SQL> set pages 2000

SQL> select thread#, group#, sequence#, status, bytes from v$standby_log;

   THREAD#     GROUP#  SEQUENCE# STATUS          BYTES

---------- ---------- ---------- ---------- ----------

         1         12          0 UNASSIGNED  419430400

         1         13          0 UNASSIGNED  419430400

         1         14          0 UNASSIGNED  419430400

         1         15          0 UNASSIGNED  419430400

         1         16          0 UNASSIGNED  419430400

         0         17          0 UNASSIGNED  419430400

         0         18          0 UNASSIGNED  419430400

         0         19          0 UNASSIGNED  419430400

         0         20          0 UNASSIGNED  419430400

         0         21          0 UNASSIGNED  419430400

         0         22          0 UNASSIGNED  419430400

         0         23          0 UNASSIGNED  419430400

##Solution.

SQL> alter database drop standby logfile group 17;

alter database drop standby logfile group 17

*

ERROR at line 1:

ORA-01156: recovery or flashback in progress may need access to files

##Stop the MRP on STANDBY###

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database drop standby logfile group 17;

Database altered.

SQL> alter database drop standby logfile group 18;

Database altered.

SQL> alter database drop standby logfile group 19;

Database altered.

SQL> alter database drop standby logfile group 20;

Database altered.

SQL> alter database drop standby logfile group 21;

Database altered.

SQL> alter database drop standby logfile group 22;

Database altered.

SQL> alter database drop standby logfile group 23;

Database altered.

SQL> select thread#, group#, sequence#, status, bytes from v$standby_log;

   THREAD#     GROUP#  SEQUENCE# STATUS          BYTES

---------- ---------- ---------- ---------- ----------

         1         12          0 UNASSIGNED  419430400

         1         13         54 ACTIVE      419430400

         1         14          0 UNASSIGNED  419430400

         1         15          0 UNASSIGNED  419430400

         1         16          0 UNASSIGNED  419430400

SQL> alter database add standby logfile thread 1 group 17 size 419430400;

Database altered.

SQL> alter database add standby logfile thread 1 group 18 size 419430400;

alter database add standby logfile thread 1 group 19 size 419430400;

alter database add standby logfile thread 1 group 20 size 419430400;

alter database add standby logfile thread 1 group 21 size 419430400;

alter database add standby logfile thread 1 group 22 size 419430400;

alter database add standby logfile thread 1 group 23 size 419430400;

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

SQL> select thread#, group#, sequence#, status, bytes from v$standby_log;

   THREAD#     GROUP#  SEQUENCE# STATUS          BYTES

---------- ---------- ---------- ---------- ----------

         1         12          0 UNASSIGNED  419430400

         1         13         54 ACTIVE      419430400

         1         14          0 UNASSIGNED  419430400

         1         15          0 UNASSIGNED  419430400

         1         16          0 UNASSIGNED  419430400

         1         17          0 UNASSIGNED  419430400

         1         18          0 UNASSIGNED  419430400

         1         19          0 UNASSIGNED  419430400

         1         20          0 UNASSIGNED  419430400

         1         21          0 UNASSIGNED  419430400

         1         22          0 UNASSIGNED  419430400

         1         23          0 UNASSIGNED  419430400

12 rows selected.

Do the same on Primary as well. 

SQL> alter database drop standby logfile group 17;

alter database drop standby logfile group 18;

alter database drop standby logfile group 19;

alter database drop standby logfile group 20;

alter database drop standby logfile group 21;

alter database drop standby logfile group 22;

alter database drop standby logfile group 23;

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

SQL>

SQL>

SQL>

SQL> select thread#, group#, sequence#, status, bytes from v$standby_log;

   THREAD#     GROUP#  SEQUENCE# STATUS          BYTES

---------- ---------- ---------- ---------- ----------

         1         12          0 UNASSIGNED  419430400

         1         13          0 UNASSIGNED  419430400

         1         14          0 UNASSIGNED  419430400

         1         15          0 UNASSIGNED  419430400

         1         16          0 UNASSIGNED  419430400

SQL> alter database add standby logfile thread 1 group 17 size 419430400;

alter database add standby logfile thread 1 group 18 size 419430400;

alter database add standby logfile thread 1 group 19 size 419430400;

alter database add standby logfile thread 1 group 20 size 419430400;

alter database add standby logfile thread 1 group 21 size 419430400;

alter database add standby logfile thread 1 group 22 size 419430400;

alter database add standby logfile thread 1 group 23 size 419430400;

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

SQL>

SQL> select thread#, group#, sequence#, status, bytes from v$standby_log;

   THREAD#     GROUP#  SEQUENCE# STATUS          BYTES

---------- ---------- ---------- ---------- ----------

         1         12          0 UNASSIGNED  419430400

         1         13          0 UNASSIGNED  419430400

         1         14          0 UNASSIGNED  419430400

         1         15          0 UNASSIGNED  419430400

         1         16          0 UNASSIGNED  419430400

         1         17          0 UNASSIGNED  419430400

         1         18          0 UNASSIGNED  419430400

         1         19          0 UNASSIGNED  419430400

         1         20          0 UNASSIGNED  419430400

         1         21          0 UNASSIGNED  419430400

         1         22          0 UNASSIGNED  419430400

         1         23          0 UNASSIGNED  419430400

12 rows selected.

## Start the MRP on standby 

SQL> alter database recover managed standby database disconnect from session;

Database altered.

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

PROCESS   STATUS        SEQUENCE#    THREAD#

--------- ------------ ---------- ----------

ARCH      CLOSING              52          1

ARCH      CONNECTED             0          0

ARCH      CLOSING              53          1

ARCH      CLOSING              51          1

RFS       IDLE                 54          1

RFS       IDLE                  0          0

RFS       IDLE                  0          0

RFS       IDLE                  0          0

MRP0      APPLYING_LOG         54          1

9 rows selected.

##Now check the switchover status

DGMGRL> validate database 'STG_PROD';

  Database Role:    Primary database

  Ready for Switchover:  Yes      <-----------Ready for Switchover

DGMGRL> validate database 'stg_prod_dg';

  Database Role:     Physical standby database

  Primary Database:  STG_PROD

  Ready for Switchover:  Yes    <--------This confirms Switchover readiness

  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:

    STG_PROD:     On

    stg_prod_dg:  Off