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;