DATAGUARD –
Setting up manual process using RMAN DUPLICATE
This is a working doc - will amend as necessary
Thanks to Russell Willcox for the instructions.
See also
There is a prebuilt Oracle VM under here - I built the Linux 7, Oracle 12.2 version to test.
(You can use the vagrant user - password "vagrant" to login to the servers built under there but for Dataguard, the root user is "rootpasswd" and the oracle password is "oracle.)
1
On Primary database check db in archive log mode
Export ORACLE_SID="insert name here"
Sqlplus / as sysdba
archive log list;
2 If not in archive log mode turn it on
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
3
Enable force logging by issuing command
alter database force logging;
alter system switch logfile;
4
Create standby redo logs on both the Primary and Standby
alter database add standby logfile
group 4 ('+NFPSADWORADATA','+FRA') size 200M;
alter database add standby logfile
group 5 ('+NFPSADWORADATA','+FRA') size 200M;
alter database add standby logfile
group 6 ('+NFPSADWORADATA','+FRA') size 200M;
5
Check the DB_NAME and DB_UNIQUE_NAME on the Primary database
Show parameter db_name
Show parameter db_unique_name
The DB_NAME of the standby database
will be the same as the primary but the DB_UNIQUE_NAME will be different.
6
Ensure that the STANDBY_FILE_MANAGEMENT is set
alter system set standby_file_management=auto;
7 TNS service names setup
Ensure that the primary and
standby databases are in the tnsnames.ora on both servers.
Standby
Primary
8
Setup the listener.ora on both servers
Primary
Standby
9
Stop and start the listener on both servers
lsnrctl stop
lsnrctl start
10
Prepare for the RMAN duplicate
11
Create the standby Control file and Pfile on the Primary database
create pfile=’/tmp/adwdata_stby.ora’;
12
Create the necessary directories on the Standby server
Mkdir -p
/u01/app/12.2.0/admin/ADWDATA
This location needs to be the
same as the primary
13
Copy the created files from the Primary server
Scp standby control file to all
locations
Scp servername:/tmp/adwdata_stby.ctl
/u01/app/oracle………
Scp servername:/tmp/adwdata_stby.ora
/u01/app/oracle/product/dbhmome/dbs
14
Establish the remote login password file
Either copy the password file from the
primary server, or create it on the standby using the same sys password as that
used on the primary, the default location for the file is:
/u01/app/oracle/product/dbhome/dbs
Syntax to create the password file is
Orapwd
file=filepath/filename password=sys password entries=10
15
Create the standby using RMAN Duplicate
Rman TARGET sys/password@ADWDATA
AUXILIARY sys/password@ADWDATA_STBY
DUPLICATE TARGET DATABASE
FOR
STANDBY
FROM
ACTIVE DATABASE
DORECOVER
SPFILE
SET
db_unique_name='DB11G_STBY' COMMENT 'Is standby'
SET LOG_ARCHIVE_DEST_2='SERVICE=db11g
ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G'
SET
FAL_SERVER='DB11G' COMMENT 'Is primary'
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 datafile, 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.
16
Check the output of the duplicate process
If no errors are seen proceed to the
next step otherwise try the following:
Dgmgrl sys/password@ADWDATA (primary)
Show
configuration
Show
database ADWDATA
Show
database ADWDATA_STBY
Show
database ADWDATA InconsistentProperties
Show
database ADWDATA_STBY InconsistentProperties
If any properties are inconsistent
then resolve them before starting the apply process
17
Enable the broker
At this point you will have a primary database and a standby
database so you need to start using the Dataguard Broker to manage those
databases, enter the following command on both the Primary and the Standby:
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/password@ADWDATA
create configuration adwdata_dg_config
as primary database is ADWDATA connect identifier is ADWDATA;
On the Standby
server enter:
add database ADWDATA_STBY as connect
identifier is ADWDATA_STBY maintained as physical;
enable configuration;
18
Start the apply process
alter database recover managed
standby database; or
alter database recover managed
standby database disconnect from session;
(this will return control to your
session after starting apply process)
If you need to cancel the apply
process:
alter database recover managed
standby database cancel;
If you want to delay the application
of changes to your standby enter:
alter database recover managed standby
database cancel;
alter database recover managed standby
database delay 30 disconnect from session;
alter database recover managed standby
database cancel;
alter database recover managed standby
database nodelay disconnect from session;
Additional
Oracle Data Guard switchover
To show the status of the databases currently
Logon onto the Oracle Data Guard broker:
DGMGRL sys/password
Ø Show configuration
Ø Show database dbname
Make a note of which database is the current standby.
To switchover to the standby instance
Ø Switchover to standby_instance
Performing switchover NOW, please wait...
Operation requires a connection to database "cdb1"
Connecting ...
Connected to "cdb1"
Connected as SYSDBA.
New primary database "cdb1" is opening...
Operation requires start up of instance "cdb1" on database "cdb1_stby"
Starting instance "cdb1"...
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Database mounted.
Connected to "cdb1_stby"
Switchover succeeded, new primary is "cdb1"
ORA-01017 error on switchover
switchover to cdb1_stby;
Performing switchover NOW, please wait...
New primary database "cdb1_stby" is opening...
Operation requires start up of instance "cdb1" on database "cdb1"
Starting instance "cdb1"...
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
Please complete the following steps to finish switchover:
start up and mount instance "cdb1" of database "cdb1"
Resolution
Copy the orapw file from the database that you are initiating the switchover from
Or
Recreate the Oracle password file using Oracle utility as below:
orapwd file=…../dbs/filename password=sys password entries=10
Or
As a quick fix, mount the standby database and put it into recovery mode
Sqlplus / as sysdba
alter database mount;
alter database recover managed standby database disconnect from session;
Verify that switchover is successful
DGMGRL sys/password
Ø Show database primary_dbname
Database - cdb1
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
cdb1
Database Status:
SUCCESS
Ø Show database standby_dbname
Database - cdb1_stby
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 27 seconds ago)
Apply Lag: 0 seconds (computed 27 seconds ago)
Average Apply Rate: 218.00 KByte/s
Real Time Query: OFF
Instance(s):
+++++++++++++++++++++++++++++
This is handy - from here :
https://dba.stackexchange.com/questions/110497/how-does-one-identify-if-oracle-data-guard-is-enabled-in-oracle-database
set serveroutput on
declare
feature_boolean number;
aux_count number;
feature_info clob;
begin
dbms_feature_data_guard(feature_boolean, aux_count, feature_info);
dbms_output.put_line(feature_boolean);
dbms_output.put_line(feature_info);
end;
/
Sample output when Data Guard is not used:
0
Data Guard usage not detected
Sample output when Data Guard is used:
1
Number of standbys: 1, Redo Apply used: TRUE, SQL Apply used: FALSE, Snapshot
Standby used: FALSE, Broker used: TRUE, Protection mode: MAXIMUM PERFORMANCE,
Log transports used: LGWR ASYNC, Fast-Start Failover used: FALSE, Real-Time
Apply used: TRUE, Compression used: FALSE, Flashback used: FALSE