[Oracle Cloud] : START/STOP Oracle DBAAS Cloud Database Instance using dbaascli

STOP/START/RESTART Oracle DBAAS Cloud Database Service Database Instance using dbaascli command line utility.

In this blog post, I will demonstrate the dbaascli database sub-commands to play with oracle database instance that how we stop, start, bounce(here bounce means, your oracle database cloud instance will me stopped with immediate option and then it starts back normally in read-write mode).

You can also change the password of SYS user from the dbaascli command line:


Login to the Oracle cloud compute node using oracle user:

Invoke dbaascli command line utility:

[oracle@RAJDB121 ~]$ dbaascli
DBAAS CLI version 1.0.0
DBAAS>

You can check options of database sub-command of dbaascli command as following:

DBAAS>database -h
Executing command database -h
Valid Subcommands:
   bounce
   stop
   changepassword
   status
   start
DBAAS>

You can check the status of current oracle database cloud service instance:

DBAAS>database status
Executing command database status
Database Status:
Database is open
Database name: RCDB121

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0      Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production


DBAAS>

You can restart/bounce the oracle database cloud service instance using the database bounce command of dbaascli command line utility as below:

bounce option of database sub-command is actually shuts down the oracle database cloud service database instance with immediate option and starts it up normally in read-write mode.

DBAAS>database bounce
Executing command database bounce
Executing command database stop
Database stopped in immediate
Executing command database start
Database started
DBAAS>

You can shutdown the oracle cloud database service database instance using following command, it shuts down the oracle cloud database instance with immediate option.

DBAAS>database stop
Executing command database stop
Database stopped in immediate
DBAAS>

You can start the oracle database cloud service database instance using following command.

DBAAS>database start
Executing command database start
Database started
DBAAS>

Finally, after starting the oracle cloud database service database instance, you can check its status.

DBAAS>database status
Executing command database status
Database Status:
Database is open
Database name: RCDB121

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0      Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production


DBAAS>

ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

--- Though it is a fuzzy thing and can be done easily in minutes, yet thought to record it here for a reference. I was to simulate a quick recovery scenario and aborted my database instance just to change my database mode to ARCHIVELOG.


DB Details:
------------

C:\app\oracle\product\12.1.0\dbhome_3\BIN>sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 31 00:17:52 2018

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name, open_mode, database_role, cdb from v$database;

NAME      OPEN_MODE            DATABASE_ROLE    CDB
--------- -------------------- ---------------- ---
PROD1     READ WRITE           PRIMARY          YES

Archiving is disabled:
------------------------

SQL> archive log list

Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     209
Current log sequence           211

Shutdown abort:
-----------------

SQL> shut abort
ORACLE instance shut down.

<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>

Wed Jan 31 00:18:22 2018
Shutting down instance (abort)
License high water mark = 8
Wed Jan 31 00:18:22 2018
USER (ospid: 11960): terminating the instance
Wed Jan 31 00:18:31 2018
Instance terminated by USER, pid = 11960
Wed Jan 31 00:18:31 2018
Instance shutdown complete

<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>

Now started  the database in MOUNT state to change the database to archivelog mode.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1006632960 bytes
Fixed Size                  3053104 bytes
Variable Size             905972176 bytes
Database Buffers           92274688 bytes
Redo Buffers                5332992 bytes
Database mounted.




SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

Above we hit the error saying that database instance requires recovery.

Reason : As we did shutdown abort of our database instance or if we do STARTUP MOUNT FORCE then database instance gets crashed which requires media recovery to bring the database changes to a consistent state.

Resolution : Well, to resolve it, we would have to open our database to initiate instance recovery and shut it down normally or with immediate option.

SQL> alter database open;
Database altered.




When you open database, crash recovery starts as you can see in the alert log of the database instance as following:

<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Wed Jan 31 00:19:16 2018
alter database archivelog
ORA-265 signalled during: alter database archivelog...
alter database open
Wed Jan 31 00:21:49 2018
Ping without log force is disabled
.
Wed Jan 31 00:21:49 2018
Beginning crash recovery of 1 threads
 parallel recovery started with 3 processes
Wed Jan 31 00:21:49 2018
Started redo scan
Wed Jan 31 00:21:49 2018
Completed redo scan
 read 927 KB redo, 265 data blocks need recovery
Wed Jan 31 00:21:49 2018
Started redo application at
 Thread 1: logseq 211, block 66010
Wed Jan 31 00:21:50 2018
Recovery of Online Redo Log: Thread 1 Group 1 Seq 211 Reading mem 0
  Mem# 0: C:\APP\ORACLE\ORADATA\PROD1\REDO01.LOG
Wed Jan 31 00:21:50 2018
Completed redo application of 0.77MB
Wed Jan 31 00:21:50 2018
Completed crash recovery at
 Thread 1: logseq 211, block 67865, scn 6044843
 265 data blocks read, 265 data blocks written, 927 redo k-bytes read
Starting background process TMON
Wed Jan 31 00:21:50 2018
TMON started with pid=28, OS id=2456
Wed Jan 31 00:21:50 2018
Thread 1 advanced to log sequence 212 (thread open)
Thread 1 opened at log sequence 212
  Current log# 2 seq# 212 mem# 0: C:\APP\ORACLE\ORADATA\PROD1\REDO02.LOG
Successful open of redo thread 1
Wed Jan 31 00:21:50 2018
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Jan 31 00:21:50 2018
SMON: enabling cache recovery
Wed Jan 31 00:21:50 2018
[564] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:137835812 end:137835984 diff:172 ms (0.2 seconds)
Verifying minimum file header compatibility (11g) for tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
Wed Jan 31 00:21:50 2018
SMON: enabling tx recovery
Starting background process SMCO

You would want to save the current state of PDBs so that it starts automatically in read-write mode at next startup of CDB [click here]

Now, do a clean shutdown of your database instance:

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Now, start the database instance in MOUNT state:

SQL> startup mount
ORACLE instance started.

Total System Global Area  845348864 bytes
Fixed Size                  1339796 bytes
Variable Size             499125868 bytes
Database Buffers          339738624 bytes
Redo Buffers                5144576 bytes
Database mounted.




Change the database mode to ARCHIVELOG:

SQL> alter database archivelog;
Database altered.




Hope it would help someone...!!

[Oracle Cloud] - Creating an Oracle Database Deployment

Creating an Oracle Database Deployment in Oracle Public Cloud:

In this blog post, I will illustrate the steps to deploy an Oracle Database Deployment in Oracle Public Cloud. Assuming you have access to Oracle identity account and have the DBAAS Database Administrator role assigned to your service account to start creating database deployments in Oracle Public Cloud.

After login to the My Services Console, select the “Oracle Database Cloud Service” from the hamburger option just left to the services pages. 

Initially when you don’t have any Database deployments created then you would be shown the page like one depicted below. Here there is no Oracle Database Deployment is created and we will start creating a Oracle Database Deployment now.

Step: 1 – Click on the “Create Service” option in the services region of the “Oracle Database Cloud Service” page

Step : 2 – Once you click on the Create Service option, you will be displayed the following screen where you specify the Service Name, Service Description about the service being created, select Region in which you would like your Oracle Database Deployment to be hosted in.

Select appropriate Software Release and Software Edition and Database Type(there are multiple options to select from Database Type i.e. Single Instance, Database Clustering with RAC, Hybrid DR….)

In this demo article, I am creating a Single Instance database deployment, Oracle Database 12c Release 2 Enterprise Edition Database Deployment.

Choose the option as per your need and click on Next.

Step : 3 – Now you would be moved to the following “Service Details” page to provide your Database Configurations, Backup and Recovery Configuration(In this case, I choose the value “none” for Backup Destination as I don’t want my Oracle Database Deployment to be backed up automatically, There are other options as well to choose from whether you want to backup your Database Deployment to both Cloud Storage and Local Storage or Cloud Storage only).

In the SSH Public Key field, click on the Edit button, Here you would be required to assign one Public key for the database deployment . You will be given three options for Public key here as explained here under.

1 - Choose "Key File Name", if you have already generated the key pairs on your client machine then browse to select the Public key to upload here on Compute Node.

2 - Select the "Key Value" option. Open the public key file in one of the text editor on your client machine and copy the complete public key contents and paste in the right area of this option without any line breaks.

3 -  Choose option "Create a New Key" if you have not already generated the key pairs beforehand. Oracle Database Deployment Create Service wizard provides you flexibility to generate the key pairs here itself. Click on "Enter" then this GUI will generate public/private key pairs for you automatically and give you the option to download the key pairs on your client machine for future use.

Please remember that private key in the downloaded key pair will be used when you connect to the Compute Node using SSH/Putty.

Once you are one with the key pairs work, you can click on the "Advanced Settings" if you would like to change the Listener Port to a none-default port, you can also change Timezone, Database Character Set, Database Deployment National Character Set.

You can check the "Enable Oracle Golden Gate" option if you wish to enable Oracle Golden Gate replication settings, etc.

Click Next.

Step 4 - Now, you will be displayed the summary page of options you have selected for your Oracle database deployment to review once before actually creating the Oracle database deployment, This is the last chance for you to review the configurations selected for your Oracle Database Deployment and go back and change a setting for the database deployment if required, before clicking on "Create Service" tab.

Once you clicked on "Create Service" tab, Oracle database deployment service creation starts now. You would see that status of database deployment is in "Creating service" status now.

You can go and grab a cup of coffee while the service is being created, oh yeah, Oracle doesn't prohibit you from getting one :-)

When Oracle Database Deployment Service Creation starts, then a New Virtual Machine is created for your and one Public IP is assigned to the VM/Compute Node. Oracle Linux is then automatically installed to the Compute Node, Oracle Software gets installed, and Oracle Database instance gets created for you.

In the end, when service creation completes then Oracle database deployment service status changes to "Ready", you can connect to the Compute Node where database deployment is running using the Public IP assigned to your Host name and starts playing with Oracle Database Cloud Service.......

Hope it would help in understanding how we create a Oracle Database Deployment in Oracle Public Cloud.....!!

[Book Review] :: Oracle Database Upgrade and Migration Methods - Including Oracle 12c Release 2

Y.V. RaviKumar, K.M.K. Kumar, D. Mike 

Thanks to YV RaviKumar and other authors for writing a wonderful book. I received the hard copy of this book today from here [Amazon.in] and very excited to finish with all its amazing contents pushed in there.

Upgrades and Migrations methods are explained wonderfully and all aspects are illustrated more than enough. I would recommend my all friends to have it.


A must have book for every Oracle DBA

▶ Explains each database upgrade/method in detail with its applicable environment
▶ Provides steps for upgrading Oracle database using an existing database backup
▶ Includes additional upgrade steps required for databases in high availability, e-Business, and multi-tenant environments
▶ Presents upgrade best practices to ensure success without any unexpected issues
▶ Covers Oracle Database 12c patching concepts Learn all of the available upgrade and migration methods in detail to move to Oracle Database version 12c. You will become familiar with database upgrade best practices to complete the upgrade in an effective manner and understand the Oracle Database 12c patching process. So it’s time to upgrade Oracle Database to version 12c and you need to choose the appropriate method while considering issues such as downtime.

This book explains all of the available upgrade and migration methods so you can choose the one that suits your environment. You will be aware of the practical issues and proactive measures to take to upgrade successfully and reduce unexpected issues.  With every release of Oracle Database there are new features and fixes to bugs identified in previous versions. As each release becomes obsolete, existing databases need to be upgraded. Oracle Database Upgrade and Migration Methods explains each method along with its strategy, requirements, steps, and known issues that have been seen so far. This book also compares the methods to help you choose the proper method according to your constraints.