1z0-083

 my 1z0-083 posts:

https://www.linkedin.com/posts/kenchendz_multitenant-usertablespaces-clause-in-activity-7039298088187961344-chDS?utm_source=share&utm_medium=member_desktop

https://www.linkedin.com/posts/kenchendz_multitenant-usertablespaces-clause-in-activity-7039305152511545344-Q-Yq?utm_source=share&utm_medium=member_desktop

More to come...

A mystery issue

A mystery issue just got resolved.

A team complained to me that one of their jobs sometimes failed and sometimes worked well. The job calls a database stored procedure to write a CSV file to a directory on the database server.

So they engaged both the Unix admin and me, the DBA. I had them run the job manually from the server locally using SQLplus, and it worked. Unix admin checked the privileges on the directory and it was 777. 

So I am very sure nothing is wrong on the database server side. Even they insisted that the only thing that changed was that we patched the database server last week.

At first, I thought it was a network blip. But the network team stated there were no issues or maintenance.

Then, I asked the app team to provide the tnsnames.ora file. The TNS entry explains the issue. There were two entries in one that was used to connect to the database. One is pointing to the DR and the other is pointing to Primary. When the primary is used, it works. When the DR is picked, it fails.

The app team said they just copied and pasted so they didn't check.

Sigh...

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;