Oracle Apps DBA Tips: 2016

Data guard Configuration

Primary db_name :INDPRIMARY

Primary db_unique_name INDPRIMARY

Standby db_name INDPRIMARY
standby db_unique_name INDSTANDBY

On Primary Server:-

2. Prepare the production database to be the primary database

Ensure that the database is in archivelog mode.

3. SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

Enable force logging

4.SQL> ALTER DATABASE FORCE LOGGING;

We have to create the password file at $ORACLE_HOME/dbs Location

-bash-3.00$ orapwd file=orapwINDPRIMARY password=welcome force=y entries=5

5.SQL> select bytes from v$log;

BYTES

----------

1073741824

6. SQL>select member,group#,type from v$logfile;

…………………………………………………………………………………………………………………………………………….

/u01/oradb/db/apps_st/data/log01b.dbf 1 ONLINE

/u01/oradb/db/apps_st/data/log01a.dbf 1 ONLINE

/u01/oradb/db/apps_st/data/log02b.dbf 2 ONLINE

/u01/oradb/db/apps_st/data/log02a.dbf 2 ONLINE

/u01/oradb/db/apps_st/data/log03b.dbf 3 ONLINE

/u01/oradb/db/apps_st/data/log03a.dbf 3 ONLINE

7. SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4

('/u01/oradb/db/apps_st/data/log04a.dbf','/u01/oradb/db/apps_st/data/log04b.dbf') SIZE 1024m;

8. SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5

('/u01/oradb/db/apps_st/data/log05a.dbf','/u01/oradb/db/apps_st/data/log05b.dbf') SIZE 1024m;

9. SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6

('/u01/oradb/db/apps_st/data/log06a.dbf','/u01/oradb/db/apps_st/data/log06b.dbf') SIZE 1024m;

10. SQL>select member,group#,type from v$logfile;

/u01/oradb/db/apps_st/data/log01b.dbf 1 ONLINE

/u01/oradb/db/apps_st/data/log01a.dbf 1 ONLINE

/u01/oradb/db/apps_st/data/log02b.dbf 2 ONLINE

/u01/oradb/db/apps_st/data/log02a.dbf 2 ONLINE

/u01/oradb/db/apps_st/data/log03b.dbf 3 ONLINE

/u01/oradb/db/apps_st/data/log03a.dbf 3 ONLINE

/u01/oradb/db/apps_st/data/log04b.dbf 4 STANDBY

/u01/oradb/db/apps_st/data/log04a.dbf 4 STANDBY

/u01/oradb/db/apps_st/data/log05b.dbf 5 STANDBY

/u01/oradb/db/apps_st/data/log05a.dbf 5 STANDBY

/u01/oradb/db/apps_st/data/log06b.dbf 6 STANDBY

/u01/oradb/db/apps_st/data/log06a.dbf 6 STANDBY

select archived, group#.thread#,sequence#,status from v$standby_log;

11. SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG= (INDPRIMARY, INDSTANDBY)';

System altered. ( here indprimary and indstandy are db_unique_names)

12. SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/oradb/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME= INDPRIMARY’ scope=both;

System altered.

(Here Location=Archive destination path)

13. SQL>alter system set LOG_ARCHIVE_DEST_2='SERVICE= INDSTANDBY ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME= INDSTANDBY ' scope=both;

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;
System altered.

14. SQL> alter system set FAL_SERVER= INDSTANDBY;

System altered.

[NOTE: FAL=> Fetch Archive Log, is used when primary switchover to standby].
15. SQL> alter system set FAL_CLIENT= INDPRIMARY ;

System altered.

16. SQL> alter system set standby_file_management = 'AUTO' scope=both;

System altered.

[NOTE: if auto then files are created/dropped automatically on standby]

17. SQL>alter system set remote_login_passwordfile= EXCLUSIVE scope=spfile;

Standby server:-

On Standby we create the listener by using below command

cd <RDBMS ORACLE_HOME>/appsutil/clone/bin
perl adcfgclone.pl dbTechStack

While running above command we have to give the sid name parameter as INDSTANDBY( standby db_unique_name) and Hostname as stand by server name

here listener.ora and tnsnames.ora files are created under $ORACLE_HOME/network/admin/SID_<hostname>.

But sqlnet.ora is not created under the above location.

So that we have to copy the sqlnet.ora file from Primary to stand by server

After we have start the database in nomount state. Here we export the database Name is export ORACLE_SID= INDSTANDBY (Not INDPRIMARY)

Before starting db in nomount state in initINDSTANDBY.ora file we have to maintain db_name is enough, remaining all parameters we have to remove

SQL>startup nomount pfile= /oracle/oradb/db/tech_st/11.1.0/dbs/ initINDSTANDBY.ora’;

Next step:-

We have to create the password file at $ORACLE_HOME/dbs Location in stand by server

-bash-3.00$ orapwd file=orapwINDSTANDBY password=welcome force=y entries=5

Next step:-

We have to add required parameters in tnsnames.ora and listener.ora for pinging purpose in

Primary and stand by servers

Next step:-

We have to check the tnsping is happening or not between primary and stand by server through db name and IP address

-bash-3.00$ tnsping INDPRIMARY (from Stand by)

--bash-3.00$ tnsping INDSTANDBY(from Stand by)

-bash-3.00$ tnsping INDPRIMARY (from Primary)

bash-3.00$ tnsping INDSTANDBY (from primary)

…………………………………………………………………………………………

Next step:

We check the sys password is working or not for both servers

SQL> exit

-bash-3.00$sqlplus sys/welcome@INDSTANDBY as sysdba (From Primary server)
bash-3.00$sqlplus sys/welcome@INDPRIMARY as sysdba (From standby server)

If everything fine

Next step:

On Standby Server:-

SQL> startup nomount pfile='/u01/oradb/db/tech_st/11.1.0/dbs/initINDSTANDBY.ora';

-bash-3.00$ cd /u01/oradb/backup (This RMAN backup has transferred from source)

contrl_30719_1_871273507.bk db_845_1_819078611.bk db_850_1_819079929.bk db_855_1_819081118.bk

-bash-3.00$ rman target /

RMAN> restore controlfile from '/u01/oradb/backup/contrl_30719_1_871273507.bk';

Starting restore at 10-FEB-15

Finished restore at 10-FEB-15

RMAN> mount database;

database mounted

released channel: ORA_DISK_1

-bash-3.00$ rman target /

Recovery Manager: Release 11.1.0.7.0 - Production on Wed Jun 26 15:04:26 2013

connected to target database: INDSTANDBY (DBID=4138996220, not open)

RMAN> catalog start with '/u01/oradb/backup'; (Provide RMAN Backup location)

RMAN> run {

allocate channel c1 type disk;

allocate channel c2 type disk;

allocate channel c3 type disk;

allocate channel c4 type disk;

restore database;

recover database;

}

SQL>shut immediate

On Primary:-

SQL>ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/standby.ctl';

Copy the control file from primary to standby server

Move the existing control files in Standby and replace with new control file.

On Standby:-

SQL>startup nomount

SQL> alter database mount standby database

SQL> recover managed standby database disconnect from session;

Execute below command for checking the log sync

SQL>select max(sequence#) from v$archived_log where applied='YES';

SQL>select status, gap_status from v$archive_dest_status where dest_id = 2;

Execute below command for checking the database_role

Primary:-

SQL>select protection_mode, protection_level, database_role, switchover_status from v$database;

Stand by:

SQL>select protection_mode, protection_level, database_role, switchover_status from v$database;

Execute below command for checking the latest archives

Primary:-

SQL>select max(sequence#) from v$archived_log;

Stand by:

SQL>select max(sequence#) from v$archived_log where applied='YES';

SQL>SELECT PROCESS, STATUS, SEQUENCE#, BLOCK#, BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY;

PROCESS STATUS SEQUENCE# BLOCK# BLOCKS DELAY_MINS

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

ARCH CLOSING 152 1 2 0

ARCH CONNECTED 0 0 0 0

ARCH CLOSING 154 1 3 0

ARCH CLOSING 151 1 483 0

ARCH CLOSING 155 1 198 0

RFS IDLE 0 0 0 0

MRP0 WAIT_FOR_LOG 156 0 0 0

SQL>How to find the Standby databases list from Primary node.

SELECT * from v$dataguard_config

show parameter log_archive_config

Saturday, February 6, 2016