!! Learning is FUN !!

This happened soon after the DRY RUN of one of the client’s application patch upgrade. Application team requested to secure the backup of SOLTP2+ SDWR2 (Pre-Prod) just after the dry run of application patch upgrade and once the backups were secured PP was supposed to go back on daily refresh cycle and SYNC up with PROD. Moreover, this backup was supposed to be retained for further testing.

Before, I explain the issue let me introduce the environment setup.

POLTP1 is relatively small database mainly for OLTP whereas PDWR1 stores huge data for warehouse reporting purpose. It has two local snapshot standby(PS & PP), which gets in-sync once in every 24 hours. There are other geographically distant DR setup as well. Also, keep in mind that  they pull data from both the database(POLTP1 + PDWR1) simultaneously and we should treat this pair logically as a single unit from application design perpective.

All three-database environment prod, prod support and pre-prod are two node RAC (11gR2), each spawned in half node exadata bare metal X6 shown as below.

For SOLTP2 Pre-Prod database RMAN full backup was taken successfully. However, we were unable to secure LVL0 backup for SDWR2 because, controlfile was having some fuzzy data and every time we were hitting ORA-19588: archived log RECID 66868 STAMP 998090247 is no longer valid.

This issue is similar with Bug 14032377 in 11gR2.

RMAN Backup Fails With "ORA-19633: Control File Record 8781 Is Out Of Sync With Recovery Catalog" (Doc ID 2493417.1)

Alternatively, multiple solutions were proposed and discussed with application team.

Clear control-file and try to secure backup and then revert PP to Daily refresh cycle but switching back to standby may fail and we might end up recreating standby database. It was highly risky hence; all possible physical backup approach closed.

We started exploring logical backup methods. During the discussion with application and project team, we discovered that the post cut-over data can be logically pulled from SDWR2(PP) and pre-cut over data can be generated from PROD backup. (PROD LVL0 can be restored on any DEV/Test environment and on top of that logically backed up post cut-over data can be inserted).

Still there is a problem with above approach because PROD might be way ahead of PP and will lead to data integrity issue.

Fortunately, there was other Snapshot standby (PS) synchronized up to same SCN as (PP) prior to cut-over.

We had a clear plan now what to do next, we could use data-pump,SQL*Loader or CTAS or pull data via DBLINK from PP to PS environment temporarily and kick LVL0 for PS rather than PP. Therefore, we jump from backup issue to DBLINK creation.

Request raised by application team as below :

“Please create a DB link from SDWR1 PS environment to SDWR2 PP environment. We will copy required data from the database. DB link should have access to DW and EDW schema.Once we have copied data, we will provide a go-ahead to you to refresh SDWR2 (PP), ETA will be ~1 hr after DB link is created”.

Solution:

---Create DBLINK in SDWR1 (PS). (I used user "DBLINK" already present in SDWR2 (PP))

CREATE PUBLIC DATABASE LINK "DBLINK" CONNECT TO "DBLINK" IDENTIFIED BY dblink USING ‘SDWR2’;

        set line 400
        col DB_LINK for a60
        col USERNAME for a40
         col HOST for a20
         col OWNER for a30
         select * from dba_db_links;

--- Grant select privs of DW and EDW to user DBLINK in SDWR2 (PP)


BEGIN
   FOR R IN (SELECT owner, table_name FROM dba_tables WHERE owner='DW') LOOP
      EXECUTE IMMEDIATE 'grant select on '||R.owner||'.'||R.table_name||' to DBLINK';
   END LOOP;
END;

BEGIN
   FOR R IN (SELECT owner, table_name FROM dba_tables WHERE owner='EDW') LOOP
      EXECUTE IMMEDIATE 'grant select on '||R.owner||'.'||R.table_name||' to DBLINK';
   END LOOP;
END;

Remember all the changes will be lost when snapshot standby will be flashed back while going back to SYNC .

--- Create Synonym for DBLINK in SDWR2 (PP).

set line 400 pages 0
spool synonym.sql

SELECT 'CREATE OR REPLACE SYNONYM DBLINK.'||TABLE_NAME||' for '||OWNER||'.'||TABLE_NAME||' ;' FROM DBA_TABLES WHERE OWNER='EDW';
SELECT 'CREATE OR REPLACE SYNONYM DBLINK.'||TABLE_NAME||' for '||OWNER||'.'||TABLE_NAME||' ;' FROM DBA_TABLES WHERE OWNER='DW';

spool off;

@synonym.sql

-- Check DBLINK in SDWR1 (PS).

 

 OWNER      DB_LINK                                                      USERNAME       HOST         CREATED

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

PUBLIC     DBLINK.REGRESS.RDBMS.DEV.US.ORACLE.COM                      DBLINK         SDWR2      22-JAN-19

SQL> select * from dual@DBLINK.REGRESS.RDBMS.DEV.US.ORACLE.COM ;

D

-

X

      --  Access the DATA in SDWR1 (PS). Seems fine .

 

       -- Try other list of tables and issue encountered

some of the table data was not being populated in SDWR1 (PS) and were returning zero rows without any error. Red flag was raised by application team.

 -- Privileges were fine

Upon investigating this strange behavior, eliminating the problem of dblink and other network related stuff, further narrowing the issue with little struggle discovered that the issue was with VPD.

--- Work around is to grant exempt access policy to dblink ,this privilege allows a role to execute a SQL command without invoking any policy function that may be associated with the target database object


SQL>  select POLICY_NAME,OBJECT_NAME,OBJECT_OWNER,POLICY_GROUP,ENABLE,FUNCTION  from DBA_POLICIES where OBJECT_NAME in('DIM_PORTFOLIO','FACT_DEAL_QUANTITY','FACT_DEAL_PNL');

POLICY_NAME                    OBJECT_NAME                    OBJECT_OWNER                   POLICY_GROUP                   ENA FUNCTION
------------------------------ ------------------------------ ------------------------------ ------------------------------ --- ------------------------------
ACCESSCONTROL_FDP              FACT_DEAL_PNL                  EDW                            SYS_DEFAULT                    YES EDW_POL_F
ACCESSCONTROL_FDQ              FACT_DEAL_QUANTITY             EDW                            SYS_DEFAULT                    YES EDW_POL_F

SQL> grant EXEMPT ACCESS POLICY to DBLINK;

Grant succeeded.

-- Finally the data was being populated successfully where as earlier it was returning zero rows due to VPD . Notice the row the count in below screenshot.

 
Data was populating fine, and we got a go ahead for PP SDWR2 to revert into daily refresh cycle. In the end LVL0 backup was secured successfully from PS SDWR1  for further application regression testing.

Now, PP SDWR2 must catch up for around ~72-80 Hrs GAP and additional refresh requests needs to be fulfilled for further testing.

Our take away was that we got to know that PP LVL0 backup will trouble us in future also and needs to be rectified eventually when we find time.

Long way to go!!