By Joel Pérez ( Oracle ACED ) & Skant Gupta(OCE)
In this article we will show you how to create a logical database on cloud. To do this, we need two oracle cloud instances.
Primary Instance Creation
:
Oracle Database Cloud Service
Log in to the "" page to create a new service
• Select a service name of your choice
Oracle Database Cloud Service”
• Select the service level as “
• Select Metering Frequency of your choice
Oracle Database 11g Release 2
• Select the Software Release as “”
Enterprise Edition
• Select Software Edition as “”
Single Instance
• Select Database Type as “”
• Click on Next to proceed
Service Details Section
Under Database Configuration
• Select a DB Name (SID)
• Provide Administration Password (this would be used as the sys password)
• Confirm the password
• Select Usable Database Storage (25 GB minimum)
• Select Compute Shape as (OC3 – 1 OCPU, 7.5 GB RAM min required configuration)
• Provide SSH Public key
• Click on Next to proceed
Finally, check the configuration and click on "Create"
After a few minutes, primary cloud instance will be created successfully.
Click on the service name (Primary) to open the main page of database.
You should enable dblistener access rule before connecting to the primary instance on the cloud machine.
Click on hamburger icon () and then select access rules.
Click on Actions menu for the ora_p2_dblistener rule and enable it
Standby Instance Creation:
Oracle Database Cloud Service
Open the "" page and create a new service
Oracle Database Cloud Service
• Select “” as the service level
• Select Metering Frequency of your choice
Oracle Database 11g Release 2
• Select Software Release as “”
Enterprise Edition
• Select Software Edition as “”
Single Instance
• Select Database Type as “”
• Click on Next to proceed
Service Details Section
Database Configuration
Undersection
• Select a DB Name (SID)
• Provide Administration Password (this would be used as the sys password)
• Confirm the password
• Select Usable Database Storage (25 GB minimum)
• Select Compute Shape as (OC3 – 1 OCPU, 7.5 GB RAM min required configuration)
• Provide SSH Public key
• Click on Next to proceed
Note: This dummy database will be dropped after creation. #FormatImgID_9#
Finally, check the configuration and click on "Create". This will create a new Standby cloud instance.
#FormatImgID_10#
After a few minutes, standby cloud instance will be created successfully.
#FormatImgID_11#
We click on the service name (Standby) to open the main page of database.
#FormatImgID_12#
You need to enable dblistener access rule before connecting to the standby instance on the cloud machine. Click on hamburger icon (#FormatImgID_13#) and then select access rules.
#FormatImgID_14#
Again, click on Actions menu for the ora_p2_dblistener rule and enable it for the newly created standby instance.\
Now we are going to open two different instances of the "Putty" executable and connect SSH to both the machines.
Check the hostname and pmon on each server.
Here, we shall drop the standby database instance which was created earlier to create a new instance manually.
Configure static registration of instance on listener file as shown below.
We will now restart the listener using below commands:
• lsnrctl stop
• lsnrctl start
You need to configure tnsnames.ora with Public IP, which is provided on console.
Make sure tnsping is working on both sides.
Create pfile and password file on the standby machine. Additionally, create necessary directories for standby instance.
Start database in nomount mode with pfile and check the “rman duplicate” command connection from primary database.
Setup the below parameters on the primary instance. These parameters are required for creating a standby instance.
Create instantiation for standby using below “rman duplicate” command
This creates a new physical standby database instance.
Note:
Newly created standby instance is running in mount state.
Open the standby in read only mode to start the recovery.
Also verify the working of physical database.
Logical Database:
Converting physical standby database into a logical database
1. Check for unsupported Data type and Tables
Before setting up a logical standby database, ensure that logical standby database can maintain the data types and tables in your primary database.
This is done on the primary database.
The physical organization in a logical standby database is different from that of the primary database, even though the logical standby database is created from a backup copy of the primary database.
The value of the BAD_COLUMN column will be either Y or N, as described in the following list:
Y
Indicates a table column is defined using an unbounded datatype, such as CLOB or BLOB. SQL Apply attempts to maintain these tables, but you must ensure the application provides uniqueness in bounded columns only. Note that if two rows in the table match except for rows in the LOB column, then the table cannot be maintained properly and SQL Apply will stop.
N
Indicates that your table contains enough column information to maintain the table in a logical standby database.
3. Enable Supplemental Logging on both primary and standby
Supplemental logging must be enabled on the primary database to support the logical standby database.
Oracle only logs the columns that were modified which may not always be sufficient to uniquely identify the row that changed. Moreover, additional (supplemental) information must be put into the redo log. The supplemental information that is added to the redo logs help log apply services to correctly identify and maintain tables in the logical standby database.
Note:
If you enable supplemental logging on a primary database in a Data Guard configuration that also contains physical standby databases, then you must issue the ALTER DATABASE ADD SUPPLEMENTAL LOG DATA statement on each physical standby database to ensure future switchovers work correctly.
4. Prepare the Primary Database for Role Transitions
This parameter takes effect only when the primary database is transitioned to the logical standby role.
5. Stop Redo Apply on the Physical Standby Database
Once the candidate physical standby database has been created, halt all redo apply processes.
This ensures that any changed data won’t be applied to the candidate physical standby until we complete the creation of the LogMiner dictionary (as stated in step:4)
Executing procedure DBMS_LOGSTBY.BUILD creates the LogMiner dictionary, enables supplemental logging, and then waits for any existing transactions on the primary database to complete. Execute the following command from Primary database:
7. Prepare the Standby Database for Role Transitions
You need to modify the LOG_ARCHIVE_DEST_n parameters because, unlike physical standby databases, logical standby databases are open databases that generate redo data and have multiple log files (online redo log files, archived redo log files, and standby redo log files).
8. Convert to a Logical Standby Database
At this point, the candidate physical standby database is ready to be converted into a logical standby database.
9. Open the Logical Standby Database
When you open the newly created logical Standby Database for the first time, its Global name is automatically adjusted to match the new initialization parameter DB_NAME
10. Verify the Logical Standby Database #FormatImgID_40#
So now you know, it is very easy to create a logical database in the cloud.
We hope this article has proved useful and we invite you to continue reading our upcoming publications focused on Oracle Cloud.
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