ORA-16698: 12c Dataguard Broker Setup Error

Recently I have configured dataguard on 12c database. While configuring dataguard broker I have encountered an "Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added"

[oracle@primdb dbs]$ dgmgrl
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/dataguard
Connected as SYSDG.
DGMGRL> CREATE CONFIGURATION 'DR_Config' AS PRIMARY DATABASE IS 'primdb' CONNECT IDENTIFIER IS primdb;
Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added

Failed.
DGMGRL>
SQL>

Solution:

Generally you will not receive this error on earlier database versions (Pre 12c). As per Oracle database 12c documentation, "Any LOG_ARCHIVE_DEST_n parameters that have the SERVICE attribute set, and if the NOREGISTER attribute is Not set, then you must remove/clear the 'LOG_ARCHIVE_DEST_n' before creating the broker configuration". Clear archive destination for both primary and standby side and then configure the Dataguard broker.

Once you complete the DataGuard broker setup then revert back the LOG_ARCHIVE_DEST parameter to old values in both primary and standby side.

On Primary Side:

SQL> show parameter LOG_ARCHIVE_DEST_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      SERVICE=standb  ASYNC VALID_FO
                                                 R=(ONLINE_LOGFILES,PRIMARY_ROL
                                                 E)  DB_UNIQUE_NAME=standb
SQL>

SQL> alter system set LOG_ARCHIVE_DEST_2='' scope=both;

System altered.

SQL> 


DGMGRL> CREATE CONFIGURATION 'DR_Config' AS PRIMARY DATABASE IS 'primdb' CONNECT IDENTIFIER IS primdb;
Configuration "DR_Config" created with primary database "primdb"
DGMGRL>

DGMGRL> show configuration;

Configuration - DR_Config

  Protection Mode: MaxPerformance
  Members:
  primdb - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL>



On Standby Side:

SQL> show parameter LOG_ARCHIVE_DEST_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      SERVICE=PRIMARY  ASYNC VALID_F
                                                 OR=(ONLINE_LOGFILES,PRIMARY_RO
                                                 LE)  DB_UNIQUE_NAME=primdb
SQL> alter system set LOG_ARCHIVE_DEST_2='' scope=both;

System altered.

SQL>


DGMGRL> ADD DATABASE 'standb' AS CONNECT IDENTIFIER IS standb MAINTAINED AS PHYSICAL;
Database "standb" added
DGMGRL>

DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;

Configuration - DR_Config

  Protection Mode: MaxPerformance
  Members:
  primdb - Primary database
    standb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 10 seconds ago)

DGMGRL> 

That's it.. Hope this article will help you.

Thanks,
Chowdari

Automated Syncup Script For Manual Standby Database

Sometimes we may created manual standby database for standard edition databases, by that time it required to apply archivelogs manually to sync-up the standby database with production database.
Here you can find automated script for applying newly generated archivelogs to standby database.

Note: For copying archivelogs from Production to standby server, need to configure passwordless connectivity between servers.

Find below list of scripts need to be copy:

1) main_db_synch_300.sh -  This main script will call below list of scripts.

  a) db_synch_301.sh
  b) db_synch_302.sh
  c) db_synch_303.sh
  d) getlog.sql
  e) getlog_prod.sql
  f) getlog_standby.sql
  g) getname_prod.sql

Find below detailed sctipts:

1) main_db_synch_300.sh - This is the main script to call.

################### Begin Script main_db_synch_300.sh##################
## Author       : Chowdari Mathukumilli
## Description  : This automated script will be useful for synch-up
##                standby database with Production database even
##                database in standard edition.
##                Need to put standby database in mount standby database.
## Call Syntax  : Schedule below line in standby server crontab file.
##                Based on your requirement you can schedule it like every
##                15mins, 30mins or 60mins etc. and also need to add both production
##                and standby database tns entries in standby server.
##             00 * * * * /u01/app/oracle/db_scripts/main_db_synch_300.sh
## Script Changes: Below scriptyou need to change
##             ORACLE_HOME -- Change it to your DB Home location
##             SCR_HOME   -- Change it to your sych-up scripts location
##             ORACLE_SID  -- Change it to your DB SID name
##             PASSWORD  --  Change it to ur sys password (Primary or Standby)
##             hostname  -- change it to your primary database server name
## Total files : 8        
##                      
################################################################
#bin ! sh 
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export SCR_HOME=/u01/app/oracle/db_scripts
export PATH=$ORACLE_HOME/bin:$PATH:
export DDATE=`date +%Y-%m-%d`

echo "*************** PROD_DB ***************"
export ORACLE_SID=PROD_DB

echo "Performing switch logfile in Production database"
$SCR_HOME/db_synch_301.sh

echo "Finding current archivelog in PROD_DB production DB"
export FIND_CUR_ARCH_PROD=`sqlplus -s sys/PASSWORD@PROD_DB as sysdba @$SCR_HOME/getlog_prod.sql | sed '/^$/d'`
echo $FIND_CUR_ARCH_PROD

echo "Find last applied archive log in stndby side"
export LAST_APPL_ARCH_STAND=`sqlplus -s sys/PASSWORD@STANDBY_DB as sysdba @$SCR_HOME/getlog_standby.sql` 
echo $LAST_APPL_ARCH_STAND

export DR_ARCH_LOCATION=/u01/oracle/STANDBY_DB/FRA/ARCH

for (( i = $LAST_APPL_ARCH_STAND; i <= $FIND_CUR_ARCH_PROD; i++ ))
do
   if [ $i -le $FIND_CUR_ARCH_PROD ]; then
        echo "Below execution will help to find new generated archivelogs with location in primary side"
        export NEW_GENERATED_ARCH_PROD=`sqlplus -s sys/PASSWORD@PROD_DB as sysdba @$SCR_HOME/getname_prod.sql $i | tail -2 |head -1`
        echo $NEW_GENERATED_ARCH_PROD
        echo "Copying achives from prod to standby side $i"
        scp -r oracle@hostname:$NEW_GENERATED_ARCH_PROD $DR_ARCH_LOCATION
  else
        echo " Nothing to DO - Already Standby database is Synch-up with production database - :$i = $FIND_CUR_ARCH_PROD "
        exit;
   fi
done

echo "Backup archivelogs not backed up in prod"
$SCR_HOME/db_synch_302.sh

echo "Applying archives in backup side"
$SCR_HOME/db_synch_303.sh

sleep 50

#Find latest applied archivelog in Standby Database
export LATEST_APPLIED_ARCH_STAND=`sqlplus -s sys/<PASSWORD>@STANDBY_DB as sysdba @$SCR_HOME/getlog.sql`
echo "Applied arch : $LATEST_APPLIED_ARCH_STAND"

###################### END SCRIPT - main_db_synch_300.sh #############

2) db_synch_301.sh  - This script is for performing switch logfile in Production database.

###################Begin Script - db_synch_301.sh #######################
## Author       : Chowdari Mathukumilli
## Description  : This file is for switch logfile
##                      
######################################################################
sqlplus /nolog << EOF 
connect sys/<PASSWORD>@PROD_DB as sysdba 
alter system switch logfile;
disc; 
quit;
###################### END SCRIPT - db_synch_301.sh## ################

3) getlog_prod.sql - This sql is used for finding current running archivelog in production side

###################Begin Script - getlog_prod.sql #########################
## Author       : Chowdari Mathukumilli
## Description  : This sql script is for finding current archivelog in prod
##                      
######################################################################
set head off;
select  lhmax "Current Seq running/applied :"
from (select thread# thrd, max(sequence#) almax
from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) al,
(select thread# thrd, trim(max(sequence#)) lhmax
from v$log_history
where first_time=(select max(first_time) from v$log_history)
group by thread#) lh
where al.thrd = lh.thrd;
#disc;
#quit;
###################### END SCRIPT - getlog_prod.sql## #####################

4) getlog_standby.sql - This SQL script is used for find last applied archivelog in stndby side

###################Begin Script - getlog_standby.sql ########################
## Author       : Chowdari Mathukumilli
## Description  : This sql script is for find last applied archivelog in stndby side
##                      
#####################################################################
set heading off;
select max(sequence#) from v$archived_log where applied='YES';
exit;
###################### END SCRIPT - getlog_standby.sql## ###############

5) getname_prod.sql -  This sql is for find new generated archivelog files in production side

###################Begin Script - getname_prod.sql #######################
## Author       : Chowdari Mathukumilli
##                      
#######################################################################
set heading off;
select name from v$archived_log where sequence# = '&&1';
exit;
###################### END SCRIPT - getname_prod.sql## ##################

6) db_synch_302.sh - Backup the archivelogs in production side

###################Begin Script - db_synch_302.sh ##########################
## Author       : Chowdari Mathukumilli
##                      
########################################################################
rman target sys/<PASSWORD>@PROD_DB << EOF
crosscheck archivelog all;
backup archivelog all not backed up;
quit;
###################### END SCRIPT - db_synch_302.sh ## ####################

7) db_synch_303.sh - Applying archives in backup side

###################Begin Script - db_synch_303.sh ###########################
## Author       : Chowdari Mathukumilli
##                      
#########################################################################
rman target / << EOF
catalog start with '/u01/oracle/STANDBY_DB/FRA/ARCH';
YES
DELETE noprompt COPY OF ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-5';
quit;
###################### END SCRIPT - db_synch_303.sh ## #####################

Note: This (main_db_synch_300.sh) script should schedule in standby server and need to add both production and standby database tns entries in standby server.

Thats it.. If you any problems with this script please contact me :-).

Thanks,
Chowdari

Setup Manual Standby Database

As everybody know oracle High Availabily DataGuard feature is available with Enterprise Edition only. But some times we need temporary High Availabily solutions for oracle standard edition databases. So here you can find detailed steps for how to setup manual standby database.

Follow below steps to setup manual standby database:

1) Primary database should be in Archivelog mode.

   Follow this post to enable archivelog mode in your database.
 
2) Create parameter file in primary side.

   SQL> Create pfile from spfile;

3) Take backup of your primary database and also take standby controlfile backup.
 
   RMAN> backup database plus archivelog;
   RMAN> copy current controlfile for standby to '/u01/app/oracle/bkp/stndby_ctrl01.ctl';
 
4) Copy pfile and backup files to standby server

   example: scp -r * oracle@hostname:<<backup_location>>

4) In Standby server, install same version of ORACLE binaries (same as production) and create same directory structure like production database.

5) Copy pfile in $ORACLE_HOME/dbs location and check the paths existed in standby server.

6) In standby side, follow below steps to restore and recover the database.

   SQL> startup nomount;
   
   RMAN> restore controlfile from '/u01/app/oracle/bkp/stndby_ctrl01.ctl';
   
   SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
   
   RMAN> catalog start with '/u01/app/oracle/bkp';
   
   RMAN> restore database;
   
   RMAN> recover database;
 
7) After restored the database put standby database in MRM mode.

   SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  DISCONNECT FROM SESSION;
 
If you want open manually created stand by database then follow below steps:

1) Activate the standby database and open it.

   SQL> alter database activate standby database;
   SQL> alter database open;
 
2) Create temprary tablespace and make it as default.

   SQL> CREATE TEMPORARY TABLESPACE <<TEMP_TABLESPACE_NAME>> TEMPFILE '<<DATAFILE_PATH>>' SIZE 1024M;
   SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE <<TEMP_TABLESPACE_NAME>>;
 
Note: Using DBVisit, you can configure standby database like dataguard in Standard Edition. But separate licence is required for DBVisit product usage.

Thats it.. Hope this will help you :)

Thanks,
Chowdari

Enable and Disable Archive Log Mode in Oracle 10g/11g

Enable Archive Log Mode:

Follow below steps to enable archive log mode on an Oracle 10g or 11g databases.

[oracle@primdb ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 12 20:57:58 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> archive log list;
Database log mode            No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     39
Next log sequence to archive   41
Current log sequence           41
SQL> 

The log mode is No Archivelog mode. The archive destination is USE_DB_RECOVERY_FILE_DEST. You can see by looking at the parameter RECOVERY_FILE_DEST.

SQL> show parameter recovery_file_dest;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------------------
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size        big integer 4122M
SQL> 

By default, archive logs are written to the flash recovery area. If you want to change that location you can set the parameter LOG_ARCHIVE_DEST_n to the new location which we want to write archive logs.

SQL> alter system set log_archive_dest_1='LOCATION=/u02/app/oracle/arch' scope = both;

System altered.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u02/app/oracle/arch
Oldest online log sequence     39
Next log sequence to archive   41
Current log sequence           41
SQL> 

Now we shutdown the database and open the database in mount state.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 

SQL> startup mount;
ORACLE instance started.
Total System Global Area  534462464 bytes
Fixed Size                  2230072 bytes
Variable Size             394266824 bytes
Database Buffers          130023424 bytes
Redo Buffers                7942144 bytes
Database mounted.
SQL> 

Now you set archive log mode and open the database.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/app/oracle/arch
Oldest online log sequence     39
Next log sequence to archive   41
Current log sequence           41
SQL>

Now we can see archivelog mode is enabled.

Disable Archive Log Mode:

Follow below steps to disable archive log mode on an Oracle 10g or 11g databases.

[oracle@primdb ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 12 21:05:05 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> 
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/app/oracle/arch
Oldest online log sequence     39
Next log sequence to archive   41
Current log sequence           41
SQL> 

Database is in Archivelog mode. We need to shutdown the database and open in mount state.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area  534462464 bytes
Fixed Size                  2230072 bytes
Variable Size             394266824 bytes
Database Buffers          130023424 bytes
Redo Buffers                7942144 bytes
Database mounted.
SQL> 

Now disable archivelog mode and open the database.

SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u02/app/oracle/arch
Oldest online log sequence     39
Next log sequence to archive   41
Current log sequence           41
SQL> 

Now you can see archivelog mode has been disabled.

Hope this will help you... :)

Best Regards,