Tuesday, November 12, 2013

Creating a standby database using OEM12C

I have to admit I'm a CLI kind of guy, but I occasionally use a GUI. This was one of these times. I'd not used OEM to do this before and was prepared for a load of JAVA error messages and other set-backs but it worked smoothly.

Before doing this, there are a few assumptions:



Source and target hosts are the same OS
Source and target Oracle versions are the same (preferably 11.2.0.3)
Source and target hosts have been added to OEM 12C
Source database has been added to OEM 12C
Source database is in archive log mode
Sufficient space in the $ORACLE_HOME file system to hold a full database backup if using the “Create new backup option”.
The source host and target host can communicate between each other.



Login to OEM, select “Targets / Databases” and click on the database that is to have the standby created.

 
 
 


At the database home screen, click on “Availability / High Availability Console”:
 

At the bottom of the screen, click on “Add Standby Database”
 



Make sure “Create a new physical standby database” is checked, and click on “Continue”:



Select the required option – either run a new backup, or use an existing backup if a recent one exists:
 

 

Click “Next”
Complete the fields accordingly, then click on "Test"
 

If the test is successful, click “Next”.
Complete the fields accordingly, then click on "Test'

 
If the test is successful, click “Next”


 

Click on “Customize” to set the new file names and locations:
 


Click “OK”
If you haven’t pre-created the target directories a warning will be shown:



This is OK. Click “Continue”.
Click “Next”
Change the archive log destination to fit the standards, and make sure the database name is correct:


 

Make sure “Use Data Guard Broker” is checked.
Click “Next”
If you see this:
 
 

It’s OK, click “Continue”.
Check all the entries, then click “Finish” if OK. If not, go back and fix anything up accordingly.
You may be thrown back to a screen that prompts you to run the dataguard wizard – just ignore this and “view jobs” to see the creation job.
It will show that a job has been submitted:




Click on “View Job” or on “Oracle Database / Job Activity” and it should be running.
 



Keep an eye on the status.
If it says “Succeeded”, click on the link to check the status:



Go to “Targets / Databases” and find the source database:
 


Should show “Primary”.
Click on it, then at the database home page, click on “Availability / High Availability Console”
 
 

If you see “Unable to determine Data Guard Information”, click on “Availability / Verify Data Guard Configuration”.
It will run through a list of checks:
 

 

It will also show a detailed log. If there are warnings / errors, investigate and fix.
Click “OK”.
It should now show up in OEM:

 
 Time to go back to the command line.


On the target host, check the standby status:

[oracle@lnfsnr81 2013_05_31]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect /
Connected.
DGMGRL> show configuration;

Configuration - DGTST_lnfsnd81

  Protection Mode: MaxPerformance
  Databases:
    DGTST - Primary database
    DGSBY - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show configuration verbose;

Configuration - DGTST_lnfsnd81

  Protection Mode: MaxPerformance
  Databases:
    DGTST - Primary database
    DGSBY - Physical standby database

  Properties:
FastStartFailoverThreshold      = '30'
OperationTimeout                = '30'
FastStartFailoverLagLimit       = '30'
CommunicationTimeout            = '180'
FastStartFailoverAutoReinstate  = 'TRUE'
FastStartFailoverPmyShutdown    = 'TRUE'
BystandersFollowRoleChange      = 'ALL'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> exit

To enable Active data guard, open the database:
[oracle@lnfsnr81 2013_05_31]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Fri May 31 10:05:19 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

Note – when we created a second database, it appeared in OEM without needing the manual addition shown here.
You now need to add it to OEM. Select “Targets / Databases” , then Add.
Enter the host name and click “Continue”




Select the standby database and click “Configure”, fill in the fields accordingly:


 


Click “Next”, then “OK”, then “Finish”, then “Save”, then “OK”.
The database should now be in the OEM list:


We found that we had to navigate around a few screens before it showed up as a physical standby:
 
 

And before it showed up in the Data Guard Console:


 

It might be worth logging off and back onto OEM.
 

You can use the links to look at the log apply status (Log File Details) etc.

It's worth testing a table creation and update on the primary to confirm it would be created on the standby:
[oracle@lnfsnd81 dbs]$ . oraenv
ORACLE_SID = [DGTST] ?
[oracle@lnfsnd81 dbs]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Fri May 31 11:01:09 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SQL> create table andy(col1 varchar2(100));

Table created.

SQL> insert into andy values('XXX');

1 row created.

SQL> commit;

Commit complete.


[oracle@lnfsnr81 trace]$ . oraenv
ORACLE_SID = [DGSBY2] ? DGSBY
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@lnfsnr81 trace]$ sqld

SQL*Plus: Release 11.2.0.3.0 Production on Fri May 31 11:02:07 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SQL> select * from andy;

COL1
--------------------------------------------------------------------------------
XXX

So that's it. No nasty JAVA errors, and very pointy-clicky.