12c OCM note 02 -- database Vs instance / CDB architect / connect to CDB, PDBs/ Tools

database Vs instance
=================
11g:
single instance: one database -- one instance
RAC: one database -- two or more instances

12c:
non-CDB: same as 11g
CDB:
single instance: one or more databases -- one instance
RAC: one or more databases -- two or more instances




CDB architect
=================
One instance is shared by multiple databases(cdb$root, pdb$seed, other pdbs)


Connect to CDB, PDBs
=================

Connect to root： sqlplus / as sysdba conn / as sysdba

EZ connect： sqlplus sys/oracle@localhost/PRODCDB.example.com:1521 as sysdba sqlplus sys/oracle@host02/PRODCDB.example.com:1521 as sysdba sqlplus sys/oracle@192.168.59.59/PRODCDB.example.com:1521 as sysdba

tns： sqlplus sys/oracle@tns_prodcdb as sysdba

switch container：

alter session set container=cdb$root; Connect to PDB： EZ connect： sqlplus sys/oracle@localhost/pdbprod1.example.com:1521 as sysdba sqlplus sys/oracle@192.168.59.59/pdbprod1.example.com:1521 as sysdba tns： sqlplus sys/oracle@tns_pdb1 as sysdba switch container： alter session set container=PDBPROD2; ---Some useful views col name format a20 col pdb format a10 select name,pdb from cdb_services order by con_id; select name,pdb from v$services order by con_id;

Tools
=================
SQL developer
OEM
TOAD --- Not recommended.

Dataguard -- what if primary has deleted the archive logs standby have not applied yet

STANDBY:
1.Get last SCN  number on Standby database
SQL>  select current_scn from v$database;
2.Stop the managed standby apply process:
SQL> alter database recover managed standby database cancel;
3.Shutdown the standby database
 SQL> shutdown immediate;


PRIMARY:
4.On the primary, take an incremental backup from the SCN number where the standby has been stuck:
RMAN>
run {
  allocate channel c1 type disk;
  allocate channel c2 type disk;
  allocate channel c3 type disk;
  allocate channel c4 type disk;
  backup incremental from scn xxxx database
    FORMAT '/db_backup/RMAN/temp/incr_standby_%U';
  }

5.On the primary, create a new standby controlfile:
SQL> alter database create standby controlfile as '//DEL1_standby.ctl';
Copy these files to standby host:
6.Scp the files to standby server
$ scp * oracle@:

STANDBY:
7.Bring up the instance in nomount mode:
SQL>   startup nomount

8.Check the location of the controlfile and Replace all controlfiles with the one you just created in primary.
SQL>    show parameter control_files
cp //DEL1_standby.ctl 
--for ASM, use RMAN to restore control file
RMAN> restore controlfile from '/db_backup/RMAN/temp/xxxxx_4standby.ctl';

9.Mount the standby database:
SQL>   alter database mount standby database;

10.Make sure the recovery is not started
SQL>    alter database recover managed standby database cancel;

11.RMAN Catalog the files and recover database noredo.
$rman target=/
RMAN>  catalog start with '';
Recover these files:
RMAN> switch database to copy; --if primary and standby use different paths
$RMAN>   recover database noredo;

12.Restart the broker on standby.(if you are using dg broker)
SQL> alter system set dg_broker_start=false scope=both;
SQL> alter system set dg_broker_start=true scope=both;

13.Start recovery on standby.
SQL>   alter database recover managed standby database disconnect from session;

Dataguard -- Gap solutions

To manually resolve gap for physical standby
select * from v$archive_gap;
--to find out the low and high seq# so you know which archive logs are missing
scp these files from primary to standby archive log location
alter database register logfile 'xxxxxxx';

Dataguard -- useful views

V$MANAGED_STANDBY -- lgwr, rfs, lns, arch, mrp information
V$ARCHIVED_LOG -- logs received by standby
V$LOG_HISTORY -- archive history details
V$DATAGUARD_STATUS -- dataguard messages/errors written to alert log and/or trace files
V$RECOVERY_PROGRESS -- recovery information
V$STANDBY_EVENT_HISTOGRAM -- log applying lag histogram
DBA_LOGSTDBY_LOG -- applied log information
DBA_LOGSTDBY_EVENTS -- log applied events in alert log
V$LOGSTDBY_PROCESS -- log apply processes current status

How to check Data Guard Physical standby status? (Oracle)

For data guard issues from an existing physical standby, I usually only use the following queries to diagnostic the problem. These queries can serve 95% of the cases and tell me where is the problem from.

For the rest 5% that you would like to look deeper, check out the srdc_DGPhyStby_diag.sql from Oracle support site. It gives you a report with all details in html format.

set tab off;
set linesize 300;
set pagesize 1000;
set long 100;
set verify off;
set feedback off;
--spool check_dg_tmp.out;
prompt
prompt
prompt *********************************
prompt showing ..... DB information
prompt *********************************

column host_name format a20
column  "STARTUP_TIME" format a25
select name Database,INSTANCE_NAME,OPEN_MODE,DATABASE_STATUS,database_role,logins,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "STARTUP_TIME" from v$database,v$instance;

prompt
prompt
prompt *********************************
prompt showing ..... Standby applying status
prompt *********************************

select process,status,sequence#,thread#,BLOCK#,BLOCKS,pid from gv$managed_standby order by 2 desc;

prompt
prompt
prompt *********************************
prompt showing ..... DG status
prompt *********************************
select INSTANCE_NAME,database_role, switchover_status,PROTECTION_MODE,PROTECTION_LEVEL , FLASHBACK_ON,force_logging "FORCE_LOGGING"  from v$database,v$instance;

prompt
prompt
prompt *********************************
prompt showing ..... archive log destination (location and roles, 1 - 6)
prompt *********************************
column dest_name format a19
column destination format a22
column db_unique_name format a12
column gap_status format a15
column dest_id format 9999
select a.DEST_ID, a.dest_name, a.type, a.STATUS, a.DESTINATION,  a.VALID_TYPE, a.VALID_ROLE, a.db_unique_name, b.gap_status
FROM V$ARCHIVE_DEST a,  V$ARCHIVE_DEST_STATUS b WHERE a.dest_id=b.dest_id and a.DESTINATION is not null;


prompt
prompt
prompt *********************************
prompt showing ..... archive log state (enable or defer)
prompt *********************************
show parameter log_archive_dest_state_1
show parameter log_archive_dest_state_2
show parameter log_archive_dest_state_3
show parameter log_archive_dest_state_4
show parameter log_archive_dest_state_5
show parameter log_archive_dest_state_6

prompt
prompt
prompt *********************************
prompt showing ..... archive log file format ( %t_%s_%r.arc)
prompt *********************************
show parameter log_archive_format

prompt
prompt
prompt *********************************
prompt showing ..... convert parameters (log and db_file)
prompt *********************************
show parameter convert

prompt
prompt
prompt *********************************
prompt showing ..... flashback retention
prompt *********************************
show parameter  db_flashback_retention_target

prompt
prompt
prompt *********************************
prompt showing ..... DB recover file dest and size
prompt *********************************
show parameter db_recovery

prompt
prompt
prompt *********************************
prompt showing ..... local listener and remote listener
prompt *********************************
show parameter remote_listener
show parameter local_listener

--spool off;
exit;

How to check cursor usage and find high version count sqls? (Oracle)

prompt ====================================================
prompt == Start of check_cursor.sql
prompt ====================================================
prompt ====================================================
prompt == Monitor cursor usage
prompt == Due to bug 21135007, the session_cached_cursor usage might be out
prompt == number due to parallel execution.
prompt ====================================================
set lines 300
col value format a30
col usage format a30
SELECT
   'session_cached_cursors' PARAMETER,
   lpad(VALUE, 5) VALUE,
   decode(VALUE, 0, 'n/a', to_char(100 * used / VALUE, '990') || '%') usage , used
FROM
   (SELECT MAX(s.VALUE) used
      FROM v$statname n, v$sesstat s
      WHERE n.name = 'session cursor cache count'
         AND s.statistic# = n.statistic#
   ),
   (SELECT VALUE
      FROM v$PARAMETER
      WHERE name = 'session_cached_cursors'
   )
UNION ALL
SELECT 'open_cursors', lpad(VALUE, 5), to_char(100 * used / VALUE, '990') || '%', used
FROM(SELECT MAX(SUM(s.VALUE)) used
      FROM v$statname n, v$sesstat s
      WHERE n.name ='opened cursors current'
         AND s.statistic# = n.statistic#
      GROUP BY s.sid
   ),
   (  SELECT VALUE
      FROM v$PARAMETER
      WHERE  name = 'open_cursors'
   );

prompt ====================================================
prompt == Top 10 cursor hungry sql statements
prompt ====================================================
select *
from (select  t.value ,s.sid, s.sql_id
from v$sesstat t, v$statname n, v$session s
where t.sid=s.sid and n.name= 'session cursor cache count' and t.statistic# = n.statistic#  order by 1 desc) where rownum <=10;

prompt ====================================================
prompt == Total cursors open, by username & machine
prompt ====================================================
select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur, s.username, s.machine
from   v$sesstat a, v$statname b, v$session s
where  a.statistic# = b.statistic# and s.sid=a.sid
and    b.name = 'opened cursors current'
group by s.username, s.machine
order by 1 desc;

prompt ====================================================
prompt == Show sqls having high child cursor count > 100
prompt ====================================================

select inst_id, sql_id, sql_profile, plan_hash_value, max(child_number)
from gv$sql group by inst_id, sql_id, sql_profile, plan_hash_value
having max(child_number) >100;


prompt ====================================================
prompt == End of check_cursor.sql
prompt ====================================================

How to check memory components of your database? (Oracle)

This script shows you information about your memory configurations, current usage, adviser views, etc. Before jumping into any conclusions, you should check if the database is using AMM, HugePage, or any other OS related configurations. Changing memory configuration involves at least a rolling restart in most cases.

prompt ====================================================
prompt == start of check_memory.sql
prompt ====================================================

prompt ====================================================
prompt == Show Memory parameters
prompt ====================================================
show parameter memory

prompt ====================================================
prompt == Top 50 memory allocations by sessions
prompt ====================================================

SET LINESIZE 300
SET Pages 300

COLUMN username FORMAT A20
COLUMN module FORMAT A20

SELECT * FROM
(SELECT a.inst_id,
       NVL(a.username,'(oracle)') AS username,
       a.sid,
       a.module,
       a.program,
       Trunc(b.value/1024/1024) AS memory_mb
FROM   gv$session a,
       gv$sesstat b,
       gv$statname c
WHERE  a.sid = b.sid
AND    a.inst_id = b.inst_id
AND    b.statistic# = c.statistic#
AND    b.inst_id = c.inst_id
AND    c.name = 'session pga memory'
AND    a.program IS NOT NULL
ORDER BY b.value DESC)
WHERE rownum <=50;

prompt ====================================================
prompt == Show SGA PGA hourly usage in last 10 days
prompt ====================================================
set lines 200
set pages 1000
select sn.INSTANCE_NUMBER, sga.allo sga, pga.allo pga,(sga.allo+pga.allo) totalGb,to_char(SN.END_INTERVAL_TIME,'YYYY-MM-DD HH24:MI') datetime
from
(select snap_id,INSTANCE_NUMBER,round(sum(bytes)/1024/1024/1024,3) allo
from DBA_HIST_SGASTAT
group by snap_id,INSTANCE_NUMBER) sga
,(select snap_id,INSTANCE_NUMBER,round(sum(value)/1024/1024/1024,3) allo
from DBA_HIST_PGASTAT where name = 'total PGA allocated'
group by snap_id,INSTANCE_NUMBER) pga
, dba_hist_snapshot sn
where sn.snap_id=sga.snap_id
and sn.INSTANCE_NUMBER=sga.INSTANCE_NUMBER
and sn.snap_id=pga.snap_id
and sn.INSTANCE_NUMBER=pga.INSTANCE_NUMBER
and sn.END_INTERVAL_TIME > sysdate - 10
order by sn.snap_id desc, sn.INSTANCE_NUMBER
;
prompt ====================================================
prompt == V$SHARED_POOL_ADVICE
prompt ====================================================
select * from V$SHARED_POOL_ADVICE;

prompt ====================================================
prompt == V$SGA_TARGET_ADVICE
prompt ====================================================
select * from v$sga_target_advice;

prompt ====================================================
prompt == V$MEMORY_TARGET_ADVICE
prompt ====================================================
select * from  v$memory_target_advice;

prompt ====================================================
prompt == Show SGA
prompt ====================================================
show sga
show parameter sga

prompt ====================================================
prompt == Current Free SGA
prompt ====================================================
select current_size/1024/1024/1024 free_gb
from V$SGA_DYNAMIC_FREE_MEMORY;

prompt ====================================================
prompt == Current SGA Components
prompt ====================================================
select component, current_size/1024/1024/1024 current_Gb
from V$SGA_DYNAMIC_COMPONENTS;

prompt ====================================================
prompt == Show PGA
prompt ====================================================
show parameter pga

prompt ====================================================
prompt == Max PGA allocated
prompt ====================================================
select name, value/1024/1024/1024 Gb
from V$PGASTAT where name='maximum PGA allocated';


prompt ====================================================
prompt == End of check_memory.sql
prompt ====================================================