All about Database Administration, Tips & Tricks

The Insights of a Geek DBA !!!

All information is offered in good faith and in the hope that it may be of use for educational purpose and for Database community purpose, but is not guaranteed to be correct, up to date or suitable for any particular purpose. db.geeksinsight.com accepts no liability in respect of this information or its use. This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content and if notify any such I am happy to remove. Product and company names mentioned in this website may be the trademarks of their respective owners and published here for informational purpose only. This is my personal blog. The views expressed on these pages are mine and learnt from other blogs and bloggers and to enhance and support the DBA community and this web blog does not represent the thoughts, intentions, plans or strategies of my current employer nor the Oracle and its affiliates or any other companies. And this website does not offer or take profit for providing these content and this is purely non-profit and for educational purpose only. If you see any issues with Content and copy write issues, I am happy to remove if you notify me. Contact Geek DBA Team, via geeksinsights@gmail.com

18c Database : Readonly Home and Password File Location Changes

In this post we cover two aspects of new features in 18c.

Readonly Oracle Home:

A read-only Oracle home separates the software from the database configuration information and log files. This separation enables you to easily share the software across different deployments. A read-only Oracle home also simplifies version control and standardization.

To enable the readonly oracle home, Run the roohctl Script

1.Go to the bin directory
$ cd /u01/app/oracle/product/18.0.0/dbhome_1/bin
2.Run the roohctl script to enable read-only Oracle home.
$ ./roohctl -enable

Password File Location:

Starting 18c, Oracle automatically searches the $ORACLE_BASE/dbs first for password files instead of $ORACLE_HOME/dbs, this facilitate the readonly Oracle Home feature to have the home in readonly but other files are out side of Home for read/write operations.

In the Oracle Cloud 18c, I tested for readonly oracle home, initially it failed with below error

[oracle@db18c-se-si bin]$ ./roohctl -enable
Enabling Read-Only Oracle home.
Cannot enable Read-Only Oracle home in a configured Oracle home.
The Oracle Home is configured with databases 'orcl'.
The Oracle Home is configured with listeners 'LISTENER'.

So I decided to comment out /etc/oratab and remove listener.ora file and give a go, and that worked.

[oracle@db18c-se-si bin]$ ./roohctl -enable
Enabling Read-Only Oracle home.
Update orabasetab file to enable Read-Only Oracle home.
Orabasetab file has been updated successfully.
Create bootstrap directories for Read-Only Oracle home.
Bootstrap directories have been created successfully.
Bootstrap files have been processed successfully.
Read-Only Oracle home has been enabled successfully.
Check the log file /u01/app/oracle/cfgtoollogs/roohctl/roohctl-180317AM065000.log.
[oracle@db18c-se-si bin]$

Disabled back

[oracle@db18c-se-si bin]$ ./roohctl -disable
Disabling Read-Only Oracle home.
Update orabasetab file to disable Read-Only Oracle home.
Orabasetab file has been updated successfully.
Read-Only Oracle home has been disabled successfully.
Check the log file /u01/app/oracle/cfgtoollogs/roohctl/roohctl-180317AM065204.log.
[oracle@db18c-se-si bin]$

In fact, this feature is available in 12c R2 as well, however it does not given the error that Database/Listener are configured

[oracle@db12c-se-si bin]$ ./roohctl -enable
Enabling Read-Only Oracle home.
Update orabasetab file to enable Read-Only Oracle home.
Orabasetab file has been updated successfully.
Create bootstrap directories for Read-Only Oracle home.
Bootstrap directories have been created successfully.
Read-Only Oracle home has been enabled successfully.
Check the log file /u01/app/oracle/cfgtoollogs/roohctl/roohctl-180317AM065422.log.
[oracle@db12c-se-si bin]$

What it mean is we cannot have the DB or Listeners configured with that home before we keep the Home in readonly.

-Thanks

Suresh

18c Database : Cancel SQL Instead of Killing Session

From 12cR2 and 18c, you can cancel the session instead of killing it which is long running and consuming time and resources.
The following is the syntax for cancelling a SQL statement:

ALTER SYSTEM CANCEL SQL 'SID, SERIAL, @INST_ID, SQL_ID';

If @INST_ID is not specified, the instance ID of the current session is used.
If SQL_ID is not specified, the currently running SQL statement in the specified session is terminated.

-Suresh

18c Database : Logging Enhancements for nologging blocks for standby

Hi,

We all aware of, when a table has attribute set with nologging option , those redo will be missed (although redo information there, the data will be missed) and standby will not apply those changes, and this will problematic when you do a switchover or failover unless you use force logging mode.

To address this in 18c, we have three different logging modes to ensure nologged blocks are also captured and send to standby depends on requirement of performance and availability perspective.

Of course, we can use FORCE LOGGING at primary database to ensure all modifications are captured, however this has an performance impact where in which you are doing a bulk load and this has to be captured in redo logs may slow down your load process.

To overcome this, we have two new Logging modes available at primary database,

ALTER DATABASE SET STANDBY NOLOGGING FOR DATA AVAILABILITY;

This mode causes the load operation to send the loaded data to each standby through its own connection to the standby. Means the data availability is critical so it has to delay the commit on primary side to ensure all non logged blocks also shipped and applied by MRP

ALTER DATABASE SET STANDBY NOLOGGING FOR LOAD PERFORMANCE;

This mode is similar to the above one except that the loading process can stop sending the data to the standbys if the network cannot keep up with the speed at which data is being loaded to the primary. Standby may miss the data but eventually it fetches the non logged blocks during recover process by MRP process.

Suresh

18c Database : ASM Database Mirror for PDB Cloning

Starting 18c, we can create a clone of PDB using ASM level mirror copy the diskgroup with a point in time recovery of the PDB database.

This will be really helpful when you want to clone the PDB in the same instance and a point-in-time database clone is a full copy of a specific database, not the underlying storage system. Only data files are copied with in the ASM instance. Other files are created, or referenced as required to create the clone.

To use this feature, your compatible.asm and compatible.rdbms must be set 18.0 or higher and the disk groups must be flex/extended disk groups only and also the filegroups are part of only diskgroup.

It works in phases

Prepare Mirror Copy - This process involves creating the cloned files and linking them with the parent files. However, the data is not copied at this time; the copying is done during remirroring. Remirroring occurs during the prepare phase of rebalance, which is initiated as part of this step
Split the Mirror - Create the PDB using mirror copy
Rebalance - Copying of ASM extents to Mirror

The progress of the operation and status can be checked in V$ASM_DBCLONE_INFO under DBCLONE_STATUS (PREPARED, SPLIT COMPLETED etc.)

Mirror Copy

ALTER SESSION SET CONTAINER = pdb1;
Session altered.

SHOW CON_NAME
CON_NAME
------------------------------
PDB1

ALTER PLUGGABLE DATABASE PREPARE MIRROR COPY pdb1_mirror;
Pluggable database altered.

Check in v$asm_dbclone_info view

SELECT * FROM V$ASM_DBCLONE_INFO;
GROUP_NUMBER DBCLONE_NAME  MIRRORCOPY_NAME  DBCLONE_STATUS   PARENT_DBNAME PARENT_FILEGROUP_NAME     CON_ID
------------ ------------- ---------------- ---------------- ------------- ---------------------- ---------
           1 DB_UNKNOWN    PDB1_MIRROR      PREPARED         ORCL_PDB1     ORCL_PDB1

Split the Mirror copy and create clone

SHOW CON_NAME
CON_NAME
------------------------------
CDB$ROOT

CREATE PLUGGABLE DATABASE newpdb1 FROM pdb1 USING MIRROR COPY pdb1_mirror;
Pluggable database created.

ALTER SESSION SET CONTAINER = newpdb1;
session altered.

SHOW CON_NAME
CON_NAME
------------------------------
NEWPDB1

Check the V$asm_dbclone_info to check the status of the SPLIT
SELECT * FROM V$ASM_DBCLONE_INFO;
GROUP_NUMBER DBCLONE_NAME  MIRRORCOPY_NAME  DBCLONE_STATUS   PARENT_DBNAME PARENT_FILEGROUP_NAME     CON_ID
------------ ------------- ---------------- ---------------- ------------- ---------------------- ---------
           1 ORCL_NEWPDB1  PDB1_MIRROR      SPLIT COMPLETED  ORCL_PDB1     ORCL_PDB1                      0

Drop the Mirror Copy

ALTER SESSION SET CONTAINER = pdb1;
Session altered.

ALTER PLUGGABLE DATABASE DROP MIRROR COPY pdb1_mirror;
Pluggable database altered.

Rebalance the Data group

ALTER DISKGROUP data REBALANCE WAIT;

Now the diskgroup data holds the datafiles for PDB1 and NEWPDB1 also. This is will really help not to touch anything on PDB level or copying datafiles for pdb clone level if you have datafiles under ASM.

-Thanks

Suresh

12.2.0.1 : Multi Instance Redo Apply

As we all aware , that we can have only one instance of Standby running with MRP (redo apply) running in RAC environments.

Starting 12.2.0.1, we can have multiple instances on standby side too with following command.

Generally, to start the MRP process we use,

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Now this has been enhanced in 12.2.0.1 to run MRP on all stand by instances.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION INSTANCES ALL;

Now this has been enhanced in 12.2.0.1 to run MRP on specific number of instances, Please note you cannot choose which instances to run.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION INSTANCES 2;

And you can check in v$recovery_progress view in each instances as MRP is running on them for redo sync

-Thanks

Suresh

Oracle Cloud : Using PSM CLI to Manage Databases

In previous post, you have seen the PSM utility installation and using PSM utility, In this post you will learn how to use CLI to effectively manage the databases using commands.

#Create Database using Json file

C:\Users\gandhi\Desktop>psm dbcs create-service -c 18cdatabase.json
Job ID : 1986734

# Check the Service aka database config

C:\Users\gandhi\Desktop>psm dbcs service -s db18c-se-si
Service:                    db18c-se-si
Status:                     In Progress
Version:                    18.0.0.0
Edition:                    SE
Shape:                      oc3
Connect String:             N/A
Backup Destination:         NONE
Cloud Storage Container:    N/A
PDB Name:                   pdb01
Created On:                 2018-03-15T03:37:45.884+0000

# Check the Database Health

C:\Users\gandhi\Desktop>psm dbcs check-health -s db18c-se-si -of json
{
"summary":{
"status":"UP",
"status_time":"2018-03-15T03:56:47.220+00:00",
"status_message":"Running"
},
"identity_domain":"idcs-093b0defb99e433c9fb6aae82cfcb8d1",
"service_components":[
{
"component":"DB",
"vms":[
{
"vm":"db18c-se-si",
"servers":[]
}
],
"health_data":[
{
"unit":"%",
"name":"user_tablespace_usage",
"value":"2",
"type":"int",
"displayName":"Max Percentage for a User Tablespace Used"
}
]
}
],
"service_name":"db18c-se-si"
}

#Check the backups of Database,

C:\Users\gandhi\Desktop>psm dbcs view-backups -s db18c-se-si
Error: Internal Server Error. Backup / Recovery operations are not supported on this service instance.

# Stop Database , errored out with some job running, so looked at operation status as below with job id

C:\Users\gandhi\Desktop>psm dbcs stop -s db18c-se-si
Error:
{
"reason":"Resource Busy. Service idcs-093b0defb99e433c9fb6aae82cfcb8d1/db18c-se-si has active jobs. active job: 1986734 when attempting to process request: idcs-093b0defb99e433c9fb6aae82cfcb8d1/db18c-se-si: serviceType = dbaas, namespace = dbaas, operation = stop-dbaas-service"
}

# Check Operations that currently running with Job Number

C:\Users\gandhi\Desktop>psm dbcs operation-status -j 1986734
Service:           db18c-se-si
Operation Type:    CREATE_SERVICE
Status:            RUNNING
Start Time:        2018-03-15T03:37:45.985+0000
End Time:          N/A

C:\Users\gandhi\Desktop>psm dbcs operation-status -j 1989704
Service:           db12c-se-si
Operation Type:    CREATE_SERVICE
Status:            RUNNING
Start Time:        2018-03-15T04:04:10.295+0000
End Time:          N/A

# Check Infrastructure activities on the database host

C:\Users\gandhi\Desktop>psm dbcs activities -s db18c-se-si
Operation Type Status Start Time End Time
CREATE_SERVICE RUNNING 2018-03-15T03:37:45.985+0000 N/A
DELETE_SERVICE SUCCEED 2018-03-14T16:00:48.245+0000 2018-03-14T16:06:35.774+0000
CREATE_SERVICE SUCCEED 2018-03-14T14:54:19.049+0000 2018-03-14T15:24:37.529+0000

# Delete Database Instance and Service

C:\Users\gandhi\Desktop>psm dbcs delete-service -s db18c-se-si
Job ID: 1989723

# Check Any patches need for databases and available

C:\Users\gandhi\Desktop>psm dbcs available-patches -s db12c-se-si
Patch ID Severity Version Release Date Requires Restart?
26710464-SE Normal 12.2.0.1.171017 2017-10-17T01:40:00.000+0000 True

# Applying Patch, Errored out I need to check why

Ex: psm dbcs patch -s ExampleInstance -p es-20831110-EE -g NODE-SAFE -h REQUIRE_STATUS_HA -t 100 -n Applying JDK80 patch

C:\Users\gandhi\Desktop>psm dbcs patch -s db12c-se-si -p 26710464-SE -of json
Error: Unsupported Media Type

# Pre check Patch

C:\Users\gandhi\Desktop>psm dbcs precheck-patch -s db12c-se-si -p 26710464-SE
Error: Unsupported Media Type

# Take snapshot of Database

C:\Users\gandhi\Desktop>psm dbcs create-snapshot -s db12c-se-si -n snapshottestest12c
Job ID : 1987024

# Scale up your database host capacity

C:\Users\gandhi\Desktop> psm dbcs scale-up -s db12c-ee -p oc4 -a 10

Specifies scaling up the shape of the service instance.

Valid values for shape-name are as follows:

•oc4 — 2 OCPUs with 15 GB RAM
•oc5 — 4 OCPUs with 30 GB RAM
•oc6 — 8 OCPUs with 60 GB RAM
•oc7 — 16 OCPUs with 120 GB RAM
•oc1m — 1 OCPU with 15 GB RAM
•oc2m — 2 OCPUs with 30 GB RAM
•oc3m — 4 OCPUs with 60 GB RAM
•oc4m — 8 OCPUs with 120 GB RAM
•oc5m — 16 OCPUs with 240 GB RAM

# Scale down your database host capacity

C:\Users\gandhi\Desktop> psm dbcs scale-down -s db12c-ee -p oc4 -a oc5-a 10

-Thanks

Suresh

Oracle Cloud : Installing PSM (PaaS Service Manager) CLI utility for Oracle Cloud

Hello All

In this post, we see how to install the PSM (Paas Service Manager) CLI utility to manage the cloud operations for Oracle. It has vide range of commands to manage different resources and can be useful to automate the Oracle Cloud Infrastructure operations.

The following will be covered in this post.

Install Python 3.3 or later and curl
Install PSM Utility (via Cloud console or using CURL)
Setup PSM
Execute first job & check the status
Look at templates

Install Python 3.3 o later and curl

Download Python Installer from Install Python 3.3 or later from https://www.python.org/downloads/

Download Curl executable from https://curl.haxx.se/download.html (check for SSL enabled and download)

Download PSM Utility via Cloud Console

Log into your Oracle Cloud Account --> MyServices --> Help Icon > Download Centre --> Click on Download link to download the PSM utility

Download PSM Utility via CURL

curl -X GET -u myuser321:mypassword321 -H X-ID-TENANT-NAME:MyIdentityDomain54321 https://psm.us.oraclecloud.com/paas/core/api/v1.1/cli/MyIdentityDomain54321/client -o psmcli.zip

Install PSMUtility with pip3

Setup PSM Utility

PSM-4

Create Sample Template for 18c Database and save as 18cdatabase.json

{
"description": "18c instance",
"edition": "SE",
"level": "PAAS",
"serviceName": "db18c-se-si",
"shape": "oc3",
"subscriptionType": "HOURLY",
"version": "18.0.0.0",
"vmPublicKeyText": "ssh-rsa AzYcAqN ....",
"parameters": [
{
"type": "db",
"usableStorage": "15",
"adminPassword": "Welcome_1",
"sid": "orcl",
"pdbName": "pdb01",
"failoverDatabase": "no",
"backupDestination": "NONE"
}
]
}

Run the PSM Utility to create service / instance or 18c Database

Check in cloud console for the status of the database creation

Using PSM Utility ex: list templates for Stacks

Next Post - Using PSM Utility to Manage Database Operations

Suresh

18c Database : Refreshable PDB’s Switchover

In 12c, we can have refreshable PDB from one PDB to another PDB via database link.

--Manual Refresh

CREATE PLUGGABLE DATABASE pdb_ref FROM pdb1@pdb_link REFRESH MODE MANUAL;

-- Automatically refresh ever 60 minutes.

CREATE PLUGGABLE DATABASE pdb_ref FROM pdb1@pdb_link REFRESH MODE EVERY 60 MINUTES;

In 18c, this is extended and we can switchover the primary and clone DB act as source and vice versa using

ALTER SESSION SET CONTAINER = pdb_ref;
ALTER PLUGGABLE DATABASE REFRESH MODE MANUAL FROM pdb1@pdb_link SWITCHOVER;

Now with above alter statement, pdb_ref will be marked as source and pdb1 will be marked as clone.

Now, you must open the PDB1 (source) as readonly and set refresh mode manual or set interval.

ALTER SESSION SET CONTAINER = pdb1

ALTER PLUGGABLE DATABASE OPEN READ ONLY;

ALTER PLUGGABLE DATABASE REFRESH MODE EVERY 10 MINUTES;

Then, you can log to the reference PDB which is actually a clone but currently the source PDB and do any DML which will eventually populates to PDB1

-Thanks

Suresh

18c Database : Create Schema only accounts

In 18c, we can create a schema with no password using NO AUTHENTICATION clause, which can be useful in proxy based authentication.

These accounts are called Schema only accounts and not required any authentication mechanism and used only via remote authentication only.

SQL . CREATE USER TEST NO AUTHENTICATION;

However, these accounts cannot directly connect to database, but useful in proxy based connectivity i.e client user only. You can grant privileges to this schema only accounts.

CONN / AS SYSDBA
CREATE USER test_user NO AUTHENTICATION; (earlier we need to provide identified by clause for this)
ALTER USER scott GRANT CONNECT THROUGH test_user;

-Thanks

Suresh

18c Database : Private Temporary Tables

Starting 18c, we can create private temporary tables which can be visible to the session that created it using "Create Private temporary table" command.

Following are use cases for private temporary tables

When an application stores temporary data in transient tables that are populated once, read few times, and then dropped at the end of a transaction or session
When a session is maintained indefinitely and must create different temporary tables for different transactions
When the creation of a temporary table must not start a new transaction or commit an existing transaction
When different sessions of the same user must use the same name for a temporary table
When a temporary table is required for a read-only databaseNote: Names of private temporary tables must be prefixed according to the initialization parameter private_temp_table_prefix.

This statement creates a private temporary table that is transaction specific:

CREATE PRIVATE TEMPORARY TABLE GEEK$PTT_test_transaction (time_id DATE, amount_sold NUMBER(10,2)) ON COMMIT DROP DEFINITION;

This statement creates a private temporary table that is session specific:

CREATE PRIVATE TEMPORARY TABLE GEEK$PTT_test_session (time_id DATE, amount_sold NUMBER(10,2)) ON COMMIT PRESERVE DEFINITION;

-Thanks

Geek DBA

All about Database Administration, Tips & Tricks

New Features for DBA’s

Subscribe to Posts by Email

Subscriber Count

Disclaimer

Recent Posts

Categories

Archives

Pages

18c Database : Readonly Home and Password File Location Changes

18c Database : Cancel SQL Instead of Killing Session

18c Database : Logging Enhancements for nologging blocks for standby

18c Database : ASM Database Mirror for PDB Cloning

12.2.0.1 : Multi Instance Redo Apply

Oracle Cloud : Using PSM CLI to Manage Databases

Oracle Cloud : Installing PSM (PaaS Service Manager) CLI utility for Oracle Cloud

18c Database : Refreshable PDB’s Switchover

18c Database : Create Schema only accounts

18c Database : Private Temporary Tables

New Features for DBA’s

Follow Me!!!

Subscribe to Posts by Email

Subscriber Count

Disclaimer

Recent Posts

Categories

Archives

Tags

Pages