Kamran Agayev's Oracle Blog

Oracle Certified Master

Configure and practice backup and recovery for Oracle Database in Cloud (DBaaS)

Posted by Kamran Agayev A. on December 14th, 2016

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

 

image-1

 

 

 

 

 

 

 

 

 

 

Select the data center and click Set

 

 

image-2

 

 

 

 

 

 

 

 

 

 

 

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

 

 

image-3

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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:

 

image-18

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

image-19

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Create a new storage container:

 

image-4

 

 

 

 

 

 

 

 

 

 

image-6

 

 

 

 

 

 

 

 

 

 

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.

 

 

image-5

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Review the configuration and click Create button.

 

image-7

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

 

 

 

image-8

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

image-9

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

image-10

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

 

image-12

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

image-13

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

image-14

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

image-15

 

 

 

 

 

 

 

 

 

 

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 image-16in 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:

image-17

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

Posted in Oracle Cloud | No Comments »

Create a clone database in Oracle Cloud

Posted by Kamran Agayev A. on December 10th, 2016

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 Aimage-8dministration 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

image-9

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

image-10

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

image-11

 

 

 

 

 

 

image-12

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

image-13

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

image-14

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

After few minutes the clone database wilimage-15l be created

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

image-16

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

Posted in Oracle Cloud | No Comments »

Step by step guide – create a primary and standby database in the Cloud!

Posted by Kamran Agayev A. on December 6th, 2016

In this guide, I will show you how to create a primary and standby database in the cloud. Login to your cloud account, switch to the Oracle Database Cloud Service page and create a new service. Select “Oracle Database Cloud Service” as a subscription type and click Next.

image-1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Select a database release and click Next.

image-2

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Select “Enterprise Edition – Extreme Performance” as a Software Edition and click Next

 

image-3

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Provide the service name, SSH public key and select “Standby Database with Data Guard” option, select “High Availability” and click Next

image-4

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Review the configuration settings and click Create to create a primary and a standby database

image-5

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

After a few minutes the primary and standby database will be created successfully

image-6

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Click on the service name (srvdg) to open the home page of both databases

 

image-7

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Now open two different Putty executables and connect to the both machines

 

image-8

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Connect to the both databases and check the DB_UNIQUE_NAME parameter. PRODDB_01 is set to the primary database, PRODDB_02 for the standby database. Also check LOG_ARCHIVE_DEST_2 parameter on the primary database. As you see, archived log files are shipped to the standby database using PRODDB_02 service.

image-9

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Now let’s test the functionality of the standby database. First of all, open an alert.log file of the standby database, switch the log file on the primary database and check if the log file is applied

 

image-10

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Now perform a switchover

 

image-11

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Switch the log file and see if it is applied to the new standby database

image-12

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

It worked. Now switch back

 

image-13

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

As you see, it’s very easy to create a database with its own standby database in the cloud!

Posted in Oracle Cloud | No Comments »

Step by step guide to create an Oracle Database in the Cloud

Posted by Kamran Agayev A. on December 5th, 2016

In this blog post I will share the steps to create an Oracle Database in Cloud. We will create a database service, create a second database in the same machine in a silent mode, access to the cloud machine with SSH and monitor the database with OEM.

First of all, make sure you have an Oracle account. Open oracle.com and click on Register link to get a free oracle account. Next, open cloud.oracle.com, select “Compute” from Infrastructure menu and click “Try It” button to get a free trial account for 1 month. Next, login with your oracle account and register for cloud account. Make sure you don’t apply with generic email addresses like hotmail, gmail and etc. Instead, use your company address.

After successfully registering you will get an email with your credential information. Click on the link specified in the email, provide username/password and login to your cloud account. Click Oracle Database Cloud Service link.

 

image-1

 

 

Click on the link under “My Service URL” to access list of cloud services you have.

 

image-2

 

 

 

 

 

 

 

 

 

 

 

 

If you haven’t signed in yet, sign in page will appear. Use the username and password provided in the email you got during the registration and login

image-3

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Next, Oracle Cloud Services dashboard will appear.

 

image-4

 

 

 

 

 

 

 

 

 

 

 

 

 

To create and manage database instances, click on the “Open Service Console” menu of the Database tab as shown below:

 

image-5

 

 

 

 

 

 

 

 

 

In this page you will get list of database services. Here, I have two database services (myfirstdb, myseconddb). I have 7.5G memory and 150gb of storage provided for each service.

image-6

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Click Delete on the drop down menu for each service and delete both services.

 

image-7

 

 

 

 

 

 

 

 

 

 

 

 

 

After successfully deleting available instances, click on “Create Service” button to create a new database instance:

image-8

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Select “Oracle Database Cloud Service” option to create a database using a wizard. For the billing frequency you have two options: Hourly and Monthly. It doesn’t make sense when you use a trial account. So select any of them and click Next.

 

image-9

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

For the software release you have 3 options: 11.2, 12.1 and 12.2. Choose any of them and click Next.

 

image-10

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

As a software edition, you have 4 options:

Standard Edition

Enterprise Edition

Enterprise Edition – High Performance

Enterprise Edition – Extreme Performance

To get more information and features that each edition provides, check the following documentation:

 

Home / Cloud / Oracle Database Cloud Service/ Using Oracle Database Cloud Service/ About Database Cloud Service Database Deployments/ Oracle Database Software Package

https://docs.oracle.com/cloud/latest/dbcs_dbaas/CSDBI/GUID-660363B8-0E2F-4A4F-A9BD-70A43F332A16.htm#GUID-6C0B1E17-98A5-4522-A3B9-36EFA05E64F9

Select “Enterprise Edition” option and click Next

 

image-11

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

In the next page you will be asked to provide a SSH Public Key for the cloud service. For this, use Putty Key Generator executable to generate a SSH public key. Click on Generate button, move your mouse over the blank are to generate some action.

 

image-12

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The SSH key will be generated as follows. Provide a password and click on “Save private key” button to save this key as a private key

 

image-13

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

To save a key as a public key, copy the text and save it.

 

image-14

 

 

 

 

 

 

 

 

 

 

 

 

 

image-15

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Provide this file for the “SSH Public Key” field of the database service creation wizard.

image-16

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Provide the service name, database storage, select necessary compute shape and click Next.

image-17

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Review the information and click Create button to create a service

image-18

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Click “In Progress” link to check the service creation progress.

image-19

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

After a while, the service will be created successfully.

 

image-20

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Click on proddb link to open the service.

 

image-21

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Before trying to connect to the database in the cloud from outside, you should enable dblistener security rule. Open “Oracle Database Cloud Service” dashboard.

 

image-22

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Click on Network tab

image-23

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

When you click on Network tab, you will get list of security roles.

 

image-24

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Click on the menu icon for ora_p2_dblistener role and select Update.

 

image-25

 

 

 

 

 

 

 

Enable the status of this security role

 

image-26

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Enable the ora_p2_dbconsole security role to get access to OEM.

 

image-27

 

 

 

 

 

 

Now, let’s connect to the database from SSH. Provide the private key that was saved above

image-28

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Provide the IP of the virtual machine that is provided in the main page of the database cloud service and click Open.

 

image-29

 

 

 

 

 

 

 

Provide username as “oracle” and password that was provided when generating a private key using PuTTy Key Generator tool and login to the server where the database is running.  Connect to SQL*Plus and run SQL commands:

image-31

 

 

 

 

 

 

 

 

 

 

 

 

 

image-32

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

No open tnsnames.ora file and add the following entry:

 

tnsnames.ora

PRODDB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 141.144.32.70)(PORT = 1521))

(CONNECT_DATA =

(SID = PRODDB)

)

)

 

Open a command prompt, login to the database in the cloud and run SQL commands:

 

image-33

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

To open an OEM, click on the menu icon on the Database Cloud Service home page and select “Open EM Console”

 

image-34

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Provide the username and password and login

 

image-35

 

 

 

 

 

 

 

 

 

 

 

image-36

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

image-37

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

After creating and configuring a database using a wizard, I decided to create a new database in a silent mode in the same machine as follows:

 

 

[oracle@proddb dbhome_1]$ dbca -silent -createdatabase -gdbname mydb -templatename /u01/app/oracle/product/11.2.0/dbhome_1/assistants/dbca/templates/General_Purpose.dbc -sid mydb -syspassword oracle -systempassword oracle -emConfiguration none -datafileDestination /u02/app/oracle/oradata/mydb -memoryPercentage 40

 

Copying database files

1% complete

3% complete

11% complete

18% complete

26% complete

37% complete

Creating and starting Oracle instance

40% complete

45% complete

50% complete

55% complete

56% complete

60% complete

62% complete

Completing Database Creation

66% complete

70% complete

73% complete

74% complete

75% complete

76% complete

77% complete

88% complete

99% complete

100% complete

Look at the log file “/u01/app/oracle/cfgtoollogs/dbca/mydb/mydb.log” for further details.

[oracle@proddb dbhome_1]$

 

Check if the database is up and running and connect to it:

 

[oracle@proddb dbhome_1]$ ps -ef | grep smon

oracle    7040     1  0 11:40 ?        00:00:00 ora_smon_mydb

oracle    7226  2625  0 11:40 pts/1    00:00:00 grep smon

oracle   11837     1  0 10:30 ?        00:00:00 ora_smon_PRODDB

[oracle@proddb dbhome_1]$ export ORACLE_SID=mydb

[oracle@proddb dbhome_1]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Sat Dec 3 11:40:39 2016

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

 

Run free command to check the free space of the machine. We have 2 databases running on this machine, so we have only 1g free memory.

[oracle@proddb dbhome_1]$ free

total       used       free     shared    buffers     cached

Mem:       7397060    6295684    1101376     266948      66356    1364664

-/+ buffers/cache:    4864664    2532396

Swap:      4194300      29916    4164384

[oracle@proddb dbhome_1]$

After having a database service with the specific parameters, you can change the parameters anytime. Let’s add 2gb free space to the machine. Switch to the home page of the database cloud service, click on the menu icon and choose “Scale Up/Down” link

image-38

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Provide size of the new space and click “Yes, Scale Up/Down Service” button

image-39

 

 

 

 

 

 

 

 

 

 

The host will reboot and the required space will be added :

image-40

 

 

 

 

 

 

 

 

 

The new space is mounted to the new mount point (u05):

 

[oracle@proddb ~]$ df -kh

Filesystem            Size  Used Avail Use% Mounted on

/dev/xvdb3             25G   12G   12G  51% /

tmpfs                 3.6G     0  3.6G   0% /dev/shm

/dev/xvdb1            477M  148M  300M  34% /boot

/dev/xvde1             59G  7.4G   49G  14% /u01

/dev/mapper/dataVolGroup-lvol0

25G  3.8G   20G  17% /u02

/dev/mapper/fraVolGroup-lvol0

6.8G  2.1G  4.4G  32% /u03

/dev/mapper/redoVolGroup-lvol0

26G  3.1G   22G  13% /u04

/dev/xvdg1            2.0G  3.1M  1.9G   1% /u05

[oracle@proddb ~]$

Posted in Uncategorized | 2 Comments »

“Oracle Certified Master 11g Study Guide” – www.ocmguide.com – Free Ebook

Posted by Kamran Agayev A. on October 14th, 2016

ocm-book

 

This is it! My second book is out!

There are bunch of certified associate and professional Oracle DBAs around the world who plan to take an OCM exam, but without having a study guide it seems to be hard to prepare for that amount of different topics and make all kind of practical tests. And most of them, just give up.

And having successfully passed the exam, I decided to help DBAs by sharing my preparation method with you. It has been 2 years since I have started writing the book about how to prepare for OCM 11g Exam. This guide covers all OCM topics with documentation references and MOS notes, with step by step installation and configuration instructions, practical and real-life examples.

This book consists of 200+ practical scenarios, 260 screenshots and 300+ assigned tasks with specified time limit that makes you 100% ready for the exam. Every single topic is explained in a practical examples using both graphic user and command line interfaces.

If you are a certified DBA and looking forward to become an OCM, then this study guide is for You!

If you are a DBA and want to have a practical experience in different topics such as Oracle Enterprise Manager, Backup and Recovery, ASM, RAC, Grid Infrastructure, Performance Tuning, Data Guard and etc, then this book is for You!

To get a free copy of some chapters of the book, visit www.ocmguide.com

 

Posted in Uncategorized | 3 Comments »

RMAN “Catalog start with” returns “no files found to be unknown to the database”

Posted by Kamran Agayev A. on January 2nd, 2016

Interesting issue happened today (running 11.2.0.3 DB on Linux) while using “CATALOG START WITH” command in RMAN. Although I was having all backups under /orabackup folder, RMAN didn’t find anything, reporting “no files found to be unknown to the database” message:

RMAN> catalog start with ‘/orabackup’;

searching for all files that match the pattern /orabackup
no files found to be unknown to the database

RMAN>

 

I changed the permission to “chmod 777” and checked the owner, used different patterns like “/orabackup/*”, “/orabackup/” and  “/orabackup/*.bkp” but failed. Then I decided to create a folder under the /orabackup folder, moved all files in it and tried “CATALOG START WITH” command again, and succeeded.

RMAN> catalog start with ‘/orabackup’;

searching for all files that match the pattern /orabackup
no files found to be unknown to the database

RMAN>

[oracle@db1 ~]$ cd /orabackup/
[oracle@db1 orabackup]$ mkdir test
[oracle@db1 orabackup]$ mv *.bkp test/
RMAN> catalog start with ‘/orabackup/test’;

searching for all files that match the pattern /orabackup/test

List of Files Unknown to the Database
=====================================
File Name: /orabackup/test/mydb_database_btqqatr2_1_1.bkp
File Name: /orabackup/test/mydb_database_bsqqakoq_1_1.bkp
File Name: /orabackup/test/mydb_arch_buqqatrp_1_1.bkp
File Name: /orabackup/test/mydb_contolfile_bvqqatsc_1_1.bkp
File Name: /orabackup/test/mydb_spfile_c0qqatsk_1_1.bkp

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files…
cataloging done

List of Cataloged Files
=======================
File Name: /orabackup/test/mydb_database_btqqatr2_1_1.bkp
File Name: /orabackup/test/mydb_database_bsqqakoq_1_1.bkp
File Name: /orabackup/test/mydb_arch_buqqatrp_1_1.bkp
File Name: /orabackup/test/mydb_contolfile_bvqqatsc_1_1.bkp
File Name: /orabackup/test/mydb_spfile_c0qqatsk_1_1.bkp

RMAN>

 

 

Posted in Administration, RMAN Backup and Recovery | 11 Comments »

Oracle 11g Clusterware failure scenarios with practical demonstrations – Webinar

Posted by Kamran Agayev A. on October 5th, 2015

Sharing my Webinar on “Oracle 11g Clusterware failure scenarios with practical demonstrations” which I did for RACSIG few days ago

 

racsig

 

 

 

 

 

 

 

 

 

Posted in Uncategorized | 2 Comments »

Interim patch apply best practices in Oracle

Posted by Kamran Agayev A. on October 2nd, 2015

Yestarday, after successfully applying an interim patch to the 3 node clusterware environment, I decided to share my experience with you. In this blog post, you can find some best practicec that I think must be followed before and during patch insallation to bring the downtime and failure risks to the minimum.

First of all, make sure you read the following metalink notes:

Master Note For OPatch (Doc ID 293369.1)
FAQ: OPatch/Patch Questions/Issues for Oracle Clusterware (Grid Infrastructure or CRS) and RAC Environments (Doc ID 1339140.1) 

 

Before applying any interim patches or upgrading the database or the clusterware, make sure you have answers to the following questions:

– Have you tested the patch installation? 

– Have you tested rollback of the patch? 

– What you will do if you can’t rollback the patch with default rollback mechanism? 

– What you will do if you fail to open the database after the patch installation? 

Do you have a backup? Have you tested it? What if you don’t have enough time to restore? 

 

Here is the list of what I would prefer to do before applying any interim patches to the mission critical environment:

– Backup the home folder that is going to be patched

tar -cvf grid_home_before_patch.tar /home/oracle/app/11.2.0

If the patch installation goes wrong and you can’t rollback the patch using default method, restore the backup of the installation home folder and bring the database (clusterware) up.

– Make sure you have a full backup of the database 

Most probably you will not go with the restoration, but you never know what might happen)

– Make sure your backup is recoverable

Yes. This might be a discussion topic, but I strongly believe (as an author of the RMAN Backup and Recovery book :) ) in “If you don’t test your backup, you don’t have a backup” philosophy. Restore it and make sure a) The backup is restorable/recoverable b) Your restore/recover scripts works fine. In my experience, I had a situation where the restore of the backup failed while restoring it for the developers for the testing purpose because of non tested recovery scripts. I heard a situation (a couple of years ago when I attended a wonderful OOW session) where one of the attendees complained how they failed to restore a backup when the production environment failed and this downtime (for days) costed them for a couple of million dollars.

– Make sure you have a Standby database.

Why? Imagine you took 30 minutes downtime to apply the patch and for any reason you were not able to do it and can’t rollback because you are in the middle of the patch apply procedure and trying to fix the issue. Or you can’t rollback the patch for any reason. You stuck! And you don’t have time to solve it. And you are forced to open the database right away. And you can’t do it as well. In this critical case, you can forward the applications to the Standby database. Build up a standby database, make sure archived log files of the production database are shipping to the standby server. You can also perform a failover to test your standby database and build it up again.

– Test the patch apply procedure on the test environment with the “same binaries”. 

Clone the database and clusterware soft to the test machine (or install the same release and apply the same patches as in the production environment) and apply the patch. Get the errors in the test environment before you get them in the production.

– Make sure there’s no any session runinng in the background related with the binaries of the home that is being patched. 

Yesterday, when I was trying to apply an interim patch to the 3 node clusterware (11.2.4) I came up with the following error:

Backing up files…

UtilSession failed: Map failed
java.lang.OutOfMemoryError: Map failed
Log file location: /home/oracle/11.2.0/grid_1124/cfgtoollogs/opatch/opatch2015-10-01_17-40-56PM_1.log

OPatch failed with error code 73

The reason was not the memory at all, we were having a lot of free memory at that time. There were some binary files in use in the background despite the fact that the whole clusterware stack was down. Anyway, I killed all processes and the installation proceeded.

The following metalink note also might be useful – OPatch Apply/Rollback Errors: ‘Prerequisite check “CheckActiveFilesAndExecutables” failed’ (Doc ID 747049.1)

– Download the latest OPatch

Check the following metalink note to download the latest OPatch. How To Download And Install The Latest OPatch Version (Doc ID 274526.1)

Download and extract it under the home folder that is going to be patched. Add the $GRID_HOME/OPatch or $ORACLE_HOME/OPatch to the PATH environment variable. Make sure which opatch command returns you a result

– Make sure you have enough free space in the mount point where the home folder reside.

A few years ago when I was trying to install a patch to the production environment, I decided to try it on the test environment (10 minutes before patching a production database). I ended up with the “There is no free space to proceed the installation” error. Home folder was full, and OPatch was taking backup of the binaries and library files that are being patched.  Check the following metalink note for more informaton: Opatch Fails Updating Archives with ” No space left on device ” Error. (Doc ID 1629444.1)

 – Bring the instance down before patching

If you have a Grid Infrastructure installed, you have a RAC database and you plan to apply the patch node by node without a downtime, bring the instance of the node you’re patching using the following command:

srvctl stop instance -d RACDB -i RACDB1

Why? Because if you start installing the patch and run the rootcrs.pl -unlock command which is the first step that brings the clusterware stack down, the database will be closed with ABORT mode and non of the sessions will be failed over.

– Try to rollback the patch installation at test environment after installing it

Why? Feel how you should rollback (and see if you get any error) the specific patch if you failed the installation and can’t proceed, or you installed successfully, but it caused another bug or problem. Check the following metalink note to learn how to rollback the patch and run opatch lsinventory to make sure it is rollbacked.  How to Rollback a Failed Interim Patch Installation (Doc ID 312767.1)

Sometimes, rollback also might fail :) In this case, the best option is to restore the whole home folder from the backup, but it is not mentioned in ths metalink note OPatch Fails to Rollback Patch Due to Relink Errors (Doc ID 1534583.1)

– Debug the OPatch if it is stuck 

You can use OPATCH_DEBUG=TRUE parameter to debug the OPatch. If it doesn’t generate enough information, use truss (or strace in Liunx) to debug OPatch. Check the following metalink note to learn how to use truss with OPatch. How To Use Truss With OPatch? (Doc ID 470225.1) 

Opatch might also hang due to corrupted jar and java executables. Check this metalink note – opatch napply Hanging (Doc ID 1055397.1)

 

This is all I have :) Please let me know if this document helped you and share your experience with me :) Have a successfull patching days ahead! :)

Posted in Administration, RAC issues | 8 Comments »

ORA-02298: cannot enable name – parent keys not found

Posted by Kamran Agayev A. on August 28th, 2015

An interesting case we just got while migrating a data to the different database where we got the following error:

 

ORA-39083: Object type REF_CONSTRAINT failed to create with error:

ORA-02298: cannot enable name – parent keys not found

 

The issue was between 2 tables. It couldn’t enable the foreign key constraint by throwing error that “parent key not found”. The migration was from production database where the foreign key constraint was enabled and it is not possible to have a data in the child table without parent rows.

After quering both tables I found the missing rows, deleted them from the child table and run the query to enable the constraint again – and it succeeded. The reason was because the first table that was exported was the primary table. Then a few rows were added to both primary and child tables (in the production database). Then child table was exported with having those rows which are missing from the primary table (because it was exported firstly)

The solution was to use FLASHBACK_SCN or FLASHBACK_TIME parameter while exporing the data and get all rows that are consistent as of this SCN or TIME

Posted in Administration | No Comments »

ORA-00304: requested INSTANCE_NUMBER is busy

Posted by Kamran Agayev A. on August 27th, 2015

There are a lot of explanation and different solutions for the error “ORA-00304: requested INSTANCE_NUMBER is busy”. But today, in my case while I was tyring to shutdown one of the cluster nodes, it hanged. There were no more information related with the hang in the log and trace files, so I went with shut abort and startup and got the following message:

SQL> startup

ORA-00304: requested INSTANCE_NUMBER is busy

SQL>

The second node of the RAC database was up and running. And the instance_number was set to 2. After a little investigation, I found out that there was one process related with the database running on OS (even the database was closed) I killed that session and started the first node and it opened successfully

Posted in Administration, RAC issues | 3 Comments »