Kamran Agayev's Oracle Blog

Few days ago, I was testing some ASM parameters in my 3 nodes 12.2 Clusterware environment and used ASM_PREFERRED_READ_FAILURE_GROUPS parameter to see how I can force ASM to read specific failure group. Testings were successfull but I didn’t know that this parameter is deprecated in 12.2, and beside that, I didn’t imagine that it might cause me a downtime and prevent Clusterware to start.

Here’s the scenario that you can try in your test environment. First of all, I set this parameter to the failure group and then resetted it back:

SQL> alter system set ASM_PREFERRED_READ_FAILURE_GROUPS=”;

System altered.

SQL> 

 

Then I made some hardware changes to my nodes and rebooted them. After nodes are rebooted, I checked the status of the clusterware, and it was down at all nodes.

 

[oracle@oratest01 ~]$ crsctl check crs

CRS-4638: Oracle High Availability Services is online

CRS-4535: Cannot communicate with Cluster Ready Services

CRS-4529: Cluster Synchronization Services is online

CRS-4534: Cannot communicate with Event Manager

 

 

[oracle@oratest01 ~]$ crsctl check cluster -all

**************************************************************

oratest01:

CRS-4535: Cannot communicate with Cluster Ready Services

CRS-4529: Cluster Synchronization Services is online

CRS-4534: Cannot communicate with Event Manager

**************************************************************

oratest02:

CRS-4535: Cannot communicate with Cluster Ready Services

CRS-4530: Communications failure contacting Cluster Synchronization Services daemon

CRS-4534: Cannot communicate with Event Manager

**************************************************************

oratest03:

CRS-4535: Cannot communicate with Cluster Ready Services

CRS-4530: Communications failure contacting Cluster Synchronization Services daemon

CRS-4534: Cannot communicate with Event Manager

**************************************************************

 

Next, I check if ohasd and crsd background processes are up

[root@oratest01 oracle]# ps -ef|grep init.ohasd|grep -v grep

root      1252     1  0 02:49 ?        00:00:00 /bin/sh /etc/init.d/init.ohasd run >/dev/null 2>&1 </dev/null

[root@oratest01 oracle]#

 

[root@oratest01 oracle]# ps -ef|grep crsd|grep -v grep

[root@oratest01 oracle]#

 

OHAS was up and running, but CRSD not. ASM instance should be up in order to bring the crsd, so I checked if ASM instance is up, but it was also down:

[oracle@oratest01 ~]$ ps -ef | grep smon

oracle    5473  3299  0 02:50 pts/0    00:00:00 grep –color=auto smon

[oracle@oratest01 ~]$

 

 

 

Next, I decided to check log files. Logged in to adrci to find the centralized Clusterware log folder:

 

[oracle@oratest01 ~]$ adrci

ADRCI: Release 12.2.0.1.0 – Production on Fri Oct 20 02:51:59 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

ADR base = “/u01/app/oracle”

adrci> show home

ADR Homes:

diag/rdbms/_mgmtdb/-MGMTDB

diag/rdbms/proddb/proddb1

diag/asm/user_root/host_4288267646_107

diag/asm/user_oracle/host_4288267646_107

diag/asm/+asm/+ASM1

diag/crs/oratest01/crs

diag/clients/user_root/host_4288267646_107

diag/clients/user_oracle/host_4288267646_107

diag/tnslsnr/oratest01/asmnet1lsnr_asm

diag/tnslsnr/oratest01/listener_scan1

diag/tnslsnr/oratest01/listener_scan2

diag/tnslsnr/oratest01/listener_scan3

diag/tnslsnr/oratest01/listener

diag/tnslsnr/oratest01/mgmtlsnr

diag/asmtool/user_root/host_4288267646_107

diag/asmtool/user_oracle/host_4288267646_107

diag/apx/+apx/+APX1

diag/afdboot/user_root/host_4288267646_107

adrci> exit

[oracle@oratest01 ~]$ cd /u01/app/oracle/diag/crs/oratest01/crs

[oracle@oratest01 crs]$cd trace

 

[oracle@oratest01 trace]$ tail -f evmd.trc

2017-10-20 02:54:26.533 :  CRSOCR:2840602368:  OCR context init failure.  Error: PROC-32: Cluster Ready Services on the local node is not running Messaging error [gipcretConnectionRefused] [29]

2017-10-20 02:54:27.552 :  CRSOCR:2840602368:  OCR context init failure.  Error: PROC-32: Cluster Ready Services on the local node is not running Messaging error [gipcretConnectionRefused] [29]

2017-10-20 02:54:28.574 :  CRSOCR:2840602368:  OCR context init failure.  Error: PROC-32: Cluster Ready Services on the local node is not running Messaging error [gipcretConnectionRefused] [29]

 

From evmd.trc file it can bees that OCR was not initialized. Then I check alert.log file:

 

[oracle@oratest01 trace]$ tail -f alert.log

2017-10-20 02:49:49.613 [OCSSD(3825)]CRS-1605: CSSD voting file is online: AFD:DATA1; details in /u01/app/oracle/diag/crs/oratest01/crs/trace/ocssd.trc.

2017-10-20 02:49:49.627 [OCSSD(3825)]CRS-1672: The number of voting files currently available 1 has fallen to the minimum number of voting files required 1.

2017-10-20 02:49:58.812 [OCSSD(3825)]CRS-1601: CSSD Reconfiguration complete. Active nodes are oratest01 .

2017-10-20 02:50:01.154 [OCTSSD(5351)]CRS-8500: Oracle Clusterware OCTSSD process is starting with operating system process ID 5351

2017-10-20 02:50:01.161 [OCSSD(3825)]CRS-1720: Cluster Synchronization Services daemon (CSSD) is ready for operation.

2017-10-20 02:50:02.099 [OCTSSD(5351)]CRS-2403: The Cluster Time Synchronization Service on host oratest01 is in observer mode.

2017-10-20 02:50:03.233 [OCTSSD(5351)]CRS-2407: The new Cluster Time Synchronization Service reference node is host oratest01.

2017-10-20 02:50:03.235 [OCTSSD(5351)]CRS-2401: The Cluster Time Synchronization Service started on host oratest01.

2017-10-20 02:50:10.454 [ORAAGENT(3362)]CRS-5011: Check of resource “ora.asm” failed: details at “(:CLSN00006:)” in “/u01/app/oracle/diag/crs/oratest01/crs/trace/ohasd_oraagent_oracle.trc”

2017-10-20 02:50:18.692 [ORAROOTAGENT(3198)]CRS-5019: All OCR locations are on ASM disk groups [DATA], and none of these disk groups are mounted. Details are at “(:CLSN00140:)” in “/u01/app/oracle/diag/crs/oratest01/crs/trace/ohasd_orarootagent_root.trc”.

 

CRS didn’t started as the ASM is not up and running. To checking why ASM wasn’t started upon the server book sounded good starting point for the investigation, so logged in and tried to start ASM instance:

 

[oracle@oratest01 ~]$ sqlplus / as sysasm

SQL*Plus: Release 12.2.0.1.0 Production on Fri Oct 20 02:55:12 2017

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

Connected to an idle instance.

SQL> startup

ORA-01078: failure in processing system parameters

SQL> startup

ORA-01078: failure in processing system parameters

SQL> startup

ORA-01078: failure in processing system parameters

SQL>

 

I checked ASM alert.log file, but it didn’t provide enough information why ASM didn’t start:

NOTE: ASM client -MGMTDB:_mgmtdb:clouddb disconnected unexpectedly.
NOTE: check client alert log.
NOTE: Trace records dumped in trace file /u01/app/oracle/diag/asm/+asm/+ASM1/trace/+ASM1_ufg_20658_-MGMTDB__mgmtdb.trc
NOTE: cleaned up ASM client -MGMTDB:_mgmtdb:clouddb connection state (reg:2993645709)
2017-10-20T02:47:20.588256-04:00
NOTE: client +APX1:+APX:clouddb deregistered
2017-10-20T02:47:21.201319-04:00
NOTE: detected orphaned client id 0x10004.
2017-10-20T02:48:49.613505-04:00
WARNING: Write Failed, will retry. group:2 disk:0 AU:9067 offset:151552 size:4096
path:AFD:DATA1
incarnation:0xf0a9ba5e synchronous result:’I/O error’
subsys:/opt/oracle/extapi/64/asm/orcl/1/libafd12.so krq:0x7f8fced52240 bufp:0x7f8fc9262000 osderr1:0xfffffff8 osderr2:0xc28
IO elapsed time: 0 usec Time waited on I/O: 0 usec
ERROR: unrecoverable error ORA-15311 raised in ASM I/O path; terminating process 20200

 

The problem seemed to be in the parameter file of ASM, so I decided to start it with default parameters and then investigate. For this, I opened searched for the string “parameters” in the ASM alert.log file to get list of parameters and paramter file location:

[oracle@oratest01 trace]$ more +ASM1_alert.log

Using parameter settings in server-side spfile +DATA/clouddb/ASMPARAMETERFILE/registry.253.949654249

System parameters with non-default values:

  large_pool_size          = 12M

  remote_login_passwordfile= “EXCLUSIVE”

  asm_diskstring           = “/dev/sd*”

  asm_diskstring           = “AFD:*”

  asm_diskgroups           = “NEW”

  asm_diskgroups           = “TESTDG”

  asm_power_limit          = 1

  _asm_max_connected_clients= 4

NOTE: remote asm mode is remote (mode 0x202; from cluster type)

2017-08-11T10:22:24.834431-04:00

Cluster Communication is configured to use IPs from: GPnP

 

Then I created parameter file (/tmp/pfile_asm.ora) and started the instance:

SQL> startup pfile=’/home/oracle/pfile_asm.ora’;

ASM instance started

 

Total System Global Area 1140850688 bytes

Fixed Size                                8629704 bytes

Variable Size                      1107055160 bytes

ASM Cache                            25165824 bytes

ASM diskgroups mounted

SQL> exit

 

Great! ASM is up. Now I can restore my parameter file and try to start ASM with it:

 

[oracle@oratest01 ~]$ sqlplus / as sysasm

SQL> create pfile=’/home/oracle/pfile_orig.ora’ from spfile=’+DATA/clouddb/ASMPARAMETERFILE/registry.253.957837377′;

File created.

SQL> 

 

And here is entry of my original ASM parameter file:

[oracle@oratest01 ~]$ more /home/oracle/pfile_orig.ora

+ASM1.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from in memory value

+ASM2.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from in memory value

+ASM3.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from in memory value

+ASM3._asm_max_connected_clients=5

+ASM2._asm_max_connected_clients=8

+ASM1._asm_max_connected_clients=5

*.asm_diskgroups=’DATA’,’ACFSDG’#Manual Mount

*.asm_diskstring=’/dev/sd*’,’AFD:*’

*.asm_power_limit=1

*.asm_preferred_read_failure_groups=”

*.large_pool_size=12M

*.remote_login_passwordfile=’EXCLUSIVE’

 

Good. Now let’s start ASM with it:

SQL> shut abort

ASM instance shutdown

SQL> startup pfile=’/home/oracle/pfile_orig.ora’;

ORA-32006: ASM_PREFERRED_READ_FAILURE_GROUPS initialization parameter has been deprecated

 

ORA-01078: failure in processing system parameters

SQL>

 

Wohoo. ASM failed to start because of deprecated parameter?! Let’s remove it and start ASM without ASM_PREFERRED_READ_FAILURE_GROUPS parameter:

[oracle@oratest01 ~]$ sqlplus / as sysasm

Connected to an idle instance.

SQL> startup pfile=’/home/oracle/pfile_orig.ora’;

ASM instance started

 

Total System Global Area 1140850688 bytes

Fixed Size                                8629704 bytes

Variable Size                      1107055160 bytes

ASM Cache                            25165824 bytes

ASM diskgroups mounted

SQL> 

 

It is started! Next I create ASM parameter file based on this pfile and start the instance:

SQL> create spfile=’+DATA’ from pfile=’/home/oracle/pfile_orig.ora’;

File created.

 

SQL> shut immediate

ASM diskgroups dismounted

ASM instance shutdown

 

SQL> startup

ASM instance started

Total System Global Area 1140850688 bytes

Fixed Size                                8629704 bytes

Variable Size                      1107055160 bytes

ASM Cache                            25165824 bytes

ASM diskgroups mounted

SQL> 

 

After having ASM up and running I restart the clusterware on all nodes and check the status:

[root@oratest01 ~]$  crsctl stop cluster –all

[root@oratest01 ~]$ crsctl start cluster –all

[oracle@oratest01 ~]$ crsctl check cluster -all

**************************************************************

oratest01:

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

**************************************************************

CRS-4404: The following nodes did not reply within the allotted time:

oratest02, oratest03

 

The first node is up, but I wasn’t able to get status of clusterware in other nodes and got CRS-4404 error. To solve it, kill gpnpd process on all nodes and run the command again:

 

[oracle@oratest01 ~]$ ps -ef | grep gpn

oracle    3418     1  0 02:49 ?        00:00:15 /u01/app/12.2.0.1/grid/bin/gpnpd.bin

[oracle@oratest01 ~]$ kill -9 3418

[oracle@oratest01 ~]$ ps -ef | grep gpn

oracle   16169     1  3 06:52 ?        00:00:00 /u01/app/12.2.0.1/grid/bin/gpnpd.bin

 

[oracle@oratest01 ~]$ crsctl check cluster -all

**************************************************************

oratest01:

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

**************************************************************

oratest02:

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

**************************************************************

oratest03:

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

**************************************************************

[oracle@oratest01 ~]$

 

From this blog post you can learn step by step clusterware startup troubleshooting and not to use depracated ASM parameter

Dear friends

Hope most of you already got my book and started preparing for the OCM exam. Every month I get an email from my readers as well as from those who used my book and passed OCM exam successfully!

If you haven’t subscribed to the OCM Newsletter and want to read the previous articles, use the following link:
http://www.ocmguide.com/category/ocm-tips-and-tricks/

 

If you want to get free trial copy of the book in pdf format, use the following address:
http://www.ocmguide.com/

 

If you also want to successfully pass the exam, then use the following address to purchase the book:
https://www.amazon.com/Oracle-Certified-Master-Study-Guide/dp/1536800791/ref=sr_1_1?ie=UTF8&qid=1474879527&sr=8-1&keywords=oracle+exam+guide

 

If you are in my facebook friend list, you have already known that I collect picture of my readers and make them famous in my facebook account So if you are a reader of my book, please send me your photo with my book and become a famous!

Please do not hesitate to contact me directly regarding any OCM topic you find it complicated. And please post your comments on amazon and here on my blog regarding the book, Your feedback is highly appreciated!

 

After dealing with root.sh script to configure 3 node clusterware environment I succeeded but ended up with the following error when post configuration OUI returned the following error and was unable to create container database for  Oracle Grid Infrastructure Management Repository:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

There was no information in the mentioned log file, however the trace of database creation job was enabled and I was able to find a long trace file under the log directory, where I saw the following message:

set newname for datafile 1 to new;

set newname for datafile 3 to new;

set newname for datafile 4 to new;

restore datafile 1;

restore datafile 3;

restore datafile 4; }
[Thread-159] [ 2017-07-24 04:24:07.299 EDT ] [RMANEngine.executeImpl:1321] Notify reader to start reading
[Thread-177] [ 2017-07-24 04:24:07.300 EDT ] [RMANEngine.readSqlOutput:988] Log RMAN Output=echo set off
[Thread-177] [ 2017-07-24 04:24:07.300 EDT ] [RMANEngine.readSqlOutput:988] Log RMAN Output=
[Thread-177] [ 2017-07-24 04:24:07.305 EDT ] [RMANEngine.readSqlOutput:988] Log RMAN Output=RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13>
[Thread-177] [ 2017-07-24 04:24:07.305 EDT ] [RMANEngine.readSqlOutput:988] Log RMAN Output=executing command: SET NEWNAME
[Thread-177] [ 2017-07-24 04:24:07.546 EDT ] [RMANEngine.readSqlOutput:988] Log RMAN Output=
[Thread-177] [ 2017-07-24 04:24:07.547 EDT ] [RMANEngine.readSqlOutput:988] Log RMAN Output=executing command: SET NEWNAME
[Thread-177] [ 2017-07-24 04:24:07.562 EDT ] [RMANEngine.readSqlOutput:988] Log RMAN Output=
[Thread-177] [ 2017-07-24 04:24:07.563 EDT ] [RMANEngine.readSqlOutput:988] Log RMAN Output=executing command: SET NEWNAME
[Thread-177] [ 2017-07-24 04:24:07.578 EDT ] [RMANEngine.readSqlOutput:988] Log RMAN Output=
[Thread-177] [ 2017-07-24 04:24:07.585 EDT ] [RMANEngine.readSqlOutput:988] Log RMAN Output=Starting restore at 24-JUL-17
[Thread-177] [ 2017-07-24 04:24:07.792 EDT ] [RMANEngine.readSqlOutput:988] Log RMAN Output=allocated channel: ORA_DISK_1
[Thread-177] [ 2017-07-24 04:24:07.797 EDT ] [RMANEngine.readSqlOutput:988] Log RMAN Output=channel ORA_DISK_1: SID=18 device type=DISK
[Thread-177] [ 2017-07-24 04:24:08.051 EDT ] [RMANEngine.readSqlOutput:988] Log RMAN Output=
[Thread-177] [ 2017-07-24 04:24:08.383 EDT ] [RMANEngine.readSqlOutput:988] Log RMAN Output=channel ORA_DISK_1: starting datafile backup set restore
[Thread-177] [ 2017-07-24 04:24:08.385 EDT ] [RMANEngine.readSqlOutput:988] Log RMAN Output=channel ORA_DISK_1: specifying datafile(s) to restore from backup set
[Thread-177] [ 2017-07-24 04:24:08.386 EDT ] [RMANEngine.readSqlOutput:988] Log RMAN Output=channel ORA_DISK_1: restoring datafile 00001 to +DATA
[Thread-177] [ 2017-07-24 04:24:08.387 EDT ] [RMANEngine.readSqlOutput:988] Log RMAN Output=channel ORA_DISK_1: reading from backup piece /u01/app/12.2.0.1/grid/assistants/dbca/templates/MGMTSeed_Database.dfb
[Thread-177] [ 2017-07-24 04:24:23.487 EDT ] [RMANEngine.readSqlOutput:988] Log RMAN Output=RMAN-00571: ===========================================================
[Thread-177] [ 2017-07-24 04:24:23.487 EDT ] [RMANEngine.readSqlOutput:988] Log RMAN Output=RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
[Thread-177] [ 2017-07-24 04:24:23.487 EDT ] [RMANEngine.readSqlOutput:988] Log RMAN Output=RMAN-00571: ===========================================================
[Thread-177] [ 2017-07-24 04:24:23.487 EDT ] [RMANEngine.readSqlOutput:988] Log RMAN Output=RMAN-03002: failure of restore command at 07/24/2017 04:24:23
[Thread-177] [ 2017-07-24 04:24:23.487 EDT ] [RMANEngine.readSqlOutput:988] Log RMAN Output=ORA-19870: error while restoring backup piece /u01/app/12.2.0.1/grid/assistants/dbca/templates/MGMTSeed_Database.dfb
[Thread-177] [ 2017-07-24 04:24:23.487 EDT ] [RMANEngine.readSqlOutput:988] Log RMAN Output=ORA-19872: Unexpected end of file at block 4800 while decompressing backup piece /u01/app/12.2.0.1/grid/assistants/dbca/templates/MGMTSeed_Database.dfb
[Thread-177] [ 2017-07-24 04:24:23.495 EDT ] [RMANEngine.readSqlOutput:988] Log RMAN Output=
[Thread-177] [ 2017-07-24 04:24:23.496 EDT ] [RMANEngine.readSqlOutput:988] Log RMAN Output=RMAN>
[Thread-177] [ 2017-07-24 04:24:23.496 EDT ] [RMANEngine.readSqlOutput:988] Log RMAN Output=echo set on
[Thread-177] [ 2017-07-24 04:24:23.504 EDT ] [RMANEngine.readSqlOutput:988] Log RMAN Output=set echo off;
[Thread-177] [ 2017-07-24 04:24:23.504 EDT ] [RMANEngine.readSqlOutput:1031] hasError is true
[Thread-177] [ 2017-07-24 04:24:23.504 EDT ] [RMANEngine.readSqlOutput:1037] ERROR TRACE DETECTED

 

So in ordreate a container database, the installer was trying to restore the database and was unable to do it and hit the following error:

ORA-19872: Unexpected end of file at block 4800 while decompressing backup piece /u01/app/12.2.0.1/grid/assistants/dbca/templates/MGMTSeed_Database.dfb

So the problem was with the backup piece of the MGMT database. Permission were ok, so I compared the size of the restored backup piece with the one in the downloaded zip file:

[root@oratest01 ~]# cd /u01/app/12.2.0.1/grid/assistants/dbca/templates/
[root@oratest01 templates]# ll
total 131452
-rw-r--r-- 1 oracle oinstall 5734 Jan 26 10:48 DomainServicesCluster_GIMR.dbc
-rw-r----- 1 oracle oinstall 18628608 Jan 26 10:46 MGMTSeed_Database.ctl
-rw-r----- 1 oracle oinstall 5177 Jan 26 10:48 MGMTSeed_Database.dbc
-rw-r----- 1 oracle oinstall 39321600 Jan 26 10:46 MGMTSeed_Database.dfb
-rw-r----- 1 oracle oinstall 10578 Jun 10 2016 New_Database.dbt
-rw-r----- 1 oracle oinstall 76619776 Jan 26 10:11 pdbseed.dfb
-rw-r----- 1 oracle oinstall 6579 Jan 26 10:11 pdbseed.xml

 

It was 39M in the extracted folder, and 104Mb in the zip file itself. 

 

 

 

 

 

Somehow it was not correctly unzipped. I moved all files to the backup folder, uploaded backup pieces from the downloaded installation zip file to the same folder in the first node and restarted the configuration – and it succeeded.

 

 

 

 

 

 

 

 

 

 

 

 

Good Luck!

Today while deconfiguring one failed node from the clusterware I faced some Perl related issues that blocked me to run ./rootcrs.pl command. After installing few required packages I was able to deconfigure the node. Check the steps and let me know if it helped you and if you had different errors

 

[root@oratest02 install]$ ./rootcrs.pl -deconfig -force -verbose
-bash: ./rootcrs.pl: /usr/bin/perl: bad interpreter: No such file or directory

 

I checked for perl and didn’t find it.
[root@oratest02 install]$ which perl
/usr/bin/which: no perl in (/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin:/home/oracle/.local/bin:/home/oracle/bin:/u01/app/12.2.0.1/grid/bin)

If the perl wasn’t installed by default, install it:

[root@oratest02 install]# yum install perl -y

Then I got the following errors and installed the required perl modules as follows:

[root@oratest02 install]$ ./rootcrs.pl -deconfig -force -verbose
Can't locate Env.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 . . ./../../perl/lib) at crsinstall.pm line 286.
BEGIN failed--compilation aborted at crsinstall.pm line 286.
Compilation failed in require at ./rootcrs.pl line 165.
BEGIN failed--compilation aborted at ./rootcrs.pl line 165.


[root@oratest02 install]# yum install perl-Env -y


[root@oratest02 install]$ ./rootcrs.pl -deconfig -force -verbose
Can't locate XML/Parser.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 . . ./../../perl/lib) at crsutils.pm line 770.
BEGIN failed--compilation aborted at crsutils.pm line 770.
Compilation failed in require at crsinstall.pm line 290.
BEGIN failed--compilation aborted at crsinstall.pm line 290.
Compilation failed in require at ./rootcrs.pl line 165.
BEGIN failed--compilation aborted at ./rootcrs.pl line 165.
[root@oratest02 install]# yum install perl-XML-Parser -y

Finally I was able to run rootcrs.pl and deconfigure the node from the clusterware

Dear reader.

You must have already known about my OCM exam Study Guide that I’ve published few months ago. If not, then get your pdf copy of sample chapters of the book from www.ocmguide.com now. With along the sample pdf copy you will also be registered for the weekly OCM exam tips email list!

Good luck to you with your exam preparation and feel free to contact me on any questions regarding the exam preparation. I’m ready to help you through it.

Check the following link to read the OCM tip of this week!

 

OCM Tip of the week – Implement fine-grained access control

http://www.ocmguide.com/ocm-tip-of-the-week-implement-fine-grained-access-control/ 

Hello guys

After posting a few articles on DBaaS, I’ve decided to create a single pdf file and collect all my cloud related step by step practical blog posts in it. Click on the following image to download the pdf file and become Oracle DBaaS master! I will keep updating the pdf and hope it will be much easier fo you to get all articles in one shot!

 

In the previous blog posts you have seen how to create a disaster recovery for on-premises Oracle Database by creating a standby database in Oracle Cloud. Sometimes, you might not need to create a standby database, but just store the backup of your database in Oracle Cloud Storage and then use it to create a database in the cloud in the feature. In this blog post I will show you how to take backup of on-premises database to Oracle Cloud Storage and use it to perform a disaster recovery by restoring/recovering from backup to the instance in the cloud and perform recovery of on-premises database using backups stored in the cloud storage using RMAN.

First of all, we need to download and install a backup model to on-premises db. Open the following link and download Oracle Database Cloud Backup Module :

http://www.oracle.com/technetwork/database/availability/oracle-cloud-backup-2162729.html

 

Create folder to store wallets and lib file, extract the zip file and install it:

[oracle@ocm11g ~]$ mkdir wallet lib

[oracle@ocm11g tmp]$ java -jar opc_install.jar -serviceName Storage -identityDomain yourIdentityDomain -opcID YourOpcId -opcPass YourOpcPassword -walletDir /home/oracle/wallet -libDir /home/oracle/lib

Oracle Database Cloud Backup Module Install Tool, build 2016-10-07

Oracle Database Cloud Backup Module credentials are valid.

Oracle Database Cloud Backup Module wallet created in directory /home/oracle/wallet.

Oracle Database Cloud Backup Module initialization file /u03/oracle/product/11.2.4/db_1/dbs/opcPROD.ora created.

Downloading Oracle Database Cloud Backup Module Software Library from file opc_linux64.zip.

Downloaded 26528348 bytes in 12 seconds. Transfer rate was 2210695 bytes/second.

Download complete.

[oracle@ocm11g tmp]$

 

The name of on-premises database is PROD. Now connect to RMAN and change the following configurations. Configure the channel to use SBT library which enable to store backups to the cloud (libopc.so) and provide OPC_FILE destination that contains Oracle Backup Cloud Service container URL.

 

RMAN> CONFIGURE CHANNEL DEVICE TYPE ‘SBT_TAPE’ PARMS  ‘SBT_LIBRARY=/home/oracle/lib/libopc.so ENV=(OPC_PFILE=/u03/oracle/product/11.2.4/db_1/dbs/opcPROD.ora)’;

 

new RMAN configuration parameters:

CONFIGURE CHANNEL DEVICE TYPE ‘SBT_TAPE’ PARMS  ‘SBT_LIBRARY=/home/oracle/lib/libopc.so ENV=(OPC_PFILE=/u03/oracle/product/11.2.4/db_1/dbs/opcPROD.ora)’;

new RMAN configuration parameters are successfully stored

 

Enable autobackup of controlfile:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

 

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters are successfully stored

 

Set the high compression for backups to consume less space in the cloud storage:

RMAN> CONFIGURE COMPRESSION ALGORITHM ‘HIGH’;

 

new RMAN configuration parameters:

CONFIGURE COMPRESSION ALGORITHM ‘HIGH’ AS OF RELEASE ‘DEFAULT’ OPTIMIZE FOR LOAD TRUE;

new RMAN configuration parameters are successfully stored

 

Change the default channel to tape (media -> Oracle Cloud Backup Storage)

RMAN> CONFIGURE DEFAULT DEVICE TYPE TO ‘SBT_TAPE’;

 

new RMAN configuration parameters:

CONFIGURE DEFAULT DEVICE TYPE TO ‘SBT_TAPE’;

new RMAN configuration parameters are successfully stored

RMAN>

 

Now connect to RMAN and run SHOW ALL command to see the backup configurations:

[oracle@ocm11g ~]$ rman target /

 

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name PROD are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO ‘SBT_TAPE’;

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO ‘%F’; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’; # default

CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE CHANNEL DEVICE TYPE ‘SBT_TAPE’ PARMS  ‘SBT_LIBRARY=/home/oracle/lib/libopc.so ENV=(OPC_PFILE=/u03/oracle/product/11.2.4/db_1/dbs/opcPROD.ora)’;

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM ‘AES128’; # default

CONFIGURE COMPRESSION ALGORITHM ‘HIGH’ AS OF RELEASE ‘DEFAULT’ OPTIMIZE FOR LOAD TRUE;

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/u03/oracle/product/11.2.4/db_1/dbs/snapcf_PROD.f’; # default

 

Before taking the backup, create a table at on-premises database. We will query it after disaster recovery in the cloud db.

 

SQL> create table mytable as select * from dba_objects where rownum<=100;

Table created.

 

SQL> select count(1) from mytable;

  COUNT(1)

———-

       100

 

SQL>

 

Now enable encryption (set the password for backups) and take backup of the database:

 

RMAN> set encryption on identified by “mypass” only;

executing command: SET encryption

 

RMAN> backup database plus archivelog;

Starting backup at 10-FEB-17

current log archived

allocated channel: ORA_SBT_TAPE_1

channel ORA_SBT_TAPE_1: SID=33 device type=SBT_TAPE

channel ORA_SBT_TAPE_1: Oracle Database Backup Service Library VER=3.16.9.21

channel ORA_SBT_TAPE_1: starting archived log backup set

channel ORA_SBT_TAPE_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=48 RECID=71 STAMP=935603816

channel ORA_SBT_TAPE_1: starting piece 1 at 10-FEB-17

channel ORA_SBT_TAPE_1: finished piece 1 at 10-FEB-17

piece handle=17rs8bjd_1_1 tag=TAG20170210T175700 comment=API Version 2.0,MMS Version 3.16.9.21

channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:25

Finished backup at 10-FEB-17

 

Starting backup at 10-FEB-17

using channel ORA_SBT_TAPE_1

channel ORA_SBT_TAPE_1: starting full datafile backup set

channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u03/oracle/oradata/PROD/system01.dbf

input datafile file number=00002 name=/u03/oracle/oradata/PROD/sysaux01.dbf

input datafile file number=00003 name=/u03/oracle/oradata/PROD/undotbs01.dbf

input datafile file number=00004 name=/u03/oracle/oradata/PROD/users01.dbf

channel ORA_SBT_TAPE_1: starting piece 1 at 10-FEB-17

channel ORA_SBT_TAPE_1: finished piece 1 at 10-FEB-17

piece handle=18rs8bk6_1_1 tag=TAG20170210T175726 comment=API Version 2.0,MMS Version 3.16.9.21

channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 02:57:07

Finished backup at 10-FEB-17

 

Starting backup at 10-FEB-17

current log archived

using channel ORA_SBT_TAPE_1

channel ORA_SBT_TAPE_1: starting archived log backup set

channel ORA_SBT_TAPE_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=49 RECID=72 STAMP=935605482

input archived log thread=1 sequence=50 RECID=73 STAMP=935614475

channel ORA_SBT_TAPE_1: starting piece 1 at 10-FEB-17

channel ORA_SBT_TAPE_1: finished piece 1 at 10-FEB-17

piece handle=1ars8m0c_1_1 tag=TAG20170210T205435 comment=API Version 2.0,MMS Version 3.16.9.21

channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:09:25

Finished backup at 10-FEB-17

 

Starting Control File and SPFILE Autobackup at 10-FEB-17

piece handle=c-345613202-20170210-02 comment=API Version 2.0,MMS Version 3.16.9.21

Finished Control File and SPFILE Autobackup at 10-FEB-17

 

RMAN>

 

The backup command completed successfully and all backups are stored in Oracle Cloud Backup Storage.

 

Now let’s perform a disaster recovery in the cloud machine. Create a new cloud database instance, configure SSH connection from on-premises to the cloud host. Copy opc_install.zip file you have downloaded from OTN to the cloud host and install it as you did it at on-premises host. Drop the database if there’s any, connect to RMAN and start it in NOMOUNT mode. Provide the RMAN password, allocate a channel as you did at on-premises database and restore the spfile:

 

RMAN> STARTUP NOMOUNT;

RMAN> set decryption identified by “mypass”;

 

executing command: SET decryption

using target database control file instead of recovery catalog

 

RMAN> run

2> {

3> allocate channel t1 type ‘SBT_TAPE’ PARMS  ‘SBT_LIBRARY=/home/oracle/lib/libopc.so ENV=(OPC_PFILE=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/opcPROD.ora)’;

4> set dbid=345613202;

5> restore spfile to pfile ‘/tmp/pfile.ora’ from autobackup;

6> }

 

allocated channel: t1

channel t1: SID=171 device type=SBT_TAPE

channel t1: Oracle Database Backup Service Library VER=3.16.9.21

 

executing command: SET DBID

 

Starting restore at 11-FEB-17

 

channel t1: looking for AUTOBACKUP on day: 20170211

channel t1: looking for AUTOBACKUP on day: 20170210

channel t1: AUTOBACKUP found: c-345613202-20170210-02

channel t1: restoring spfile from AUTOBACKUP c-345613202-20170210-02

channel t1: SPFILE restore from AUTOBACKUP complete

Finished restore at 11-FEB-17

released channel: t1

 

RMAN>

 

Server parameter file is restored. If you need to specify different location for some parameters, create a readable parameter file from it, make your changes, create a server parameter file from it and start the database in NOMOUNT mode using the restored (and modified) spfile.

SQL> startup nomount force;

ORACLE instance started.

 

Total System Global Area 1235959808 bytes

Fixed Size                  2252784 bytes

Variable Size             385875984 bytes

Database Buffers          838860800 bytes

Redo Buffers                8970240 bytes

SQL> exit

 

 

Now restore controlfile from autobackup:

RMAN> set decryption identified by “mypass”;

 

executing command: SET decryption

 

RMAN> run

2> {

3> allocate channel t1 type ‘SBT_TAPE’ PARMS  ‘SBT_LIBRARY=/home/oracle/lib/libopc.so ENV=(OPC_PFILE=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/opcPROD.ora)’;

4> set dbid=345613202;

5> restore controlfile from autobackup;

6> }

 

allocated channel: t1

channel t1: SID=134 device type=SBT_TAPE

channel t1: Oracle Database Backup Service Library VER=3.16.9.21

 

executing command: SET DBID

 

Starting restore at 11-FEB-17

 

channel t1: looking for AUTOBACKUP on day: 20170211

channel t1: looking for AUTOBACKUP on day: 20170210

channel t1: AUTOBACKUP found: c-345613202-20170210-02

channel t1: restoring control file from AUTOBACKUP c-345613202-20170210-02

channel t1: control file restore from AUTOBACKUP complete

output file name=/u04/app/oracle/oradata/control01.ctl

output file name=/u04/app/oracle/oradata/control02.ctl

Finished restore at 11-FEB-17

released channel: t1

 

RMAN>

 

Controlfile are restored. Start the database in MOUNT mode and restore the datafiles. Specify a new folder using SET NEWNAME FOR DATABASE TO command as follows:

RMAN> run

2> {

3> allocate channel t1 type ‘SBT_TAPE’ PARMS  ‘SBT_LIBRARY=/home/oracle/lib/libopc.so ENV=(OPC_PFILE=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/opcPROD.ora)’;

4> set newname for database to ‘/u04/app/oracle/oradata/%U.dbf’;

5> restore database;

6> switch datafile all;

7> }

 

allocated channel: t1

channel t1: SID=133 device type=SBT_TAPE

channel t1: Oracle Database Backup Service Library VER=3.16.9.21

 

executing command: SET NEWNAME

 

Starting restore at 11-FEB-17

Starting implicit crosscheck backup at 11-FEB-17

Crosschecked 1 objects

Finished implicit crosscheck backup at 11-FEB-17

 

Starting implicit crosscheck copy at 11-FEB-17

Crosschecked 2 objects

Finished implicit crosscheck copy at 11-FEB-17

 

searching for all files in the recovery area

cataloging files…

no files cataloged

 

 

channel t1: starting datafile backup set restore

channel t1: specifying datafile(s) to restore from backup set

channel t1: restoring datafile 00001 to /u04/app/oracle/oradata/data_D-PROD_TS-SYSTEM_FNO-1.dbf

channel t1: restoring datafile 00002 to /u04/app/oracle/oradata/data_D-PROD_TS-SYSAUX_FNO-2.dbf

channel t1: restoring datafile 00003 to /u04/app/oracle/oradata/data_D-PROD_TS-UNDOTBS1_FNO-3.dbf

channel t1: restoring datafile 00004 to /u04/app/oracle/oradata/data_D-PROD_TS-USERS_FNO-4.dbf

channel t1: reading from backup piece 18rs8bk6_1_1

channel t1: piece handle=18rs8bk6_1_1 tag=TAG20170210T175726

channel t1: restored backup piece 1

channel t1: restore complete, elapsed time: 00:00:45

Finished restore at 11-FEB-17

 

datafile 1 switched to datafile copy

input datafile copy RECID=14 STAMP=935693831 file name=/u04/app/oracle/oradata/data_D-PROD_TS-SYSTEM_FNO-1.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=15 STAMP=935693831 file name=/u04/app/oracle/oradata/data_D-PROD_TS-SYSAUX_FNO-2.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=16 STAMP=935693831 file name=/u04/app/oracle/oradata/data_D-PROD_TS-UNDOTBS1_FNO-3.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=17 STAMP=935693831 file name=/u04/app/oracle/oradata/data_D-PROD_TS-USERS_FNO-4.dbf

released channel: t1

 

RMAN>

 

Now run ALTER DATABASE RENAME FILE command to rename redo log files:

SQL> alter database rename file ‘/u03/oracle/oradata/PROD/redo03.log’ to ‘/u04/app/oracle/oradata/redo03.log’;

Database altered.

 

SQL> alter database rename file ‘/u03/oracle/oradata/PROD/redo02.log’ to ‘/u04/app/oracle/oradata/redo02.log’;

Database altered.

 

SQL> alter database rename file ‘/u03/oracle/oradata/PROD/redo01.log’ to ‘/u04/app/oracle/oradata/redo01.log’;

Database altered.

 

SQL>

 

Now run RECOVER DATABASE command to recover the database and open the database:

 

RMAN> set decryption identified by “mypass”;

 

executing command: SET decryption

 

RMAN> run

2> {

3> allocate channel t1 type ‘SBT_TAPE’ PARMS  ‘SBT_LIBRARY=/home/oracle/lib/libopc.so ENV=(OPC_PFILE=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/opcPROD.ora)’;

4> recover database;

5> }

 

allocated channel: t1

channel t1: SID=125 device type=SBT_TAPE

channel t1: Oracle Database Backup Service Library VER=3.16.9.21

 

Starting recover at 11-FEB-17

 

starting media recovery

 

channel t1: starting archived log restore to default destination

channel t1: restoring archived log

archived log thread=1 sequence=49

channel t1: restoring archived log

archived log thread=1 sequence=50

channel t1: reading from backup piece 1ars8m0c_1_1

channel t1: piece handle=1ars8m0c_1_1 tag=TAG20170210T205435

channel t1: restored backup piece 1

channel t1: restore complete, elapsed time: 00:00:07

archived log file name=/u03/app/oracle/fast_recovery_area/PROD/archivelog/2017_02_11/o1_mf_1_49_d9yqs878_.arc thread=1 sequence=49

channel default: deleting archived log(s)

archived log file name=/u03/app/oracle/fast_recovery_area/PROD/archivelog/2017_02_11/o1_mf_1_49_d9yqs878_.arc RECID=75 STAMP=935693995

archived log file name=/u03/app/oracle/fast_recovery_area/PROD/archivelog/2017_02_11/o1_mf_1_50_d9yqs8cn_.arc thread=1 sequence=50

channel default: deleting archived log(s)

archived log file name=/u03/app/oracle/fast_recovery_area/PROD/archivelog/2017_02_11/o1_mf_1_50_d9yqs8cn_.arc RECID=74 STAMP=935693994

unable to find archived log

archived log thread=1 sequence=51

released channel: t1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 02/11/2017 19:00:00

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 51 and starting SCN of 1153764

 

RMAN> alter database open resetlogs;

database opened

 

RMAN>

 

Connect to SQL*Plus and query the table you have created before taking a backup at on-premises database:

SQL> select count(1) from mytable;

 

  COUNT(1)

———-

       100

 

SQL>

 

Great! We have successfully performed a disaster recovery of on-premises database to the cloud using RMAN backups stored in Oracle Cloud Storage!

Now let’s use backups stored in the cloud to perform a recovery to on-premises database. Let’s create a new table, take backup of the datafile, corrupt a block of the datafile and recover it from backups stored in the cloud.

 

SQL> create table test_table tablespace users as select * from dba_objects where rownum<=10;

Table created.

 

RMAN> set encryption on identified by “mypass” only;

executing command: SET encryption

 

RMAN> backup datafile 4;               

 

Starting backup at 11-FEB-17

using channel ORA_SBT_TAPE_1

channel ORA_SBT_TAPE_1: starting full datafile backup set

channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set

input datafile file number=00004 name=/u03/oracle/oradata/PROD/users01.dbf

channel ORA_SBT_TAPE_1: starting piece 1 at 11-FEB-17

channel ORA_SBT_TAPE_1: finished piece 1 at 11-FEB-17

piece handle=1drsaim0_1_1 tag=TAG20170211T141008 comment=API Version 2.0,MMS Version 3.16.9.21

channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:45

Finished backup at 11-FEB-17

 

Starting Control File and SPFILE Autobackup at 11-FEB-17

piece handle=c-345613202-20170211-00 comment=API Version 2.0,MMS Version 3.16.9.21

Finished Control File and SPFILE Autobackup at 11-FEB-17

RMAN> exit

 

SQL> SELECT header_block FROM dba_segments WHERE segment_name=’TEST_TABLE’;

HEADER_BLOCK

————

                 170

 

SQL>

 

[oracle@ocm11g ~]$ dd of=/u03/oracle/oradata/PROD/users01.dbf bs=8192 conv=notrunc seek=170 <<EOF

> Corruption

> Corruption

> EOF

0+1 records in

0+1 records out

23 bytes (23 B) copied, 0.000147784 s, 156 kB/s

[oracle@ocm11g ~]$ sqlplus / as sysdba

 

SQL> alter system flush buffer_cache;

System altered.

 

SQL> select count(1) from test_table;

select count(1) from test_table

                     *

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 4, block # 170)

ORA-01110: data file 4: ‘/u03/oracle/oradata/PROD/users01.dbf’

 

SQL> select * from v$database_block_corruption;

     FILE#     BLOCK#             BLOCKS CORRUPTION_CHANGE# CORRUPTIO

———- ———- ———- —————— ———

                 4               170              1                         0 CORRUPT

 

 

Ok, we have a corrupted block. Now connect to RMAN and recover it:

 

RMAN> recover datafile 4 block 170;

 

Starting recover at 11-FEB-17

using channel ORA_SBT_TAPE_1

using channel ORA_DISK_1

 

channel ORA_SBT_TAPE_1: restoring block(s)

channel ORA_SBT_TAPE_1: specifying block(s) to restore from backup set

restoring blocks of datafile 00004

channel ORA_SBT_TAPE_1: reading from backup piece 1drsaim0_1_1

channel ORA_SBT_TAPE_1: piece handle=1drsaim0_1_1 tag=TAG20170211T141008

channel ORA_SBT_TAPE_1: restored block(s) from backup piece 1

channel ORA_SBT_TAPE_1: block restore complete, elapsed time: 00:00:15

 

starting media recovery

media recovery complete, elapsed time: 00:00:01

 

Finished recover at 11-FEB-17

 

RMAN> exit

 

[oracle@ocm11g ~]$ sqlplus / as sysdba

SQL> select count(1) from test_table;

 

  COUNT(1)

———-

                10

 

SQL>

 

As you see, we used backups stored in Oracle Cloud Storage to recover a corrupted block of on-premises database.

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.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

In this post I will show you how to configure backup for Oracle Database in Cloud. First of all, make sure you use Oracle Storage Cloud Service and you set the replication policy. Open the following link, scroll down to Oracle Storage Cloud Service section and click “Set Replication Policy” link:

https://myservices.em2.oraclecloud.com/mycloud/faces/dashboard.jspx?showOld=true

 

 

 

 

 

 

 

 

 

 

 

Select the data center and click Set

 

 

 

 

 

 

 

 

 

 

 

 

 

Next, open Oracle Database Cloud Service and create a new service. The GUI has changed and we have only 3 steps to create a database in the cloud. Provide the service name, software version and edition, upload SSH public key and click Next

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

In order to enable the automatic backup of the database in the cloud, you have to create a cloud storage container. Before creating a cloud storage container, switch to Oracle Storage Cloud Service details and get the REST Endpoint:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Next, open https://storageconsole.em2.oraclecloud.com/ link, provide the Service REST Endpoint and login to Oracle Storage Cloud Service:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Create a new storage container:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

In the second screen of database service creation page, select “Both Cloud Storage and Local Storage” option as a Backup Destination, provide cloud storage container name, username and password and click Next.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Review the configuration and click Create button.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

After creating the service successfully, open it and click on Administration section. From the Backup tab click on Backup Now button to create a backup of the database. You can use RMAN and schedule your own backups as well.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Click Backup Now and check the log file for more information:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

If you switch to the storage container, you will see bunch of files created

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Open RMAN and run LIST BACKUPSET SUMMARY command to get list of backupsets:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Now let’s try to recover the database to the specific point in time using DBaaS wizard. For this, create a new table with some data, get the current SCN number and drop the table.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Next, switch to DBaaS backup page, click Recover, provide the SCN number and click Recover

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The recover process will run in the background automatically. Check alert.log file of the database for more information:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

After the recover process is completed successfully login to the database and query the table

 

 

 

 

 

 

 

 

 

 

You can also take backup and recover the database from command line interface using bkup_api utility. Now let’s delete all backups, take a new backup and try the recovery.

Delete all available RMAN backups:

RMAN> delete backup;

 

Use bkup_api utility with bkup_start parameter to take a backup from CLI:

[root@srvtest spool]# /var/opt/oracle/bkup_api/bkup_api bkup_start

DBaaS Backup API V1.5 @2016 Multi-Oracle home

DBaaS Backup API V1.5 @2015 Multi-Oracle home

-> Action : bkup_start

-> logfile: /var/opt/oracle/bkup_api/log/bkup_api.log

UUID d6bf0bde-c130-11e6-8534-c6b0e87f74cb for this backup

** process started with PID: 16524

** see log file for monitor progress

————————————-

[root@srvtest spool]#

 

 

Check the log file for more information:

[root@srvtest spool]# tail -f /var/opt/oracle/bkup_api/log/bkup_api.log

Tue, 13 Dec 2016 12:36:58 ** process started with PID: 16524

Tue, 13 Dec 2016 12:36:58 ** see log file for monitor progress

Tue, 13 Dec 2016 12:36:58 ————————————-

Tue, 13 Dec 2016 12:36:58 d6bf0bde-c130-11e6-8534-c6b0e87f74cb Checking if TESTDB resource is available

Tue, 13 Dec 2016 12:36:58 d6bf0bde-c130-11e6-8534-c6b0e87f74cb has a lock TESTDB

Tue, 13 Dec 2016 12:36:58 UUID d6bf0bde-c130-11e6-8534-c6b0e87f74cb written with PID 16524

Tue, 13 Dec 2016 12:36:58 d6bf0bde-c130-11e6-8534-c6b0e87f74cb The process is no longer running removing

lock

Tue, 13 Dec 2016 12:36:58 d6bf0bde-c130-11e6-8534-c6b0e87f74cb registering request into the database

Tue, 13 Dec 2016 12:37:00 d6bf0bde-c130-11e6-8534-c6b0e87f74cb current backups 0

Tue, 13 Dec 2016 12:37:00 d6bf0bde-c130-11e6-8534-c6b0e87f74cb command /home/oracle/bkup/TESTDB/obkup -dbname=TESTDB

 

 

Tue, 13 Dec 2016 12:38:51 d6bf0bde-c130-11e6-8534-c6b0e87f74cb@ backups after execution 4

Tue, 13 Dec 2016 12:38:51 d6bf0bde-c130-11e6-8534-c6b0e87f74cb rman tag TAG20161213T123750

Tue, 13 Dec 2016 12:38:51 d6bf0bde-c130-11e6-8534-c6b0e87f74cb rman tag TAG20161213T123729

Tue, 13 Dec 2016 12:38:51 d6bf0bde-c130-11e6-8534-c6b0e87f74cb rman tag TAG20161213T123758

Tue, 13 Dec 2016 12:38:51 d6bf0bde-c130-11e6-8534-c6b0e87f74cb rman tag TAG20161213T123834

Tue, 13 Dec 2016 12:38:51 d6bf0bde-c130-11e6-8534-c6b0e87f74cb Backup succeded TAG20161213T123834

 

 

Now having valid backups, let’s create a new table, drop it and recover it using dbaascli utility.

 

[oracle@srvtest opc]$ sqlplus / as sysdba

SQL> create table mytable2 as select * from dba_objects;

Table created.

 

SQL> select count(1) from mytable2;

  COUNT(1)

———-

     88911

 

SQL> select current_scn from v$database;

CURRENT_SCN

———–

    1333654

 

SQL> drop table mytable2 purge;

Table dropped.

 

SQL> exit

 

Now use dbaascli utility and provide the SCN number to perform SCN based incomplete recovery:

[root@srvtest opc]# dbaascli orec –args -scn 1333654

DBAAS CLI version 1.0.0

Executing command orec –args -scn 1333654

–args : -scn 1333654

 

OREC version: 16.0.0.0

 

Starting OREC

Logfile is /var/opt/oracle/log/TESTDB/orec/orec_2016-12-13_13:41:18.log

Config file is /var/opt/oracle/orec/orec.cfg

 

DB name: TESTDB

OREC:: RUNNING IN NON DATAGUARD ENVIRONMENT

OREC:: Verifying scn validity…

PITR using SCN: 1333654

OREC:: Catalog mode:  Disabled

OREC:: Checking prerequirements before recovery process.

OREC:: DB Status : OPEN

OREC:: Changing instance to MOUNT stage.

OREC:: Shutting down the database… Completed.

OREC:: (RMAN) Startup MOUNT… Completed.

OREC:: Checking for PDBs directories.

OREC:: Checking for REDO logs.

OREC:: Restablishing DB instance to the original stage.

OREC:: Shutting down the database… Completed.

OREC:: Starting up database… Completed.

OREC:: Testing RMAN connection.

OREC:: Verifying backups dates ..

    :: OK

OREC:: Performing PITR using SCN number 1333654 …

INFO : DB instance is up and running after recovery procedure.

OREC:: Completed.

 

[root@srvtest opc]#

 

 

Now connect to the database and check if the table is recovered:

[oracle@srvtest opc]$ sqlplus / as sysdba

SQL> select count(1) from mytable2;

  COUNT(1)

———-

     88911

 

SQL>

 

The database backups are also stored in the flash recovery area in the database host:

 

 

 

 

 

 

 

 

If you want to change the automatic backup schedule, edit /etc/crontab file with a root user. Below you can see the current schedule of the database backup:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

You can use a DBaaS backup wizard, DBaaS command line interface commands and RMAN to perform backup and recovery for Oracle Database in Cloud

In this step by step tutorial, we will create a clone database for the development or testing purposes. Using Oracle Database Cloud service you don’t need to configure and run DUPLICATE command of RMAN and create a clone of production database for developers team. All you need is to create a snapshot of your production database and clone it in a few minutes.

So first of all, let’s create a new database. Open cloud.oracle.com, login with your credentials and create a new database service. Please check my previous blog posts to create a new database service:

kamranagayev.com/2016/12/05/step-by-step-guide-to-create-an-oracle-database-in-the-cloud/

kamranagayev.com/2016/12/06/step-by-step-guide-create-a-primary-and-standby-database-in-the-cloud/ 

 

After successully creating a new database open it and select Administration section

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Before creating a snapshot of the database, login to the database and create a new table, insert one row and commit the transaction. We will check this table after cloning the snapshot

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Next, switch to the Snapshots tab, click “Create Storage Snapshot” button and provide the name of the snapshot. Don’t click Create button

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

When the snapshot of the database is taken, the database is placed into the backup mode. To test it, open SQL connection, click on Create button to create a snapshot and switch to the SQL session and run a command. The session will hang

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

When the snapshot is created, click on the menu icon on the right and choose Create Database Clone to create a clone database from the snapshot

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Next, provide the service name and the database name for the clone database and create it

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

After few minutes the clone database will be created

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Now login to the clone database and check the table that was created before

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The first table was created before the snapshot and thus it’s there. But the second table was create after creating a snapshot and is not available.

As you see, it’s very easy to create a clone database using snapshots in Oracle Cloud. By having a trial account you are provided 500Gb of free space. Each database service consumes 150Gb space, so having 1 production db (150Gb), 1 snapshot (150Gb) and 1 clone database (150Gb) you can easily test the clone database creation with your trial account