Joel Pérez
By( Oracle ACED )&. ( Oracle ACED )
If you have a production database and you plan to build a standby database on the different geographic location, Oracle Cloud is the best option. In this article you will see a step by step guide on how to create a Standby Database in Oracle Cloud for your on-premises database.
Ok, now let’s get started.
First of all, login to your Oracle Cloud account, switch to Oracle Database Cloud Service and create a new Service. Provide a service name, SSH Public, choose “Enterprise Edition – Extreme Performance” for Software Edition option and click Next.
We will create a standby database based on on-premises production database, so in the next screen provide any database name. We will delete it once it is created and will create a standby database using DUPLICATE DATABASE command.
Review the configuration and click Create to create a Database Cloud Service instance.
It take only 20 minutes to create a new machine, install an Oracle Software and create a new database in the cloud.
Next, create a new virtual machine in your own laptop, install Oracle 11.2.4 on Linux (OEL is preferred) and add two network cards – “Host-only Adapter” and “Bridged Adapter”. “Host-Only Adapter” is used to connect to the virtual machine from the host machine and “Bridged Adapter” is used to connect from the Virtual Machine to the outside world (internet, cloud instance and etc.). Enable both network devices, make sure you have internet connection, edit tnsnames.ora file as follows and use tnsping to ping the cloud host.
Next, use private key to connect to the cloud machine using putty and drop the ORCL database in the cloud machine
Drop the database in the cloud machine:
Before trying to connect to the new dummy instance on the cloud machine, you have to enable dblistener access rule. Open the database service and Access Rule from the menu.
Click on Actions menu for the ora_p2_dblistener rule and enable it
Now you will be able to using tnsping to test the connection:
In order to connect to the cloud machine from outside, you need to configure SSH. Open Virtual Machine box, switch to .ssh folder and generate ssh key using ssh-keygen utility as follows:
Now copy the source of id_rsa.pub file and append it to the /home/oracle/.ssh/authorized_keys file at the cloud machine.
Now test the connection from virtual box to the cloud machine:
Before duplicating the database, create necessary folders on the cloud machine
Create a parameter file to start standby instance:
vi /home/oracle/pfile.ora
Connect to SQL*Plus, create spfile and open the instance in the NOMOUNT mode:
Create a password file on the standby machine
Connect to both target and auxiliary instances and duplicate the database
[oracle@ocm11g dbs]$ rman target sys/oracle@PROD auxiliary sys/oracle@STBDB
connected to target database: PROD (DBID=345613202)
connected to auxiliary database: PROD (not mounted)
RMAN> duplicate target database for standby from active database;
Starting Duplicate Db at 20-JAN-17
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=171 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile ‘/u03/oracle/product/11.2.4/db_1/dbs/orapwPROD’ auxiliary format
‘/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwSTBDB’ ;
}
executing Memory Script
Starting backup at 20-JAN-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 device type=DISK
Finished backup at 20-JAN-17
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format ‘/home/oracle/oradata/STBDB/control01.ctl’;
}
executing Memory Script
Starting backup at 20-JAN-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u03/oracle/product/11.2.4/db_1/dbs/snapcf_PROD.f tag=TAG20170120T145657 RECID=3 STAMP=933778620
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:05
Finished backup at 20-JAN-17
contents of Memory Script:
{
sql clone ‘alter database mount standby database’;
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
“/home/oracle/oradata/STBDB/temp01.dbf”;
switch clone tempfile all;
set newname for datafile 1 to
“/home/oracle/oradata/STBDB/system01.dbf”;
set newname for datafile 2 to
“/home/oracle/oradata/STBDB/sysaux01.dbf”;
set newname for datafile 3 to
“/home/oracle/oradata/STBDB/undotbs01.dbf”;
set newname for datafile 4 to
“/home/oracle/oradata/STBDB/users01.dbf”;
backup as copy reuse
datafile 1 auxiliary format
“/home/oracle/oradata/STBDB/system01.dbf” datafile
2 auxiliary format
“/home/oracle/oradata/STBDB/sysaux01.dbf” datafile
3 auxiliary format
“/home/oracle/oradata/STBDB/undotbs01.dbf” datafile
4 auxiliary format
“/home/oracle/oradata/STBDB/users01.dbf” ;
sql ‘alter system archive log current’;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /home/oracle/oradata/STBDB/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 20-JAN-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u03/oracle/oradata/PROD/system01.dbf
output file name=/home/oracle/oradata/STBDB/system01.dbf tag=TAG20170120T145917
channel ORA_DISK_1: datafile copy complete, elapsed time: 02:14:37
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u03/oracle/oradata/PROD/sysaux01.dbf
output file name=/home/oracle/oradata/STBDB/sysaux01.dbf tag=TAG20170120T145917
channel ORA_DISK_1: datafile copy complete, elapsed time: 01:24:17
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u03/oracle/oradata/PROD/undotbs01.dbf
output file name=/home/oracle/oradata/STBDB/undotbs01.dbf tag=TAG20170120T145917
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:05:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u03/oracle/oradata/PROD/users01.dbf
output file name=/home/oracle/oradata/STBDB/users01.dbf tag=TAG20170120T145917
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:56
Finished backup at 20-JAN-17
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP=933824671 file name=/home/oracle/oradata/STBDB/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=933824671 file name=/home/oracle/oradata/STBDB/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=933824671 file name=/home/oracle/oradata/STBDB/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=933824671 file name=/home/oracle/oradata/STBDB/users01.dbf
Finished Duplicate Db at 20-JAN-17
RMAN>
Connect to cloud database and query V$DATABASE view: #FormatImgID_17#
Make sure you set LOG_ARCHIVE_DEST_2 parameter on the on-premises database and specify the instance running on the cloud machine:
Now switch to the cloud machine and start the apply process:
Ok, the standby database is ready. Perform some logfile switches, create a new table and switch log file again. Move the standby machine and check alert.log file to see if log files are moved and applied to the standby database.
Next, open the standby database in the read only mode and see if you can query the table created on on-premises database:
As you see, the table has been moved within archived log file to the cloud machine and applied to the standby instance.
We hope this article could be useful for your learning about databases in the cloud.
Regards!
About the Authors
Joel Pérez is an Expert DBA ( Oracle ACE Director, Maximum Availability OCM, OCM Cloud Admin. & OCM12c/11g ) with over 17 years of Real World Experience in Oracle Technology, specialized in design and implement solutions of: High Availability, Disaster Recovery, Upgrades, Replication, Cloud and all area related to Oracle Databases. International consultant with duties, conferences & activities in more than 50 countries in 5 continents and countless clients around the world. Habitual and one of leading writers of Technical Oracle articles for: OTN Spanish & OTN Portuguese. Regular Speaker in worldwide Oracle events like: OTN LAD (Latin America), OTN MENA(Middle East & Africa), OTN APAC ( Asian Pacific), DTCC China and more. Joel has always been known for being a pioneer in Oracle technology since the early days of his career being the first Latin American awarded as “OTN Expert” at year 2003 by Oracle Corp., one of the first “Oracle ACE” globally in the Oracle ACE Program at year 2004 and as one of the biggest professional achievement in his career he was honoured as one of the first “OCM Database Cloud Administrator” & Maximum Availability OCM in the world. Currently Joel works as Chief Technologist in “Yunhe Enmo (Beijing) Technology Co.,Ltd”., company located in Beijing, China www.Enmotech.com
Kamran Agayev A. is an Oracle Certified Master, Oracle Clusterware Certified Expert and RAC Implement Specialist, Oracle Certified Professional (9i,10g,11g) and Oracle ACE Director working as a DBA team head at AzerCell Telecom LLC. He's author of the books Oracle Backup and Recovery: Expert secrets for using RMAN and Data Pump and Oracle Certified Master 11G Exam Guide. He runs a popular blog kamranagayev.com where he shares his experience and contributes fairly regularly to newsgroups, forums, and user group meetings and events around the world. He is a frequent speaker and presented in many countries, most recently at the USA, Japan, Thailand, China, India, Argentina, Uruguay, Panama, Costa Rica, Mexico, Guatemala, Finland and Turkey He is President of Azerbaijan Oracle User Group (AzerOUG) and delivers a class about Oracle Database Administration at Qafqaz University.