Create a Standby database in Oracle Cloud for On-Premises production database
Posted by Kamran Agayev A. on 23rd January 2017
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 blog post you will see a step by step guide on how to create a Standby Database in Oracle Cloud for your on-premises database.
Before reading this blog post, check my previous articles to become a familiar with Oracle Cloud:
Configure and practice backup and recovery for Oracle Database in Cloud (DBaaS)
Create a clone database in Oracle Cloud
http://kamranagayev.com/2016/12/10/create-a-clone-database-in-oracle-cloud/
Step by step guide to create an Oracle Database in the Cloud
http://kamranagayev.com/2016/12/05/step-by-step-guide-to-create-an-oracle-database-in-the-cloud/
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 Key (check above mentioned articles to see how to create a SSH public key), 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.
STBDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 140.86.3.98)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = STBDB)
(UR = A)
)
)
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:
[oracle@srvtst ~]$ sqlplus / as sysdba
SQL> startup force mount exclusive restrict;
ORACLE instance started.
Total System Global Area 2655657984 bytes
Fixed Size 2256192 bytes
Variable Size 637534912 bytes
Database Buffers 1996488704 bytes
Redo Buffers 19378176 bytes
Database mounted.
SQL> drop database;
Database dropped.
SQL>
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:
[oracle@ocm11g admin]$ tnsping STBDB
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 140.86.3.98)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = STBDB) (UR = A)))
OK (250 msec)
[oracle@ocm11g admin]$
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:
[oracle@ocm11g ~]$ cd .ssh
[oracle@ocm11g .ssh]$ ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/home/oracle/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/oracle/.ssh/id_rsa.
Your public key has been saved in /home/oracle/.ssh/id_rsa.pub.
The key fingerprint is:
1f:e8:8d:08:78:80:12:e5:c6:cb:cb:7a:97:2e:1b:02 oracle@ocm11g
The key’s randomart image is:
+–[ RSA 2048]—-+
|… |
| = |
|o = |
|.o + . |
|E + o S . |
|.. o . o + . |
|. + .. o o |
| oo.o |
|…=. |
+—————–+
[oracle@ocm11g .ssh]$
Now copy the source of id_rsa.pub file and append it to the /home/oracle/.ssh/authorized_keys file at the cloud machine.
[oracle@ocm11g .ssh]$ more id_rsa.pub
ssh-rsa AAAAB3NzaC1yc2EAAAABIwAAAQEAn2fjBDvcycbxQxVrzFQS2URSERkdJXTdpHGw68GiQWUnCR8T8jSwntDWH4az37Lyj7WgN0NGW7HFWC0m9EMJ/RfCPj6SXnCjdXOO2qwuxMit9B9suqm7plfQl+HpGTrdx6KIW2UXW1M/7l2CDNjJD7zDFZ4MNwBIOtlT5lpHm61iquVeBUwFg/3fjpnk6/IjX5K0mM8gLHWpc6WEDLcLKHgKWcVUGvY/KF1W2ehbGIo6tSDkDV2wwEj8H5G5DCxLs2Mczq1dzgt99SLVpw3s7/aGRWrzPVRVPjmn1Y7AHnDFNFvP32V3fzKCaAHHQLjDeA6ZQyjMjBUFAxWuiymunw== oracle@ocm11g
Now test the connection from virtual box to the cloud machine:
[oracle@ocm11g .ssh]$ ssh 140.86.3.98
The authenticity of host ‘140.86.3.98 (140.86.3.98)’ can’t be established.
RSA key fingerprint is 73:93:3c:62:41:d4:12:aa:09:07:c7:94:aa:ea:00:16.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ‘140.86.3.98’ (RSA) to the list of known hosts.
[oracle@srvtst ~]$ exit
logout
Connection to 140.86.3.98 closed.
Before duplicating the database, create necessary folders on the cloud machine
[oracle@ocm11g .ssh]$ ssh 140.86.3.98
[oracle@srvtst ~]$ mkdir -p admin/STBDB/adump
[oracle@srvtst ~]$ mkdir -p oradata/STBDB
[oracle@srvtst ~]$ mkdir flash_recovery_area
[oracle@srvtst ~]$ mkdir arch
Create a parameter file to start standby instance:
vi /home/oracle/pfile.ora
*.audit_file_dest=’/home/oracle/admin/STBDB/adump’
*.control_files=’/home/oracle/oradata/STBDB/control01.ctl’
*.db_file_name_convert=’/u03/oracle/oradata/PROD/’,’/home/oracle/oradata/STBDB/’
*.db_name=’PROD’
*.db_unique_name=’STBDB’
*.db_recovery_file_dest=’/home/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=5g
*.fal_client=’STBDB’
*.fal_server=’PROD’
*.log_archive_dest_1=’location=/home/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=STBDB’
*.log_file_name_convert=’/u03/oracle/oradata/PROD/’,’/home/oracle/oradata/STBDB/’
*.compatible=’11.2.0.4.0′
Connect to SQL*Plus, create spfile and open the instance in the NOMOUNT mode:
[oracle@srvtst ~]$ sqlplus / as sysdba
Connected to an idle instance.
SQL> startup nomount pfile=’/home/oracle/pfile.ora’;
ORACLE instance started.
Total System Global Area 229683200 bytes
Fixed Size 2251936 bytes
Variable Size 171967328 bytes
Database Buffers 50331648 bytes
Redo Buffers 5132288 bytes
SQL> create spfile from pfile=’/home/oracle/pfile.ora’;
File created.
SQL> shut immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 229683200 bytes
Fixed Size 2251936 bytes
Variable Size 171967328 bytes
Database Buffers 50331648 bytes
Redo Buffers 5132288 bytes
SQL>
Create a password file on the standby machine
[oracle@srvtst ~]$ orapwd file=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwSTBDB password=oracle entries=5
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:
SQL> select name, db_unique_name, database_role, switchover_status from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE SWITCHOVER_STATUS
——— —————————— —————- ——————–
PROD STBDB PHYSICAL STANDBY TO PRIMARY
SQL>
Make sure you set LOG_ARCHIVE_DEST_2 parameter on the on-premises database and specify the instance running on the cloud machine:
SQL> ALTER SYSTEM SET log_archive_dest_2=’SERVICE=STBDB ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STBDB’;
System altered.
SQL>
No switch to the cloud machine and start the apply process:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Database altered.
SQL>
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.
SQL> alter system switch logfile;
System altered.
SQL> create table mytable as select * from dba_objects where rownum<=100;
Table created.
SQL> alter system switch logfile;
System altered.
SQL>
Next, open the standby database in the read only mode and see if you can query the table created on on-premises database:
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> select count(1) from mytable;
COUNT(1)
———-
100
SQL>
As you see, the table has been moved within archived log file to the cloud machine and applied to the standby instance.
Posted in Oracle Cloud | No Comments »