Overview:

In this article we explain the process of how to De-Register DBV on RAC DB.

Oracle Database Vault is a security product which is being accessed by privileged database users like DBAs. Application data can be protected with policies that control access by setting up the rules.

Environment Details

Hostname	Node1 : db01 Node 2: db02
Database version	11.2.0.4
Database Environment	OMFDB

Disable DB vault

$sqlplus -s / as sysdba

SQL>set lines 200

SQL>set pages 0

SQL>set feed off

SQL>select status from dba_registry where COMP_NAME=’Oracle Database Vault’;

STATUS

———–

VALID

SQL>conn dbvowner/******

Connected.

SQL>BEGIN

DBMS_MACADM.UPDATE_COMMAND_RULE(

command => ‘CONNECT’,

rule_set_name => ‘LIMIT_DIP_MONITOR_USER’,

object_owner => NULL,

object_name => NULL,

enabled => DBMS_MACUTL.G_NO); —-> it will disable the DBV rule and it will allow to application to connect db

END;

PL/SQL procedure successfully completed.

SQL>BEGIN

DBMS_MACADM.UPDATE_RULE_SET (

rule_set_name => ‘LIMIT_DIP_MONITOR_USER’,

description => ‘Limit DIP_MONITOR_USER Access to certain IP’,

enabled => DVSYS.DBMS_MACUTL.G_NO,

eval_options => DVSYS.DBMS_MACUTL.G_RULESET_EVAL_ANY,

audit_options => DVSYS.DBMS_MACUTL.G_RULESET_AUDIT_FAIL,

fail_options => DVSYS.DBMS_MACUTL.G_RULESET_FAIL_SHOW,

fail_message => ‘Rule Set Limit DIP_MONITOR_USER Access failed.’,

fail_code => -20001,

handler_options => DVSYS.DBMS_MACUTL.G_RULESET_HANDLER_OFF,

handler => NULL);

END;

PL/SQL procedure successfully completed.

SQL> conn dbvowner/*******

Connected.

SQL> select ENABLED from dba_dv_rule_set where RULE_SET_NAME=’LIMIT_DIP_MONITOR_USER’;

–

Run following scripts to remove Vault.

SQL> set line 200

SQL> SELECT HOST_NAME,name,INSTANCE_NAME,open_mode,to_char(startup_time,’DD-MON-YYYY HH24:MI:SS’) “DB Startup Time” FROM gv$database,sys.gv_$instance;

HOST_NAME NAME INSTANCE_NAME OPEN_MODE DB Startup Time

—————————————————————- ——— —————-

dm01.tfn.com OMFDB omfdb01 READ WRITE 14-JAN-2017

dm01.tfn.com OMFDB omfdb02 READ WRITE 14-JAN-2017

SQL> update dvsys.config$ set status=0;

1 row updated.

SQL> commit;

Commit complete.

SQL> @?/rdbms/admin/dvremov.sql

PL/SQL procedure successfully completed.

Commit complete.

PL/SQL procedure successfully completed.

1 row deleted.

PL/SQL procedure successfully completed.

Drop DBV users and grant back the roles and privs to DBA

SQL> drop user dbvowner cascade;

User dropped.

SQL> drop user dbvacctmgr cascade;

User dropped.

SQL> grant BECOME USER to DBA;

Grant succeeded.

SQL> grant SELECT ANY TRANSACTION to DBA;

Grant succeeded.

SQL> grant CREATE ANY JOB to DBA;

Grant succeeded.

SQL> grant CREATE EXTERNAL JOB to DBA;

Grant succeeded.

SQL> grant EXECUTE ANY PROGRAM to DBA;

Grant succeeded.

SQL> grant EXECUTE ANY CLASS to DBA;

Grant succeeded.

SQL> grant MANAGE SCHEDULER to DBA;

Grant succeeded.

SQL> grant DEQUEUE ANY QUEUE to DBA;

Grant succeeded.

SQL> grant ENQUEUE ANY QUEUE to DBA;

Grant succeeded.

SQL> grant MANAGE ANY QUEUE to DBA;

Grant succeeded.

SQL> grant BECOME USER to IMP_FULL_DATABASE;

Grant succeeded.

SQL> grant MANAGE ANY QUEUE to IMP_FULL_DATABASE;

Grant succeeded.

SQL> grant DBA to INFA_ADMIN;

Grant succeeded.

SQL> grant EXECUTE ON DBMS_LOGMNR to EXECUTE_CATALOG_ROLE;

Grant succeeded.

SQL> grant EXECUTE ON DBMS_LOGMNR_D to EXECUTE_CATALOG_ROLE;

Grant succeeded.

SQL> grant EXECUTE ON DBMS_LOGMNR_LOGREP_DICT to EXECUTE_CATALOG_ROLE;

Grant succeeded.

SQL> grant EXECUTE ON DBMS_LOGMNR_SESSION to EXECUTE_CATALOG_ROLE;

Grant succeeded.

SQL> grant EXECUTE ON DBMS_FILE_TRANSFER to EXECUTE_CATALOG_ROLE;

Grant succeeded.

SQL> grant EXECUTE ON UTL_FILE to PUBLIC;

Grant succeeded.

SQL> grant CREATE ANY JOB to SCHEDULER_ADMIN;

Grant succeeded.

SQL> grant CREATE EXTERNAL JOB to SCHEDULER_ADMIN;

Grant succeeded.

SQL> grant EXECUTE ANY PROGRAM to SCHEDULER_ADMIN;

Grant succeeded.

SQL> grant EXECUTE ANY CLASS to SCHEDULER_ADMIN;

Grant succeeded.

SQL> grant MANAGE SCHEDULER to SCHEDULER_ADMIN;

Grant succeeded.

SQL> ALTER SYSTEM SET AUDIT_SYS_OPERATIONS=FALSE SCOPE=SPFILE sid=’*’;

System altered.

SQL> ALTER SYSTEM SET RECYCLEBIN=’ON’ SCOPE=SPFILE sid=’*’;

System altered.

SQL> ALTER SYSTEM SET SQL92_SECURITY=FALSE SCOPE=SPFILE sid=’*’;

System altered.

Check the status of DBV

SQL> select status from dba_registry where COMP_NAME=’Oracle Database Vault’;

no rows selected

exit

Bounce the DB

$ srvctl status database -d omfdb

Instance omfdb1 is running on node dm01

Instance omfdb2 is running on node dm02

$ srvctl stop database -d omfdb

$ srvctl status database -d omfdb

Instance omfdb1 is not running on node dm01

Instance omfdb2 is not running on node dm02

$ srvctl start database -d omfdb

$ srvctl status database -d omfdb

Instance omfdb1 is running on node dm01

Instance omfdb2 is running on node dm02

Verify DBV removed completely.

SQL> select status from dba_registry where COMP_NAME=’Oracle Database Vault’;

no rows selected

exit

Conculsion:

In this article we learnt the process of how to De-Register DBV on RAC DB.

Update Existing Database Vault settings.

By Netsoftmate on

February 11, 2017

- Uncategorized

Overview:

In this article we explain the process of how to Update the existing Database Vault settings.

Oracle Database Vault is a security product which is being accessed by privileged database users like DBAs.Application data can be protected with policies that control access by setting up the rules.

Environment Details

Hostname	Node1 : db01 Node 2: db02
Database version	11.2.0.4
Database Environment	OMFDB

$sqlplus / as sysdba

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,

OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

SQL> set line 200

SQL> set pages 00

SQL> set pages 200

SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE

——— ——————– —————-

OMFDB READ WRITE PRIMARY

SQL> SELECT RULE_SET_NAME, HANDLER_OPTIONS, HANDLER FROM DVSYS.DBA_DV_RULE_SET

WHERE RULE_SET_NAME = ‘Allow MONITOR_USER Access’; 2

no rows selected

SQL>

SQL> SELECT * FROM DVSYS.DBA_DV_RULE WHERE NAME = ‘Allow MONITOR_USER Access’;

NAME RULE_EXPR

——————————————————————————————

Allow MONITOR_USER Access DVF.F$CLIENT_IP in (‘10.10.1.1′,’172.10.1.1′,’192.168.1.1’) and DVF.F$SESSION_USER = ‘MONITOR_USER’

SQL> SELECT RULE_SET_NAME, RULE_NAME, RULE_EXPR FROM DVSYS.DBA_DV_RULE_SET_RULE WHERE RULE_NAME = ‘Allow MONITOR_USER Access’;

RULE_SET_NAME RULE_NAME RULE_EXPR

—————————————————————————————— ——————————————————————————————

LIMIT_MONITOR_USER Allow MONITOR_USER Access DVF.F$CLIENT_IP in (‘10.10.1.1′,’172.10.1.1′,’192.168.1.1’) and DVF.F$SESSION_USER = ‘MONITOR_USER’

SQL> conn dbvowner/*********
Connected.
SQL> SELECT * FROM DVSYS.DBA_DV_RULE WHERE NAME = ‘Allow MONITOR_USER Access’;

NAME RULE_EXPR
——————————————————————————————

Allow MONITOR_USER Access DVF.F$CLIENT_IP in (‘10.10.1.1′,’172.10.1.1′,’192.168.1.1’) and DVF.F$SESSION_USER = ‘MONITOR_USER’

SQL> begin
DVSYS.DBMS_MACADM.UPDATE_RULE
2 3 (rule_name => ‘Allow MONITOR_USER Access’,
4 rule_expr => ‘DVF.F$CLIENT_IP in (”10.10.1.1”,”172.10.1.1”,”192.168.1.1”, ”10.10.10.1”,”10.10.10.120”) and DVF.F$SESSION_USER = ”MONITOR_USER”’);
5 end;
6 /

PL/SQL procedure successfully completed.

Verify the changes

SQL> SELECT * FROM DVSYS.DBA_DV_RULE WHERE NAME = ‘Allow MONITOR_USER Access’;

NAME RULE_EXPR
——————————————————————————————
Allow MONITOR_USER Access DVF.F$CLIENT_IP in (‘10.10.1.1′,’172.10.1.1′,’192.168.1.1′,10.10.10.1′,’10.10.10.120’) and DVF.F$SESSION_USER = ‘MONITOR_USER’

Overview:

In this article we learnt how to Update the existing Database Vault settings.

Enable Database Vault on RAC Database

By Netsoftmate on

February 11, 2017

- Uncategorized

Overview:

In this article we explain the process of how to register RAC DB in Database Vault

Oracle Database Vault is a security product which is being accessed by privileged database users like DBAs.

Application data can be protected with policies that control access by setting up the rules.

Environment Details

Hostname	Node1 : db01 Node 2: db02
Database version	11.2.0.4
Database Environment	OMFDB

$export PATH=$PATH:$ORACLE_HOME/bin
$dbca -silent -configureDatabase -sourceDB omfdb -sysDBAUserName sys -sysDBAPassword ******* -addDBOption OMS,DV -dvUserName dbvowner -dvUserPassword ******* -dvAccountManagerName dbvacctmgr -dvAccountManagerPassword ********

Preparing to Configure Database

1% complete

3% complete

18% complete

Adding Oracle Label Security

19% complete

20% complete

21% complete

22% complete

23% complete

24% complete

42% complete

54% complete

Adding Oracle Database Vault

65% complete

90% complete

Completing Database Configuration

100% complete

Look at the log file “/u01/app/oracle/cfgtoollogs/dbca/omfdb/omfdb.log” for further details.

$sqlplus / as sysdba

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,

OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

SQL> set line 200

SQL> set pages 200

SQL> show parameter AUDIT_SYS_OPERATIONS

NAME TYPE VALUE

———————————— ———– ——————————

audit_sys_operations boolean FALSE

SQL> show parameter AUDIT_SYS_OPERATIONS

NAME TYPE VALUE

———————————— ———– ——————————

audit_sys_operations boolean FALSE

SQL> show parameter os_roles

NAME TYPE VALUE

———————————— ———– ——————————

os_roles boolean FALSE

remote_os_roles boolean FALSE

SQL> show parameter RECYCLEBIN

NAME TYPE VALUE

———————————— ———– ——————————

recyclebin string on

SQL> show parameter REMOTE_LOGIN_PASSWORDFILE

NAME TYPE VALUE

———————————— ———– ——————————

remote_login_passwordfile string EXCLUSIVE

SQL> show parameter SQL92_SECURITY

NAME TYPE VALUE

———————————— ———– ——————————

sql92_security boolean FALSE

SQL> ALTER SYSTEM SET AUDIT_SYS_OPERATIONS=TRUE SCOPE=SPFILE sid=’*’;

System altered.

sql>ALTER SYSTEM SET OS_ROLES=FALSE SCOPE=SPFILE sid=’*’;

System altered.

SQL>ALTER SYSTEM SET RECYCLEBIN=’OFF’ SCOPE=SPFILE sid=’*’;

System altered.

SQL>ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=’EXCLUSIVE’ SCOPE=SPFILE sid=’*’;

System altered.

SQL>ALTER SYSTEM SET SQL92_SECURITY=TRUE SCOPE=SPFILE sid=’*’;

System altered.

Check the changes done and DBV Enabled.

SQL> show parameter AUDIT_SYS_OPERATIONS

NAME TYPE VALUE

———————————— ———– ——————————

audit_sys_operations boolean FALSE

SQL> set pages 200

SQL> select status from dba_registry where COMP_NAME=’Oracle Database Vault’;

STATUS

——————————————–

VALID

Bouce database

$ srvctl status database -d omfdb

Instance omfdb1 is running on node dm01

Instance omfdb2 is running on node dm02

$ srvctl stop database -d omfdb

$ srvctl status database -d omfdb

Instance omfdb1 is not running on node dm01

Instance omfdb2 is not running on node dm02

$ srvctl start database -d omfdb

$ srvctl status database -d omfdb

Instance omfdb1 is running on node dm01

Instance omfdb2 is running on node dm02

Re-grant for app specific

SQL> connect / as sysdba

SQL> grant become user to IMP_FULL_DATABASE;

Grant succeeded.

Setting up DBV rules for app

Here in this example we are setting up the DBV rule to allow apps users to access the Database.

$sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 2017

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,

OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

SQL> set line 200

SQL> set pages 00

SQL> set pages 200

SQL> select name from v$database;

NAME

———

OMFDB

SQL> select status from dba_registry where COMP_NAME=’Oracle Database Vault’;

STATUS

——————————————–

VALID

SQL> conn dbvowner/*******

Connected.

SQL>BEGIN

DBMS_MACADM.CREATE_RULE (

rule_name => ‘Allow MONITOR_USER Access’,

rule_expr => ‘DVF.F$CLIENT_IP in (”10.10.1.1”,”172.10.1.1”,”192.168.1.1”) and DVF.F$SESSION_USER = ”MONITOR_USER”’);

END;

PL/SQL procedure successfully completed.

SQL>BEGIN

DBMS_MACADM.CREATE_RULE (

rule_name => ‘Allow non MONITOR_USER Access’,

rule_expr => ‘UPPER(DVF.F$CLIENT_IP) NOT in (”10.10.1.1”,”172.10.1.1”,”192.168.1.1”) OR DVF.F$CLIENT_IP is NULL’ );

END;

PL/SQL procedure successfully completed.

Now Create the Rule set

SQL>BEGIN

DBMS_MACADM.CREATE_RULE_SET (

rule_set_name => ‘LIMIT_MONITOR_USER’,

description => ‘Limit MONITOR_USER Access to certain IP’,

enabled => DVSYS.DBMS_MACUTL.G_YES,

eval_options => DVSYS.DBMS_MACUTL.G_RULESET_EVAL_ANY,

audit_options => DVSYS.DBMS_MACUTL.G_RULESET_AUDIT_FAIL,

fail_options => DVSYS.DBMS_MACUTL.G_RULESET_FAIL_SHOW,

fail_message => ‘Rule Set Limit MONITOR_USER Access failed.’,

fail_code => -20001,

handler_options => DVSYS.DBMS_MACUTL.G_RULESET_HANDLER_OFF,

handler => NULL);

END;

PL/SQL procedure successfully completed.

SQL>BEGIN

DBMS_MACADM.ADD_RULE_TO_RULE_SET (

rule_set_name => ‘LIMIT_MONITOR_USER’,

rule_name => ‘Allow non MONITOR_USER Access’,

rule_order => 1);

DBMS_MACADM.ADD_RULE_TO_RULE_SET (

rule_set_name => ‘LIMIT_MONITOR_USER’,

rule_name => ‘Allow MONITOR_USER Access’,

rule_order => 1);

END;

PL/SQL procedure successfully completed.

SQL>BEGIN

DBMS_MACADM.CREATE_COMMAND_RULE (

command => ‘CONNECT’,

rule_set_name => ‘LIMIT_MONITOR_USER’,

object_owner => NULL,

object_name => NULL,

enabled => DBMS_MACUTL.G_YES);

END;

PL/SQL procedure successfully completed.

Verify the DBV rule creation.

SQL> SELECT * FROM DVSYS.DBA_DV_RULE WHERE NAME = ‘Allow MONITOR_USER Access’;

NAME

——————————————————————————————

RULE_EXPR

——————————————————————————————————————————————————————————————————–

Allow MONITOR_USER Access

DVF.F$CLIENT_IP in (‘10.10.1.1′,’172.10.1.1′,’192.168.1.1’) and DVF.F$SESSION_USER = ‘MONITOR_USER’

SQL> SELECT RULE_SET_NAME, RULE_NAME, RULE_EXPR FROM DVSYS.DBA_DV_RULE_SET_RULE

WHERE RULE_NAME = ‘Allow MONITOR_USER Access’; 2

RULE_SET_NAME RULE_NAME

—————————————————————————————— ——————————————————————————————

RULE_EXPR

LIMIT_MONITOR_USER_ACCESS Allow MONITOR_USER Access

DVF.F$CLIENT_IP in (‘10.10.1.1′,’172.10.1.1′,’192.168.1.1’) and DVF.F$SESSION_USER = ‘MONITOR_USER’

SQL> conn dbvowner/*********

Connected.

SQL> SELECT * FROM DVSYS.DBA_DV_RULE WHERE NAME = ‘Allow MONITOR_USER Access’;

NAME

——————————————————————————————

RULE_EXPR

Allow MONITOR_USER Access

DVF.F$CLIENT_IP in (‘10.10.1.1′,’172.10.1.1′,’192.168.1.1’) and DVF.F$SESSION_USER = ‘MONITOR_USER’

Conclusion:

In this article we explain the process of how to register RAC DB in Database Vault

RAC DB creation on Exadata Machine using DBCA.

By Netsoftmate on

February 11, 2017

- Uncategorized

Overview:

In this article we explain the process of how to create RAC DB on Exadata Machine using DBCA

DBCA is the oracle utility which provide the easy and fast way to create the DB.
Environment Details

Hostname	Node1 : db01 Node 2: db02
Database version	11.2.0.4
Database Environment	OMFDB

Login to Exadata Machine node1 and initate DB creation using DBCA.

$export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db
$dbca

DBCA launched db creation window.
select oracle Real appliction clusters(RAC) database option than next

Select create database option then Next

Select General purpose and click next

Select Admin-Managed and give the DB Name and SID and select nodes in Exadata Machine.Then Next

Un-check Register with Grid crontrol and slect configuration Database control for Local Management.Then Next

Provide the SYS and SYS and all the other password.Then Next

Provide the Database files location.Then Next

Click on Multiplex Redo logs and Control Files if you want.Then Next

Provide ASMSNMP Password.Then Next

Provide the Flash Recovery Area Diskgroup. Then Next

Provide the Archive log format.Then Next

Provide SGA and PGA and all the sizing and character set ,Then Next

Provide the Redo log size and Next.

Create Database and Finish.

Read Summary and Click OK

Progress

Database Creation completion Summary, Click Exit.

Check the Database status it should run on both nodes.

$ srvctl status database -d omfdb

Instance omfdb1 is running on node dm01

Instance omfdb2 is running on node dm02

Conclusion:

We learnt the process of how to create RAC DB on Exadata Machine using DBCA

Data guard Broker setup

By Netsoftmate on

February 11, 2017

- Uncategorized

Overview:

In this article we explain the process of Data guard Broker setup

DG Broker maintains, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions

Environment Details

Hostname	Primary server :–Primary Standby Server :– Standby
Operating system	Linux
Database Environment	NSMPRD and NSMDR

Ensure that both the primary and standby databases are up and running as shown in the following query

NSMPRD@PRIMARY

SQL> select db_unique_name,open_mode,database_role from v$database;

DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE

—————————— ——————– —————-

nsmprd READ WRITE PRIMARY

NSMDR@STANDBY

SQL> select db_unique_name,open_mode,database_role from v$database;

DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE

—————————— ——————– —————-

nsmdr READ WRITE PRIMARY

Ensure that both the primary and standby databases are using server parameter files, so that the broker can form a healthy relationship between the broker properties and parameter values as follows

NSMPRD@PRIMARY

SQL> show parameter spfile

NAME TYPE VALUE

———————————— ——————————-

spfile string /u01/app/oracle/product/11.2.0

.4/db/dbs/spfilensmprd.ora

NSMDR@STANDBY

SQL> show parameter spfile

NAME TYPE VALUE

———————————— ——————————-

spfile string /u01/app/oracle/product/11.2.0.4/db/dbs/spfilensmdr.ora

Set configuration file parameter locations on both primary and standby

NSMPRD@PRIMARY

SQL> ALTER SYSTEM SET dg_broker_config_file1 = ‘/u01/oraadmin1/nsmprd/dbs/dr1nsmprd.dat’ scope=both;

System altered.

SQL> ALTER SYSTEM SET dg_broker_config_file2 = ‘/u01/oraadmin1/nsmprd/dbs/dr2nsmprd.dat’ scope=both;

System altered.

NSMDR@STANDBY

SQL> ALTER SYSTEM SET dg_broker_config_file1 = ‘/u01/oraadmin1/nsmdr/dbs/dr1nsmdr.dat’ scope=both;

System altered.

SQL> ALTER SYSTEM SET dg_broker_config_file2 = ‘/u01/oraadmin1/nsmdr/dbs/dr2nsmdr.dat’ scope=both;

System altered.

Start the DMON process on both the primary and standby databases by setting the DG_BROKER_START parameter as follows

NSMPRD@PRIMARY

SQL> alter system set dg_broker_start=TRUE scope=both;

System altered.

NSMDR@STANDBY

SQL> alter system set dg_broker_start=TRUE scope=both;

System altered.

The configuration files will be created under the specified location or in the default directory automatically. The Data Guard broker will maintain two copies of its configuration files as follows: Primary and standby

NSMPRD@PRIMARY

SQL> show parameter DG_BROKER_CONFIG_FILE

NAME TYPE VALUE

———————————— ——————————-

dg_broker_config_file1 string

/u01/oraadmin1/nsmprd/dbs/dr1 nsmprd.dat

dg_broker_config_file2 string /u01/oraadmin1/nsmprd/dbs/dr2nsmprd.dat

NSMDR@STANDBY

SQL> show parameter DG_BROKER_CONFIG_FILE

NAME TYPE VALUE

———————————— ——————————-

dg_broker_config_file1 string /u01/oraadmin1/nsmdr/dbs/dr1nsmdr.dat

dg_broker_config_file2 string /u01/oraadmin1/nsmdr/dbs/dr2nsmdr.dat

NSMPRD@PRIMARY

Connect DGMGRL on the primary system and create the configuration as follows

Note: Make sure all the required directories are created before proceeding with enable configuration

$dgmgrl /

DGMGRL for Linux: Version 11.2.0.4.0 – 64bit Production

Welcome to DGMGRL, type “help” for information.

Connected.

DGMGRL>

DGMGRL> CREATE CONFIGURATION ‘nsmprd’ AS PRIMARY DATABASE IS ‘nsmprd’ CONNECT IDENTIFIER IS nsmprd;

Configuration “nsmprd created with primary database “nsmprd”

DGMGRL> ADD DATABASE ‘nsmdr’ AS CONNECT IDENTIFIER IS ‘nsmdr’ maintained as physical;

Dataase “nsmdr” added

Enable DG broker config

$dgmgrl /

DGMGRL for Linux: Version 11.2.0.4.0 – 64bit Production

Welcome to DGMGRL, type “help” for information.

Connected.

DGMGRL>

DGMGRL> show configuration

Configuration – nsmprd

Protection Mode: MaxPerformance

Databases:

nsmprd – Primary database

nsmdr – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED

DGMGRL> enable configuration

Enabled.

DGMGRL> show configuration

Configuration – nsmprd

Protection Mode: MaxPerformance

Databases:

nsmprd – Primary database

nsmdr – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

Update Listener entries on both primary and standby for DG broker

On Standby :

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent

ADR_BASE_LISTENER=/u01/oraadmin1

SID_LIST_LISTENER =

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=nsmdr_DGMGRL)

(ORACLE_HOME= /u01/app/oracle/product/11.2.0.4/db)

(SID_NAME=nsmdr))

)

On Primary:

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent

ADR_BASE_LISTENER=/u01/oraadmin1

SID_LIST_LISTENER =

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=nsmprd_DGMGRL)

(ORACLE_HOME= /u01/app/oracle/product/11.2.0.4/db)

(SID_NAME=nsmprd))

)

$dgmgrl /

DGMGRL for Linux: Version 11.2.0.4.0 – 64bit Production

Welcome to DGMGRL, type “help” for information.

Connected.

DGMGRL> enable configuration

Enabled.

DGMGRL> show configuration

Configuration – nsmprd

Protection Mode: MaxPerformance

Databases:

nsmprd – Primary database

nsmdr – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL>

Conclusion

In this article we have learnt the steps to the data guard broker.

Physical Standby Build using RMAN Active Duplicate

By Netsoftmate on

February 11, 2017

- Uncategorized

Overview:

In this article we explain the process of build a physical standby database using RMAN active duplicate.

Standby database is exact copy of production in read only mode with apply. In case if the production goes down for any outage. Standby can failover and make as primary.

Environment Details

Hostname	Primary server :–Primary Standby Server :– Standby
Operating system	Linux
Database Environment	NSMPRD and NSMDR

NSMPRD@PRIMARY

Determine if the FORCE LOGGIN is enabled.

SQL> select force_logging from v$database;

For

——

YES

If the output of the query is YES, then proceed with the next step. If the output of the above query is NO then enable the FORCE LOGGING at the database level.

SQL> alter database force logging;

Set the primary database initialization parameters

SQL> SHOW PARAMETER DB_NAME

NAME TYPE VALUE

—————————————————————-

db_name string nsmprd

SQL> ALTER SYSTEM SET log_archive_config=’dg_config=(NSMPRD,NSMDR)’;

System altered

SQL> show parameter log_archive_config

NAME TYPE VALUE

—————————————————————-

log_archive_config string ‘dg_config=(NSMPRD,NSMDR)

Set the archive loctions on primary

SQL> alter system set log_archive_dest_1=’LOCATION=/u01/oraarch1/nsmprd valid_for=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=nsmprd REOPEN=30 MAX_FAILURE=3 scope=both;

SYSTEM ALTERED

SQL> alter system set log_archive_dest_2=’service=”nsmdr”‘,’LGWR SYNC AFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=10 db_unique_name=”nsmdr” net_timeout=30′,’template=”/u02/oraarch2/nsmdr_standby/LOG_%r_%s_%t.ARC” valid_for=(all_logfiles,primary_role)’ scope=both;

System altered

SQL> alter system set log_archive_dest_state_3=’ENABLE’;

System altered

SQL> alter system fal_client= NSMPRD;

System altered

SQL> alter system fal_server= NSMDR;

System altered

SQL> Show parameter fal

NAME TYPE VALUE

—————————————————————-

fal_client string NSMPRD

fal_server sting NSMDR

SQL> ALTER SYSTEM SET log_archive_dest_state_2=defer;

System altered

These parameters are dynamic and will take effect immediately. Check the parameter to make sure it points to the correct locations as specified.

SQL> SHOW PARAMETER LOG_ARCHIVE_DEST_2

NAME TYPE VALUE

—————————————————————-

LOG_ARCHIVE_DEST_2 string service=”nsmdr”‘,’LGWR

SYNC AFFIRM delay=0

Optional

compression=disable

max_failure=0

max_connections=1

reopen=10 db_unique_name=”nsmdr”

net_timeout=30′,’template=”/u02/oraarch2/nsmdr_standby/LOG_%r_%s_%t.ARC” valid_for=(all_logfiles,primary_role)’

SQL> SHOW PARAMETER REMOTE_LOGIN_PASSWORDFILE ( this must be set to “EXCLUSIVE” (default))

NAME TYPE VALUE

—————————————————————-

REMOTE_LOGIN_PASSWORDFILE string EXCLUSIVE

Create standby redo log files on the primary database.

SQL>column member format a50

SQL>SELECT a.group#, a.member, b.bytes/1024/1024 bytes_MB

FROM v$logfile a, v$log b WHERE a.group# = b.group#

Union select a.group#, a.member, b.bytes/1024/1024 bytes_MB

from v$logfile a, v$standby_log b WHERE a.group# = b.group#;

GROUP# MEMBER BYTES_MB

—————————————————————-

1 /u01/oradata1/nsmprd/nsmprd_redo01.log 50MB

2 /u01/oradata1/nsmprd/nsmprd_redo02.log 50MB

3 /u01/oradata1/nsmprd/nsmprd_redo03.log 50MB

4 /u01/oradata1/nsmprd/nsmprd_redo04.log 50MB

SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ‘/u01/oradata1/nsmprd/nsmprd_sb_redo05.log’ SIZE 50M;

Database altered

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ‘/u01/oradata1/nsmprd/nsmprd_sb_redo06.log’ SIZE 50M;

Database altered

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ‘/u01/oradata1/nsmprd/nsmprd_sb_redo07.log’ SIZE 50M;

Database altered

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 ‘/u01/oradata1/nsmprd/nsmprd_sb_redo08.log’ SIZE 50M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 9 ‘/u01/oradata1/nsmprd/nsmprd_sb_redo09.log’ SIZE 50M;

Database altered

SQL>column member format a50

SQL>SELECT a.group#, a.member, b.bytes/1024/1024 bytes_MB

FROM v$logfile a, v$log b WHERE a.group# = b.group#

Union select a.group#, a.member, b.bytes/1024/1024 bytes_MB

from v$logfile a, v$standby_log b WHERE a.group# = b.group#;

GROUP# MEMBER BYTES_MB

—————————————————————-

1 /u01/oradata1/nsmprd/nsmprd_redo01.log 50MB

2 /u01/oradata1/nsmprd/nsmprd_redo02.log 50MB

3 /u01/oradata1/nsmprd/nsmprd_redo03.log 50MB

4 /u01/oradata1/nsmprd/nsmprd_redo04.log 50MB

5 /u01/oradata1/nsmprd/nsmprd_sb_redo05.log 50MB

6 /u01/oradata1/nsmprd/nsmprd_sb_redo06.log 50MB

7 /u01/oradata1/nsmprd/nsmprd_sb_redo07.log 50MB

8 /u01/oradata1/nsmprd/nsmprd_sb_redo08.log 50MB

9 /u01/oradata1/nsmprd/nsmprd_sb_redo09.log 50MB

Verify the standby redo log file groups were created (do this after the creation of standby database)

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

GROUP# THREAD# SEQUENCE# ARC STATUS

———- ———- ———- — ———-

5 0 0 YES UNASSIGNED

6 0 0 YES UNASSIGNED

7 0 0 YES UNASSIGNED

8 0 0 YES UNASSIGNED

9 0 0 YES UNASSIGNED

Configuring TNS for primary and physical standby database:

NSMPRD@PRIMARY

NSMDR =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = nsmdr)

)

NSMDR@STANDBY

NSMPRD =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1 )(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = nsmprd)

)

NSMDR =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = nsmdr)

)

LISTENER_NSMDR=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.2)(PORT=1521))

Creating the physical standby database:

NSMDR@STANDBY

Configure listener entry for the standby database in the Listener.ora file on standby server.

$cat listener.ora

SID_LIST_LISTENER =

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=nsmdr)

(ORACLE_HOME= /u01/app/oracle/product/11.2.0.4/db)

(SID_NAME=nsmdr))

)

$tnsping nsmprd

TNS Ping Utility for Linux: Version 11.2.0.4.0 – Production on 05-FEB-2017 09:42:43

Used parameter files:

/u01/app/oracle/product/11.2.0.4/db/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = nsmprd)))

OK (0 msec)

$tnsping nsmdr

TNS Ping Utility for Linux: Version 11.2.0.4.0 – Production on 05-FEB-2017 09:42:51

Used parameter files:

/u01/app/oracle/product/11.2.0.4/db/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = nsmdr)))

OK (0 msec)

NSMPRD@PRIMARY

create password file on Primary

$orapwd file=orapwnsmprd password=****** force=y entries=20

Copy the password file from the primary database server to the standby server.

$scp $ORACLE_HOME/dbs/ orapwnsmprd oracle@STANDBY: $ORACLE_HOME/dbs

NSMDR@STANDBY

In the $ORACLE_HOME/dbs directory of the standby database server create an initialization parameter file named initnsmdr.ora containing only one parameter DB_NAME.

$cd $ORACLE_HOME/dbs

$vi initnsmdr.ora

DB_NAME= NSMPRD

:wq (save and exit the file)

On the standby database server go to ORACLE_BASE/admin and create a directory as your DB_UNIQUE_NAME.

$ cd $ORACLE_BASE/admin

$mkdir NSMDR

On the standby server se the ORACLE_SID for the standby database.

$vi /etc/oratab –>file and entry as below

nsmdr:/u01/app/oracle/product/11.2.0.4/db:N

$ . oraenv

ORACLE_SID = [oracle] ? nsmdr

The Oracle base remains unchanged with value /u01/app/oracle

$ echo $ORACLE_SID=nsmdr

nsmdr

Connect as sysdba and start the database in NOMOUNT state.

SQL> STARTUP NOMOUNT PFILE=’/u01/app/oracle/product/11.2.0.4/db/dbs/ initnsmdr.ora’;

Total System Global Area 225509376 bytes

Fixed Size 2252896 bytes

Variable Size 167773064 bytes

Redo Buffers 5152768 bytes

SQL> EXIT

NSMPRD@PRIMARY

On the primary database server set the ORACLE_SID for primary database.

$ echo $ORACLE_SID

nsmprd

On the primary database server CHECK RMAN CONNECTION and connect to the primary database and auxiliary database as sys.

$ rman

RMAN>connect target sys

target database Password:

connected to target database : NSMPRD (DBID=139468621)

RMAN>connect auxiliary sys@nsmdr ( Make sure listener is running on standby)

auxiliary database Password:

connected to auxiliary database: NSMDR(not mounted)

RMAN>

Now execute the below script from RMAN on the primary database server. When this script finishes you will have a new standby database that was created over the network without any interim storage.

RMAN>duplicate target database for standby from active database

spfile

SET audit_file_dest=’ /u01/oraadmin1/nsmdr/adump’

SET DB_UNIQUE_NAME ‘NSMDR’

SET log_archive_dest_2 ”

SET log_archive_dest_3 ”

SET dg_broker_config_file1 ”

SET dg_broker_config_file2 ”

SET dg_broker_start ‘FALSE’

nofilenamecheck;

After DR restore please set the standby parameters.

NSMDR@STANDBY

Update standby database parameter.

SQL> ALTER SYSTEM SET log_archive_config=’dg_config=(NSMPRD,NSMDR)’;

System altered

SQL> ALTER SYSTEM SET log_archive_dest_state_2=defer;

System altered

ALTER SYSTEM SET log_archive_dest_2=’service=”nsmprd”‘,’LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name=”nsmprd” net_timeout=30′,’template=”/u01/oraarch2/nsmprd_standby/LOG_%r_%s_%t.ARC” valid_for=(all_logfiles,primary_role)’ scope=both;

System altered

SQL> alter system set fal_client= NSMDR;

System altered

SQL> alter system set fal_server= NSMPRD;

System altered

Enable the log_archive_dest_state_2 on primary

SQL> ALTER SYSTEM SET log_archive_dest_state_2=ENABLE;

System altered

Perform a log switch on the primary database and redo will start being sent to the standby.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered

SQL>/

System altered

On the standby database set the database SID and start the manage recovery process.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Database altered

Verify that the standby database is performing correctly:

On the primary database switch the logfile.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered

Check the log sequence on the primary database.

SQL> SELECT THREAD#, MAX(SEQUENCE#) FROM V$LOG_HISTORY GROUP BY THREAD#;

THREAD# MAX(SEQUENCE#)

————————

1 199

On the standby database execute the above SQL statement to check the log sequence.

SQL> SELECT THREAD#, MAX(SEQUENCE#) FROM V$LOG_HISTORY GROUP BY THREAD#;

THREAD# MAX(SEQUENCE#)

————————

1 198

SQL> select PROCESS,THREAD#,SEQUENCE#,STATUS from v$managed_standby where process=’MRP0′;

PROCESS THREAD# SEQUENCE# STATUS

————————— ———- ———- ————————————

MRP0 1 199 WAIT_FOR_LOG

Conclusion

In this article we have learnt the steps to build a physical standby database using RMAN active duplicate.

Good Contents Are Everywhere, But Here, We Deliver The Best of The Best.Please Hold on!

Home

About

Services

Blog

Contact

De-Register Database Vault on RAC DB

Update Existing Database Vault settings.

Enable Database Vault on RAC Database

RAC DB creation on Exadata Machine using DBCA.

Data guard Broker setup

Physical Standby Build using RMAN Active Duplicate

ABOUT US

Services

CONTACT US

Follow Us!