Kamran Agayev's Oracle Blog

Oracle Certified Master

Archive for January 23rd, 2017

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)

http://kamranagayev.com/2016/12/14/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.

Image 1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

 

Image 2

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

 

Image 3

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Click on Actions menu for the ora_p2_dblistener rule and enable it

 

Image 4

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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>

 

Image 5

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Image 6

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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 »