Tag Archives: example

DDL Execution in Oracle Sharded Database

DDL Execution in Oracle Sharded Database
The Shard catalog database has copies of all the objects present in sharded database. If catalog validate the DDL then it applied on all shards(database).
If new shard is added then from catalog database it will propagate the ddls to all the shards (databases).

Two ways you can issue DDLs in an SDB:

1. GDSCTL sql command.
When you execute a command , all shards are updated and return status of execution. A master copy is present in shard catalog.


GDSCTL> sql "create tablespace set tbsset"

2. Connect to the shard catalog database using SQL*Plus using the GDS$CATALOG.sdbname service
When you execute the command, local shards response on execution and all other shards are working inbackgroud.

SQL> create tablespace set tbsset;

For SQL PLUS two type of object is created. SDB object and local object. Local objects are the object that exist in Shard catalog and used for administrative purpose.
Note: type of object (SDB or local) that is created in a SQL*Plus session depends on whether the SHARD DDL mode is enabled in the session. Mode is enabled default if connect with catalog database of SDB user.

--For Disable create local object
alter session disable shard ddl;

--For enable create shared objects
alter session enable shard ddl;

Note: If you direct connected with shared user or catalog, you donot need to enable shared it automatic enabled.

Check Status of DDL Commands:
SHOW DDL and CONFIG SHARD is used from GDSCTL. It will tell you failed shards on which command is failed.

GDSCTL> show ddl
id DDL Text Failed shards
-- ------------------------------------------- -------------
1 create user example_user identified by *****

Note: IF DDL failed on one shard all further DDL are blocked until failure is resolved (GDSCTL recover shard)

Example For checking the DDL command executed on all shreds:

-- Enable the Shard DDL Session
SQL> alter session enable shard ddl;

-- Fire the Command for creating a User
SQL> CREAT USER example_user IDENTRIFIED BY password1;
CREATE USER example_user IDENTRIFIED BY password1
*
ERROR at line 1:
ORA-00922: missing or invalid option

-- Check the Command show in List.
GDSCTL> show ddl
id DDL Text Failed shards
-- -------- -------------

Note: it show command is not listed means not executed in any Shard(database)

--Fire the create user command again
SQL> CREATE USER example_user IDENTIFIED BY password1;
User created.

-- Show the command is executed successfully
GDSCTL>show ddl
id DDL Text Failed shards
-- ------------------------------------------- -------------
1 create user example_user identified by *****

Advertisements

Oracle Redaction

Oracle 12c having new feature Oracle Redaction

Oracle redaction means mask the data while fetching by application. It have the ability to redact sensitive data.

Types of Redaction:
Full Redaction: Redact full column data.
Partial Redaction: column data portion or part is redact as per need.
Regular Redaction: Regular expression to redact column data as a pattern. It is for character data only.
Random Redaction: It will generate random value at time of query.
No Redaction: Test the internal operation of redaction policy.

Syntax to add policy of Redaction:

DBMS_REDACT.ADD_POLICY (
object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2 := NULL,
policy_name IN VARCHAR2,
policy_description IN VARCHAR2 := NULL,
column_name IN VARCHAR2 := NULL,
column_description IN VARCHAR2 := NULL,
function_type IN BINARY_INTEGER := DBMS_REDACT.FULL,
function_parameters IN VARCHAR2 := NULL,
expression IN VARCHAR2,
enable IN BOOLEAN := TRUE,
regexp_pattern IN VARCHAR2 := NULL,
regexp_replace_string IN VARCHAR2 := NULL,
regexp_position IN BINARY_INTEGER :=1,
regexp_occurrence IN BINARY_INTEGER :=0,
regexp_match_parameter IN VARCHAR2 := NULL);

Example to add full redaction
In number datatype it change the value to 0 as default in full redaction.

BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'hr',
object_name => 'employees',
column_name => 'commission_pct',
policy_name => 'redact_com_pct',
function_type => DBMS_REDACT.FULL,
expression => '1=1');
END;
/

Check the table after apply the redact policy

SELECT COMMISSION_PCT FROM HR.EMPLOYEES;
COMMISSION_PCT
--------------
0
0

Check the reduction present in database

SELECT * FROM REDACTION_POLICIES;

SELECT * FROM REDACTION_COLUMNS;

SELECT * FROM REDACTION_VALUES_FOR_TYPE_FULL;

Alter the existing reduction syntax

DBMS_REDACT.ALTER_POLICY (
object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2 := NULL,
policy_name IN VARCHAR2,
action IN BINARY_INTEGER := DBMS_REDACT.ADD_COLUMN,
column_name IN VARCHAR2 := NULL,
function_type IN BINARY_INTEGER := DBMS_REDACT.FULL,
function_parameters IN VARCHAR2 := NULL,
expression IN VARCHAR2 := NULL,
regexp_pattern IN VARCHAR2 := NULL,
regexp_replace_string IN VARCHAR2 := NULL,
regexp_position IN BINARY_INTEGER := NULL,
regexp_occurrence IN BINARY_INTEGER := NULL,
regexp_match_parameter IN VARCHAR2 := NULL,
policy_description IN VARCHAR2 := NULL,
column_description IN VARCHAR2 := NULL);

ACTION parameter value defines what will the change done on the existing redaction policy: Following are the value defines the ACTION value:
DBMS_REDACT.MODIFY_COLUMN : Change the column_name value.
DBMS_REDACT.ADD_COLUMN : Add a new column in existing redaction policy.
DBMS_REDACT.DROP_COLUMN : Remove column from redaction policy.
DBMS_REDACT.MODIFY_EXPRESSION: Change the expression value.
DBMS_REDACT.SET_POLICY_DESCRIPTION: Change the description
DBMS_REDACT.SET_COLUMN_DESCRIPTION: Change the description of column.

Example of adding a new column in existing policy of data redaction

BEGIN
DBMS_REDACT.ALTER_POLICY(
object_schema => 'hr',
object_name => 'employees',
policy_name => 'hr_employees_pol',
action => DBMS_REDACT.ADD_COLUMN,
column_name => 'hire_date',
function_type => DBMS_REDACT.PARTIAL,
function_parameters => DBMS_REDACT.REDACT_DATE_EPOCH);
END;
/
Note: detail you got in partial redaction page.

Drop the policy

BEGIN
DBMS_REDACT.DROP_POLICY (
object_schema => 'hr',
object_name => 'employees',
policy_name => 'hr_employees_pol');
END;
/

Enable the data redaction policy

BEGIN
DBMS_REDACT.ENABLE_POLICY (
object_schema => 'hr',
object_name => 'employees',
policy_name => 'hr_employees_pol');
END;
/

Disable the data redaction policy

BEGIN
DBMS_REDACT.DISABLE_POLICY (
object_schema => 'hr',
object_name => 'employees',
policy_name => 'hr_employees_pol');
END;
/

Rman Merged backup

Rman merged backup script for windows environment:

Rman Merged Backup Script works as for both level 1 backup merged with level 0 backup.

Level 0 Backup is full backup of database

Level 1 is incremental backup after level 0.

Merged level 1 backup merged backup with level 0 backup files. It update old level 0 files backup and merge with them.

Following command is used at Enterprise edition to enable the block change tracking for level 1 incremental backup. Its not supported in Standard Editions

It will track all the record updated after level 0 backup so that it fasten the process of rman level 1 backup

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE 'C:\blockchangetracking.dbf' REUSE;

Level 0 Backup Script

run {
CONFIGURE RETENTION POLICY TO REDUNDANCY 10;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'C:\RMAN\Db_df%t_s%s_s%p';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'c:\rman\ctrl_%F';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;
allocate channel c1 device type disk ;
allocate channel c2 device type disk ;
BACKUP INCREMENTAL LEVEL 0 DATABASE format 'C:\RMAN\%d_%s_%T_%c_DB0' TAG="DB_LEV0";
BACKUP ARCHIVELOG ALL format 'C:\RMAN\%d_%s_%T_%c_ARCH0' tag "ARCH_LEV0";
backup current controlfile format 'C:\RMAN\%d_%s_%T_%c_ARCH0%d_%s_%T_CONTROL' tag "CNTRLFL";
backup spfile format 'C:\RMAN\%d_%s_%T_%c_ARCH0%d_%s_%T_SPFILE' tag "SPFILE" ;
}

Level 1 Backup

run {
allocate channel c1 device type disk ;
allocate channel c2 device type disk ;
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG DB_LEV0 DATABASE;
RECOVER COPY OF DATABASE WITH TAG DB_LEV0;
BACKUP ARCHIVELOG ALL format 'C:\RMAN\%d_%s_%T_%c_ARCH0' tag "ARCH_LEV1";
backup current controlfile format 'C:\RMAN\%d_%s_%T_CONTROL' tag "CNTRLFL" ;
backup spfile format 'C:\RMAN\%d_%s_%T_SPFILE' tag "SPFILE" ;
}