LGWR: Primary database is in MAXIMUM AVAILABILITY mode | ORA-16072: a minimum of one standby database destination is required

July 29, 2019 Leave a comment

Problem:

One of our customer cloned database from a DG environment to a different host and tried to open it as a standalone database. Controlfile and datafiles still considered the database in maximum availability mode.

Errors after trying to open the database:

LGWR: Primary database is in MAXIMUM AVAILABILITY mode
LGWR: Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR
LGWR: Minimum of 1 LGWR standby database required
Thu Jul 18 18:43:14 2019
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl2/trace/orcl2_lgwr_39735_39805.trc:
ORA-16072: a minimum of one standby database destination is required

Solution: 

SQL> startup mount;
SQL> alter database set standby database to maximize performance;
SQL> shutdown immediate;

$ srvctl start database -d orcl

Filed under 12c, ORA-, STANDBY Tagged with ,

ORA-01103: when creating a Standby Database on the same Host as the Primary Database

September 26, 2018 1 Comment

Typically the standby and the primary databases are located on the different hosts to ensure the full DR capabilities. However, there are some situations when you want to have the primary and standby database on the same Host.

Problem #1: You are not able to start two databases with the same SID on the same server.

Problem #2: You cannot change db_name, because it is used in the controlfile and if you try to duplicate the standby database from the primary using different db_name, you will get the following error:

ORA-01103: database name 'orcldgst' in control file is not 'orcldg'

Assume db_name=orcldg and ORACLE_SID for the primary is orcldg1. To solve problem #1 and problem #2, you need to the following steps:

db_name must be the same for both databases. But during startup nomount of the standby database, you need to set ORACLE_SID to the different value:

$ export ORACLE_SID=orcldgst1
$ sqlplus / as sysdba
SQL> startup nomount pfile='/tmp/mypfile.ora'

After that you will be able to run RMAN duplicate command to create the standby database on the same host as the primary.

Filed under 11g, 12c, 18c, RMAN, STANDBY Tagged with , , , ,

Best Practices for Configuring Redo Transport for Data Guard and Active Data Guard 12c

November 22, 2016 Leave a comment

I have three standby databases db01 located in HQ, db02 located in DR, db03 located in DR and it should be late standby with delay 15 days.

My task is to configure the following standby architecture:

When db01 is primary it should send logs in SYNC mode to db02 and at the same time db02 should send logs in ASYNC mode to db03.

When db02 is in primary role it should send logs in SYNC mode to db01 and at the same time db01 should send logs in ASYNC mode to db03.

So db01 and db02 database should be in sync mode with real-time apply and db03 should be late standby with delay 15 days and it should receive logs from standby database in ASYNC mode.

I have underlined the above sentence, because for now this cannot be achieved with cascading standby. Read bellow…

I have found very useful documentation so here is the link: http://www.oracle.com/technetwork/database/availability/broker-12c-transport-config-2082184.pdf

It introduces data broker new feature that is available in 12c. Property RedoRoutes.

So in my broker configuration I will set RedoRoutes property by the following way:

DGMGRL> show configuration

Configuration – DB_HQ_DR

Protection Mode: MaxPerformance
Members:
db01 – Primary database
db02 – Physical standby database
db03- Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> DGMGRL> edit database ‘db01′ set property RedoRoutes='(LOCAL:db02)(db02:db03)’;
Property “redoroutes” updated
DGMGRL> edit database ‘db02′ set property RedoRoutes='(LOCAL:db01)(db01:db03)’;
Property “redoroutes” updated

Normally, delayed apply can be configured by property DelayMins:

DGMGRL> edit database ‘db03′ set property DelayMins=’21600’;
Property “delaymins” updated

21600 is 15 days.

BUT, I must tell you a bad news:  according to this article https://docs.oracle.com/database/121/SBYDB/log_arch_dest_param.htm#SBYDB01105

“The DELAY value that a cascaded standby uses is the value that was set for the LOG_ARCHIVE_DEST_n parameter on the primary that shipped the redo to the cascading standby.”

So I cannot have the following architecture:

db01 —-real_time_apply—-db02—-delayed_apply—–db03

because db03 will take delay parameter from db02 that is no delay.

Async/Sync mode can be configured by property LogXptMode:

DGMGRL> edit database ‘db01′ set property LogXptMode=’SYNC’;
DGMGRL> edit database ‘db02′ set property LogXptMode=’SYNC’;
DGMGRL> edit database ‘db03′ set property LogXptMode=’ASYNC’;

If I want to achieve my goal I should not use cascading standby but primary must be the sender for db02(with DelayMins=0) and db03(with DelayMins=21600)

I hope it helps.

Filed under 12c, Data Guard Broker, DB, STANDBY Tagged with , , , ,

ORA-01184: logfile group 5 already exists

September 2, 2016 Leave a comment

Command:  

ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 11 (‘+DATA’) size 52428800;

Error:

ORA-01184: logfile group 5 already exists

Troubleshoot:

SYS> SELECT thread#, group#
FROM gv$log;

THREAD# GROUP#
———- ———-
1                 1
1                 2
1                 3
1                 4
1                 5
1                 6
1                 7
1                 8
1                 9
1                 10

As we see there are just 10 groups.

Check standby redo logs:

SYS> SELECT group#
FROM v$standby_log;

GROUP#
———-
11

So we have standby redo log with group id 11.

Solution: 

Add logfile group with number more than 11.

I prefer to save numbers for standby logs and add new log with number 22(10 redo log groups+(10+1) recommended number of standby logs+ 1)

SYS> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 22 (‘+DATA’) SIZE 52428800;

Database altered.

Filed under 10g, 11g, 12c, DB, Real World Scenarios, STANDBY Tagged with , , , ,

Oracle Clusterware is restarting database “” | shut down instance “” of database “” | start up instance “” of database “”

July 4, 2016 Leave a comment

When I used data guard broker to switchover primary database to standby database sometimes broker writes “Oracle Clusterware is restarting database”  … hangs and times out.

The problem was that database was not registered with srvctl.

srvctl add database -d db_unique_name -o oracle_home
srvctl add instance -d db_unique_name -i instance_name1 -n node_name1
srvctl add instance -d db_unique_name -i instance_name2 -n node_name2

instead of db_unique_name, oracle_home , instance_name1, node_name1,  instance_name2, node_name2 enter values according to your database. 

Filed under 12c, Data Guard Broker, DB, RAC, STANDBY Tagged with , , , , ,

Switchover: convert primary database to standby

September 25, 2012 Leave a comment

–On Primary

— Convert primary database to standby

CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;

— Shutdown primary database

SHUTDOWN IMMEDIATE;

— Mount old primary database as standby database, open and enable real-time apply

STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE OPEN;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE using current logfile DISCONNECT FROM SESSION;

–On Standby

— Convert standby database to primary

CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

— Shutdown standby database

SHUTDOWN IMMEDIATE;

— Open old standby database as primary

STARTUP;

Filed under DB, STANDBY Tagged with , , ,

Enable Real-Time apply on Standby(Standalone,RAC)

September 21, 2012 1 Comment

In this post we will discuss how to enable real-time apply feature on standalone and Real Application Cluster standby databases.

On standalone standby:

1. See what is recovery mode for now:

SQL> select dest_name,status,type,recovery_mode
from v$archive_dest_status
where dest_id=1;

DEST_NAME               STATUS    TYPE           RECOVERY_MODE
———————– ——— ————– ————————-
LOG_ARCHIVE_DEST_1      VALID     LOCAL          MANAGED

2. Enable real-time apply

On physical:

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.

On logical:

SQL> alter database start logical standby apply immediate;

3. Check

SQL>  select dest_name,status,type,recovery_mode
from v$archive_dest_status
where dest_id=1;

DEST_NAME               STATUS    TYPE           RECOVERY_MODE
———————– ——— ————– ————————-
LOG_ARCHIVE_DEST_1      VALID     LOCAL          MANAGED REAL TIME APPLY

On RAC standby:

1. See what is recovery mode for now:

SQL> select dest_name,status,type,recovery_mode
from v$archive_dest_status
where dest_id=1;

DEST_NAME               STATUS    TYPE           RECOVERY_MODE
———————– ——— ————– ————————-
LOG_ARCHIVE_DEST_1      VALID     LOCAL          MANAGED

2. Enable real-time apply

On node1:

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.

3. Check

SQL>  select dest_name,status,type,recovery_mode
from v$archive_dest_status
where dest_id=1;

DEST_NAME               STATUS    TYPE           RECOVERY_MODE
———————– ——— ————– ————————-
LOG_ARCHIVE_DEST_1      VALID     LOCAL          MANAGED REAL TIME APPLY

Filed under DB, RAC, STANDBY Tagged with , , , ,