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 least 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.
alter database add standby logfile thread 1 group 10
('/acfspoc/GGTRG_STD/redo4.log') size 50m;
alter database add standby logfile thread 1 group 11
('/acfspoc/GGTRG_STD/redo5.log') size 50m;
alter database add standby logfile thread 1 group 12
('/acfspoc/GGTRG_STD/redo6.log') size 50m;
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/acfspoc/GGTRG_STD/redo03.log
/acfspoc/GGTRG_STD/redo02.log
/acfspoc/GGTRG_STD/redo01.log
/acfspoc/GGTRG_STD/redo4.log
/acfspoc/GGTRG_STD/redo5.log
/acfspoc/GGTRG_STD/redo6.log
If you want to use
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<code> parameters. In
this case they are both set to "GGTRG" on the primary database.
SQL> show parameter db_name
NAME TYPE
VALUE
------------------------------------ -----------
--------------------------=
----
db_name string
GGTRG
SQL> show parameter db_unique_name
NAME TYPE
VALUE
------------------------------------ -----------
--------------------------=
----
db_unique_name string
GGTRG
SQL>
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 "GGTRG_STD".
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
the= se using the Network Configuration Utility (netca) or manually. The
followi= ng entries were used during this setup. Notice the use of the SID
, rather than the SERVICE_NAME in the entries. This is impo= rtant as the
broker will need to connect to the databases when they are dow= n, so the
services will not be present.
GGTRG=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL =
TCP)(HOST =rac2.databasexpert.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID =GGTRG)
)
)
GGTRG_STD=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL =
TCP)(HOST =rac1.databasexpert.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID =GGTRG_STD)
)
)
The
"$ORACLE_HOME/network/admin/listener.ora" file on the primary server contains the following configuration.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL =
TCP)(HOST = rac2.databasexpert.com)(PORT = 1521))
(ADDRESS = (PROTOCOL =
IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME =
GGTRG)
(ORACLE_HOME = /u008/app/oracle/product/19.3.0/dbhome_1/)
(SID_NAME = GGTRG)
(ENVS="TNS_ADMIN=/u008/app/oracle/product/19.3.0/dbhome_1//network/admin")
)
)
ADR_BASE_LISTENER = /u01/app/oracle
The
"$ORACLE_HOME/network/admin/listener.ora" file on the standby server
contains the following configuration. Since the broker will need to connec t
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 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL =
TCP)(HOST = rac1.databasexpert.com)(PORT = 1521))
(ADDRESS = (PROTOCOL =
IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = GGTRG_STD)
(ORACLE_HOME = /u008/app/oracle/product/19.3.0/dbhome_1/)
(SID_NAME = GGTRG_STD)
(ENVS="TNS_ADMIN=/u008/app/oracle/product/19.3.0/dbhome_1/network/admin")
)
)
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/initGGTRG_s= tby.ora" with the
following contents.
*.db_name='GGTRG'
Create the necessary
directories on the standby server.
mkdir -p /u01/app/oracle/oradata/GGTRG/pdbseed
mkdir -p /u01/app/oracle/oradata/GGTRG/pdb1
mkdir -p /u01/app/oracle/fast_recovery_area/GGTRG
mkdir -p /u01/app/oracle/admin/GGTRG/adump
Create a password file,
with the SYS password matching that of the primary database.
$ orapwd file=/u008/app/oracle/product/19.3.0/dbhome_1/dbs/orapwGGTRG
password=System123# entries=10
Create Standby Using DUPLICATE
Start the auxiliary
instance on the standby server by starting it using the temporary
"init.ora" file.
$ export ORACLE_SID=GGTRG
$ sqlplus / as sysdba
SQL> STARTUP NOMOUNT PFILE='/tmp/initGGTRG_STD.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/System123#@GGTRG AUXILIARY sys/System123#@GGTRG_STD
Now issue the following
DUPLICATE command.
duplicate target database
for standby
from active database
dorecover
spfile
set db_unique_name='GGTRG_STD'
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='GGTRG_STD'
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
becreated directly from the source datafiles, without an additional backup
step.
- DORECOVER: The DUPLICATE will
include the r ecovery 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 nowwe need to start using the
Data Guard Broker to manage them. Connect to both databases (primary and
standby) and issue the following command.
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/System123#@GGTRG
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Jan 26
22:39:33 20=
18
Version 19.2.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserve=
d.
Welcome to DGMGRL, type "help" for information.
Connected as SYSDBA.
DGMGRL> create configuration my_dg_config as primary
database is GGTRG connect identifier is GGTRG;
Configuration "my_dg_config" created with primary
database "GGTRG"
DGMGRL>
Now add the standby
database.
DGMGRL> add database GGTRG_STD as connect identifier is GGTRG_STD
maintained as physical;
Database "GGTRG_STD" 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
Members:
GGTRG -
Primary database
GGTRG_STD - Physical
standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 26
seconds ago)
DGMGRL> show database GGTRG;
Database - GGTRG
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
GGTRG
SUCCESS
DGMGRL> show database GGTRG_STD;
Database - GGTRG_STD
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: 5.00
KByte/s
Real Time Query: OFF
Instance(s):
GGTRG
Database Status:
SUCCESS
DGMGRL>
-- Stop managed recovery.
alter database recover managed standby database cancel;
-- Start managed recovery.
alter database recover managed standby database disconnect;
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 (GGTRG) and switchover to the standby database
(GGTRG_STD).
$ dgmgrl sys/System123#@GGTRG
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Jan 26
22:39:33 2018
Version 19.2.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected as SYSDBA.
DGMGRL> switchover to GGTRG_STD;
Performing switchover NOW, please wait...
Operation requires a connection to instance "GGTRG" on
database "GGTRG_STD"
Connecting to instance "GGTRG"...
Connected as SYSDBA.
New primary database "GGTRG_STD" is opening...
Operation requires start up of instance "GGTRG" on
database "GGTRG"
Starting instance "GGTRG"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "GGTRG_STD"
DGMGRL>
Let's switch back to the
original primary. Connect to the new primary (GGTRG_STD) and switchover to the
new standby database (GGTRG).
$ dgmgrl sys/System123#@GGTRG_STD
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Jan 26
22:53:36 2018
Version 19.2.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected as SYSDBA.
DGMGRL> switchover to GGTRG;
Performing switchover NOW, please wait...
Operation requires a connection to instance "GGTRG" on
database "GGTRG"
Connecting to instance "GGTRG"...
Connected as SYSDBA.
New primary database "GGTRG" is opening...
Operation requires start up of instance "GGTRG" on
database "GGTRG_STD"
Starting instance "GGTRG"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "GGTRG"
DGMGRL>
Error :-
Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.databasexpert.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=GGTRG_STD_DGMGRL)(INSTANCE_NAME=GGTRG_STD)(SERVER=DEDICATED)))
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Solution :-
DGMGRL> show database ggtrg staticconnectidentifier;
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2.databasexpert.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=GGTRG_DGMGRL)(INSTANCE_NAME=GGTRG)(SERVER=DEDICATED)))'
DGMGRL> show database ggtrg_std staticconnectidentifier;
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.databasexpert.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=GGTRG_STD_DGMGRL)(INSTANCE_NAME=GGTRG_STD)(SERVER=DEDICATED)))'
=========================
[oracle@rac2 admin]$ sqlplus sys@"(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.databasexpert.com)(PORT=1529))(CONNECT_DATA=(SERVICE_NAME=GGTRG_STD)(INSTANCE_NAME=GGTRG_STD)(SERVER=DEDICATED)))" as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 28 22:20:00 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
==
[oracle@rac2 admin]$ vi listener.ora
[oracle@rac2 admin]$ sqlplus sys@"(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2.databasexpert.com)(PORT=1529))(CONNECT_DATA=(SERVICE_NAME=GGTRG)(INSTANCE_NAME=GGTRG)(SERVER=DEDICATED)))" as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 28 22:14:48 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
========================
DGMGRL> edit database ggtrg set property staticconnectidentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2.databasexpert.com)(PORT=1529))(CONNECT_DATA=(SERVICE_NAME=GGTRG)(INSTANCE_NAME=GGTRG)(SERVER=DEDICATED)))';
Property "staticconnectidentifier" updated
DGMGRL> edit database ggtrg_std set property staticconnectidentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.databasexpert.com)(PORT=1529))(CONNECT_DATA=(SERVICE_NAME=GGTRG_STD)(INSTANCE_NAME=GGTRG_STD)(SERVER=DEDICATED)))';
Property "staticconnectidentifier" updated
===========================
DGMGRL> show configuration
Configuration - my_dg_config
Protection Mode: MaxPerformance
Members:
ggtrg_std - Primary database
ggtrg - Physical standby database
Warning: ORA-16809: multiple warnings detected for the member
Fast-Start Failover: Disabled
Configuration Status:
WARNING (status updated 52 seconds ago)
DGMGRL> switchover to ggtrg_std
============================
DGMGRL> switchover to ggtrg
Performing switchover NOW, please wait...
New primary database "ggtrg" is opening...
Operation requires start up of instance "GGTRG_STD" on database "ggtrg_std"
Starting instance "GGTRG_STD"...
Connected to an idle instance.
ORACLE instance started.
Connected to "GGTRG_STD"
Database mounted.
Connected to "GGTRG_STD"
Switchover succeeded, new primary is "ggtrg"
=============================
Comments
Post a Comment