Convert snapshot standby to physical standby in Oracle RAC

========================================================================
STEP-1 : Verify the DR database
========================================================================

Connect to DR server : example01a

-- Verify the existing status in DR database which is open in R/W mode using "snapshot" standby:

SQL> select name, DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
TESTPRD    testdr                         READ WRITE           SNAPSHOT STANDBY

SQL> 

-- Flashback status
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> select name,scn,time,guarantee_flashback_database,storage_size from v$restore_point;

NAME                                                            SCN TIME                                GUARA  STORAGE_SIZE
-------------------------------------------------- ---------------- ----------------------------------- ----- -------------
SNAPSHOT_STANDBY_REQUIRED_09/11/2018 11:11:32             992874058 11-SEP-18 11.11.32.000000000 AM     YES      3722444800

SQL> select instance_name,status,host_name from gv$instance;

INSTANCE_NAME    STATUS       HOST_NAME
---------------- ------------ --------------------------------------
testdr2          OPEN         example01b.facedba.com
testdr1          OPEN         example01a.facedba.com
testdr3          OPEN         example01c.facedba.com

SQL> 


========================================================================
STEP-2 : Verify LAG details
========================================================================

-- Verify archive logs pending to apply

-- connect to primary : examplerac1a

[oracle@examplerac1a ~]$ testprd
[oracle@examplerac1a ~]$ env | grep ORA
ORACLE_UNQNAME=TESTPRD
ORACLE_SID=TESTPRD1
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1
[oracle@examplerac1a ~]$ !sql
sqlplus / as sysdba

SQL> set lines 222
SQL> select instance_name,status,host_name from gv$instance;

INSTANCE_NAME    STATUS       HOST_NAME
---------------- ------------ ----------------------------------------------------------------
TESTPRD1          OPEN         examplerac1a
TESTPRD3          OPEN         examplerac1c
TESTPRD2          OPEN         examplerac1b

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
TESTPRD    READ WRITE

SQL> 

SQL> select thread#,max(sequence#) from v$archived_log group by thread# order by 1;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1           9393
         2           8797
         3           9209

SQL> 


-- Connect to standby : example01a
-- Verify LAG details w.r.t. primary

-- Using DG broker

DGMGRL> connect testdr
Password:
Connected as SYSDG.
DGMGRL> show database testdr;

Database - testdr

  Role:               SNAPSHOT STANDBY
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          2 days 11 hours 55 minutes 6 seconds (computed 0 seconds ago)
  Instance(s):
    testdr1
    testdr2
    testdr3

Database Status:
SUCCESS

DGMGRL> 

-- Using query

select * from v$dataguard_stats; SQL> 

SOURCE_DBID SOURCE_DB_UN NAME                             VALUE                UNIT                           TIME_COMPUTED                  DATUM_TIME                        CON_ID
----------- ------------ -------------------------------- -------------------- ------------------------------ -------------------------- --------------------- ----------
  311374056 TESTPRD       transport lag                    +00 00:00:00         day(2) to second(0) interval   09/13/2018 23:03:36        09/13/2018 23:03:35         0
  311374056 TESTPRD       apply lag                        +02 11:58:00         day(2) to second(0) interval   09/13/2018 23:03:36        09/13/2018 23:03:35         0
  311374056 TESTPRD       apply finish time                +00 00:00:55.650     day(2) to second(3) interval   09/13/2018 23:03:36                                    0
          0              estimated startup time           28                   second                         09/13/2018 23:03:36                                     0

SQL> 


-- Verify the Archivelog Gap

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD#
ORDER BY 1; 
    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                   9393                    11       9382
         2                   8797                    16       8781
         3                   9209                    11       9198

SQL> 


========================================================================
STEP-3 : Converting Snapshot Standby Database into Physical Standby Database
========================================================================

Step 1: Check for current database role
---------------------------------------

SQL> Select DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
------------------------------ -------------------- ----------------
testdr                         READ WRITE           SNAPSHOT STANDBY

SQL> 

SQL> select name, guarantee_flashback_database from v$restore_point;

NAME                                                                                                                             GUA
-------------------------------------------------------------------------------------------------------------------------------- ---
SNAPSHOT_STANDBY_REQUIRED_09/11/2018 11:11:32                                                                                    YES

SQL> 



Step 2: If the standby database is a RAC setup then we need to shutdown all the instances except one on which we will be using the conversion commands.
--------------------------------------------------------------------------------------------------------------------------
-- status

[oracle@example01a ~]$ srvctl status database -d testdr -verbose
Instance testdr1 is running on node example01a. Instance status: Open.
Instance testdr2 is running on node example01b. Instance status: Open.
Instance testdr3 is running on node example01c. Instance status: Open.
[oracle@example01a ~]$ 


-- Stop other two instances


[oracle@example01a ~]$ srvctl status instance -d testdr -i testdr2 -verbose
Instance testdr2 is running on node example01b. Instance status: Open.
[oracle@example01a ~]$ srvctl stop instance -d testdr -i testdr2

[oracle@example01a ~]$ srvctl status instance -d testdr -i testdr3 -verbose
Instance testdr3 is running on node example01c. Instance status: Open.
[oracle@example01a ~]$ srvctl stop instance -d testdr -i testdr3

[oracle@example01a ~]$ srvctl status instance -d testdr -i testdr2 -verbose
Instance testdr2 is not running on node example01b
[oracle@example01a ~]$ 

-- Cross-verify instance status in all the instances

SQL> select instance_name,status,host_name from gv$instance;

INSTANCE_NAME    STATUS       HOST_NAME
---------------- ------------ --------------------------------------
testdr1          OPEN         example01a.facedba.com


Step 3: Ensure that the database is mounted.
---------------------------------------------
shut immediate;
startup mount

e.g.,
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
...
Database mounted.
SQL> 


Step 4: Do the conversion of snapshot standby database to physical standby database.
------------------------------------------------------------------------------------
Using Manual method:

Issue the following SQL statement to convert the snapshot standby back to the physical standby:
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.

SQL> 

Note: Open the same instance alert log and see the status as well.

e.g.,

Archived Log entry 27095 added for thread 3 sequence 9210 ID 0x1292157e dest 1:
RFS[3]: Selected log 15 for thread 2 sequence 8799 dbid 311374056 branch 919823934
Thu Sep 13 23:26:46 2018
Archived Log entry 27096 added for thread 2 sequence 8798 ID 0x1292157e dest 1:
Thu Sep 13 23:26:54 2018
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='testdr1';
Thu Sep 13 23:26:54 2018
ALTER SYSTEM SET log_archive_format=' %t_%s_%r.dbf' SCOPE=SPFILE SID='testdr1';
Thu Sep 13 23:27:14 2018
Decreasing number of real time LMS from 2 to 0
Thu Sep 13 23:29:48 2018
ALTER DATABASE CONVERT TO PHYSICAL STANDBY
Thu Sep 13 23:29:48 2018
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (testdr1)
Thu Sep 13 23:29:48 2018
Killing 6 processes (PIDS:59198,59196,59192,59194,59188,59190) (all RFS) in order to disallow current and future RFS connections. Requested by OS process 58293 on instance 1
Thu Sep 13 23:29:53 2018
Flashback Restore Start
Thu Sep 13 23:30:27 2018
Flashback Restore Complete
Drop guaranteed restore point 
Guaranteed restore point  dropped
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_274.5232.968804217
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_289.22000.970880737
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_125.12987.969976835
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_133.3707.969980407
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_144.22476.969983421
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_320.12499.975754841
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_149.3074.969985135
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_291.14763.970898411
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_150.16805.969989881
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_293.3098.970904117
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_161.4691.967916083
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_278.1284.968832017
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_199.3977.968839221
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_162.11578.970005681
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_163.6733.970005751
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_173.10531.970005901
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_12.1444.966125419
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_9.14265.968846697
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_174.8083.970013031
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_181.15345.970013051
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_164.7255.968850011
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_167.6290.970020023
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_206.13203.966125443
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_165.13970.968858533
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_182.15693.970033625
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_322.14307.975805261
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_22.20883.961207247
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_188.11958.970040197
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_26.22421.967959611
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_211.19980.966153929
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_4.8659.970048829
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_171.22135.968882689
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_196.2045.967075681
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_112.7933.968890729
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_30.19766.970059643
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_33.572.970061957
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_170.14302.970063873
Clearing standby activation ID 3291529591 (0xc430c177)
The primary database controlfile was created using the
'MAXLOGFILES 40' clause.
There is space for up to 31 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl5.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl6.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl7.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl8.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl9.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl10.f' SIZE 52428800;
Thu Sep 13 23:30:28 2018
Waiting for all non-current ORLs to be archived...
Thu Sep 13 23:30:28 2018
ARC0: Becoming the active heartbeat ARCH
ARC0: Becoming the active heartbeat ARCH
Thu Sep 13 23:30:28 2018
All non-current ORLs have been archived.
Clearing online redo logfile 1 +DATA/testdr/ONLINELOG/group_1.1266.920900689

Clearing online log 1 of thread 1 sequence number 13
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 +DATA/testdr/ONLINELOG/group_2.3177.920900689

Clearing online log 2 of thread 1 sequence number 11
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 +DATA/testdr/ONLINELOG/group_3.7942.920900691

Clearing online log 3 of thread 1 sequence number 12
Clearing online redo logfile 3 complete
Clearing online redo logfile 4 +DATA/testdr/ONLINELOG/group_4.5528.920900691

Clearing online log 4 of thread 2 sequence number 16
Clearing online redo logfile 4 complete
Clearing online redo logfile 5 +DATA/testdr/ONLINELOG/group_5.6487.920900691

Clearing online log 5 of thread 2 sequence number 17
Clearing online redo logfile 5 complete
Clearing online redo logfile 6 +DATA/testdr/ONLINELOG/group_6.7986.920900691

Clearing online log 6 of thread 2 sequence number 15
Clearing online redo logfile 6 complete
Clearing online redo logfile 7 +DATA/testdr/ONLINELOG/group_7.857.920900691

Clearing online log 7 of thread 3 sequence number 10
Clearing online redo logfile 7 complete
Clearing online redo logfile 8 +DATA/testdr/ONLINELOG/group_8.7774.920900691

Clearing online log 8 of thread 3 sequence number 11
Clearing online redo logfile 8 complete
Clearing online redo logfile 9 +DATA/testdr/ONLINELOG/group_9.1351.920900691

Clearing online log 9 of thread 3 sequence number 12
Clearing online redo logfile 9 complete
Physical Standby Database mounted.
CONVERT TO PHYSICAL STANDBY: Complete - Database mounted as physical standby
Completed: ALTER DATABASE CONVERT TO PHYSICAL STANDBY
Thu Sep 13 23:30:38 2018
Network Resource Management enabled for Process  (pid 70054) for Exadata I/O
Primary database is in MAXIMUM PERFORMANCE mode
RFS[4]: Assigned to RFS process (PID:70054)
RFS[4]: Selected log 19 for thread 3 sequence 9211 dbid 311374056 branch 919823934
Thu Sep 13 23:30:38 2018
Network Resource Management enabled for Process  (pid 70046) for Exadata I/O
Primary database is in MAXIMUM PERFORMANCE mode
Thu Sep 13 23:30:38 2018
Network Resource Management enabled for Process  (pid 70050) for Exadata I/O
Primary database is in MAXIMUM PERFORMANCE mode
RFS[5]: Assigned to RFS process (PID:70046)
RFS[5]: Selected log 15 for thread 2 sequence 8799 dbid 311374056 branch 919823934
RFS[6]: Assigned to RFS process (PID:70050)
RFS[6]: Selected log 10 for thread 1 sequence 9395 dbid 311374056 branch 919823934
RFS[4]: Selected log 18 for thread 3 sequence 9212 dbid 311374056 branch 919823934
Thu Sep 13 23:30:39 2018
Archived Log entry 27097 added for thread 3 sequence 9211 ID 0x1292157e dest 1:
RFS[6]: Selected log 11 for thread 1 sequence 9396 dbid 311374056 branch 919823934
Thu Sep 13 23:30:39 2018
Archived Log entry 27098 added for thread 1 sequence 9395 ID 0x1292157e dest 1:
Thu Sep 13 23:30:40 2018
Archived Log entry 27099 added for thread 2 sequence 8799 ID 0x1292157e dest 1:
RFS[5]: Selected log 14 for thread 2 sequence 8800 dbid 311374056 branch 919823934
....


Step 5: Start database in mount, if not already mounted.
-----------------------------------------------------------------------

SQL> select name, DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
TESTPRD    testdr                         MOUNTED              PHYSICAL STANDBY

SQL> 

Note: You can see here now standby db status converted to "PHYSICAL STANDBY"


Step 6: Recover Physical Standby database
-------------------------------------------------

SQL> alter database recover managed standby database disconnect from session using current logfile;

Database altered.

SQL> 

-- Verify Lag
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;   2    3    4    5    6  

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                   9395                  9395          0
         2                   8799                  8799          0
         3                   9211                  9211          0

SQL> 


Step-7 : Corect the DG Broker info:
-------------------------------------

-- before

DGMGRL> show database testdr;

Database - testdr

  Role:               SNAPSHOT STANDBY
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Instance(s):
    testdr1
      Warning: ORA-16782: instance not open for read and write access

    testdr2
    testdr3

  Database Error(s):
    ORA-16816: incorrect database role

Database Status:
ERROR

DGMGRL> quit


--- Now apply below commands

DGMGRL> 
DGMGRL> connect testdr;
Password:
Connected as SYSDG.
DGMGRL> 
DGMGRL> CONVERT DATABASE testdr TO PHYSICAL STANDBY;            
Converting database "testdr" to a Physical Standby database, please wait...
Database "testdr" converted successfully
DGMGRL> 

-- Post applied commands

DGMGRL> show database testdr;

Database - testdr

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 4.42 MByte/s
  Real Time Query:    OFF
  Instance(s):
    testdr1 (apply instance)
    testdr2
    testdr3

Database Status:
SUCCESS

DGMGRL> 

Now all looks good.

Step 7: Check for database role:
-----------------------------------------

SQL> select DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
------------------------------ -------------------- ----------------
testdr                         MOUNTED              PHYSICAL STANDBY

SQL>


-- end of all the steps --

PLS-00201: identifier 'DBMS_CRYPTO' must be declared issue - Fix

I received below message from my blog message:

I am trying to create following DECRYPT function but it failed with below error. How I can fix it?

e.g.,
CREATE OR REPLACE EDITIONABLE FUNCTION "SCOTT"."DECRYPT_CHAR" (input IN RAW, key IN VARCHAR)
RETURN VARCHAR PARALLEL_ENABLE
IS

.....

BEGIN

....
END;
/


Warning: Function created with compilation errors.

SQL> show error
Errors for FUNCTION "SCOTT"."DECRYPT_CHAR":

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/19     PL/SQL: Item ignored
6/34     PLS-00201: identifier 'DBMS_CRYPTO' must be declared
17/1     PL/SQL: Statement ignored
17/24    PLS-00201: identifier 'DBMS_CRYPTO' must be declared
SQL> 


Ans:
You need to give access on dbms_crypto to your schema. Here your schema is "scott".
Sure it will work.

e.g.,

SQL>  grant execute on sys.dbms_crypto to SCOTT;

Grant succeeded.

Data Redaction in Oracle 12c

What is Data Redaction?
Oracle Data Redaction is one of the new features introduced in Oracle Database 12c. This new feature is part of the Advanced Security option and enables the protection of data shown to the user in real time, without requiring changes to the application. Oracle Database 12c applies protection at query execution time.

How It Works?
We can create redaction policies which specify conditions that must be met before the data gets redacted and returned to the user. During the definition of such policies, the DBA can specify which columns and the type of protection that must be applied.

The package used to create protection rules is called DBMS_REDACT. The package includes five procedures to manage the rules and an additional procedure to change the default value for full redaction policy.

DBMS_REDACT.ALTER_POLICY – allows changes to existing policies.
DBMS_REDACT.DISABLE_POLICY – disables an existing policy.
DBMS_REDACT.DROP_POLICY – drop an existing policy.
DBMS_REDACT.ENABLE_POLICY – enables an existing policy.
DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES – change the default return value for full redaction. You must restart the database to take effect.

How many ways to protect data using data redaction?

You can protect data at the column level using one of the following methods::

Full redaction – All content of the column is protected and the type of value returned depends on the data type of the column. For numeric columns, the value zero will be returned. For columns of type character, a space will be returned. This setting can be changed at the database level.
Partial redaction – Only part of the information is changed. For example, the first digits of the credit card number are replaced by asterisks.
Regular expressions - You can use regular expressions to search for patterns of data that must be protected.
Random redaction – Returned values ​​are random; each time a query is executed, the displayed data will be different.
No redaction - Allows testing the inner workings of redaction policies, with no effect on the results of current running queries. This is widely used during testing phase of redaction policies that will eventually find their way to production environments.

Can be see here using graphical presentation:

What data types can be used for data redaction?

It can be used with the following column data types: NUMBER, BINARY_FLOAT, BINARY_DOUBLE, CHAR, VARCHAR2, NCHAR, NVARCHAR2, DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, BLOB, CLOB, and NCLOB.


Examples to show use of data redaction features  

-- Find data redaction enabled or not

SQL> select * from redaction_policies;
no rows selected
SQL> 

SQL> GRANT EXECUTE ON sys.dbms_redact TO scott;
Grant succeeded.

SQL> connect scott/xxxxx;
Connected.
SQL> desc emp_test;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(38)
 NAME                                               VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 BOSS                                               NUMBER(38)
 HIREDATE                                           VARCHAR2(12)
 SALARY                                             NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(38)

SQL> 

SQL> select * from employee;

     EMPNO NAME       JOB             BOSS HIREDATE         SALARY       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7839 KING       PRESIDENT            1981-11-17         5200                    10
      7566 JONES      MANAGER         7839 1981-04-02         2975                    20
      7788 SCOTT      ANALYST         7566 1982-12-09         3000                    20
      7876 ADAMS      CLERK           7788 1983-01-12         1100                    20
      7902 FORD       ANALYST         7566 1981-12-03         3000                    20
      7369 SMITH      CLERK           7902 1980-12-17          800                    20
      7698 BLAKE      MANAGER         7839 1981-05-01         2850                    30
      7499 ALLEN      SALESMAN        7698 1981-02-20         1600        300         30
      7521 WARD       SALESMAN        7698 1981-02-22         1250        500         30
      7654 MARTIN     SALESMAN        7698 1981-09-28         1250       1400         30
      7844 TURNER     SALESMAN        7698 1981-09-08         1500          0         30
      7900 JAMES      CLERK           7698 1981-12-03          950                    30
      7782 CLARK      MANAGER         7839 1981-06-09         2450                    10
      7934 MILLER     CLERK           7782 1982-01-23         1300                    10

14 rows selected.


-- Applying Full Data Redaction

SQL> connect / as sysdba
Connected.
SQL> sho user
USER is "SYS"

SQL> 

BEGIN
  DBMS_REDACT.add_policy(
    object_schema => 'scott',
    object_name   => 'emp_test',
    column_name   => 'salary',
    policy_name   => 'redact_sal_info',
    function_type => DBMS_REDACT.full,
    expression    => '1=1'
  );
END;
/

PL/SQL procedure successfully completed.

-- after data redaction

     EMPNO NAME       JOB             BOSS HIREDATE         SALARY       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7839 KING       PRESIDENT            1981-11-17         5200                    10
      7566 JONES      MANAGER         7839 1981-04-02         2975                    20
      7788 SCOTT      ANALYST         7566 1982-12-09         3000                    20
      7876 ADAMS      CLERK           7788 1983-01-12         1100                    20
      7902 FORD       ANALYST         7566 1981-12-03         3000                    20
      7369 SMITH      CLERK           7902 1980-12-17          800                    20
      7698 BLAKE      MANAGER         7839 1981-05-01         2850                    30
      7499 ALLEN      SALESMAN        7698 1981-02-20         1600        300         30
      7521 WARD       SALESMAN        7698 1981-02-22         1250        500         30
      7654 MARTIN     SALESMAN        7698 1981-09-28         1250       1400         30
      7844 TURNER     SALESMAN        7698 1981-09-08         1500          0         30
      7900 JAMES      CLERK           7698 1981-12-03          950                    30
      7782 CLARK      MANAGER         7839 1981-06-09         2450                    10
      7934 MILLER     CLERK           7782 1982-01-23         1300                    10

14 rows selected.


-- See the applied policies

col OBJECT_OWNER for a20
col OBJECT_NAME for a20;
col POLICY_DESCRIPTION for a10
col POLICY_NAME for a20
col EXPRESSION for a10

SQL> select * from redaction_policies;

OBJECT_OWNER         OBJECT_NAME          POLICY_NAME          EXPRESSION ENABLE  POLICY_DES
-------------------- -------------------- -------------------- ---------- ------- ----------
SCOTT                EMP_TEST             redact_sal_info     1=1        YES
SQL> 


-- Data Redaction default values

SET LINESIZE 250
COLUMN char_value FORMAT A10
COLUMN varchar_value FORMAT A10
COLUMN nchar_value FORMAT A10
COLUMN nvarchar_value FORMAT A10
COLUMN timestamp_value FORMAT A27
COLUMN timestamp_with_time_zone_value FORMAT A32
COLUMN blob_value FORMAT A20
COLUMN clob_value FORMAT A10
COLUMN nclob_value FORMAT A10

SELECT * FROM   redaction_values_for_type_full;



-- Alter an Existing Policy
The ALTER_POLICY procedure allows you to make changes to an existing policy. The type of change being made is controlled using the ACTION parameter. Depending on the action required, the relevant parameters must be specified.

The following example changes the previously created redaction policy so that it uses partial redaction. Notice the FUNCTION_PARAMETERS are now specified to give instructions how the partial redaction should take place. For a numeric data type we specify a comma separated list of three elements (value to redact to, start point, end point), so in this case we want the first 12 characters of the number to always display as "111111111111".


SQL> show user
USER is "SYS"
SQL> BEGIN
  DBMS_REDACT.alter_policy (
    object_schema       => 'scott',
    object_name         => 'emp_test',
    policy_name         => 'redact_sal_info',
    action              => DBMS_REDACT.modify_column,
    column_name         => 'salary',
    function_type       => DBMS_REDACT.partial,
    function_parameters => '1,1,12'
  );
END;
/

PL/SQL procedure successfully completed.

SQL> 


-- Verify

SQL> select * from emp_test;

     EMPNO NAME       JOB             BOSS HIREDATE         SALARY       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7839 KING       PRESIDENT            1981-11-17         1111                    10
      7566 JONES      MANAGER         7839 1981-04-02         1111                    20
      7788 SCOTT      ANALYST         7566 1982-12-09         1111                    20
      7876 ADAMS      CLERK           7788 1983-01-12         1111                    20
      7902 FORD       ANALYST         7566 1981-12-03         1111                    20
      7369 SMITH      CLERK           7902 1980-12-17          111                    20
      7698 BLAKE      MANAGER         7839 1981-05-01         1111                    30
      7499 ALLEN      SALESMAN        7698 1981-02-20         1111        300         30
      7521 WARD       SALESMAN        7698 1981-02-22         1111        500         30
      7654 MARTIN     SALESMAN        7698 1981-09-28         1111       1400         30
      7844 TURNER     SALESMAN        7698 1981-09-08         1111          0         30
      7900 JAMES      CLERK           7698 1981-12-03          111                    30
      7782 CLARK      MANAGER         7839 1981-06-09         1111                    10
      7934 MILLER     CLERK           7782 1982-01-23         1111                    10

14 rows selected.

SQL> 


-- Drop an Existing Policy
The DROP_POLICY procedure is used to remove an existing redaction policy. The following example drops the redaction policy and queries the data, showing the redaction is no longer taking place.

CONN test/test@pdb1

BEGIN
  DBMS_REDACT.drop_policy (
    object_schema => 'scott',
    object_name   => 'emp_test',
    policy_name   => 'redact_sal_info'
  );
END;
/

PL/SQL procedure successfully completed.

SQL> show user;
USER is "SCOTT"
SQL> select * from emp_test;

     EMPNO NAME       JOB             BOSS HIREDATE         SALARY       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7839 KING       PRESIDENT            1981-11-17         5200                    10
      7566 JONES      MANAGER         7839 1981-04-02         2975                    20
      7788 SCOTT      ANALYST         7566 1982-12-09         3000                    20
      7876 ADAMS      CLERK           7788 1983-01-12         1100                    20
      7902 FORD       ANALYST         7566 1981-12-03         3000                    20
      7369 SMITH      CLERK           7902 1980-12-17          800                    20
      7698 BLAKE      MANAGER         7839 1981-05-01         2850                    30
      7499 ALLEN      SALESMAN        7698 1981-02-20         1600        300         30
      7521 WARD       SALESMAN        7698 1981-02-22         1250        500         30
      7654 MARTIN     SALESMAN        7698 1981-09-28         1250       1400         30
      7844 TURNER     SALESMAN        7698 1981-09-08         1500          0         30
      7900 JAMES      CLERK           7698 1981-12-03          950                    30
      7782 CLARK      MANAGER         7839 1981-06-09         2450                    10
      7934 MILLER     CLERK           7782 1982-01-23         1300                    10

14 rows selected.

SQL> 

-- Apply on Varchar Column

SQL> show user;
USER is "SYS"
SQL> 
SQL> BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'scott',
object_name => 'emp_test',
column_name => 'job',
policy_name => 'partially mask job',
function_type => DBMS_REDACT.PARTIAL,
function_parameters => 'VVVVVVVVV,VVVVVVVVV,*,3,12',
expression => '1=1'
);
END;
/   

PL/SQL procedure successfully completed.

SQL> connect scott
Enter password: 
Connected.
SQL> show user
USER is "SCOTT"
SQL> select * from emp_test;

     EMPNO NAME       JOB             BOSS HIREDATE         SALARY       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7839 KING       PR*******            1981-11-17         5200                    10
      7566 JONES      MA*****         7839 1981-04-02         2975                    20
      7788 SCOTT      AN*****         7566 1982-12-09         3000                    20
      7876 ADAMS      CL***           7788 1983-01-12         1100                    20
      7902 FORD       AN*****         7566 1981-12-03         3000                    20
      7369 SMITH      CL***           7902 1980-12-17          800                    20
      7698 BLAKE      MA*****         7839 1981-05-01         2850                    30
      7499 ALLEN      SA******        7698 1981-02-20         1600        300         30
      7521 WARD       SA******        7698 1981-02-22         1250        500         30
      7654 MARTIN     SA******        7698 1981-09-28         1250       1400         30
      7844 TURNER     SA******        7698 1981-09-08         1500          0         30
      7900 JAMES      CL***           7698 1981-12-03          950                    30
      7782 CLARK      MA*****         7839 1981-06-09         2450                    10
      7934 MILLER     CL***           7782 1982-01-23         1300                    10

14 rows selected.

SQL> 

---- Let us create new table to apply data redaction on varchar column

-- Create table
SQL> show user;
USER is "SCOTT"
SQL> 

create table creditcard (cust_id number(4) primary key, card_no varchar2(19), gate_name varchar2(20), expiry_date DATE, PIN number(4));

insert into creditcard values(1000,'1234-1234-1234-1234','VISA POWER','01-JAN-2022',5555);
insert into creditcard values(1001,'2323-2323-2323-2323','VISA POWER','01-MAR-2028',6666);
insert into creditcard values(1002,'4321-4321-4321-4321','RUPAY GATE','09-JAN-2026',9999);
insert into creditcard values(1003,'6789-6789-6789-6789','VISA POWER','31-DEC-2022',8888);
insert into creditcard values(1004,'1234-5678-1234-5678','RUPAY GATE','31-DEC-2024',7777);


SQL> select * from creditcard;

   CUST_ID CARD_NO             GATE_NAME            EXPIRY_DATE               PIN
---------- ------------------- -------------------- ------------------ ----------
      1000 1234-1234-1234-1234 VISA POWER           01-JAN-22                5555
      1001 2323-2323-2323-2323 VISA POWER           01-MAR-28                6666
      1002 4321-4321-4321-4321 RUPAY GATE           09-JAN-26                9999
      1003 6789-6789-6789-6789 VISA POWER           31-DEC-22                8888
      1004 1234-5678-1234-5678 RUPAY GATE           31-DEC-24                7777

SQL> 


-- Applying data redaction on varchar and number col
-- Fisrt add column to data redaction

BEGIN
  DBMS_REDACT.add_policy(
    object_schema => 'scott',
    object_name   => 'creditcard',
    column_name   => 'PIN',
    policy_name   => 'redact_card_pin',
    function_type => DBMS_REDACT.full,
    expression    => '1=1'
  );
END;
/

PL/SQL procedure successfully completed.

SQL> show user;
USER is "SCOTT"
SQL> select * from creditcard;

   CUST_ID CARD_NO             GATE_NAME            EXPIRY_DATE               PIN
---------- ------------------- -------------------- ------------------ ----------
      1000 1234-1234-1234-1234 VISA POWER           01-JAN-22                   0
      1001 2323-2323-2323-2323 VISA POWER           01-MAR-28                   0
      1002 4321-4321-4321-4321 RUPAY GATE           09-JAN-26                   0
      1003 6789-6789-6789-6789 VISA POWER           31-DEC-22                   0
      1004 1234-5678-1234-5678 RUPAY GATE           31-DEC-24                   0

SQL> 

SQL> show user;
USER is "SYS"
SQL> 
BEGIN
  DBMS_REDACT.drop_policy (
    object_schema => 'scott',
    object_name   => 'creditcard',
    policy_name   => 'redact_card_pin'
  );
END;
/

PL/SQL procedure successfully completed.

--- redact card_no

SQL> show user;
USER is "SYS"
SQL> 
SQL> BEGIN
        DBMS_REDACT.ADD_POLICY(
        object_schema => 'scott',
        object_name => 'creditcard',
        column_name => 'card_no',
        policy_name => 'partially mask cardno',
        function_type => DBMS_REDACT.PARTIAL,
        function_parameters => 'VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,*,3,12', -- put '*' from 3rd position to 12th position
        expression => '1=1'
);
END;
/

PL/SQL procedure successfully completed.

SQL> 
SQL> connect scott/scott
Connected.
SQL> show user;
USER is "SCOTT"
SQL> set lines 222
SQL> select * from creditcard;

   CUST_ID CARD_NO             GATE_NAME            EXPIRY_DATE               PIN
---------- ------------------- -------------------- ------------------ ----------
      1000 12**-****-****-1234 VISA POWER           01-JAN-22                5555
      1001 23**-****-****-2323 VISA POWER           01-MAR-28                6666
      1002 43**-****-****-4321 RUPAY GATE           09-JAN-26                9999
      1003 67**-****-****-6789 VISA POWER           31-DEC-22                8888
      1004 12**-****-****-5678 RUPAY GATE           31-DEC-24                7777

SQL> 

SQL>

-- Again redefine (put '*' from 1st position to 12th position)

SQL> show user
USER is "SYS"
SQL> BEGIN
  DBMS_REDACT.drop_policy (
    object_schema => 'scott',
    object_name   => 'creditcard',
    policy_name   => 'partially mask cardno'
  );
END;
/  

PL/SQL procedure successfully completed.

SQL>

SQL> BEGIN
        DBMS_REDACT.ADD_POLICY(
        object_schema => 'scott',
        object_name => 'creditcard',
        column_name => 'card_no',
        policy_name => 'partially mask cardno',
        function_type => DBMS_REDACT.PARTIAL,
        function_parameters => 'VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,*,3,12', -- put '*' from 3rd position to 12th position
        expression => '1=1'
);
END;
/

SQL> 
BEGIN
        DBMS_REDACT.ADD_POLICY(
        object_schema => 'scott',
        object_name => 'creditcard',
        column_name => 'card_no',
        policy_name => 'partially mask cardno',
        function_type => DBMS_REDACT.PARTIAL,
        function_parameters => 'VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,*,1,12',
        expression => '1=1'
);
END;
/

PL/SQL procedure successfully completed.
SQL> 

-- Now verify

SQL> show user
USER is "SCOTT"
SQL> select * from creditcard;

   CUST_ID CARD_NO             GATE_NAME            EXPIRY_DATE               PIN
---------- ------------------- -------------------- ------------------ ----------
      1000 ****-****-****-1234 VISA POWER           01-JAN-22                5555
      1001 ****-****-****-2323 VISA POWER           01-MAR-28                6666
      1002 ****-****-****-4321 RUPAY GATE           09-JAN-26                9999
      1003 ****-****-****-6789 VISA POWER           31-DEC-22                8888
      1004 ****-****-****-5678 RUPAY GATE           31-DEC-24                7777

SQL> 

To be added more ...