Thursday, June 28, 2018

How To Enable Database Vault For PDB In Containter DB 12c

First Enable DV in Container DB (CDB$ROOT)

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

Create Common User c##dbvowner & c##dbvactmgr in CDB$ROOT
SQL>connect / as sysdba
SQL>create user c##dbvowner identified by pdit_dv15 CONTAINER=ALL;
SQL>create user c##dbvactmgr identified by pdit_dv15 CONTAINER=ALL;
SQL>grant SET CONTAINER,CREATE SESSION to c##dbvowner ;
SQL>grant SET CONTAINER,CREATE SESSION to c##dbvactmgr ;


Configure DV on CDB$ROOT
SQL>exec dvsys.configure_dv('c##dbvowner','c##dbvactmgr');
SQL>@?/rdbms/admin/utlrp.sql
SQL>conn c##dbvowner/****
SQL>EXEC DBMS_MACADM.ENABLE_DV;
SQL>Shutdown immediate
SQL>startup

Enable DV in PDB

-------------------------------------------------------------
If PDB has database vault configured previously (before migration)(DV_CONFIGURE_STATUS in dba_dv_status is TRUE)
We need to drop and recreate database vault metadata to proceed after it is migrated into Container DB.
Drop DV metadata:
SQL>conn sys@PDB1 as sysdba
SQL>@$ORACLE_HOME/rdbms/admin/dvremov.sql
Recreate DV metadata:
SQL>@?/rdbms/admin/catols.sql
SQL>exec lbacsys.configure_ols
SQL>exec lbacsys.ols_enforcement.enable_ols
SQL>@$ORACLE_HOME/rdbms/admin/catmac.sql system temp <syspasswd>

SQL>GRANT CREATE SESSION, SET CONTAINER TO c##dbvowner ;
SQL>GRANT CREATE SESSION, SET CONTAINER TO c##dbvactmgr ;

Configure DV on PDB1
SQL>exec dvsys.configure_dv('c##dbvowner','c##dbvactmgr');
SQL>exit
SQL>@?/rdbms/admin/utlrp.sql
SQL>connect c##dbvowner@PDB1;
SQL>EXEC DBMS_MACADM.ENABLE_DV;
SQL>alter pluggable database PDB1 close immediate;
SQL>alter pluggable database PDB1 open;

SQL to check Database Vault enable and configuration status

 select parameter,value from gv$option where parameter in  ('Oracle Database Vault','Oracle Label Security');

select * from dba_dv_status;

 select wrl_type,con_id,wrl_parameter,status from v$encryption_wallet;


Tuesday, June 26, 2018

ORA-12528: TNS:listener: all appropriate instances are blocking new connections via RMAN DUPLICATE

Symptom:

   We try to use rman duplicate to create a new standby DB.
   When we run rman> connect auxiliary sys/password@newstandby
   It error out ORA-12528: TNS:listener: all appropriate instances are blocking new connections
  When we check the new standby listener status, it shows BLOCKED

New standby DB must be in  nomount mode for rman dupplicate to work
In nomount mode, PMON can't register it to listener with ready status, so the listener status is BLOCKED,

Solution:

We need to add it manually to listener.ora
   LISTENER_standby_1529 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY=LISTENER_standby_1529))
      (ADDRESS = (PROTOCOL = TCP)(HOST = newstandby-vip)(PORT = 1529))
    )
  )

SID_LIST_LISTENER_standby_1529 =
  (SID_LIST =
    (SID_DESC =
        (ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_2)
        (SID_NAME =standby1)
    )
  )

The listener status should shows UNKNOWN after listener bounce, but it will allow primary to connect into nomount DB.
We can't use service_name to connect nomount db, we need to use SID, modify primary DB tnsnames.ora like this :
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = primary.us.test.com)(PORT = 1529))(CONNECT_DATA = (SERVER = DEDICATED)(SID = primarydb1)))


How to Handle Slack Message Too Long

Symptom:

   When you try to input a long sql ouput into slack thread . I get error "text is too long".



Solution:

   We need to paste the long output in the channel via '+'  beside the input box via "code or text snippet"
Unfortunately this function is not available on thread input, only in channel input.

Thursday, June 21, 2018

Service Name Conflict When Opening PDB

Symptom:

    After we  create a new PDB  from non-CDB via dblink for migration project.  The PDB opens with warning.

select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where status <> 'RESOLVED';

TESTPDB                      Service Name Conflict                                            WARNING

Service name or network name of service TESTSTG in the PDB is invalid or conflicts with an existing service name or network name in the CDB.

Solution:

    It is an old service name inherited  from source non-CDB. We can remove it and recreate them.

Delete on PDB Level:

PDB> exec DBMS_SERVICE.DELETE_SERVICE('TESTSTG ');

Recreate:

srvctl add service -db TESTDB -service TEST_APP -preferred "TESTDB1,TESTDB2" -pdb TESTDBPDB

srvctl status service -s TESTDBPDB_APP -db TESTDB

srvctl start service -s TESTDBPDB_APP -db TESTDB

Saturday, June 16, 2018

Easy Way to Set Auto Splash Page on F5 BIGIP

Requirement:

   We have 4 nodes Midtiers running behind F5 bigip.  One node has index corrupted while other 3 nodes are running well.
   I need to take down the node to re-index.  We need to set automatic splash page for this node while not on other 3 nodes

Solution:

    1. Add redirect rule into the httpd.conf file of this node to redirect traffic to splash page
    2. There is an automatic way to do it via bigip.

  • Create http monitor and assign it to the node.
  • Create http profile , assign it to the frontend virtual server and set fallback host to  be the splash page 
  • The theory is when http monitor detects the node is down, it marks the node is down in bigip. So any traffic to this node will be subjected to http profile and redirect to the splash page with http code 302

Wednesday, June 13, 2018

ORA-65066: The specified changes must apply to all containers

Symptom:

   We try to modify sys system password in a PDB(pluggable DB). It error out with code

   ORA-65066: The specified changes must apply to all containers
    The reason is sys system are common users among CDB and PDB  link oracle doc link
    So PDBs do not have their own sys system users,  they are inherited from CDB$ROOT

Solution:

  sqlplus / as sysdba to login  CDB$ROOT
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL>alter user system identified by XXXXXX;

Wednesday, June 06, 2018

How To Debug Http Request Timeout

Symptom:

   When users click a we link, it spins and timeout (normally 5 min per default).
   The http error code could be 502

Proxy Error
The request could not be processed. An invalid response was received by the proxy or gateway server.

Diagnosis:

   The reasons of timeout could be many. Network traffic, Server busy, Local client issue.....etc
   To analyse which part has issues, we can generate HAR file and do some analysis.
    HAR file not only for this timeout issue, but all other http related issues. It basically gathers all related web traffic and pack them.

   Google has toolbox to help you generate the HAR file and analyse it. Below screenshot is detailed timing of a timeout url
   https://toolbox.googleapps.com/apps/har_analyzer/



Saturday, June 02, 2018

RMAN-06023: no backup or copy of datafile found to restore

Symptom:

 We took backup and catalog backupset in the DB, We try to restore backup on rman. Error out with
RMAN-06023: no backup or copy of datafile 23 found to restore

Reasons:

  The backupset is in place in controlfile, however due to source db was open resetlogs. It is with different incarnation.  We need to make sure the target DB has the same incarnation

Solution:

rman>list incarnation;
rman>reset database to incarnation XXX ;   ( same in carnation of source DB)
rman>restore database from tag 'XXX';