failover testing

select name,open_mode,database_role from v$database; NAME      OPEN_MODE            DATABASE_ROLE --------- -------------------- ---------------- RDP    READ WRITE           PRIMARY

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS'; Session altered.

select sequence#,first_time,next_time,applied from v$archived_log order by sequence#; SEQUENCE# FIRST_TIME           NEXT_TIME            APPLIED ---------- -------------------- -------------------- ---------        491 01-OCT-19 02-OCT-19 NO        491 01-OCT-19 02-OCT-19 YES        492 02-OCT-19 02-OCT-19 YES        492 02-OCT-19 02-OCT-19 NO

select switchover_status from v$database; SWITCHOVER_STATUS -------------------- TO STANDBY

alter system archive log current;

alter database commit to switchover to standby; database altered; or alter database commit to switchover to standby with session shutdown;

shut immedite

startup nomount

alter database mount standby database; database altered;

alter database recover managed standby database disconnect from session;

alter system set log_archive_dest_state_2=defer;

Standby database  SDEFR2PB02

select name,open_mode,database_role from v$database; NAME      OPEN_MODE            DATABASE_ROLE --------- -------------------- ---------------- PRD    MOUNTED              PHYSICAL STANDBY

alter database recover managed standby database cancel;

alter database commit to switchover to primary with session shutdown; or alter database commit to switchover to primary;

shut immediate

startup mount

Alter database open; database altered.

SQL> select INSTANCE_NAME,name,open_mode,database_role from v$database,v$instance; INSTANCE_NAME    NAME      OPEN_MODE            DATABASE_ROLE ---------------- --------- -------------------- ---------------- RDSBY        PRD    READ WRITE           PRIMARY SQL> ! [oracle@SDEFRB02 ~]$ date Wed Oct  2 09:12:57 CEST 2019

Alter system switch logfile

SQL> alter system switch logfile   2  ; System altered. SQL> ! [oracle@SDEFB02 ~]$ date Wed Oct  2 09:15:53 CEST 2019

CONNECTIVITY CHECKING BY O & M TEAM

[oracle@SDEFB02 ~]$ sqlplus SQL*Plus: Release 18.0.0.0.0 - Production on Wed Oct 2 09:16:37 2019 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle.  All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0 SQL> show user; USER is "GBO_"

Gather stats example 2

To check tables and indexes last analyzed date and Database stats

set pages 200

col index_owner form a10
col table_owner form a10
col owner form a10

spool checkstat.lst

PROMPT Regular Tables

select owner,table_name,last_analyzed, global_stats
from dba_tables
where owner not in (‘SYS’,’SYSTEM’)
order by owner,table_name
/

PROMPT Partitioned Tables

select table_owner, table_name, partition_name, last_analyzed, global_stats
from dba_tab_partitions
where table_owner not in (‘SYS’,’SYSTEM’)
order by table_owner,table_name, partition_name
/

PROMPT Regular Indexes

select owner, index_name, last_analyzed, global_stats
from dba_indexes
where owner not in (‘SYS’,’SYSTEM’)
order by owner, index_name
/

PROMPT Partitioned Indexes

select index_owner, index_name, partition_name, last_analyzed, global_stats
from dba_ind_partitions
where index_owner not in (‘SYS’,’SYSTEM’)
order by index_owner, index_name, partition_name
/

spool off

To check last collected stats for database

a) Show the current stats history configuration:

select dbms_stats.get_stats_history_availability from dual;

b) Show the current history level:

select dbms_stats.get_stats_history_availability from dual;

c) Disable automatic purge ( -1 = statistics history never purged by autopurge):

exec dbms_stats.alter_stats_history_retention(-1);

d) Purge by hand running successively:

exec dbms_stats.purge_stats(sysdate-&days);

using &days = n, n-1, n-2, …, n-x

e) Show the new history level

select dbms_stats.get_stats_history_availability from dual;

This should show that the GET_STATS_HISTORY_AVAILABILITY is indeed equal to sysdate – (n-x).

After they are purged, set the desired retention.

It is recommended to set &days to purge little by little.

AWR load spikes

AWR query to find load spikes
select
to_char(round(sub1.sample_time, ‘HH24′), ‘YYYY-MM-DD HH24:MI’) as sample_hour,
round(avg(sub1.on_cpu),1) as cpu_avg,
round(avg(sub1.waiting),1) as wait_avg,
round(avg(sub1.active_sessions),1) as act_avg,
round( (variance(sub1.active_sessions)/avg(sub1.active_sessions)),1) as act_var_mean
from
( — sub1: one row per ASH/AWR sample observation
select
sample_id,
sample_time,
sum(decode(session_state, ‘ON CPU’, 1, 0))  as on_cpu,
sum(decode(session_state, ‘WAITING’, 1, 0)) as waiting,
count(*) as active_sessions
from
dba_hist_active_sess_history
where
sample_time > sysdate – (&hours/24)
group by
sample_id,
sample_time
) sub1
group by
round(sub1.sample_time, ‘HH24′)
order by
round(sub1.sample_time, ‘HH24′)
;

Gather stats database

set lines 100
col operation form a40 wrap head 'operation(on)'
col target form a1
spool show_auto_stat_runs.lst
select operation||decode(target,null,null,'-'||target) operation
      ,to_char(start_time,'YYMMDD HH24:MI:SS.FF4') start_time
      ,to_char(  end_time,'YYMMDD HH24:MI:SS.FF4') end_time
from dba_optstat_operations
order by start_time desc
/




EXEC DBMS_STATS.gather_database_stats;
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15, cascade => TRUE);

EXEC DBMS_STATS.gather_schema_stats('SCOTT');
EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15);
EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15, cascade => TRUE);

EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES');
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES', estimate_percent => 15);
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES', estimate_percent => 15, cascade => TRUE);

EXEC DBMS_STATS.gather_dictionary_stats;



set pages 50000 lines 32767
col owner format a15
col object_type format a20
col object_name format a30
select owner, object_name, object_type,status,created,last_ddl_time from dba_objects where object_name in ('&object_name') and owner='&owner' order by object_type, object_name desc
/


To Gathers statistics for all objects in a schema

SQL> select owner, table_name,last_analyzed from dba_tables where owner='SCOTT' order by last_analyzed;

SQL> select ocwner, index_name, last_analyzed from dba_indexes where owner='SCOTT' order by last_analyzed;

SQL> exec dbms_stats.gather_schema_stats(ownname => 'SCOTT', options => 'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

SQL> select owner, table_name,last_analyzed from dba_tables where owner='SCOTT' order by last_analyzed;

SQL> select owner, index_name, last_analyzed from dba_indexes where owner='SCOTT' order by last_analyzed;


select client_name, status from dba_autotask_operation;

CLIENT_NAME                                      STATUS
---------------------------------------------- ------------
auto optimizer stats collection                ENABLED
auto space advisor                             ENABLED
sql tuning advisor                             ENABLED



missing index



select * from (
  select 'the column ' || c.name || ' of the table ' || us.name || '.' || o.name || ' was used ' || u.equality_preds || ' times in an equality predicate and ' || u.equijoin_preds || ' times in an equijoin predicate and is not indexed' as colum_to_index
  from sys.col_usage$ u,
       sys.obj$ o,
       sys.col$ c,
       sys.user$ us
  where u.obj# = o.obj#
  and   u.obj# = c.obj#
  and   us.user# = o.owner#
  and   u.intcol# = c.col#
  and   us.name='&SCHEMA_NAME'
  and   c.name not in (select column_name from dba_ind_columns where index_owner ='&SCHEMA_NAME')
  and   (u.equality_preds > 100 OR u.equijoin_preds > 100)
  order by u.equality_preds+u.equijoin_preds desc)
WHERE rownum <11;


To Gather statistics for tables in a schema

SQL>exec dbms_stats.gather_table_stats(ownname=>'SCOTT', tabname => 'EMP',estimate_percent=> 100,cascade => true);


SELECT * FROM dba_autotask_schedule;



select m.TABLE_OWNER,
 m.TABLE_NAME,
 m.INSERTS,
 m.UPDATES,
 m.DELETES,
 m.TRUNCATED,
 m.TIMESTAMP as LAST_MODIFIED,
 round((m.inserts+m.updates+m.deletes)*100/NULLIF(t.num_rows,0),2) as EST_PCT_MODIFIED,
 t.num_rows as last_known_rows_number,
 t.last_analyzed
From dba_tab_modifications m,
 dba_tables t
where m.table_owner=t.owner
and m.table_name=t.table_name
and table_owner not in ('SYS','SYSTEM')
and ((m.inserts+m.updates+m.deletes)*100/NULLIF(t.num_rows,0) > 10 or t.last_analyzed is null)
order by timestamp desc;

long running session

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# = sl.serial#;


***************************************************


SELECT l.sid, l.start_time, l.username, l.elapsed_seconds,
a.sql_text, a.elapsed_time
FROM v$session_longops l, v$sqlarea a
WHERE a.elapsed_time = l.elapsed_seconds
AND l.elapsed_seconds > 1;

onstat

• onstat Portal: onstat Utility Commands Sorted by Functional Category
  
  The information in this topic lists onstat commands that are sorted by functional category.
• Monitor the database server status
  
  To monitor the database server status, view the heading of the onstat command.
• onstat command syntax
  
  The complete syntax for the onstat command, including information about the interactive mode and how to have options to execute repeatedly.
• onstat command: Equivalent to the onstat -pu command
  
  If you invoke onstat without any options, the command is interpreted as onstat -pu (the -p option and the -u option).
• onstat - command: Print output header
  
  All onstat output includes a header. The onstat - command displays only the output header and the value that is returned from this command indicates the database server mode.
• onstat -- command: Print onstat options and functions
  
  Use the onstat -- command to display a listing of all of the onstat options and their functions. You cannot combine this option with any other flag.
• Running onstat Commands on a Shared Memory Dump File
  
  You can run onstat commands against a shared memory dump file. The shared memory dump file can be produced explicitly by using the onstat -o command. If the DUMPSHMEM configuration parameter is set to 1 or set to 2, the dump file is created automatically at the time of an assertion failure.
• onstat -a command: Print overall status of the database server
  
  Use the onstat -a command to display information about the status of the database server. This command does not display information about all of the onstat options, only about those onstat options used for initial troubleshooting.
• onstat -b command: Print buffer information for buffers in use
  
  Use the onstat -b option to display information about the buffers that are currently in use, including the total number of resident pages in the buffer pool.
• onstat -B command: Prints information about used buffers
  
  Use the onstat -B option to display information about buffers that are not on the free-list.
• onstat -c command: Print ONCONFIG file contents
  
  Use the onstat -c command to display the contents of the ONCONFIG file.
• onstat -C command: Print B-tree scanner information
  
  Use the -C command to display information about the B-tree scanner subsystem and each B-tree scanner thread.
• onstat -d command: Print chunk information
  
  Use the onstat -d command to show information about chunks in each storage space.
• onstat -D command: Print page-read and page-write information
  
  Use the onstat -D command to display page-read and page-write information for the first 50 chunks in each space.
• onstat -f command: Print dbspace information affected by dataskip
  
  Use the -f command to list the dbspaces that the dataskip feature currently affects.
• onstat -F command: Print counts
  
  Use the onstat -F command to display a count for each type of write that flushes pages to disk.
• onstat -g monitoring options
  
  The options that you can use with onstat -g command are used for support and debugging only. You can include only one of these options in the onstat -g command.
• onstat -G command: Print TP/XA transaction information
  
  Use the onstat -G command to display information about global transactions generated through the TP/XA library.
• onstat -h command: Print buffer header hash chain information
  
  Use the onstat -h command to display information about the buffer header hash chains (sometimes called "hash buckets") that are used to access pages in each buffer pool.
• onstat -i command: Initiate interactive mode
  
  Use the onstat -i command to put the onstat utility in the interactive mode.
• onstat -j command: Provide onpload status information
  
  Use the onstat -j command to provide information about the status of an onpload job.
• onstat -k command: Print active lock information
  
  Use the onstat -k command to print information about active locks, including the address of the lock in the lock table.
• onstat -l command: Print physical and logical log information
  
  Use the onstat -l command to display information about the physical logs, logical logs, and temporary logical logs.
• onstat -L command: Print the number of free locks
  
  Use the onstat -L command to print the number of free locks on a lock-free list.
• onstat -m command: Print recent system message log information
  
  Use the onstat -m command to display the 20 most recent lines of the system message log.
• onstat -o command: Output shared memory contents to a file
  
  Use the onstat -o command to write the contents of shared memory to a specified file for later analysis. If you do not specify an output file, a file named onstat.out is created in the current directory.
• onstat -p command: Print profile counts
  
  Use the onstat -p command to display information about profile counts either since you started the database server or since you ran the onstat -z command.
• onstat -P command: Print partition information
  
  Use the onstat -P command to display the partition number and the pages in the buffer pool for all of the partitions.
• onstat -r command: Repeatedly print selected statistics
  
  Use the onstat -r command to repeatedly print the statistics for other options specified in the command at specified intervals.
• onstat -R command: Print LRU, FLRU, and MLRU queue information
  
  Use the onstat -R command to display detailed information about the LRU queues, FLRU queues, and MLRU queues. For each queue, the onstat -R command displays the number of buffers in the queue and the number and percentage of buffers that have been modified.
• onstat -s command: Print latch information
  
  Use the onstat -s command to display general latch information, including the resource that the latch controls.
• onstat -t and onstat -T commands: Print tblspace information
  
  Use the onstat -t command to display tblspace information for active tblspaces. Use the onstat -T command to display tblspace information for all tblspaces.
• onstat -u command: Print user activity profile
  
  Use the onstat -u command to display a profile of user activity.
• onstat -x command: Print database server transaction information
  
  Use the onstat -x command to display transaction information on the database server.
• onstat -X command: Print thread information
  
  Use the onstat -X command to obtain precise information about the threads that are waiting for buffers.
• onstat -z command: Clear statistics
  
  Use the onstat -z command to clear database server statistics, including statistics that relate to Enterprise Replication, and set the profile counts to 0.
• Return codes on exiting the onstat utility
  
  The onstat utility displays a set of return codes when you exit the utility.

Drop user ddl

connect / as sysdba
set time on  timing on  echo on pagesize 200 linesize 200 feed on
show user
select to_char(sysdate, 'YYYYMMDD HH24:MI:SS') from dual;
select name from v$database;

select object_type, count(1) from dba_objects where owner = 'ODSADMIN' group by object_type order by 1;

select 'drop table '||owner||'.'||table_name||' cascade constraints purge;'
from dba_tables
where owner = 'SADMIN'
union all
select 'drop '||object_type||' '||owner||'.'||object_name||';'
from dba_objects
where object_type not in ('TABLE','INDEX','PACKAGE BODY','TRIGGER','LOB')
and object_type not like '%LINK%'
and object_type not like '%PARTITION%'
and owner = 'SADMIN'
order by 1;

quit;