[oracle@primary admin]$ rman TARGET sys/Furqan_6677@primary AUXILIARY sys/Furqan_6677@standby
Recovery Manager: Release 12.2.0.1.0 - Production on Wed Sep 19 15:02:51 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRSTD (DBID=452457302)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
---------------------
==== On Primary =====
---------------------
rman TARGET sys/Furqan_6677@primary AUXILIARY sys/Furqan_6677@standby
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET db_unique_name='standby' COMMENT 'Is standby'
NOFILENAMECHECK;
=========================================================================================
Primary Server Setup
Logging
Check that the primary database is in archivelog mode.
SELECT log_mode FROM v$database;
LOG_MODE
------------
NOARCHIVELOG
SQL>
If it is noarchivelog mode, switch is to archivelog mode.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
Enabled forced logging by issuing the following command.
ALTER DATABASE FORCE LOGGING;
-- Make sure at leat one logfile is present.
ALTER SYSTEM SWITCH LOGFILE;
Create standby redo logs on the primary database (in case of switchovers). The standby redo logs should be at least as big as the largest
online redo log and there should be one extra group per thread compared the online redo logs. In my case, the following standby redo logs
must be created on both servers.
------------------------------------------------------------------------------------------------------------------------------------------
SQL> select GROUP#,THREAD#,BYTES/1024/1024 "MB" from v$log;
GROUP# THREAD# MB
------ ---------- ----------
1 1 200
2 1 200
3 1 200
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/PEKO/onlinelog/standby_redo05.log') SIZE 200M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/fast_recovery_area/peko/PEKO/onlinelog/standby_redo06.log') SIZE 200M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/PEKO/onlinelog/standby_redo07.log') SIZE 200M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/fast_recovery_area/peko/PEKO/onlinelog/standby_redo08.log') SIZE 200M;
===========================================================================================================================
SQL> alter database add standby logfile thread 1 group 8 size 200m;
SQL> alter database add standby logfile thread 1 group 9 size 200m;
SQL> alter database add standby logfile thread 1 group 10 size 200m;
SQL> alter database add standby logfile thread 1 group 11 size 200m;
If you want to user flashback database, enable it on the primary now, so it will be enabled on the standby also. It's very useful as you will see below.
--------------------------------------------------------------------------------------------------------------------------------------------------------
ALTER DATABASE FLASHBACK ON;
Initialization Parameters
Check the setting for the DB_NAME and DB_UNIQUE_NAME parameters. In this case they are both set to "db11g" on the primary database.
------------------------------------------------------------------------------------------------------------------------------------
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string PEKO
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string db11g
SQL> alter system set db_unique_name=primary scope=spfile;
SQL> shut immediate;
SQL> startup;
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string PRIMARY
The DB_NAME of the standby database will be the same as that of the primary, but it must have a different DB_UNIQUE_NAME value. For this example,
the standby database will have the value "db11g_stby".Set suitable remote archive log destinations. In this case I'm using the fast recovery area
for the local location, but you could specify a location explicitly if you prefer. Notice the SERVICE and the DB_UNIQUE_NAME for the remote location reference the standby location.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby';
==== FOR STANDBY====== ( Only this line )
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=primary NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary';
==========================================================================================================================================
SQL> SHOW PARAMETER LOG_ARCHIVE_DEST_STATE_2
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;
SQL> Select status, error from v$archive_dest where dest_id=2;
SQL> select sequence#, applied from v$archived_log;
Make sure the STANDBY_FILE_MANAGEMENT parameter is set.
--------------------------------------------------------------
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
Service Setup
Entries for the primary and standby databases are needed in the "$ORACLE_HOME/network/admin/tnsnames.ora" files on both servers.
You can create these using the Network Configuration Utility (netca) or manually. The following entries were used during this setup.
Notice the use of the SID, rather than the SERVICE_NAME in the entries. This is important as the broker will need to connect to the
databases when they are down, so the services will not be present.
------------------------------------------------------------------------------------------------------------------------------------
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_PEKO =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby.oracle.com)(PORT = 1522))
PRIMARY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.59)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = peko.oracle.com)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.60)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = peko)
)
)
The "$ORACLE_HOME/network/admin/listener.ora" file on the primary server contains the following configuration. Since the broker will need to connect to the database when it's down, we can't rely on auto-registration with the listener, hence the explicit entry for the database.
--------------------------------------------------------------------------------------------------------------
# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
PEKO_PRY =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.59)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = peko_PRY_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/db_1)
(SID_NAME = peko)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
The "$ORACLE_HOME/network/admin/listener.ora" file on the standby server contains the following configuration.
---------------------------------------------------------------------------------------------------------------
PEKO_STD =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.60)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = peko_STD_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/db_1)
(SID_NAME = peko)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
Once the listener.ora changes are in place, restart the listener on both servers.
-----------------------------------------------------------------------------------
lsnrctl stop
lsnrctl start
Standby Server Setup
Prepare for Duplicate
Create a parameter file for the standby database called "/tmp/initdb11g_stby.ora" with the following contents.
--------------------------------------------------------------------------------------------------------------
*.db_name='db11g'
Create the necessary directories on the standby server.
----------------------------------------------------------
$ mkdir -p /u01/app/oracle/oradata/PEKO/datafile
$ mkdir -p /u01/app/oracle/fast_recovery_area/peko
$ mkdir -p /u01/app/oracle/admin/peko/adump
Create a password file, with the SYS password matching that of the primary database.
---------------------------------------------------------------------------------------------------
$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=Furqan_6677 entries=10 FORMAT=12 force=y
-------------------------------------------------------------------------------
\/\/\/\/\/\/\/\/\/\\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\
-------------------------------------------------------------------------------
ENCOUNTERED THE ERROR
==============================
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-01017: invalid username/password; logon denied
SOLUTION :=
============================================================================
12c: RMAN Remote Connection from Backup User Raises ORA-1017 (Doc ID 1997947.1)
Recreate the passwordfile making sure to include FORMAT=12
orapwd FILE=filename ENTRIES=max_users FORMAT=12
-------------------------------------------------------------------------------
\/\/\/\/\/\/\/\/\/\\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\
-------------------------------------------------------------------------------
Create Standby Using DUPLICATE
Start the auxiliary instance on the standby server by starting it using the temporary "init.ora" file.
-------------------------------------------------------------------------------------------------------
cd /u01/oracle/software
$ ll initdb11g_stby.ora
-rw-r--r-- 1 oracle oinstall 17 Oct 25 11:52 initdb11g_stby.ora
$ cat initdb11g_stby.ora
*.db_name='peko'
$ export ORACLE_SID=peko
$ sqlplus / as sysdba
SQL> STARTUP NOMOUNT PFILE='/u01/oracle/software/initdb11g_stby.ora';
Connect to RMAN, specifying a full connect string for both the TARGET and AUXILIARY instances. Do not attempt to use OS authentication.
---------------------------------------------------------------------------------------------------------------------------------------
$ rman TARGET sys/Furqan_6677@primary AUXILIARY sys/Furqan_6677@standby
Now issue the following DUPLICATE command. (PRIMARY SERVER OPERATIONS ONLY)
----------------------------------------------------------------------------
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET db_unique_name='standby' COMMENT 'Is standby'
NOFILENAMECHECK;
If you need to convert file locations, or alter any initialisation parameters, you can do this during the DUPLICATE using the SET command.
-------------------------------------------------------------------------------------------------------------------------------------------
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET db_unique_name='DB11G_STBY' COMMENT 'Is standby'
SET db_file_name_convert='/original/directory/path1/','/new/directory/path1/','/original/directory/path2/','/new/directory/path2/'
SET log_file_name_convert='/original/directory/path1/','/new/directory/path1/','/original/directory/path2/','/new/directory/path2/'
SET job_queue_processes='0'
NOFILENAMECHECK;
A brief explanation of the individual clauses is shown below.
FOR STANDBY: This tells the DUPLICATE command is to be used for a standby, so it will not force a DBID change.
FROM ACTIVE DATABASE: The DUPLICATE will be created directly from the source datafiles, without an additional backup step.
DORECOVER: The DUPLICATE will include the recovery step, bringing the standby up to the current point in time.
SPFILE: Allows us to reset values in the spfile when it is copied from the source server.
NOFILENAMECHECK: Destination file locations are not checked.
Once the command is complete, we can start using the broker.
-----------------------------------------------------------------------------------------------------
+++++++++++++++++++++++++++++++++++++++++++ Enable Broker +++++++++++++++++++++++++++++++++++++++++++
-----------------------------------------------------------------------------------------------------
At this point we have a primary database and a standby database, so now we need to start using the Data Guard
Broker to manage them. Connect to both databases (primary and standby) and issue the following command.
----------------------------------------------------------------------------------------------
NOTES: When we will configure the Dataguard Broker so we will undefine or unbset the log_archive_dest_2 for the
archive shipment on both machine primary and standby.
------
alter system set log_archive_dest_2='' scope=both SID='*';
alter system set log_archive_dest_state_2=defer SID='*';
Its recommendation for dataguard broker.
=========================================================
1. Prerequisits,
Start the Dataguard broker DMON process.
On Primary and Standby,
SQL> alter system set dg_broker_start=true sid='*';
ALTER SYSTEM SET dg_broker_start=true;
On the primary server, issue the following command to register the primary server with the broker.
---------------------------------------------------------------------------------------------------
$ dgmgrl sys/Furqan_6677@PRIMARY
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> CREATE CONFIGURATION 'MYDGMGR' AS PRIMARY DATABASE IS 'primary' CONNECT IDENTIFIER IS primary;
Configuration "my_dg_config" created with primary database "db11g"
DGMGRL>
Now add the standby database.
-----------------------------------------
DGMGRL> add database standby as connect identifier is standby;
Database "db11g_stby" added
DGMGRL>
Now we enable the new configuration.
-----------------------------------
DGMGRL> ENABLE CONFIGURATION;
Enabled.
DGMGRL>
The following commands show how to check the configuration and status of the databases from the broker.
--------------------------------------------------------------------------------------------------------
DGMGRL> SHOW CONFIGURATION;
Configuration - my_dg_config
Protection Mode: MaxPerformance
Databases:
db11g - Primary database
db11g_stby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> SHOW DATABASE PRIMARY;
Database - db11g
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
db11g
Database Status:
SUCCESS
DGMGRL> SHOW DATABASE STANDBY;
Database - db11g_stby
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Apply Rate: 0 Byte/s
Real Time Query: OFF
Instance(s):
db11g
Database Status:
SUCCESS
DGMGRL>
Database Switchover
A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs. This process is known as a Switchover and can be performed using the following commands. Connect to the primary database (db11g) and switchover to the standby database (db11g_stby).
$ dgmgrl sys/Password1@db11g
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> SWITCHOVER TO db11g_stby;
Performing switchover NOW, please wait...
Operation requires a connection to instance "db11g" on database "db11g_stby"
Connecting to instance "db11g"...
Connected.
New primary database "db11g_stby" is opening...
Operation requires startup of instance "db11g" on database "db11g"
Starting instance "db11g"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "db11g_stby"
DGMGRL>
Let's switch back to the original primary. Connect to the new primary (db11g_stby) and switchover to the new standby database (db11g).
$ dgmgrl sys/Password1@db11g_stby
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> SWITCHOVER TO db11g;
Performing switchover NOW, please wait...
Operation requires a connection to instance "db11g" on database "db11g"
Connecting to instance "db11g"...
Connected.
New primary database "db11g" is opening...
Operation requires startup of instance "db11g" on database "db11g_stby"
Starting instance "db11g"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "db11g"
DGMGRL>
Database Failover
If the primary database is not available the standby database can be activated as a primary database using the following statements. Connect to the standby database (db11g_stby) and failover.
$ dgmgrl sys/Password1@db11g_stby
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> FAILOVER TO db11g_stby;
Performing failover NOW, please wait...
Failover succeeded, new primary is "db11g_stby"
DGMGRL>
Since the standby database is now the primary database it should be backed up immediately.
The original primary database can now be configured as a standby. If flashback database was enabled on the primary database, then this can be done relatively easily with the following command.
DGMGRL> REINSTATE DATABASE db11g;
Reinstating database "db11g", please wait...
Operation requires shutdown of instance "db11g" on database "db11g"
Shutting down instance "db11g"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "db11g" on database "db11g"
Starting instance "db11g"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "db11g" ...
Operation requires shutdown of instance "db11g" on database "db11g"
Shutting down instance "db11g"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "db11g" on database "db11g"
Starting instance "db11g"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "db11g" ...
Reinstatement of database "db11g" succeeded
DGMGRL>
If flashback database is not enabled, you would have to manually recreate db11g as a standby. The basic process is the reverse of what you did previously.
# 1) Cleanup the old instance.
sqlplus / as sysdba <<EOF
SHUTDOWN IMMEDIATE;
EXIT;
EOF
rm -Rf /u01/app/oracle/oradata/db11g/*
rm -Rf /u01/app/oracle/fast_recovery_area/db11g
rm -Rf /u01/app/oracle/admin/db11g
mkdir -p /u01/app/oracle/fast_recovery_area/db11g
mkdir -p /u01/app/oracle/admin/db11g/adump
rm $ORACLE_HOME/dbs/spfiledb11g.ora
export ORACLE_SID=db11g
sqlplus / as sysdba <<EOF
STARTUP NOMOUNT PFILE='/tmp/initdb11g_stby.ora';
EXIT;
EOF
# 2) Connect to RMAN.
$ rman TARGET sys/Password1@db11g_stby AUXILIARY sys/Password1@db11g
# 3) Duplicate the database.
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET db_unique_name='DB11G' COMMENT 'Is standby'
NOFILENAMECHECK;
# 4) Connect to DGMDRL on the current primary.
$ dgmgrl sys/Password1@db11g_stby
# 5) Enable the new standby.
DGMGRL> ENABLE DATABASE db11g;
Flashback Database
It was already mentioned in the previous section, but it is worth drawing your attention to Flashback Database once more. Although a switchover/switchback is safe for both the primary and standby database, a failover renders the original primary database useless for converting to a standby database. If flashback database is not enabled, the original primary must be scrapped and recreated as a standby database.
An alternative is to enable flashback database on the primary (and the standby if desired) so in the event of a failover, the primary can be flashed back to the time before the failover and quickly converted to a standby database, as shown above.
Read-Only Standby and Active Data Guard
Once a standby database is configured, it can be opened in read-only mode to allow query access. This is often used to offload reporting to the standby server, thereby freeing up resources on the primary server. When open in read-only mode, archive log shipping continues, but managed recovery is stopped, so the standby database becomes increasingly out of date until managed recovery is resumed.
To switch the standby database into read-only mode, do the following.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;
To resume managed recovery, do the following.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
In 11g, Oracle introduced the Active Data Guard feature. This allows the standby database to be open in read-only mode, but still apply redo information. This means a standby can be available for querying, yet still be up to date. There are licensing implications for this feature, but the following commands show how active data guard can be enabled.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Since managed recovery continues with active data guard, there is no need to switch back to managed recovery from read-only mode in this case.
Snapshot Standby
Introduced in 11g, snapshot standby allows the standby database to be opened in read-write mode. When switched back into standby mode, all changes made whilst in read-write mode are lost. This is achieved using flashback database, but the standby database does not need to have flashback database explicitly enabled to take advantage of this feature, thought it works just the same if it is.
Connect to the primary (db11g) database and convert the standby database (db11g_stby) to a snapshot standby.
$ dgmgrl sys/Password1@db11g
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> CONVERT DATABASE db11g_stby TO SNAPSHOT STANDBY;
Converting database "db11g_stby" to a Snapshot Standby database, please wait...
Database "db11g_stby" converted successfully
DGMGRL>
When you are finished with the snapshot standby, convert it back to a standby database.
$ dgmgrl sys/Password1@db11g
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> CONVERT DATABASE db11g_stby TO PHYSICAL STANDBY;
Converting database "db11g_stby" to a Snapshot Standby database, please wait...
Database "db11g_stby" converted successfully
DGMGRL> CONVERT DATABASE db11g_stby TO PHYSICAL STANDBY;
Converting database "db11g_stby" to a Physical Standby database, please wait...
Operation requires shutdown of instance "db11g" on database "db11g_stby"
Shutting down instance "db11g"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "db11g" on database "db11g_stby"
Starting instance "db11g"...
ORACLE instance started.
Database mounted.
Continuing to convert database "db11g_stby" ...
Operation requires shutdown of instance "db11g" on database "db11g_stby"
Shutting down instance "db11g"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "db11g" on database "db11g_stby"
Starting instance "db11g"...
ORACLE instance started.
Database mounted.
Database "db11g_stby" converted successfully
DGMGRL>
The standby is once again in managed recovery and archivelog shipping is resumed. Notice that flashback database is still not enabled.
DGMGRL> SHOW CONFIGURATION;
Configuration - my_dg_config
Protection Mode: MaxPerformance
Databases:
db11g - Primary database
db11g_stby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
DGMGRL> show database standby
Database - standby
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 10.00 KByte/s
Real Time Query: ON
Instance(s):
peko
Warning: ORA-16714: the value of property ArchiveLagTarget is inconsistent with the member setting
Warning: ORA-16714: the value of property LogArchiveMaxProcesses is inconsistent with the member setting
Warning: ORA-16714: the value of property LogArchiveMinSucceedDest is inconsistent with the member setting
Warning: ORA-16714: the value of property DataGuardSyncLatency is inconsistent with the member setting
Warning: ORA-16714: the value of property LogArchiveTrace is inconsistent with the member setting
Warning: ORA-16675: database instance restart required for property value modification to take effect
Warning: ORA-16714: the value of property LogArchiveFormat is inconsistent with the member setting
Database Status:
WARNING
DGMGRL> show database primary statusreport;
STATUS REPORT
INSTANCE_NAME SEVERITY ERROR_TEXT
DGMGRL> exit
[oracle@standby admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Oct 27 14:38:27 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> alter system set log_archive_max_processes=4 scope=both;
System altered.
SQL> alter system set archive_lag_target=0 scope=both;
System altered.
SQL> alter system set log_archive_min_succeed_dest=1 scope=both;
System altered.
SQL>
alter system set log_archive_max_processes=4 scope=spfile;
alter system set archive_lag_target=0 scope=spfile;
alter system set log_archive_min_succeed_dest=1 scope=spfile;
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
ERROR 1
======================
DGMGRL> show configuration;
Configuration - MYDGMGR
Protection Mode: MaxPerformance
Members:
primary - Primary database
Warning: ORA-16792: configurable property value is inconsistent with member setting
standby - Physical standby database
Warning: ORA-16792: configurable property value is inconsistent with member setting
Fast-Start Failover: DISABLED
Configuration Status:
WARNING (status updated 20 seconds ago)
DGMGRL> show database primary;
Database - primary
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
peko
Warning: ORA-16714: the value of property ArchiveLagTarget is inconsistent with the member setting
Database Status:
WARNING
DGMGRL>
ERROR 2
======================
DGMGRL> show database verbose standby
Database - standby
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 1.00 KByte/s
Active Apply Rate: 1.65 MByte/s
Maximum Apply Rate: 1.76 MByte/s
Real Time Query: ON
Instance(s):
peko
Warning: ORA-16714: the value of property ArchiveLagTarget is inconsistent with the member setting
Warning: ORA-16714: the value of property DataGuardSyncLatency is inconsistent with the member setting
Properties:
DGConnectIdentifier = 'standby'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
RedoRoutes = ''
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DataGuardSyncLatency = '0'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
PreferredObserverHosts = ''
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standby.oracle.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=standby_DGMGRL.oracle.com)(INSTANCE_NAME=peko)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Log file locations:
Alert log : /u01/app/oracle/diag/rdbms/standby/peko/trace/alert_peko.log
Data Guard Broker log : /u01/app/oracle/diag/rdbms/standby/peko/trace/drcpeko.log
Database Status:
WARNING
DGMGRL> show database standby InconsistentProperties ;
INCONSISTENT PROPERTIES
INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
peko ArchiveLagTarget 60 1 0
peko DataGuardSyncLatency 0 0
=========================================
Solution Apply on Standby Machine
=========================================
dgmgrl /
show database standby InconsistentProperties ;
INCONSISTENT PROPERTIES
INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
UXOSTBY ArchiveLagTarget 0 0
UXOSTBY LogArchiveMaxProcesses 4 4
UXOSTBY LogArchiveMinSucceedDest 1 1
UXOSTBY DataGuardSyncLatency 0 0
UXOSTBY LogArchiveTrace 0 (missing) 0
UXOSTBY LogArchiveFormat %t_%s_%r.dbf (missing) %t_%s_%r.dbf
sqlplus / as sysdba
alter system set archive_lag_target=0 scope=both sid='*';
alter system set log_archive_max_processes=4 scope=both sid='*';
alter system set log_archive_min_succeed_dest=1 scope=both sid='*';
alter system set data_guard_sync_latency=0 scope=both sid='*';
alter system set log_archive_trace=0 scope=both sid='*';
alter system set log_archive_format='%t_%s_%r.dbf' scope=spfile sid='*';
shutdown immediate;
startup mount;
dgmgrl / "show database UXOSTBY"
Database - uxostby
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 47.00 KByte/s
Real Time Query: OFF
Instance(s):
UXOSTBY
Database Status:
SUCCESS
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
ERROR
===================
DGMGRL> show database primary
Database - primary
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
peko
Warning: ORA-16714: the value of property ArchiveLagTarget is inconsistent with the member setting
Database Status:
WARNING
Solution: (Apply on Primary & Standby Machine)
===============================================
show database 'primary' 'ArchiveLagTarget';
EDIT DATABASE standby SET PROPERTY ArchiveLagTarget=1200;
==== OR ====
ALTER SYSTEM SET archive_lag_target=1200 SCOPE=BOTH SID='*';
#############################################
dgmgrl -logfile $HOME/observer.log sys/Furqan_6677@primary "start observer" &
dgmgrl -logfile $HOME/observer.log sys/Furqan_6677@standby "start observer" &
alter package body ARP_PROCESS_APPLICATION compile;
ALTER PACKAGE AR.ARP_PROCESS_APPLICATION COMPILE BODY;
Recovery Manager: Release 12.2.0.1.0 - Production on Wed Sep 19 15:02:51 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRSTD (DBID=452457302)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
---------------------
==== On Primary =====
---------------------
rman TARGET sys/Furqan_6677@primary AUXILIARY sys/Furqan_6677@standby
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET db_unique_name='standby' COMMENT 'Is standby'
NOFILENAMECHECK;
=========================================================================================
Primary Server Setup
Logging
Check that the primary database is in archivelog mode.
SELECT log_mode FROM v$database;
LOG_MODE
------------
NOARCHIVELOG
SQL>
If it is noarchivelog mode, switch is to archivelog mode.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
Enabled forced logging by issuing the following command.
ALTER DATABASE FORCE LOGGING;
-- Make sure at leat one logfile is present.
ALTER SYSTEM SWITCH LOGFILE;
Create standby redo logs on the primary database (in case of switchovers). The standby redo logs should be at least as big as the largest
online redo log and there should be one extra group per thread compared the online redo logs. In my case, the following standby redo logs
must be created on both servers.
------------------------------------------------------------------------------------------------------------------------------------------
SQL> select GROUP#,THREAD#,BYTES/1024/1024 "MB" from v$log;
GROUP# THREAD# MB
------ ---------- ----------
1 1 200
2 1 200
3 1 200
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/PEKO/onlinelog/standby_redo05.log') SIZE 200M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/fast_recovery_area/peko/PEKO/onlinelog/standby_redo06.log') SIZE 200M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/PEKO/onlinelog/standby_redo07.log') SIZE 200M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/fast_recovery_area/peko/PEKO/onlinelog/standby_redo08.log') SIZE 200M;
===========================================================================================================================
SQL> alter database add standby logfile thread 1 group 8 size 200m;
SQL> alter database add standby logfile thread 1 group 9 size 200m;
SQL> alter database add standby logfile thread 1 group 10 size 200m;
SQL> alter database add standby logfile thread 1 group 11 size 200m;
If you want to user flashback database, enable it on the primary now, so it will be enabled on the standby also. It's very useful as you will see below.
--------------------------------------------------------------------------------------------------------------------------------------------------------
ALTER DATABASE FLASHBACK ON;
Initialization Parameters
Check the setting for the DB_NAME and DB_UNIQUE_NAME parameters. In this case they are both set to "db11g" on the primary database.
------------------------------------------------------------------------------------------------------------------------------------
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string PEKO
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string db11g
SQL> alter system set db_unique_name=primary scope=spfile;
SQL> shut immediate;
SQL> startup;
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string PRIMARY
The DB_NAME of the standby database will be the same as that of the primary, but it must have a different DB_UNIQUE_NAME value. For this example,
the standby database will have the value "db11g_stby".Set suitable remote archive log destinations. In this case I'm using the fast recovery area
for the local location, but you could specify a location explicitly if you prefer. Notice the SERVICE and the DB_UNIQUE_NAME for the remote location reference the standby location.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby';
==== FOR STANDBY====== ( Only this line )
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=primary NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary';
==========================================================================================================================================
SQL> SHOW PARAMETER LOG_ARCHIVE_DEST_STATE_2
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;
SQL> Select status, error from v$archive_dest where dest_id=2;
SQL> select sequence#, applied from v$archived_log;
Make sure the STANDBY_FILE_MANAGEMENT parameter is set.
--------------------------------------------------------------
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
Service Setup
Entries for the primary and standby databases are needed in the "$ORACLE_HOME/network/admin/tnsnames.ora" files on both servers.
You can create these using the Network Configuration Utility (netca) or manually. The following entries were used during this setup.
Notice the use of the SID, rather than the SERVICE_NAME in the entries. This is important as the broker will need to connect to the
databases when they are down, so the services will not be present.
------------------------------------------------------------------------------------------------------------------------------------
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_PEKO =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby.oracle.com)(PORT = 1522))
PRIMARY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.59)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = peko.oracle.com)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.60)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = peko)
)
)
The "$ORACLE_HOME/network/admin/listener.ora" file on the primary server contains the following configuration. Since the broker will need to connect to the database when it's down, we can't rely on auto-registration with the listener, hence the explicit entry for the database.
--------------------------------------------------------------------------------------------------------------
# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
PEKO_PRY =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.59)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = peko_PRY_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/db_1)
(SID_NAME = peko)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
The "$ORACLE_HOME/network/admin/listener.ora" file on the standby server contains the following configuration.
---------------------------------------------------------------------------------------------------------------
PEKO_STD =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.60)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = peko_STD_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/db_1)
(SID_NAME = peko)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
Once the listener.ora changes are in place, restart the listener on both servers.
-----------------------------------------------------------------------------------
lsnrctl stop
lsnrctl start
Standby Server Setup
Prepare for Duplicate
Create a parameter file for the standby database called "/tmp/initdb11g_stby.ora" with the following contents.
--------------------------------------------------------------------------------------------------------------
*.db_name='db11g'
Create the necessary directories on the standby server.
----------------------------------------------------------
$ mkdir -p /u01/app/oracle/oradata/PEKO/datafile
$ mkdir -p /u01/app/oracle/fast_recovery_area/peko
$ mkdir -p /u01/app/oracle/admin/peko/adump
Create a password file, with the SYS password matching that of the primary database.
---------------------------------------------------------------------------------------------------
$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=Furqan_6677 entries=10 FORMAT=12 force=y
-------------------------------------------------------------------------------
\/\/\/\/\/\/\/\/\/\\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\
-------------------------------------------------------------------------------
ENCOUNTERED THE ERROR
==============================
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-01017: invalid username/password; logon denied
SOLUTION :=
============================================================================
12c: RMAN Remote Connection from Backup User Raises ORA-1017 (Doc ID 1997947.1)
Recreate the passwordfile making sure to include FORMAT=12
orapwd FILE=filename ENTRIES=max_users FORMAT=12
-------------------------------------------------------------------------------
\/\/\/\/\/\/\/\/\/\\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\
-------------------------------------------------------------------------------
Create Standby Using DUPLICATE
Start the auxiliary instance on the standby server by starting it using the temporary "init.ora" file.
-------------------------------------------------------------------------------------------------------
cd /u01/oracle/software
$ ll initdb11g_stby.ora
-rw-r--r-- 1 oracle oinstall 17 Oct 25 11:52 initdb11g_stby.ora
$ cat initdb11g_stby.ora
*.db_name='peko'
$ export ORACLE_SID=peko
$ sqlplus / as sysdba
SQL> STARTUP NOMOUNT PFILE='/u01/oracle/software/initdb11g_stby.ora';
Connect to RMAN, specifying a full connect string for both the TARGET and AUXILIARY instances. Do not attempt to use OS authentication.
---------------------------------------------------------------------------------------------------------------------------------------
$ rman TARGET sys/Furqan_6677@primary AUXILIARY sys/Furqan_6677@standby
Now issue the following DUPLICATE command. (PRIMARY SERVER OPERATIONS ONLY)
----------------------------------------------------------------------------
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET db_unique_name='standby' COMMENT 'Is standby'
NOFILENAMECHECK;
If you need to convert file locations, or alter any initialisation parameters, you can do this during the DUPLICATE using the SET command.
-------------------------------------------------------------------------------------------------------------------------------------------
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET db_unique_name='DB11G_STBY' COMMENT 'Is standby'
SET db_file_name_convert='/original/directory/path1/','/new/directory/path1/','/original/directory/path2/','/new/directory/path2/'
SET log_file_name_convert='/original/directory/path1/','/new/directory/path1/','/original/directory/path2/','/new/directory/path2/'
SET job_queue_processes='0'
NOFILENAMECHECK;
A brief explanation of the individual clauses is shown below.
FOR STANDBY: This tells the DUPLICATE command is to be used for a standby, so it will not force a DBID change.
FROM ACTIVE DATABASE: The DUPLICATE will be created directly from the source datafiles, without an additional backup step.
DORECOVER: The DUPLICATE will include the recovery step, bringing the standby up to the current point in time.
SPFILE: Allows us to reset values in the spfile when it is copied from the source server.
NOFILENAMECHECK: Destination file locations are not checked.
Once the command is complete, we can start using the broker.
-----------------------------------------------------------------------------------------------------
+++++++++++++++++++++++++++++++++++++++++++ Enable Broker +++++++++++++++++++++++++++++++++++++++++++
-----------------------------------------------------------------------------------------------------
At this point we have a primary database and a standby database, so now we need to start using the Data Guard
Broker to manage them. Connect to both databases (primary and standby) and issue the following command.
----------------------------------------------------------------------------------------------
NOTES: When we will configure the Dataguard Broker so we will undefine or unbset the log_archive_dest_2 for the
archive shipment on both machine primary and standby.
------
alter system set log_archive_dest_2='' scope=both SID='*';
alter system set log_archive_dest_state_2=defer SID='*';
Its recommendation for dataguard broker.
=========================================================
1. Prerequisits,
Start the Dataguard broker DMON process.
On Primary and Standby,
SQL> alter system set dg_broker_start=true sid='*';
ALTER SYSTEM SET dg_broker_start=true;
On the primary server, issue the following command to register the primary server with the broker.
---------------------------------------------------------------------------------------------------
$ dgmgrl sys/Furqan_6677@PRIMARY
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> CREATE CONFIGURATION 'MYDGMGR' AS PRIMARY DATABASE IS 'primary' CONNECT IDENTIFIER IS primary;
Configuration "my_dg_config" created with primary database "db11g"
DGMGRL>
Now add the standby database.
-----------------------------------------
DGMGRL> add database standby as connect identifier is standby;
Database "db11g_stby" added
DGMGRL>
Now we enable the new configuration.
-----------------------------------
DGMGRL> ENABLE CONFIGURATION;
Enabled.
DGMGRL>
The following commands show how to check the configuration and status of the databases from the broker.
--------------------------------------------------------------------------------------------------------
DGMGRL> SHOW CONFIGURATION;
Configuration - my_dg_config
Protection Mode: MaxPerformance
Databases:
db11g - Primary database
db11g_stby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> SHOW DATABASE PRIMARY;
Database - db11g
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
db11g
Database Status:
SUCCESS
DGMGRL> SHOW DATABASE STANDBY;
Database - db11g_stby
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Apply Rate: 0 Byte/s
Real Time Query: OFF
Instance(s):
db11g
Database Status:
SUCCESS
DGMGRL>
Database Switchover
A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs. This process is known as a Switchover and can be performed using the following commands. Connect to the primary database (db11g) and switchover to the standby database (db11g_stby).
$ dgmgrl sys/Password1@db11g
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> SWITCHOVER TO db11g_stby;
Performing switchover NOW, please wait...
Operation requires a connection to instance "db11g" on database "db11g_stby"
Connecting to instance "db11g"...
Connected.
New primary database "db11g_stby" is opening...
Operation requires startup of instance "db11g" on database "db11g"
Starting instance "db11g"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "db11g_stby"
DGMGRL>
Let's switch back to the original primary. Connect to the new primary (db11g_stby) and switchover to the new standby database (db11g).
$ dgmgrl sys/Password1@db11g_stby
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> SWITCHOVER TO db11g;
Performing switchover NOW, please wait...
Operation requires a connection to instance "db11g" on database "db11g"
Connecting to instance "db11g"...
Connected.
New primary database "db11g" is opening...
Operation requires startup of instance "db11g" on database "db11g_stby"
Starting instance "db11g"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "db11g"
DGMGRL>
Database Failover
If the primary database is not available the standby database can be activated as a primary database using the following statements. Connect to the standby database (db11g_stby) and failover.
$ dgmgrl sys/Password1@db11g_stby
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> FAILOVER TO db11g_stby;
Performing failover NOW, please wait...
Failover succeeded, new primary is "db11g_stby"
DGMGRL>
Since the standby database is now the primary database it should be backed up immediately.
The original primary database can now be configured as a standby. If flashback database was enabled on the primary database, then this can be done relatively easily with the following command.
DGMGRL> REINSTATE DATABASE db11g;
Reinstating database "db11g", please wait...
Operation requires shutdown of instance "db11g" on database "db11g"
Shutting down instance "db11g"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "db11g" on database "db11g"
Starting instance "db11g"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "db11g" ...
Operation requires shutdown of instance "db11g" on database "db11g"
Shutting down instance "db11g"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "db11g" on database "db11g"
Starting instance "db11g"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "db11g" ...
Reinstatement of database "db11g" succeeded
DGMGRL>
If flashback database is not enabled, you would have to manually recreate db11g as a standby. The basic process is the reverse of what you did previously.
# 1) Cleanup the old instance.
sqlplus / as sysdba <<EOF
SHUTDOWN IMMEDIATE;
EXIT;
EOF
rm -Rf /u01/app/oracle/oradata/db11g/*
rm -Rf /u01/app/oracle/fast_recovery_area/db11g
rm -Rf /u01/app/oracle/admin/db11g
mkdir -p /u01/app/oracle/fast_recovery_area/db11g
mkdir -p /u01/app/oracle/admin/db11g/adump
rm $ORACLE_HOME/dbs/spfiledb11g.ora
export ORACLE_SID=db11g
sqlplus / as sysdba <<EOF
STARTUP NOMOUNT PFILE='/tmp/initdb11g_stby.ora';
EXIT;
EOF
# 2) Connect to RMAN.
$ rman TARGET sys/Password1@db11g_stby AUXILIARY sys/Password1@db11g
# 3) Duplicate the database.
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET db_unique_name='DB11G' COMMENT 'Is standby'
NOFILENAMECHECK;
# 4) Connect to DGMDRL on the current primary.
$ dgmgrl sys/Password1@db11g_stby
# 5) Enable the new standby.
DGMGRL> ENABLE DATABASE db11g;
Flashback Database
It was already mentioned in the previous section, but it is worth drawing your attention to Flashback Database once more. Although a switchover/switchback is safe for both the primary and standby database, a failover renders the original primary database useless for converting to a standby database. If flashback database is not enabled, the original primary must be scrapped and recreated as a standby database.
An alternative is to enable flashback database on the primary (and the standby if desired) so in the event of a failover, the primary can be flashed back to the time before the failover and quickly converted to a standby database, as shown above.
Read-Only Standby and Active Data Guard
Once a standby database is configured, it can be opened in read-only mode to allow query access. This is often used to offload reporting to the standby server, thereby freeing up resources on the primary server. When open in read-only mode, archive log shipping continues, but managed recovery is stopped, so the standby database becomes increasingly out of date until managed recovery is resumed.
To switch the standby database into read-only mode, do the following.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;
To resume managed recovery, do the following.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
In 11g, Oracle introduced the Active Data Guard feature. This allows the standby database to be open in read-only mode, but still apply redo information. This means a standby can be available for querying, yet still be up to date. There are licensing implications for this feature, but the following commands show how active data guard can be enabled.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Since managed recovery continues with active data guard, there is no need to switch back to managed recovery from read-only mode in this case.
Snapshot Standby
Introduced in 11g, snapshot standby allows the standby database to be opened in read-write mode. When switched back into standby mode, all changes made whilst in read-write mode are lost. This is achieved using flashback database, but the standby database does not need to have flashback database explicitly enabled to take advantage of this feature, thought it works just the same if it is.
Connect to the primary (db11g) database and convert the standby database (db11g_stby) to a snapshot standby.
$ dgmgrl sys/Password1@db11g
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> CONVERT DATABASE db11g_stby TO SNAPSHOT STANDBY;
Converting database "db11g_stby" to a Snapshot Standby database, please wait...
Database "db11g_stby" converted successfully
DGMGRL>
When you are finished with the snapshot standby, convert it back to a standby database.
$ dgmgrl sys/Password1@db11g
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> CONVERT DATABASE db11g_stby TO PHYSICAL STANDBY;
Converting database "db11g_stby" to a Snapshot Standby database, please wait...
Database "db11g_stby" converted successfully
DGMGRL> CONVERT DATABASE db11g_stby TO PHYSICAL STANDBY;
Converting database "db11g_stby" to a Physical Standby database, please wait...
Operation requires shutdown of instance "db11g" on database "db11g_stby"
Shutting down instance "db11g"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "db11g" on database "db11g_stby"
Starting instance "db11g"...
ORACLE instance started.
Database mounted.
Continuing to convert database "db11g_stby" ...
Operation requires shutdown of instance "db11g" on database "db11g_stby"
Shutting down instance "db11g"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "db11g" on database "db11g_stby"
Starting instance "db11g"...
ORACLE instance started.
Database mounted.
Database "db11g_stby" converted successfully
DGMGRL>
The standby is once again in managed recovery and archivelog shipping is resumed. Notice that flashback database is still not enabled.
DGMGRL> SHOW CONFIGURATION;
Configuration - my_dg_config
Protection Mode: MaxPerformance
Databases:
db11g - Primary database
db11g_stby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
DGMGRL> show database standby
Database - standby
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 10.00 KByte/s
Real Time Query: ON
Instance(s):
peko
Warning: ORA-16714: the value of property ArchiveLagTarget is inconsistent with the member setting
Warning: ORA-16714: the value of property LogArchiveMaxProcesses is inconsistent with the member setting
Warning: ORA-16714: the value of property LogArchiveMinSucceedDest is inconsistent with the member setting
Warning: ORA-16714: the value of property DataGuardSyncLatency is inconsistent with the member setting
Warning: ORA-16714: the value of property LogArchiveTrace is inconsistent with the member setting
Warning: ORA-16675: database instance restart required for property value modification to take effect
Warning: ORA-16714: the value of property LogArchiveFormat is inconsistent with the member setting
Database Status:
WARNING
DGMGRL> show database primary statusreport;
STATUS REPORT
INSTANCE_NAME SEVERITY ERROR_TEXT
DGMGRL> exit
[oracle@standby admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Oct 27 14:38:27 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> alter system set log_archive_max_processes=4 scope=both;
System altered.
SQL> alter system set archive_lag_target=0 scope=both;
System altered.
SQL> alter system set log_archive_min_succeed_dest=1 scope=both;
System altered.
SQL>
alter system set log_archive_max_processes=4 scope=spfile;
alter system set archive_lag_target=0 scope=spfile;
alter system set log_archive_min_succeed_dest=1 scope=spfile;
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
ERROR 1
======================
DGMGRL> show configuration;
Configuration - MYDGMGR
Protection Mode: MaxPerformance
Members:
primary - Primary database
Warning: ORA-16792: configurable property value is inconsistent with member setting
standby - Physical standby database
Warning: ORA-16792: configurable property value is inconsistent with member setting
Fast-Start Failover: DISABLED
Configuration Status:
WARNING (status updated 20 seconds ago)
DGMGRL> show database primary;
Database - primary
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
peko
Warning: ORA-16714: the value of property ArchiveLagTarget is inconsistent with the member setting
Database Status:
WARNING
DGMGRL>
ERROR 2
======================
DGMGRL> show database verbose standby
Database - standby
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 1.00 KByte/s
Active Apply Rate: 1.65 MByte/s
Maximum Apply Rate: 1.76 MByte/s
Real Time Query: ON
Instance(s):
peko
Warning: ORA-16714: the value of property ArchiveLagTarget is inconsistent with the member setting
Warning: ORA-16714: the value of property DataGuardSyncLatency is inconsistent with the member setting
Properties:
DGConnectIdentifier = 'standby'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
RedoRoutes = ''
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DataGuardSyncLatency = '0'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
PreferredObserverHosts = ''
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standby.oracle.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=standby_DGMGRL.oracle.com)(INSTANCE_NAME=peko)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Log file locations:
Alert log : /u01/app/oracle/diag/rdbms/standby/peko/trace/alert_peko.log
Data Guard Broker log : /u01/app/oracle/diag/rdbms/standby/peko/trace/drcpeko.log
Database Status:
WARNING
DGMGRL> show database standby InconsistentProperties ;
INCONSISTENT PROPERTIES
INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
peko ArchiveLagTarget 60 1 0
peko DataGuardSyncLatency 0 0
=========================================
Solution Apply on Standby Machine
=========================================
dgmgrl /
show database standby InconsistentProperties ;
INCONSISTENT PROPERTIES
INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
UXOSTBY ArchiveLagTarget 0 0
UXOSTBY LogArchiveMaxProcesses 4 4
UXOSTBY LogArchiveMinSucceedDest 1 1
UXOSTBY DataGuardSyncLatency 0 0
UXOSTBY LogArchiveTrace 0 (missing) 0
UXOSTBY LogArchiveFormat %t_%s_%r.dbf (missing) %t_%s_%r.dbf
sqlplus / as sysdba
alter system set archive_lag_target=0 scope=both sid='*';
alter system set log_archive_max_processes=4 scope=both sid='*';
alter system set log_archive_min_succeed_dest=1 scope=both sid='*';
alter system set data_guard_sync_latency=0 scope=both sid='*';
alter system set log_archive_trace=0 scope=both sid='*';
alter system set log_archive_format='%t_%s_%r.dbf' scope=spfile sid='*';
shutdown immediate;
startup mount;
dgmgrl / "show database UXOSTBY"
Database - uxostby
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 47.00 KByte/s
Real Time Query: OFF
Instance(s):
UXOSTBY
Database Status:
SUCCESS
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
ERROR
===================
DGMGRL> show database primary
Database - primary
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
peko
Warning: ORA-16714: the value of property ArchiveLagTarget is inconsistent with the member setting
Database Status:
WARNING
Solution: (Apply on Primary & Standby Machine)
===============================================
show database 'primary' 'ArchiveLagTarget';
EDIT DATABASE standby SET PROPERTY ArchiveLagTarget=1200;
==== OR ====
ALTER SYSTEM SET archive_lag_target=1200 SCOPE=BOTH SID='*';
#############################################
dgmgrl -logfile $HOME/observer.log sys/Furqan_6677@primary "start observer" &
dgmgrl -logfile $HOME/observer.log sys/Furqan_6677@standby "start observer" &
alter package body ARP_PROCESS_APPLICATION compile;
ALTER PACKAGE AR.ARP_PROCESS_APPLICATION COMPILE BODY;
No comments:
Post a Comment