Martins Blog

Trying to explain complex things in simple terms

Experimenting with the ZFSSA’s snapshot capability using the simulator

Posted by Martin Bach on May 17, 2016

Recently I have been asked how the Copy-on-Write cloning works on the ZFS Storage Appliance. More specifically, the question was about the “master” copy: did it have to be static or could it be rolled forward? What better than a test to work out how it works. Unfortunately I don’t have an actual system available to me at home so I had to revert to the simulator, hoping that it represents the real appliance accurately.

Setup

First I downloaded the ZFS Storage Appliance Simulator from the Oracle website and created a nice, new, shiny storage system (albeit virtual). Furthermore I have an Oracle Linux 7 system with UEK3 that will attach to the ZFSSA using dNFS. The appliance has an IP address of 192.168.56.101 while the Linux system is accessible via 192.168.56.20. This is of course a virtual toy environment, a real life setup would be quite different using IPMP and multiple paths preferably over Infiniband.

Configuration

Configuring the system is a two step process. The first is to create a storage pool on the ZFSSA that will host database backups, snapshots and clones. The second part is the configuration of the database server to use dNFS. I have written about that in detail in a previous blog post: https://martincarstenbach.wordpress.com/2014/07/09/setting-up-direct-nfs-on-oracle-12c/.

Step 1: Configure the ZFSSA

Again this is the simulator and I can only wish I had the real thing :) I have created a mirrored pool across all disks (that’s possible at this point because I skipped the pool creation during the initial appliance configuration). Navigating to Configuration -> Storage I clicked on the + button to create the pool and assigned all disks to it. I used a mirrored configuration, which again is owned to my lab setup. Depending on your type of (Exadata) backup you would probably choose something else. There are white papers that explain the best data profile based on the workload.

Next I created a new project, named NCDB_BKP to have a common location to set attributes. I tend to set a different mount point, in this case /export/ncdb_bkp to group all shares about to be created. Set the other attributes (compression, record size, access permissions etc) according to your workload. Following the recommendation in the white paper listed in the reference section I created 4 shares under the NCDB_BKP project:
– data
– redo
– alert
– archive

You probably get where this is heading … With those steps I took from the white paper listed in the reference section, the setup of the ZFSSA Simulator is done, at least for now. Head over to the database server.

Step 2: configure the database server

On the database server I customarily create a /zfssa/<projectName>/ mount point where I’m intending to mount the project’s shares. In other words I have this:

[oracle@oraclelinux7 ~]$ ls -l /zfssa/ncdb_bkp
total 0
drwxr-xr-x. 2 oracle oinstall 6 Jan 12 16:28 alert
drwxr-xr-x. 2 oracle oinstall 6 Jan 12 16:28 archive
drwxr-xr-x. 2 oracle oinstall 6 Jan 12 16:28 data
drwxr-xr-x. 2 oracle oinstall 6 Jan 12 16:28 redo

These will be mounted from the ZFSSA – edit your fstab to mount the shares from the appliance (simulator). When mounted, you’d see something like that:

[root@oraclelinux7 ~]# mount | awk '/^[0-9].*/ {print $1}'
192.168.56.101:/export/ncdb_bkp/alert
192.168.56.101:/export/ncdb_bkp/archive
192.168.56.101:/export/ncdb_bkp/data
192.168.56.101:/export/ncdb_bkp/redo

The next step is to add those to the oranfstab which I covered in the previous post I referred to. That should be it for now! Time to take a backup of the source database in preparation for the cloning. Be careful: adding image copies to an existing backup strategy might have adverse side effects-as always make sure you understand the implications of this technique and its impact. As always, test thoroughly!

Creating a backup

Let’s have a look at the database before taking a backup:

[oracle@oraclelinux7 ~]$ NLS_DATE_FORMAT="dd.mm.yyyy hh24:mi:ss" rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Thu Mar 3 10:12:28 2016

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

connected to target database: NCDB (DBID=3358649481)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name NCDB

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    790      SYSTEM               YES     +DATA/NCDB/DATAFILE/system.279.905507757
3    610      SYSAUX               NO      +DATA/NCDB/DATAFILE/sysaux.273.905507723
4    280      UNDOTBS1             YES     +DATA/NCDB/DATAFILE/undotbs1.259.905507805
5    1243     EXAMPLE              NO      +DATA/NCDB/DATAFILE/example.283.905507865
6    5        USERS                NO      +DATA/NCDB/DATAFILE/users.266.905507803

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    60       TEMP                 32767       +DATA/NCDB/TEMPFILE/temp.282.905507861

RMAN>

Nothing special, just a standard DBCA-created General Purpose database … Time to take the image copy.

RMAN> @/u01/app/oracle/admin/NCDB/scripts/imagecopy.rman

RMAN> run {
2>   allocate channel c1 device type disk format '/zfssa/ncdb_bkp/data/%U';
3>   allocate channel c2 device type disk format '/zfssa/ncdb_bkp/data/%U';
4>   backup incremental level 1 for recover of copy with tag 'zfssa' database ;
5>   recover copy of database with tag 'zfssa';
6> }

allocated channel: c1
channel c1: SID=36 device type=DISK

allocated channel: c2
channel c2: SID=258 device type=DISK

Starting backup at 03.03.2016 14:48:25
no parent backup or copy of datafile 5 found
no parent backup or copy of datafile 1 found
no parent backup or copy of datafile 3 found
no parent backup or copy of datafile 4 found
no parent backup or copy of datafile 6 found
channel c1: starting datafile copy
input datafile file number=00005 name=+DATA/NCDB/DATAFILE/example.283.905507865
channel c2: starting datafile copy
input datafile file number=00001 name=+DATA/NCDB/DATAFILE/system.279.905507757
output file name=/zfssa/ncdb_bkp/data/data_D-NCDB_I-3358649481_TS-SYSTEM_FNO-1_6jqvie1q 
  tag=ZFSSA RECID=36 STAMP=905525356
channel c2: datafile copy complete, elapsed time: 00:00:55
channel c2: starting datafile copy
input datafile file number=00003 name=+DATA/NCDB/DATAFILE/sysaux.273.905507723
output file name=/zfssa/ncdb_bkp/data/data_D-NCDB_I-3358649481_TS-EXAMPLE_FNO-5_6iqvie1p 
  tag=ZFSSA RECID=37 STAMP=905525386
channel c1: datafile copy complete, elapsed time: 00:01:23
channel c1: starting datafile copy
input datafile file number=00004 name=+DATA/NCDB/DATAFILE/undotbs1.259.905507805
output file name=/zfssa/ncdb_bkp/data/data_D-NCDB_I-3358649481_TS-UNDOTBS1_FNO-4_6lqvie4d
  tag=ZFSSA RECID=39 STAMP=905525414
channel c1: datafile copy complete, elapsed time: 00:00:25
channel c1: starting datafile copy
input datafile file number=00006 name=+DATA/NCDB/DATAFILE/users.266.905507803
output file name=/zfssa/ncdb_bkp/data/data_D-NCDB_I-3358649481_TS-SYSAUX_FNO-3_6kqvie3k
  tag=ZFSSA RECID=38 STAMP=905525409
channel c2: datafile copy complete, elapsed time: 00:00:51
output file name=/zfssa/ncdb_bkp/data/data_D-NCDB_I-3358649481_TS-USERS_FNO-6_6mqvie57
  tag=ZFSSA RECID=40 STAMP=905525416
channel c1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 03.03.2016 14:50:18

Starting recover at 03.03.2016 14:50:18
no copy of datafile 1 found to recover
no copy of datafile 3 found to recover
no copy of datafile 4 found to recover
no copy of datafile 5 found to recover
no copy of datafile 6 found to recover
Finished recover at 03.03.2016 14:50:19
released channel: c1
released channel: c2

RMAN> **end-of-file**

The Oracle white paper I just mentioned proposes using the %b flag as part of the RMAN formatSpec in the backup command – which interestingly does not work. The second time I ran it it failed like this:

RMAN> @/u01/app/oracle/admin/NCDB/scripts/imagecopy.rman

RMAN> run {
2>   allocate channel c1 device type disk format '/zfssa/ncdb_bkp/data/%b';
3>   allocate channel c2 device type disk format '/zfssa/ncdb_bkp/data/%b';
4>   backup incremental level 1 for recover of copy with tag 'zfssa' database ;
5>   recover copy of database with tag 'zfssa';
6> }

allocated channel: c1
channel c1: SID=36 device type=DISK

allocated channel: c2
channel c2: SID=258 device type=DISK

Starting backup at 03.03.2016 14:42:04
channel c1: starting incremental level 1 datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00005 name=+DATA/NCDB/DATAFILE/example.283.905507865
input datafile file number=00006 name=+DATA/NCDB/DATAFILE/users.266.905507803
input datafile file number=00004 name=+DATA/NCDB/DATAFILE/undotbs1.259.905507805
channel c1: starting piece 1 at 03.03.2016 14:42:04
RMAN-03009: failure of backup command on c1 channel at 03/03/2016 14:42:04
ORA-19715: invalid format b for generated name
ORA-27302: failure occurred at: slgpn
continuing other job steps, job failed will not be re-run
channel c2: starting incremental level 1 datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/NCDB/DATAFILE/system.279.905507757
input datafile file number=00003 name=+DATA/NCDB/DATAFILE/sysaux.273.905507723
channel c2: starting piece 1 at 03.03.2016 14:42:04
released channel: c1
released channel: c2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on c2 channel at 03/03/2016 14:42:04
ORA-19715: invalid format b for generated name
ORA-27302: failure occurred at: slgpn

RMAN> **end-of-file**

This makes sense-Oracle tries to create an incremental backup with the same name as the data file copy, which would erase the copy and replace it with the incremental backup. Thankfully RMAN does not allow that to happen. This is why I chose the %U flag in the formatSpec, as it allows the incremental backup to be created successfully in addition to the datafile image copies. I am conscious of the fact that the image copies have somewhat ugly names.

After this little digression it’s time to back up the archived logs. When using copies of archived logs you need to make sure that you don’t have overlapping backups. The Oracle white paper has the complete syntax, I spare you the detail as it’s rather boring.

After some time the result is a set of image copies of the database plus the archived logs:

[oracle@oraclelinux7 ~]$ ls -lR /zfssa/ncdb_bkp/
/zfssa/ncdb_bkp/:
total 14
drwxr-xr-x. 2 oracle dba 2 Mar  3 10:32 alert
drwxr-xr-x. 2 oracle dba 4 Mar  3  2016 archive
drwxr-xr-x. 2 oracle dba 7 Mar  3  2016 data
drwxr-xr-x. 2 oracle dba 2 Mar  3 10:32 redo

/zfssa/ncdb_bkp/alert:
total 0

/zfssa/ncdb_bkp/archive:
total 2821
-r--r-----. 1 oracle asmdba 2869760 Mar  3  2016 1_117_905507850.arc
-r--r-----. 1 oracle asmdba    1024 Mar  3  2016 1_118_905507850.arc

/zfssa/ncdb_bkp/data:
total 3001657
-rw-r-----. 1 oracle asmdba 1304174592 Mar  3  2016 data_D-NCDB_I-3358649481_TS-EXAMPLE_FNO-5_6iqvie1p
-rw-r-----. 1 oracle asmdba  639639552 Mar  3  2016 data_D-NCDB_I-3358649481_TS-SYSAUX_FNO-3_6kqvie3k
-rw-r-----. 1 oracle asmdba  828383232 Mar  3  2016 data_D-NCDB_I-3358649481_TS-SYSTEM_FNO-1_6jqvie1q
-rw-r-----. 1 oracle asmdba  293609472 Mar  3  2016 data_D-NCDB_I-3358649481_TS-UNDOTBS1_FNO-4_6lqvie4d
-rw-r-----. 1 oracle asmdba    5251072 Mar  3  2016 data_D-NCDB_I-3358649481_TS-USERS_FNO-6_6mqvie57

/zfssa/ncdb_bkp/redo:
total 0

[oracle@oraclelinux7 ~]$

Create a Clone

Now that the database is backed up in form of an image copy and I have archived redo logs I can create a clone of it. This requires you to jump back to the ZFSSA interface (either CLI or BUI) and create snapshots followed by clones on the directories used.

One way is to log in to the BUI, select the NCDB_BKP project, navigate to “snapshots” and creating one by clicking on the + button. I named it snap0. If you plan on doing this more regularly it can also be scripted.

A clone is a writeable snapshot and is as easy to create. Add a new project – for example NCDB_CLONE1 – and set it up as required for your workload. In the next step you need to switch back to the backup project and for each of the 4 shares create a clone. To do so, navigate to the list of shares underneath NCDB_BKP and click on the pencil icon. This takes you to the share settings. Click on snapshots and you should see snap0. Hovering the mouse over the snapshot’s name reveals additional icons on the right, one of which allows you to create a clone (“clone snapshot as a new share”). Hit that plus sign and change the project to your clone (NCDB_CLONE1) and assign a name to the clone. I tend to use the same name as the source. The mount point should automatically be updated to /export/ncdb_clone1/sharename.

Now you need to get back to the database server and add the mount points for the recently created clones: /zfssa/ncdb_clone1/{data,redo,arch,alert}. Edit the fstab and oranfstab files, then mount the new shares.

Finishing the clone creation

The following procedure is most likely familiar to DBAs who created databases as file system copies. The steps are somewhere along the line of this:
– register the database in oratab
– create an initialisation file
– create a password file
– back up the source controlfile to trace in order to create a “create controlfile” statement for the clone
– start the clone
– create the controlfile
– recover the clone using the newly created backup controlfile
– open the clone with the resetlogs option
– add temp file(s)

Based on my source database’s parameter file, I created the following for the clone database which I’ll call CLONE1:

*.audit_file_dest='/u01/app/oracle/admin/CLONE1/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/zfssa/ncdb_clone1/redo/control1.ctl'
*.db_block_size=8192
*.db_create_file_dest='/zfssa/ncdb_clone1/data'
*.db_domain=''
*.db_name='CLONE1'
*.db_recovery_file_dest='/zfssa/ncdb_clone1/archive'
*.db_recovery_file_dest_size=4560m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=CLONE1XDB)'
*.nls_language='ENGLISH'
*.nls_territory='UNITED KINGDOM'
*.open_cursors=300
*.pga_aggregate_target=512m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1024m
*.undo_tablespace='UNDOTBS1'

Notice how some of the filesystem related parameters changed to point to the mount points exported via NFS from the ZFSSA. I have refrained from changing diagnostic_dest to the ZFSSA simulator, this prevented the database from starting (perf told me that the sqlplus session spent all the time trying to use the network).

I’ll spare you the details of the create controlfile command, just make sure you change paths and point to the data files on the cloned shares (/zfssa/ncdb_clone1/data/*) and NOT on the ones where the master copy resides. After the controlfile is created, recover the database using the backup controlfile, then open it. Voila! You have just created CLONE1. Just add a temp file and you are almost good to go.

In the next part

The post has already become a bit too long, so I’ll stop here and split it into this one and a second part. In the next part you will read about changes to the source database (NCDB) and how I’ll roll the image copies forward.

References

http://www.oracle.com/technetwork/articles/systems-hardware-architecture/cloning-solution-353626.pdf

Posted in Linux, Oracle | Tagged: | 1 Comment »

Trouble with multiple SCAN listeners in 12c Release 1

Posted by Martin Bach on May 14, 2016

Prompted by comments made by readers about my posts describing how to add a second SCAN in 12c Release 1 and problems regarding the listener_networks parameter I thought it was time to revisit the situation. I’m still running Oracle Linux 7.1/UEK3 (although that should not matter) but upgraded to 12.1.0.2.5. This is the release that is reportedly showing the odd behaviour. I don’t recall my exact version when I wrote the original posts back in April 2014, but by looking at them I think it all worked ok at the time. Here is my current patchlevel after the upgrade to the troublesome PSU.

 SQL> select patch_id,description,status from DBA_REGISTRY_SQLPATCH;

  PATCH_ID DESCRIPTION                                                                      STATUS
---------- -------------------------------------------------------------------------------- ---------------
  21555660 Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015)                       SUCCESS
  21359755 Database Patch Set Update : 12.1.0.2.5 (21359755)                                SUCCESS

This is not the latest patch level! After having seen a presentation by Ludovico Caldara (@ludodba) just this week I would think that the April 2016 patch which was current at the time of writing is the one you want to be on :) I have an upgrade to the April 2016 PSU planned but first wanted to test against 12.1.0.2.5 to see if I could reproduce the issue.

Problem Statement

The parameter listener_networks should be populated automatically by CRS when the database comes up-and indeed you can see evidence of that in the respective node’s crsd_oraagent_oracle.trc file. However, with 2 SCANs present, there is something not quite right. You can see this in the usual locations:

– SCAN listeners
– listener_networks parameter
– CRSD trace

The most obvious clue is that you cannot connect to the database any more using one of the SCANs. To avoid you having to flick back and forth between my posts, here is the network configuration again:

[oracle@ron12cprinode2 ~]$ srvctl config scan -netnum 1
SCAN name: ron12cpri-scan, Network: 1
Subnet IPv4: 192.168.100.0/255.255.255.0/eth0, static
Subnet IPv6: 
SCAN 0 IPv4 VIP: 192.168.100.65
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes: 
SCAN VIP is individually disabled on nodes: 
SCAN 1 IPv4 VIP: 192.168.100.66
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes: 
SCAN VIP is individually disabled on nodes: 
SCAN 2 IPv4 VIP: 192.168.100.64
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes: 
SCAN VIP is individually disabled on nodes: 

[oracle@ron12cprinode2 ~]$ srvctl config scan -netnum 2
SCAN name: ron12cpri-dg-scan, Network: 2
Subnet IPv4: 192.168.102.0/255.255.255.0/eth2, static
Subnet IPv6: 
SCAN 0 IPv4 VIP: 192.168.102.66
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes: 
SCAN VIP is individually disabled on nodes: 
SCAN 1 IPv4 VIP: 192.168.102.64
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes: 
SCAN VIP is individually disabled on nodes: 
SCAN 2 IPv4 VIP: 192.168.102.65
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes: 
SCAN VIP is individually disabled on nodes: 

It’s essentially using the 192.168.100/24 network for the “public” traffic and 192.168.102/24 for Data Guard. I still use my RAC One Node database RON, which is currently active on node 2. All of my SCAN listeners should know about its services, RON_SRV and RON_DG_SRV. However, that’s not the case, as a quick check reveals:

[oracle@ron12cprinode1 ~]$ lsnrctl status LISTENER_SCAN1

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 14-MAY-2016 19:56:14

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN1
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                14-MAY-2016 19:21:39
Uptime                    0 days 0 hr. 34 min. 35 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/12.1.0.2/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ron12cprinode1/listener_scan1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.65)(PORT=1521)))
Services Summary...
Service "-MGMTDBXDB" has 1 instance(s).
  Instance "-MGMTDB", status READY, has 1 handler(s) for this service...
Service "RON" has 1 instance(s).
  Instance "RON_1", status READY, has 1 handler(s) for this service...
Service "RONXDB" has 1 instance(s).
  Instance "RON_1", status READY, has 1 handler(s) for this service...
Service "RON_DG_SRV" has 1 instance(s).
  Instance "RON_1", status READY, has 1 handler(s) for this service...
Service "RON_SRV" has 1 instance(s).
  Instance "RON_1", status READY, has 1 handler(s) for this service...
Service "_mgmtdb" has 1 instance(s).
  Instance "-MGMTDB", status READY, has 1 handler(s) for this service...
Service "ron12cpri" has 1 instance(s).
  Instance "-MGMTDB", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@ron12cprinode1 ~]$ lsnrctl status DGSCANLSNR_SCAN2_NET2

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 14-MAY-2016 19:56:23

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=DGSCANLSNR_SCAN2_NET2)))
STATUS of the LISTENER
------------------------
Alias                     DGSCANLSNR_SCAN2_NET2
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                14-MAY-2016 19:21:42
Uptime                    0 days 0 hr. 34 min. 41 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/12.1.0.2/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ron12cprinode1/dgscanlsnr_scan2_net2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=DGSCANLSNR_SCAN2_NET2)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.102.64)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@ron12cprinode1 ~]$ 

Before generating the above output I specifically “reset” the listener_networks settings on both instances, and ensured that they were created dynamically. After the database restart I couldn’t make out ANY entry for listener_networks:

SQL> select inst_id,name,value from gv$parameter where name = 'listener_networks';

   INST_ID NAME                           VALUE
---------- ------------------------------ ------------------------------
         1 listener_networks

SQL> select inst_id,name,value from gv$spparameter where name = 'listener_networks';

   INST_ID NAME                           VALUE
---------- ------------------------------ ------------------------------
         1 listener_networks

Nothing at all. I manage to reach the system using the RON_SRV service that’s known to the first (public) network’s SCAN:

[oracle@ron12cprinode1 ~]$ sqlplus a/b@ron12cpri-scan/RON_SRV

SQL*Plus: Release 12.1.0.2.0 Production on Sat May 14 20:18:02 2016

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

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: 
[oracle@ron12cprinode1 ~]$ 

The same test failed for connections against ron12cpri-dg-scan, stating that the listener didn’t know about the service. Checking the CRSD trace (on the node the instance runs!) I could see the reason:

2016-05-14 19:47:49.637611 : USRTHRD:2023044864: {1:58687:2893} Endp=ron12cpri-dg-scan:1521
2016-05-14 19:47:49.637638 : USRTHRD:2023044864: {1:58687:2893} Final Endp=(( NAME=net2)(LOCAL_LISTENER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.102.63)(PORT=1523)))))','(( NAME=net2)(REMOTE_LISTENER=ron12cpri-dg-scan:1521ron12cpri-dg-scan:1521ron12cpri-dg-scan:1521)), remoteEndp= ron12cpri-scan:1521','ron12cpri-scan:1521','ron12cpri-scan:1521
2016-05-14 19:47:49.637662 : USRTHRD:2023044864: {1:58687:2893} sqlStmt = ALTER SYSTEM SET REMOTE_LISTENER=' ron12cpri-scan:1521','ron12cpri-scan:1521','ron12cpri-scan:1521' SCOPE=MEMORY SID='RON_1' /* db agent *//* {1:58687:2893} */
2016-05-14 19:47:49.645739 : USRTHRD:2023044864: {1:58687:2893} sqlStmt = ALTER SYSTEM SET LISTENER_NETWORKS='(( NAME=net2)(LOCAL_LISTENER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.102.63)(PORT=1523)))))','(( NAME=net2)(REMOTE_LISTENER=ron12cpri-dg-scan:1521ron12cpri-dg-scan:1521ron12cpri-dg-scan:1521))' SCOPE=MEMORY SID='RON_1' /* db agent *//* {1:58687:2893} */
2016-05-14 19:47:49.655035 : USRTHRD:2023044864: {1:58687:2893} ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00119: invalid specification for system parameter LISTENER_NETWORKS
ORA-00137: invalid LISTENER_NETWORKS specification with NAME=net2
ORA-00138: all addresses specified for attribute REMOTE_LISTENER are invalid
ORA-00132: syntax error or unresolved network name 'ron12cpri-dg-scan:1521ron12cpri-dg-scan:1521ron12cpri-dg-scan:1521'

2016-05-14 19:47:49.655191 : USRTHRD:2023044864: {1:58687:2893} DbAgent::DedicatedThread::run setRemoteListener Exception OCIException
2016-05-14 19:47:49.655207 : USRTHRD:2023044864: {1:58687:2893} ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00119: invalid specification for system parameter LISTENER_NETWORKS
ORA-00137: invalid LISTENER_NETWORKS specification with NAME=net2
ORA-00138: all addresses specified for attribute REMOTE_LISTENER are invalid
ORA-00132: syntax error or unresolved network name 'ron12cpri-dg-scan:1521ron12cpri-dg-scan:1521ron12cpri-dg-scan:1521'

Looking at this output it appears that setting the remote_listener worked, although I thought we’d only set the host once and not thrice? This looks fishy. It appears to work though, as confirmed in v$parameter and the fact that I can connect against the system.

Interestingly setting listener_networks fails with an ORA-02097: parameter cannot be modified because specified value is invalid. This makes sense: there are white spaces missing in the alter system command, and even if the spaces were correct, the command would fail. Trying manually confirms that thought:

SQL> ALTER SYSTEM SET LISTENER_NETWORKS='(( NAME=net2)(LOCAL_LISTENER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.102.63)(PORT=1523)))))','(( NAME=net2)(REMOTE_LISTENER=ron12cpri-dg-scan:1521 ron12cpri-dg-scan:1521 ron12cpri-dg-scan:1521))' SCOPE=MEMORY SID='RON_1';
ALTER SYSTEM SET LISTENER_NETWORKS='(( NAME=net2)(LOCAL_LISTENER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.102.63)(PORT=1523)))))','(( NAME=net2)(REMOTE_LISTENER=ron12cpri-dg-scan:1521 ron12cpri-dg-scan:1521 ron12cpri-dg-scan:1521))' SCOPE=MEMORY SID='RON_1'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00119: invalid specification for system parameter LISTENER_NETWORKS
ORA-00137: invalid LISTENER_NETWORKS specification with NAME=net2
ORA-00138: all addresses specified for attribute REMOTE_LISTENER are invalid
ORA-00132: syntax error or unresolved network name 'ron12cpri-dg-scan:1521
ron12cpri-dg-scan:1521 ron12cpri-dg-scan:1521'

OK – that’s all I need to know. When changing the command to look like what I thought it should look like in the first place (the remote listener specifies only 1 host:port) it works:

SQL> ALTER SYSTEM SET LISTENER_NETWORKS='(( NAME=net2)(LOCAL_LISTENER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.102.63)(PORT=1523)))))','(( NAME=net2)(REMOTE_LISTENER=ron12cpri-dg-scan:1521))' SCOPE=MEMORY SID='RON_1';

System altered.

SQL> alter system register;

System altered.

What does that mean? Back to my SCAN listeners again:

[oracle@ron12cprinode1 ~]$ lsnrctl status LISTENER_SCAN1

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 14-MAY-2016 20:07:19

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
STATUS of the LISTENER
------------------------   
Alias                     LISTENER_SCAN1
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                14-MAY-2016 19:21:39
Uptime                    0 days 0 hr. 45 min. 40 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/12.1.0.2/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ron12cprinode1/listener_scan1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.65)(PORT=1521)))
Services Summary...
Service "-MGMTDBXDB" has 1 instance(s).
  Instance "-MGMTDB", status READY, has 1 handler(s) for this service...
Service "RON" has 1 instance(s).
  Instance "RON_1", status READY, has 1 handler(s) for this service...
Service "RONXDB" has 1 instance(s).
  Instance "RON_1", status READY, has 1 handler(s) for this service...
Service "RON_DG_SRV" has 1 instance(s).
  Instance "RON_1", status READY, has 1 handler(s) for this service...
Service "RON_SRV" has 1 instance(s).
  Instance "RON_1", status READY, has 1 handler(s) for this service...
Service "_mgmtdb" has 1 instance(s).
  Instance "-MGMTDB", status READY, has 1 handler(s) for this service...
Service "ron12cpri" has 1 instance(s).
  Instance "-MGMTDB", status READY, has 1 handler(s) for this service...
The command completed successfully

No negative change there, but would the DG_SCAN listener also pick it up?

[oracle@ron12cprinode1 ~]$ lsnrctl status DGSCANLSNR_SCAN2_NET2

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 14-MAY-2016 20:07:21

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=DGSCANLSNR_SCAN2_NET2)))
STATUS of the LISTENER
------------------------   
Alias                     DGSCANLSNR_SCAN2_NET2
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                14-MAY-2016 19:21:42
Uptime                    0 days 0 hr. 45 min. 39 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/12.1.0.2/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ron12cprinode1/dgscanlsnr_scan2_net2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=DGSCANLSNR_SCAN2_NET2)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.102.64)(PORT=1521)))
Services Summary...
Service "RON" has 1 instance(s).
  Instance "RON_1", status READY, has 1 handler(s) for this service...
Service "RONXDB" has 1 instance(s).
  Instance "RON_1", status READY, has 1 handler(s) for this service...
Service "RON_DG_SRV" has 1 instance(s).
  Instance "RON_1", status READY, has 1 handler(s) for this service...
Service "RON_SRV" has 1 instance(s).
  Instance "RON_1", status READY, has 1 handler(s) for this service...
The command completed successfully

Well it seems it did. Now the question is: can I connect?

[oracle@ron12cprinode1 ~]$ sqlplus system/secretPWD@ron12cpri-scan/RON_SRV

SQL*Plus: Release 12.1.0.2.0 Production on Sat May 14 20:08:45 2016

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

ERROR:
ORA-28002: the password will expire within 7 days


Last Successful login time: Sat May 14 2016 19:58:28 +01:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
[oracle@ron12cprinode1 ~]$ sqlplus system/secretPWD@ron12cpri-dg-scan/RON_DG_SRV

SQL*Plus: Release 12.1.0.2.0 Production on Sat May 14 20:08:54 2016

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

ERROR:
ORA-28002: the password will expire within 7 days


Last Successful login time: Sat May 14 2016 20:08:45 +01:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> 

Summary

Well I can! So that should solve the problem for the active instance, however there are problems bound to happen when the instance restarts. Since I don’t really have control over the instance name in RAC One Node (RON_1 can be started on node 1 and node 2) I can’t hard-code the value for listener_networks into the spfile. As an end result I’d lock myself out just like CRS did. This is likely a similar issue for multi-node RAC using policy managed databases.

I have repeated the test with the latest version of the stack (upgraded in place), and got the same result. Here are the version numbers:

[oracle@ron12cprinode2 ~]$ /u01/app/oracle/product/12.1.0.2/dbhome_1/OPatch/opatch lspatches
22674709;Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016)
22502555;OCW Patch Set Update : 12.1.0.2.160419 (22502555)
22291127;Database Patch Set Update : 12.1.0.2.160419 (22291127)

OPatch succeeded.

[oracle@ron12cprinode2 ~]$ /u01/app/12.1.0.2/grid/OPatch/opatch lspatches
22674709;Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016)
22502555;OCW Patch Set Update : 12.1.0.2.160419 (22502555)
22291127;Database Patch Set Update : 12.1.0.2.160419 (22291127)

OPatch succeeded.

SQL> select action_time, patch_id,description,status from DBA_REGISTRY_SQLPATCH order by action_time;

ACTION_TIME                                PATCH_ID DESCRIPTION                                                            STATUS
---------------------------------------- ---------- ---------------------------------------------------------------------- ---------------
29-OCT-15 12.46.55.763581                  21555660 Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015)             SUCCESS
29-OCT-15 12.46.55.939750                  21359755 Database Patch Set Update : 12.1.0.2.5 (21359755)                      SUCCESS
14-MAY-16 21.32.15.211167                  21555660 Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015)             SUCCESS
14-MAY-16 21.32.15.233105                  22674709 Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016)        SUCCESS
14-MAY-16 21.32.15.591460                  22291127 Database Patch Set Update : 12.1.0.2.160419 (22291127)                 SUCCESS

The same values for remote_listener and listener_networks as with 12.1.0.2.5 have been observed, and the error about setting listener_networks in the CRSD log was identical to the previous release. I guess that needs to be raised with Oracle …

Posted in 12c Release 1, Linux, RAC | 1 Comment »

TABLE ACCESS INMEMORY FULL – but there may be more

Posted by Martin Bach on November 13, 2015

While preparing to teach a class this week I have had the time to look into the In Memory (cost) option a bit closer. I noticed a few interesting things along the way and would like to share one of these here.

Background

One of the questions I was asking myself was:

“What happens if I scan segments that are within the IM area, and some are not?”

I was primarily thinking of joins in a DWH environment, but in order to keep the test case simple enough and reproducible I decided to go with a partitioned table where the current partition is assigned to the IMCS, and the rest is not. For this test I’m relying on the Swingbench SH schema. All of this runs on my laptop in a VM so I had to be selective when it comes to indexes. I also chose to NOT partition the tables at this stage, I wanted to chose my own partitioning scheme. For reference, here is the command that created the SH schema:

[oracle@oraclelinux7 bin]$ ./shwizard -cl -create -cs //localhost/sh_pdb \
> -dba system -dbap somePassword -nopart -pkindexes -scale 1 -tc 2 -u sh -p sh
...
============================================
|           Datagenerator Run Stats        |
============================================
Connection Time                        0:00:00.005
Data Generation Time                   0:04:35.890
DDL Creation Time                      0:00:14.063
Total Run Time                         0:04:49.962
Rows Inserted per sec                       64,707
Data Generated (MB) per sec                    5.3
Actual Rows Generated                   17,848,007

...

I wanted to create a range partitioned version of the sales table. After the SH-wizard completed the data distribution is as shown:

SQL> select count(*), trunc(time_id,'yy') year from sales group by trunc(time_id,'yy') order by year;

  COUNT(*) YEAR
---------- ---------
    130653 01-JAN-95
    149319 01-JAN-96
    251974 01-JAN-97
    326632 01-JAN-98
    365547 01-JAN-99
    388318 01-JAN-00
    393919 01-JAN-01
    406703 01-JAN-02
    483605 01-JAN-03
    566384 01-JAN-04
    422289 01-JAN-05
    619858 01-JAN-06
    782244 01-JAN-07
   1099551 01-JAN-08
   1249340 01-JAN-09
   1346025 01-JAN-10
   1690302 01-JAN-11
   2028048 01-JAN-12
   2028048 01-JAN-13

19 rows selected.

So based on this information I can create a table that has data from 2013 in the IMCS and everything else will be excluded from it. For the sake of the following discussion 2013 is considered the “current” partition. Here is the partitioned sales table’s DDL with my changes to enable my partitioning scheme.

CREATE TABLE SALES_PART
  (
    PROD_ID            NUMBER NOT NULL ENABLE,
    CUST_ID            NUMBER NOT NULL ENABLE,
    TIME_ID            DATE NOT NULL ENABLE,
    CHANNEL_ID         NUMBER NOT NULL ENABLE,
    PROMO_ID           NUMBER NOT NULL ENABLE,
    QUANTITY_SOLD      NUMBER(10,2) NOT NULL ENABLE,
    SELLER             NUMBER(6,0) NOT NULL ENABLE,
    FULFILLMENT_CENTER NUMBER(6,0) NOT NULL ENABLE,
    COURIER_ORG        NUMBER(6,0) NOT NULL ENABLE,
    TAX_COUNTRY        VARCHAR2(3) NOT NULL ENABLE,
    TAX_REGION         VARCHAR2(3),
    AMOUNT_SOLD        NUMBER(10,2) NOT NULL ENABLE
  )
  partition BY range (time_id)
  (
    partition p_old VALUES less than       (DATE '2001-01-01'),
    partition p_2000_2010 VALUES less than (DATE '2011-01-01'),
    partition p_2011 VALUES less than      (DATE '2012-01-01'),
    partition p_2012 VALUES less than      (DATE '2013-01-01'),
    partition p_2013 VALUES less than      (DATE '2014-01-01') inmemory
  )
  TABLESPACE SH;

SQL> alter session enable parallel dml;

Session altered.

SQL> insert /*+ parallel append */ into sales_part select /*+ parallel */ * from sales
  2  /

14728759 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(user,'SALES_PART')

PL/SQL procedure successfully completed.

SQL> select partition_name, num_rows, inmemory
  2  from user_tab_partitions
  3  where table_name = 'SALES_PART'
  4  order by partition_position;

PARTITION_NAME                   NUM_ROWS INMEMORY
------------------------------ ---------- --------
P_OLD                             1612443 DISABLED
P_2000_2010                       7369918 DISABLED
P_2011                            1690302 DISABLED
P_2012                            2028048 DISABLED
P_2013                            2028048 ENABLED

So that should give me what I needed. The IMCS was now populated with the segment as well:

SQL> select segment_name, partition_name, bytes_not_populated, populate_status from v$im_user_segments;

SEGMENT_NAME         PARTITION_NAME                 BYTES_NOT_POPULATED POPULATE_
-------------------- ------------------------------ ------------------- ---------
SALES_PART           P_2013                                           0 COMPLETED

Test

With the setup work complete I am ready to test. First of all, a simple SQL trace should show me what is happening. Note that I am specifically targeting the “current” partition.

SQL> sho user
USER is "SH"
SQL> alter session set events 'sql_trace level 8';

Session altered.

SQL> select /* current_partition */ count(*) from sales_part
  2  where time_id > DATE '2013-01-02'
  3  and promo_id = 316;

  COUNT(*)
----------
      3947

SQL> alter session set events 'sql_trace off';

Session altered.

SQL> select value from v$diag_info where name like 'Def%';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/cdb/CDB/trace/CDB_ora_14034.trc

The tkprof’d trace shows this result:

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

select /* current_partition */ count(*) from sales_part
where time_id > DATE '2013-01-02'
and promo_id = 316

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.01          0          5          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 117  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=3 pr=0 pw=0 time=8441 us)
      3947       3947       3947   PARTITION RANGE SINGLE PARTITION: 5 5 (cr=3 pr=0 pw=0 time=7583 us cost=214 size=48252 card=4021)
      3947       3947       3947    TABLE ACCESS INMEMORY FULL SALES_PART PARTITION: 5 5 (cr=3 pr=0 pw=0 time=6972 us cost=214 size=48252 card=4021)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        6.79          6.79
********************************************************************************

As you can see, my partitioning strategy paid off-only the 5th partition is accessed (that’s p_2013 or the “current” partition based on the data). This partition is the one entirely found in the IMCS.

A result like the one above is what I’d expect and the access path “TABLE ACCESS INMEMORY FULL” does it justice. But now the actual reason for the blog post: mixing scans against segments in memory and on disk. Logging on again I ran the same query but without the restriction to 2013.

SQL> alter session set events 'sql_trace level 8';

Session altered.

SQL> select /* every_partition */ count(*) from sales_part
  2  where promo_id = 316;

  COUNT(*)
----------
     28993

SQL> alter session set events 'sql_trace off';

Session altered.

SQL> select value from v$diag_info where name like 'Def%';

VALUE
----------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/cdb/CDB/trace/CDB_ora_14437.trc

The tkprof’d result is shown here:

select /* every_partition */ count(*) from sales_part
where promo_id = 316

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      3.15       3.84      94878      94901          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      3.15       3.84      94878      94901          0           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 117  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=94901 pr=94878 pw=0 time=3848392 us)
     28993      28993      28993   PARTITION RANGE ALL PARTITION: 1 5 (cr=94901 pr=94878 pw=0 time=4503583 us cost=26217 size=117128 card=29282)
     28993      28993      28993    TABLE ACCESS INMEMORY FULL SALES_PART PARTITION: 1 5 (cr=94901 pr=94878 pw=0 time=4254867 us cost=26217 size=117128 card=29282)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  Disk file operations I/O                        4        0.00          0.00
  db file sequential read                         6        0.00          0.01
  direct path read                               42        0.01          0.18
  SQL*Net message from client                     2        7.63          7.63
********************************************************************************

Again the access path is clearly indicated as “TABLE ACCESS INMEMORY FULL SALES_PART”. This time it references all 5 partitions (which is expected since I didn’t have a date in my predicate list). And I can see direct path reads plus some other I/O related information! Direct Path Reads are quite likely going to be Smart Scans on Exadata by the way…

Of course “TABLE ACCESS INMEMORY FULL” is correct (because one partition is accessed that way), but I guess that you cannot see the disk-based I/O against the other segments from the regular execution plan as shown in SQLPlus for example .

Is there any other instrumentation I could use?

The tkprof output shows that data retrieval is possible for segments that are not (entirely) found in the IMCS. Where else could I learn about this fact? Session statistics (V$STATNAME, V$SESSTAT, etc) are another useful source of information. I am a great fan of session snapper (who isn’t?). Snapper can be used to display the change in the session counters while the session you are monitoring is running. Another option is Adrian Billington’s mystats script. It can help you capture the changes in session statistics during the execution of a SQL command. I executed the same query again, sandwiched into calls to mystats. Note that the statistics shown next do not match those of to the execution of the query above. I also tried to limit the output only to the relevant ones, output is sorted by statistic name.


------------------------------------------------------------------------------------------
2. Statistics Report
------------------------------------------------------------------------------------------
Type    Statistic Name                                                               Value
------  ----------------------------------------------------------------  ----------------

STAT    IM scan CUs columns accessed                                                     4
STAT    IM scan CUs columns theoretical max                                             48
STAT    IM scan CUs memcompress for query low                                            4
STAT    IM scan CUs predicates applied                                                   4
STAT    IM scan CUs predicates received                                                  4
STAT    IM scan CUs split pieces                                                         4
STAT    IM scan bytes in-memory                                                 47,683,308
STAT    IM scan bytes uncompressed                                              99,118,124
STAT    IM scan rows                                                             2,028,048
STAT    IM scan rows projected                                                       3,963
STAT    IM scan rows valid                                                       2,028,048
STAT    IM scan segments minmax eligible                                                 4

STAT    consistent gets                                                             95,093
STAT    consistent gets direct                                                      94,872
STAT    consistent gets examination                                                      4
STAT    consistent gets examination (fastpath)                                           4
STAT    consistent gets from cache                                                     221
STAT    consistent gets pin                                                            217
STAT    consistent gets pin (fastpath)                                                 217

STAT    logical read bytes from cache                                            9,666,560
STAT    no work - consistent read gets                                              94,871

STAT    physical read IO requests                                                      749
STAT    physical read bytes                                                    777,191,424
STAT    physical read total IO requests                                                749
STAT    physical read total bytes                                              777,191,424
STAT    physical read total multi block requests                                       749
STAT    physical reads                                                              94,872
STAT    physical reads direct                                                       94,872

STAT    session logical reads                                                      111,201
STAT    session logical reads - IM                                                  15,149

STAT    table scan blocks gotten                                                    94,872
STAT    table scan disk non-IMC rows gotten                                     12,700,711
STAT    table scan rows gotten                                                  14,728,759
STAT    table scans (IM)                                                                 1
STAT    table scans (direct read)                                                        4
STAT    table scans (long tables)                                                        5

STAT    temp space allocated (bytes)                                             1,048,576

STAT    workarea executions - optimal                                                    1
STAT    workarea memory allocated                                                        5
------------------------------------------------------------------------------------------
3. About
------------------------------------------------------------------------------------------
- MyStats v2.01 by Adrian Billington (http://www.oracle-developer.net)
- Based on the SNAP_MY_STATS utility by Jonathan Lewis
==========================================================================================
End of report
==========================================================================================

The highlights are:

  • You can see the compression ratio of the data in the IMCS (IM scan bytes in-memory and IM scan bytes uncompressed)
  • All rows in the partition were valid (the number of IM Scan rows equals the number of rows in the partition)
  • One segment was scanned using IM, and 4 were scanned as direct (path) reads. A total of 5 segments were scanned

Hope this helps!

Posted in 12c Release 1, Performance | 3 Comments »

Little things worth knowing: automatic generation of extended statistics in 12c Part II

Posted by Martin Bach on November 6, 2015

In my last post I demonstrated an example how to use a PL/SQL package to monitor a workload in order to identify correlated columns. Such columns are good candidates for the creation of extended statistics since the optimiser does not assume that information stored in different columns may be related. Before starting my tests I invoked DBMS_STATS.SEED_COL_USAGE, which in turn increased Oracle’s attention level trying to find correlated columns. Eventually these have been identified (as shown in the output of DBMS_STATS.REPORT_COL_USAGE) and a subsequent call to DBMS_STATS.GATHER_TABLE_STATS caused extended statistics to be created, including histograms. This is one way you can get extended statistics automatically, but it requires you to enable monitoring of a workload by invoking a PL/SQL API. In other words, it is a conscious effort.

More Ways to Extended Statistics

The other way of ending up with extended statistics is more subtle as it does not require user intervention. As described in the documentation (Database SQL Tuning Guide, chapter 13 “Managing Optimizer Statistics: Advanced Topics”) there is another way to gather extended statistics automatically. It is based on statistics feedback and SQL Plan Directives, both new to 12c (actually statistics feedback is not quite so new, it seems to be an evolution of cardinality feedback).

Demo

To start with a clean sheet I dropped the table I had used previously, which got rid of all the extended stats and SQL Plan Directives (SPD) from the previous example. To be really thorough I also flushed the shared pool.

SQL> drop table martin.customers purge;

Table dropped.

SQL> select * from dba_stat_extensions where owner = user;

no rows selected

SQL> select count(*) from DBA_SQL_PLAN_DIR_OBJECTS where owner = user;

  COUNT(*)
----------
         0

SQL> alter system flush shared_pool;

System altered.

BTW I have switched environments to an Oracle Restart 12.1.0.2 database with the JUL 2015 PSU applied. The second patch you see here is the OJVM patch.

SQL> select patch_id, version, action from dba_registry_sqlpatch;

  PATCH_ID VERSION              ACTION
---------- -------------------- ---------------
  20831110 12.1.0.2             APPLY
  21068507 12.1.0.2             APPLY

Back to creating the table… I am using the same technique as before, but this time without the call to DBMS_STATS.SEED_COL_USAGE:

SQL> create table martin.customers as select * from sh.customers;

Table created.

SQL> select num_rows, last_analyzed from user_tables where table_name = 'CUSTOMERS';

  NUM_ROWS LAST_ANALYZED
---------- -------------------
     55500 15.10.2015 16:57:42

I love the 12c feature where stats are gathered during a CTAS operation …

And now a few queries to tickle the optimiser. Apologies for the wide output but that makes it so much easier to use RLWRAP and command line history. Credit again to
the blog post by Maria Colgan
for the query examples.

SQL> select /*+ gather_plan_statistics */ count(*) from customers where cust_city = 'Los Angeles' and cust_state_province = 'CA' and country_id = 52790;

  COUNT(*)
----------
       932

SQL>
SQL> select * from table(dbms_xplan.display_cursor(format => 'allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
SQL_ID  34zmr3acgz06g, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from customers where
cust_city = 'Los Angeles' and cust_state_province = 'CA' and country_id
= 52790

Plan hash value: 296924608

---------------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |      1 |00:00:00.03 |    1521 |   1516 |
|   1 |  SORT AGGREGATE    |           |      1 |      1 |      1 |00:00:00.03 |    1521 |   1516 |
|*  2 |   TABLE ACCESS FULL| CUSTOMERS |      1 |      1 |    932 |00:00:00.04 |    1521 |   1516 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("CUST_CITY"='Los Angeles' AND "CUST_STATE_PROVINCE"='CA' AND
              "COUNTRY_ID"=52790))


22 rows selected.

There is nothing too new here-the optimiser’s cardinality estimate is not great. Following the example from the SQL Tuning Guide-chapter 13 (btw country_id is a number, not a varchar2 field) we can now check if there is anything fancy going to happen next. And sure enough there is:

SQL> select sql_id, child_number, is_obsolete, is_reoptimizable, is_resolved_adaptive_plan from v$sql where sql_id = '34zmr3acgz06g';

SQL_ID        CHILD_NUMBER I I I
------------- ------------ - - -
34zmr3acgz06g            0 N Y

The second flag, is_reoptimisable, is interesting. It indicates the optimiser’s intention to apply information found at run-time to the next execution of the cursor. Let’s run the original query again.

SQL> select /*+ gather_plan_statistics */ count(*) from customers where cust_city = 'Los Angeles' and cust_state_province = 'CA' and country_id = 52790;

  COUNT(*)
----------
       932

So what did that do? Let’s have a look at the diagnostics:


SQL> select sql_id, child_number, is_obsolete, is_reoptimizable, is_resolved_adaptive_plan from v$sql where sql_id = '34zmr3acgz06g';

SQL_ID        CHILD_NUMBER I I I
------------- ------------ - - -
34zmr3acgz06g            0 N Y
34zmr3acgz06g            1 N N

SQL> select * from table(dbms_xplan.display_cursor(sql_id=>'34zmr3acgz06g',cursor_child_no=>1,format => 'allstats last'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  34zmr3acgz06g, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from customers where
cust_city = 'Los Angeles' and cust_state_province = 'CA' and country_id
= 52790

Plan hash value: 296924608

------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |      1 |00:00:00.01 |    1521 |
|   1 |  SORT AGGREGATE    |           |      1 |      1 |      1 |00:00:00.01 |    1521 |
|*  2 |   TABLE ACCESS FULL| CUSTOMERS |      1 |    932 |    932 |00:00:00.01 |    1521 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("CUST_CITY"='Los Angeles' AND "CUST_STATE_PROVINCE"='CA' AND
              "COUNTRY_ID"=52790))

Note
-----
   - statistics feedback used for this statement


26 rows selected.

As you can see another child cursor has been created. Why?

SQL> select xmltype(reason) from v$sql_shared_cursor where dbms_lob.getlength(reason) > 1 and sql_id = '34zmr3acgz06g';

XMLTYPE(REASON)
-----------------------------------------------------------------------------------------------------------------------------
<ChildNode>
  <ChildNumber>0</ChildNumber>
  <ID>49</ID>
  <reason>Auto Reoptimization Mismatch(1)</reason>
  <size>3x4</size>
  <kxscflg>32</kxscflg>
  <kxscfl4>4194560</kxscfl4>
  <dnum_kksfcxe>262144</dnum_kksfcxe>
</ChildNode>

Ah – the cursor was created because of a reoptimisation mismatch. Checking against v$sql you can see that the optimiser is now happy with the cardinality estimate (not anticipating further reoptimisation for this statement). Note that depending on the query’s complexity many more child cursors can be created as part of the reoptimisation. Also note that for child cursor 1 the A and E rows are perfect matches. Our work here is done. But wait- what about that column usage?


SQL> select dbms_stats.report_col_usage(user, 'CUSTOMERS') from dual;

DBMS_STATS.REPORT_COL_USAGE(USER,'CUSTOMERS')
--------------------------------------------------------------------------------
LEGEND:
.......

EQ         : Used in single table EQuality predicate
RANGE      : Used in single table RANGE predicate
LIKE       : Used in single table LIKE predicate
NULL       : Used in single table is (not) NULL predicate
EQ_JOIN    : Used in EQuality JOIN predicate
NONEQ_JOIN : Used in NON EQuality JOIN predicate
FILTER     : Used in single table FILTER predicate
JOIN       : Used in JOIN predicate
GROUP_BY   : Used in GROUP BY expression
...............................................................................

###############################################################################

COLUMN USAGE REPORT FOR MARTIN.CUSTOMERS
........................................

1. COUNTRY_ID                          : EQ
2. CUST_CITY                           : EQ
3. CUST_STATE_PROVINCE                 : EQ
###############################################################################

SQL> select * from dba_stat_extensions where owner = user;

no rows selected

Nothing here except some equality predicates! This is the big difference to part I of this article where the filter on the 3 columns was detected thanks to the call to DBMS_STATS.SEED_COL_USAGE.

It took me a while to connect the dots and understand what needed to be done next. In the meantime, hidden from view, and in the background, Oracle created a SQL Plan Directive for that table which I failed to notice for a while. SQL Plan Directives are not persisted immediately, the documentation states they are written to SYSAUX every 15 minutes. I must have thought about this for more than 15 minutes, as you will see shortly. If you are a bit more impatient then force the write of the SPD to disk using DBMS_SPD.FLUSH_SQL_PLAN_DIRECTIVE now.

To verify if there were any SPDs I used a query taken more or less literally from the documentation, and yes, there are:

SQL> SELECT TO_CHAR(d.DIRECTIVE_ID) dir_id, o.OBJECT_NAME,
  2         o.SUBOBJECT_NAME col_name, o.OBJECT_TYPE, d.TYPE, d.STATE, d.REASON
  3  FROM   DBA_SQL_PLAN_DIRECTIVES d, DBA_SQL_PLAN_DIR_OBJECTS o
  4  WHERE  d.DIRECTIVE_ID=o.DIRECTIVE_ID
  5  AND    o.OWNER = user
  6  ORDER BY 1,2,3,4,5;

DIR_ID                         OBJECT_NAME          COL_NAME             OBJECT TYPE             STATE    REASON
------------------------------ -------------------- -------------------- ------ ---------------- -------- ------------------------------------
15273172249382976180           CUSTOMERS            COUNTRY_ID           COLUMN DYNAMIC_SAMPLING USABLE   SINGLE TABLE CARDINALITY MISESTIMATE
15273172249382976180           CUSTOMERS            CUST_CITY            COLUMN DYNAMIC_SAMPLING USABLE   SINGLE TABLE CARDINALITY MISESTIMATE
15273172249382976180           CUSTOMERS            CUST_STATE_PROVINCE  COLUMN DYNAMIC_SAMPLING USABLE   SINGLE TABLE CARDINALITY MISESTIMATE
15273172249382976180           CUSTOMERS                                 TABLE  DYNAMIC_SAMPLING USABLE   SINGLE TABLE CARDINALITY MISESTIMATE

Once you can see the SPD in the dictionary, you can also see them in action when you run a statement with the same predicates but different SQL_ID, as in this example.

SQL> select /*+ gather_plan_statistics new_parse_please */ count(*) from customers where cust_city = 'Los Angeles' and cust_state_province = 'CA' and country_id = 52790;

  COUNT(*)
----------
       932

SQL> select * from table(dbms_xplan.display_cursor(format => 'allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  85qvryzgzj57q, child number 0
-------------------------------------
select /*+ gather_plan_statistics new_parse_please */ count(*) from
customers where cust_city = 'Los Angeles' and cust_state_province =
'CA' and country_id = 52790

Plan hash value: 296924608

------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |      1 |00:00:00.01 |    1521 |
|   1 |  SORT AGGREGATE    |           |      1 |      1 |      1 |00:00:00.01 |    1521 |
|*  2 |   TABLE ACCESS FULL| CUSTOMERS |      1 |    829 |    932 |00:00:00.01 |    1521 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("CUST_CITY"='Los Angeles' AND "CUST_STATE_PROVINCE"='CA' AND
              "COUNTRY_ID"=52790))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - 1 Sql Plan Directive used for this statement


27 rows selected.

The SQL Plan directive stays even if you flush the cursor with SQL ID 34zmr3acgz06g from the cursor cache or supply a different set of predicates. I used Kerry Osborne’s flush_sql.sql script for this.

SQL> @flush_sql 
Enter value for sql_id: 34zmr3acgz06g
old  14:   where sql_id like '&sql_id';
new  14:   where sql_id like '34zmr3acgz06g';

PL/SQL procedure successfully completed.

SQL> select sql_id, child_number, is_obsolete, is_reoptimizable, is_resolved_adaptive_plan from v$sql where sql_id = '34zmr3acgz06g';

no rows selected

SQL> select /*+ gather_plan_statistics */ count(*) from customers where cust_city = 'Los Angeles' and cust_state_province = 'CA' and country_id = 52790;

  COUNT(*)
----------
       932

SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  34zmr3acgz06g, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from customers where
cust_city = 'Los Angeles' and cust_state_province = 'CA' and country_id
= 52790

Plan hash value: 296924608

------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |      1 |00:00:00.01 |    1521 |
|   1 |  SORT AGGREGATE    |           |      1 |      1 |      1 |00:00:00.01 |    1521 |
|*  2 |   TABLE ACCESS FULL| CUSTOMERS |      1 |    953 |    932 |00:00:00.01 |    1521 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("CUST_CITY"='Los Angeles' AND "CUST_STATE_PROVINCE"='CA' AND
              "COUNTRY_ID"=52790))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - 1 Sql Plan Directive used for this statement


27 rows selected.


SQL> select /*+ gather_plan_statistics */ count(*) from customers where cust_city = 'Palmdale' and cust_state_province = 'FL' and country_id = 52790;

  COUNT(*)
----------
       250

SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dbkfpchpfwap3, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from customers where
cust_city = 'Palmdale' and cust_state_province = 'FL' and country_id =
52790

Plan hash value: 296924608

------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |      1 |00:00:00.01 |    1521 |
|   1 |  SORT AGGREGATE    |           |      1 |      1 |      1 |00:00:00.01 |    1521 |
|*  2 |   TABLE ACCESS FULL| CUSTOMERS |      1 |    287 |    250 |00:00:00.02 |    1521 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("CUST_CITY"='Palmdale' AND "CUST_STATE_PROVINCE"='FL' AND
              "COUNTRY_ID"=52790))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - 1 Sql Plan Directive used for this statement


27 rows selected.

And NOW you get the update to REPORT_COL_USAGE:

SQL> select dbms_stats.report_col_usage(user, 'CUSTOMERS') from dual;

DBMS_STATS.REPORT_COL_USAGE(USER,'CUSTOMERS')
--------------------------------------------------------------------------------
LEGEND:
.......

EQ         : Used in single table EQuality predicate
RANGE      : Used in single table RANGE predicate
LIKE       : Used in single table LIKE predicate
NULL       : Used in single table is (not) NULL predicate
EQ_JOIN    : Used in EQuality JOIN predicate
NONEQ_JOIN : Used in NON EQuality JOIN predicate
FILTER     : Used in single table FILTER predicate
JOIN       : Used in JOIN predicate
GROUP_BY   : Used in GROUP BY expression
...............................................................................

###############################################################################

COLUMN USAGE REPORT FOR MARTIN.CUSTOMERS
........................................

1. COUNTRY_ID                          : EQ
2. CUST_CITY                           : EQ
3. CUST_STATE_PROVINCE                 : EQ
4. (CUST_CITY, CUST_STATE_PROVINCE,
    COUNTRY_ID)                        : FILTER
###############################################################################

From here on it’s the same as in part 1 of this article. You gather stats, either manually like me or automatically like Oracle would, and the end result are extended statistics on the “filter” shown in line 4.


SQL> exec dbms_stats.gather_table_stats(user, 'CUSTOMERS')

PL/SQL procedure successfully completed.

SQL> select table_name, extension_name, extension from dba_stat_extensions where owner = user;

TABLE_NAME                     EXTENSION_NAME                           EXTENSION
------------------------------ ---------------------------------------- --------------------------------------------------------------------------------
CUSTOMERS                      SYS_STSMZ$C3AIHLPBROI#SKA58H_N           ("CUST_CITY","CUST_STATE_PROVINCE","COUNTRY_ID")

Voila! Extended statistics.

There are many more interesting implications to this whole concept, which is something I’ll write about in another post. It’s incredibly interesting, I can’t believe I’m writing optimiser posts …

PS: thanks to @Mautro for casting an eye over this article!

Posted in 12c Release 1, Performance | 1 Comment »

Little things worth knowing: automatic generation of extended statistics in 12c

Posted by Martin Bach on October 14, 2015

When you are migrating to Oracle 12c I hope you might this post useful. I came across this feature when researching what’s new with Oracle 12c (and yes I still find lots of new ones I haven’t noticed before). This one is a bit hidden away in section 2.2.4.3 Automatic Column Group Detection of the 12c New Features Guide. And it’s a lot more complex than I first thought! In this first post I’ll try and show the generation of extended statistics in 12c. I am planning on another post to explain how the rest of the adaptive optimisations that are new with 12c fit into the picture.

What is the motivation?

Previously, in Oracle versions up to 12c you needed to be on the lookout for candidates for correlated columns. Extended statistics on groups of columns allow the optimiser to come up with better cardinality estimates if columns in a table are correlated. Instead of me trying to explain the concept in depth I’d like to link to a post written by Maria Colgan. I strongly recommend you have a look at this post if you haven’t used extended statistics a lot.

When putting my post together I referred to the same SH.CUSTOMERS table as Maria. The queries I am using are based on another excellent post by Maria Colgan that builds on the foundations of the one I just referenced. In fact I really needed a data set that had correlated columns but I couldn’t come up with an example that was easy-to-follow until I found that blog entry. I suggest you have a look at this post first as it explains a nifty tool for helping you finding candidates for extended statistics in 11.2. Oracle 12c takes this concept a step further as you will see.

My test case

My test environment is 12.1.0.2.3 on Exadata (but that shouldn’t matter), and this is how it goes. First I create a table in my schema which is an exact replica of the table in the SH schema.

SQL> create table martin.customers as select * from sh.customers;

Table created.

The next step is to tell Oracle to monitor column usage. I have to accelerate this a bit, Oracle does this over the cause of the day anyway.

SQL> exec dbms_stats.seed_col_usage(null,null,300)

PL/SQL procedure successfully completed.

By calling seed_col_usage() without a SQLSET and OWNER I can report column usage. The PL/SQL API documentation reads:

This procedure also records group of columns. Extensions for the recorded group of columns can be created using the CREATE_EXTENDED_STATS Function procedure. If sqlset_name and owner_name are NULL, it records the column (group) usage information for the statements executed in the system in next time_limit seconds.

That sounds like what is needed. To start with a level playing field I gather statistics again using dbms_stats.gather_table_stats(user, ‘customers’).

Let there be queries

The next step is to run a few queries. Cardinality estimates are not correct-the optimiser does not “know” that L.A. can only be in California in this example. Pay attention to the number of rows returned and the cardinality estimates:

SQL> select count(*) from customers
  2  where cust_city = 'Los Angeles'
  3  and cust_state_province = 'CA'
  4  and country_id = 52790;

  COUNT(*)
----------
       932

Elapsed: 00:00:00.03
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  ap71092cqnj1y, child number 0
-------------------------------------
select count(*) from customers where cust_city = 'Los Angeles' and
cust_state_province = 'CA' and country_id = 52790

Plan hash value: 296924608

----------------------------------------------------------------------------------------
| Id  | Operation                  | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |           |       |       |   423 (100)|          |
|   1 |  SORT AGGREGATE            |           |     1 |    26 |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL| CUSTOMERS |     1 |    26 |   423   (1)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - storage(("CUST_CITY"='Los Angeles' AND "CUST_STATE_PROVINCE"='CA' AND
              "COUNTRY_ID"=52790))
       filter(("CUST_CITY"='Los Angeles' AND "CUST_STATE_PROVINCE"='CA' AND
              "COUNTRY_ID"=52790))

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

27 rows selected.

Following advice I have been given by people who know a lot more about SQL tuning than I will ever comprehend, I gather execution statistics and then display actual and expected rows:

SQL> select /*+ gather_plan_statistics */ count(*) from customers
  2  where cust_city = 'Los Angeles'
  3  and cust_state_province = 'CA'
  4  and country_id = 52790;

  COUNT(*)
----------
       932

SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  0qmjk7qm3zwkw, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from customers  where
cust_city = 'Los Angeles'  and cust_state_province = 'CA'  and
country_id = 52790

Plan hash value: 296924608

--------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |           |      1 |        |      1 |00:00:00.01 |    1521 |
|   1 |  SORT AGGREGATE            |           |      1 |      1 |      1 |00:00:00.01 |    1521 |
|*  2 |   TABLE ACCESS STORAGE FULL| CUSTOMERS |      1 |      1 |    932 |00:00:00.01 |    1521 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - storage(("CUST_CITY"='Los Angeles' AND "CUST_STATE_PROVINCE"='CA' AND
              "COUNTRY_ID"=52790))
       filter(("CUST_CITY"='Los Angeles' AND "CUST_STATE_PROVINCE"='CA' AND
              "COUNTRY_ID"=52790))

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

The number of actual rows in plan line 2 is quite different from what the optimiser expected it to be.

I also executed the next example from Maria’s blog post:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  5h284vjm2xtp9, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ country_id, cust_state_province,
count(*) from customers group by country_id, cust_state_province

Plan hash value: 1577413243

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |           |      1 |        |    145 |00:00:00.02 |    1521 |       |       |          |
|   1 |  HASH GROUP BY             |           |      1 |   1949 |    145 |00:00:00.02 |    1521 |  1015K|  1015K| 1371K (0)|
|   2 |   TABLE ACCESS STORAGE FULL| CUSTOMERS |      1 |  55500 |  55500 |00:00:00.02 |    1521 |  1025K|  1025K|          |
-----------------------------------------------------------------------------------------------------------------------------

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

Again cardinality estimates differ in plan line 1: instead of 1949 rows the optimiser expects to be returned there are only 145. This is a simple example, but quite often incorrect cardinality estimates lead to sub-optimal plan generation (although 12c has another new feature that tries to mitigate the effects of these incorrect cardinality estimates as well)

Column Groups

On the other hand the column usage monitor picked up information based on the “workload”. You can see this in sys.col_group_usage$

SQL> select u.*, o.object_name, object_type
  2  from sys.col_group_usage$ u, dba_objects o
  3  where u.obj# = o.data_object_id
  4  and owner = 'MARTIN';

      OBJ# COLS                           TIMESTAMP      FLAGS OBJECT_NAME                    OBJECT_TYPE
---------- ------------------------------ --------- ---------- ------------------------------ -----------------------
    111118 11,13                          26-JUN-15         36 CUSTOMERS                      TABLE
    111118 9,11,13                        26-JUN-15         33 CUSTOMERS                      TABLE

This is of course not the way to view this information-there’s an API for that.

SQL> select dbms_stats.report_col_usage(user, 'CUSTOMERS') from dual;

DBMS_STATS.REPORT_COL_USAGE(USER,'CUSTOMERS')
----------------------------------------------------------------------------------------------
LEGEND:
.......

EQ         : Used in single table EQuality predicate
RANGE      : Used in single table RANGE predicate
LIKE       : Used in single table LIKE predicate
NULL       : Used in single table is (not) NULL predicate
EQ_JOIN    : Used in EQuality JOIN predicate
NONEQ_JOIN : Used in NON EQuality JOIN predicate
FILTER     : Used in single table FILTER predicate
JOIN       : Used in JOIN predicate
GROUP_BY   : Used in GROUP BY expression
...............................................................................

###############################################################################

COLUMN USAGE REPORT FOR MARTIN.CUSTOMERS
........................................

1. COUNTRY_ID                          : EQ
2. CUST_CITY                           : EQ
3. CUST_STATE_PROVINCE                 : EQ
4. (CUST_CITY, CUST_STATE_PROVINCE,
    COUNTRY_ID)                        : FILTER
5. (CUST_STATE_PROVINCE, COUNTRY_ID)   : GROUP_BY
###############################################################################

So there is a potential candidates for extended stats in line 5. So far, nothing new. In version before 12c you now had to create the column groups manually. Again, please refer to Maria’s post for an example.

The difference in 12c

In 12c you don’t need to create these column groups manually. Consider this sequence of events:

SQL> select * from dba_stat_extensions where owner = 'MARTIN';

no rows selected

Elapsed: 00:00:00.28

SQL> exec dbms_stats.gather_table_stats(user,'CUSTOMERS')

PL/SQL procedure successfully completed.

SQL> select * from user_stat_extensions

TABLE_NAME                     EXTENSION_NAME                 EXTENSION                                          CREATO DRO
------------------------------ ------------------------------ -------------------------------------------------- ------ ---
CUSTOMERS                      SYS_STU#S#WF25Z#QAHIHE#MOFFMM_ ("CUST_STATE_PROVINCE","COUNTRY_ID")               USER   YES
CUSTOMERS                      SYS_STUMZ$C3AIHLPBROI#SKA58H_N ("CUST_CITY","CUST_STATE_PROVINCE","COUNTRY_ID")   USER   YES

2 rows selected.

SQL> SELECT column_name,
  2    num_distinct,
  3    num_buckets,
  4    histogram
  5  FROM dba_tab_col_statistics
  6  WHERE table_name = 'CUSTOMERS'
  7  AND owner        = 'MARTIN'
  8  AND column_name LIKE 'SYS%';

COLUMN_NAME                    NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------------------------ ------------ ----------- ---------------
SYS_STUMZ$C3AIHLPBROI#SKA58H_N          620         254 HYBRID
SYS_STU#S#WF25Z#QAHIHE#MOFFMM_          145           1 NONE

2 rows selected.

So in the above output you can see that there are no extended stats on the CUSTOMERS table before gathering stats. There is nothing special about the stats gathering command, the preferences are the defaults (reformatted here for readability)

SQL> l
  1  declare
  2    type prefs_t is table of varchar2(150);
  3    prefs prefs_t := prefs_t(
  4      'AUTOSTATS_TARGET','CASCADE','CONCURRENT','DEGREE','ESTIMATE_PERCENT',
  5      'METHOD_OPT','NO_INVALIDATE','GRANULARITY','PUBLISH','INCREMENTAL',
  6      'INCREMENTAL_STALENESS','INCREMENTAL_LEVEL','STALE_PERCENT',
  7      'GLOBAL_TEMP_TABLE_STATS','TABLE_CACHED_BLOCKS','OPTIONS');
  8    prefs_value varchar2(100);
  9  begin
 10    for p in prefs.first .. prefs.last loop
 11      select dbms_stats.get_prefs(prefs(p), user,'CUSTOMERS') into prefs_value from dual;
 12      dbms_output.put_line(prefs(p) || ': ' || prefs_value);
 13    end loop;
 14* end;
SQL> /
AUTOSTATS_TARGET:        AUTO
CASCADE:                 DBMS_STATS.AUTO_CASCADE
CONCURRENT:              MANUAL
DEGREE:                  NULL
ESTIMATE_PERCENT:        DBMS_STATS.AUTO_SAMPLE_SIZE
METHOD_OPT:              FOR ALL COLUMNS SIZE AUTO
NO_INVALIDATE:           DBMS_STATS.AUTO_INVALIDATE
GRANULARITY:             AUTO
PUBLISH:                 TRUE
INCREMENTAL:             FALSE
INCREMENTAL_STALENESS:
INCREMENTAL_LEVEL:       PARTITION
STALE_PERCENT:           10
GLOBAL_TEMP_TABLE_STATS: SESSION
TABLE_CACHED_BLOCKS:     1
OPTIONS:                 GATHER

PL/SQL procedure successfully completed.

Did you see the extended stats have histograms? There is even one of the new fancy “hybrid” ones. The effect is noticeable:

SQL> select /*+ gather_plan_statistics */ count(*) from customers
  2   where cust_city = 'Los Angeles'
  3   and cust_state_province = 'CA'
  4   and country_id = 52790;

SQL> select * from table(dbms_xplan.display_cursor(format => 'allstats last'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
SQL_ID  0qmjk7qm3zwkw, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from customers  where
cust_city = 'Los Angeles'  and cust_state_province = 'CA'  and
country_id = 52790

Plan hash value: 296924608

--------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |           |      1 |        |      1 |00:00:00.01 |    1521 |
|   1 |  SORT AGGREGATE            |           |      1 |      1 |      1 |00:00:00.01 |    1521 |
|*  2 |   TABLE ACCESS STORAGE FULL| CUSTOMERS |      1 |    969 |    932 |00:00:00.01 |    1521 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - storage(("CUST_CITY"='Los Angeles' AND "CUST_STATE_PROVINCE"='CA' AND
              "COUNTRY_ID"=52790))
       filter(("CUST_CITY"='Los Angeles' AND "CUST_STATE_PROVINCE"='CA' AND
              "COUNTRY_ID"=52790))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - 1 Sql Plan Directive used for this statement

The cardinality estimate is a lot closer now. What surprised me was the SQL Plan Directive referenced. But this is material for another blog post.

Posted in 12c Release 1, Linux, Performance | 7 Comments »

Example of Full Transportable Export to create a 12c PDB

Posted by Martin Bach on October 8, 2015

The Cool Stuff

Oracle has introduced a new way of transporting data from one platform to another in 12c. The new feature is called “Full Transportable Export”. This enhancement works from 11.2.0.3 and later and is a great way to move data, as it allows for an easy-to-use combination of Export Data Pump and Transportable Tablespaces. The documentation specifically praises it as a means to move from an 11g Release 2 database into a 12c Pluggable Database. And Pluggable Databases I like :)

Unfortunately the Full Transportable Export/Import process does not perform endianness conversion for you. And in fact, if you have encrypted data you want to move you cannot do so unless you are on the same endianness.

Limitations

Before we begin, here is a list of restrictions pertaining to the new Full Transportable Export, taken from the 12c Utilities Guide:

  • You must use a privileged account, at least DATAPUMP_EXP_FULL_DATABASE must be granted to user. That user’s default tablespace must not be part of the transport set
  • Encrypted data can be transported only between same endianness.
  • Endianness conversion not transparently taken care off: you must do so using dbms_file_transfer or RMAN convert
  • Export is not restartable
  • Objects to be transported must be on user-created tablespaces
  • LONG and LONG RAW objects in administrative tablespaces (SYSTEM, SYSAUX) cannot be transported across the network
  • All the user defined tablespaces must be placed in read-only mode: this is an outage!

There might be more, check the documentation for an updated version.

Let’s test

In order to assess the suitability of the concept, two users have been created in an Oracle 11.2.0.3 database on Solaris/Intel. I would have liked to test the procedure with a big endian source but my last SPARC box was a Sun Blade 100 from 10 years ago that finally went to the skip, or in other words I don’t have a SPARC server available in my lab right now…

To make it more interesting I am going to migrate these accounts into a 12.1.0.2 PDB on Linux. The source database is 11.2.0.3 with the April 2015 database PSU. Here is the data creation part in case you would like to follow the demo. First the Order Entry schema, part of the Swingbench suite is created.

oracle@solaris:~/swingbench/bin$ ./oewizard -allindexes -cl -create -cs //solaris/sol11203 -dba system \
-dbap secretpwd -ts soe_tbs -part -p soe -scale 1 -tc 2 -u soe
SwingBench Wizard
Author  :        Dominic Giles
Version :        2.5.0.971

Running in Lights Out Mode using config file : oewizard.xml
The following statement failed : GRANT EXECUTE ON dbms_lock TO soe :
   Due to : ORA-01031: insufficient privileges

============================================
|           Datagenerator Run Stats        |
============================================
Connection Time                        0:00:00.002
Data Generation Time                   0:06:53.268
DDL Creation Time                      0:03:17.259
Total Run Time                         0:10:10.533
Rows Inserted per sec                       29,294
Data Generated (MB) per sec                    2.4
Actual Rows Generated                   13,004,989

Post Creation Validation Report
===============================
The schema appears to have been created successfully.

Valid Objects
=============
Valid Tables : 'ORDERS','ORDER_ITEMS','CUSTOMERS','WAREHOUSES','ORDERENTRY_METADATA',
'INVENTORIES','PRODUCT_INFORMATION','PRODUCT_DESCRIPTIONS','ADDRESSES','CARD_DETAILS'
Valid Indexes : 'PRD_DESC_PK','PROD_NAME_IX','PRODUCT_INFORMATION_PK','PROD_SUPPLIER_IX',
'PROD_CATEGORY_IX','INVENTORY_PK','INV_PRODUCT_IX','INV_WAREHOUSE_IX','ORDER_PK',
'ORD_SALES_REP_IX','ORD_CUSTOMER_IX','ORD_ORDER_DATE_IX','ORD_WAREHOUSE_IX',
'ORDER_ITEMS_PK','ITEM_ORDER_IX','ITEM_PRODUCT_IX','WAREHOUSES_PK','WHS_LOCATION_IX',
'CUSTOMERS_PK','CUST_EMAIL_IX','CUST_ACCOUNT_MANAGER_IX','CUST_FUNC_LOWER_NAME_IX',
'ADDRESS_PK','ADDRESS_CUST_IX','CARD_DETAILS_PK','CARDDETAILS_CUST_IX'
Valid Views : 'PRODUCTS','PRODUCT_PRICES'
Valid Sequences : 'CUSTOMER_SEQ','ORDERS_SEQ','ADDRESS_SEQ','LOGON_SEQ','CARD_DETAILS_SEQ'
Valid Code : 'ORDERENTRY'
Schema Created

The Swingbench benchmark suite does not need introducing any more. I have used it many times and am truly grateful for @dominic_giles having written this for the Oracle community.

About to cause grief-deliberately

A second user is created to test all sorts of segment types to see how these are handled. All data is bundled under the MARTIN account. The idea is to test against (some of) the limitations documented against Transportable Tablespaces, which is invoked under the covers. It would be nice if segments that cannot be transported using TTS were part of the export dump-let’s see if that is going to be the case. And although the transport might work without problems for me, it will do so because I tested it the way I did. It does not mean that for example objects using XMLDB will always be migrated successfully! As always, test, test, test … with your systems. There is nothing like a few dummy runs to hone in the procedures.

Back to the user creation:

SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.3

SQL> create user martin identified by secretpwd quota unlimited on users
  2  /

User created.

SQL> begin
  2   for i in 1..10 loop
  3    execute immediate 'create or replace procedure martin.p' || i || ' as begin null; end;';
  4   end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select owner, object_name from dba_objects
  2  where owner = 'MARTIN'
  3* and object_name like 'P%'

OWNER                          OBJECT_NAME
------------------------------ ------------------------------
MARTIN                         P9
MARTIN                         P8
MARTIN                         P7
MARTIN                         P6
MARTIN                         P5
MARTIN                         P4
MARTIN                         P3
MARTIN                         P2
MARTIN                         P10
MARTIN                         P1

10 rows selected.

SQL> create table martin.xmltest of xmltype tablespace users;

Table created.

SQL> insert into martin.xmltest values ('<toto/>');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from martin.xmltest;

SYS_NC_ROWINFO$
--------------------------------------------------------------------------------
<toto/>

SQL> create table martin.iot_test (
  2    object_id,
  3    owner,
  4    object_name,
  5    subobject_name,
  6    object_type,
  7    constraint pk_iot_test primary key (object_id))
  8  organization index tablespace users as
  9  select object_id,owner,object_name,subobject_name,
 10  object_type from dba_objects;

Table created.

SQL> create table martin.lobtest (
  2   id number primary key,
  3   l_data clob)
  4  lob (l_data)
  5 store as secrefile;

Table created.

SQL> insert into martin.lobtest values (1, 'firstlob');

1 row created.

SQL> commit;

Commit complete.

SQL> select table_name, column_name,in_row, tablespace_name
  2  from dba_lobs
  3* where owner = 'MARTIN'

TABLE_NAME           COLUMN_NAME                    IN_ TABLESPACE_NAME
-------------------- ------------------------------ --- --------------------
XMLTEST              XMLDATA                        YES USERS
LOBTEST              L_DATA                         YES USERS

SQL> create table martin.tztest (id number primary key, d timestamp with local time zone)
  2   tablespace users;

SQL> insert into martin.tztest values (1, systimestamp);

SQL> commit;

Commit complete

That’s all I could think of. I may have added nested tables but I am not aware of their use in an Oracle application outside of PL/SQL. Now in this place I am deliberately causing trouble, and I am also working against the limitations and recommendations taken from the documentation. The following is documented behaviour, my interest was to see how it actually works (and also give you a clue when you search the Internet for error messages).

The Migration

Before migrating the database you need to check if the data is self-contained. Since there are two tablespaces to be migrated, I need to check them both.


SQL> select name,PLATFORM_NAME from v$database

NAME      PLATFORM_NAME
--------- --------------------------------------------------
SOL11203  Solaris Operating System (x86-64)

SQL> exec sys.dbms_tts.transport_set_check('users,soe_tbs',true)

PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;

no rows selected

Just as with “regular” transportable tablespaces, you need to create a directory to store the export dump and put the tablespaces to, or use the default DATA_PUMP_DIR.

You then need to place the user-defined tablespaces into read-only mode. Essentially all non-Oracle managed tablespaces are affected, causing an outage at this point.


SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
SOE_TBS

6 rows selected.

SQL> alter tablespace users read only;

Tablespace altered.

SQL> c.users.soe_tbs
  1* alter tablespace soe_tbs read only
SQL> r
  1* alter tablespace soe_tbs read only

Tablespace altered.

The exciting bit is when you start the export. Here is a sample command:

oracle@solaris:~/full_tts$ expdp system full=y transportable=always version=12 directory=full_tts_dir \
> dumpfile=exp_full_transportable.dmp logfile=exp_full_transportable.log 

Export: Release 11.2.0.3.0 - Production on Wed Oct 7 21:04:10 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/******** full=y  
 transportable=always version=12 directory=full_tts_dir 
 dumpfile=exp_full_transportable.dmp 
 logfile=exp_full_transportable.log 
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/PLUGTS_FULL/FULL/PLUGTS_TABLESPACE
Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 80.43 MB
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
...
. . exported "SYSTEM"."REPCAT$_REPCATLOG"                    0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_REPCOLUMN"                    0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_REPGROUP_PRIVS"               0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_REPOBJECT"                    0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_REPPROP"                      0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_REPSCHEMA"                    0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_RESOLUTION"                   0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_RESOLUTION_STATISTICS"        0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_RESOL_STATS_CONTROL"          0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_RUNTIME_PARMS"                0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_SITES_NEW"                    0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_SITE_OBJECTS"                 0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_SNAPGROUP"                    0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_OBJECTS"             0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_PARMS"               0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_REFGROUPS"           0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_SITES"               0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_TARGETS"             0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_USER_AUTHORIZATIONS"          0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_USER_PARM_VALUES"             0 KB       0 rows
. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE"              0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
  /export/home/oracle/full_tts/exp_full_transportable.dmp
******************************************************************************
Datafiles required for transportable tablespace SOE_TBS:
  /u01/oradata/SOL11203/datafile/o1_mf_soe_tbs_bomhd219_.dbf
Datafiles required for transportable tablespace USERS:
  /u01/oradata/SOL11203/datafile/o1_mf_users_bolbn3sn_.dbf
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at 21:09:33

A quick check on the logfile revealed that objects owned by XDB, SYS, WMSYS, SYSTEM, APEX, SYSMAN, OLAPSYS, ORDDATA were exported. I did not see any non-Oracle provided objects exported in the list of tables. So the import will be interesting!

The next step is to move the dump and data files across to the target system. In this example NFS is used, but any other efficient means of transportation will do.

Importing into a PDB

In preparation for the metadata import you need to either create a directory or point impdp to an existing one. In the example, FULL_TTS_DIR is used. The directory points to the NFS mount. The files stored in the directory include the data files (/u01/oradata/SOL11203/datafile/o1_mf_soe_tbs_bomhd219_.dbf and /u01/oradata/SOL11203/datafile/o1_mf_users_bolbn3sn_.dbf plus the dump file exp_full_transportable.dmp). Since both systems are little endian no conversion is necessary at this stage. Before starting here is some information about the destination. The PDB created for the import is named SOE_PDB.

SQL> select name,cdb, platform_name from v$database;

NAME      CDB PLATFORM_NAME
--------- --- ----------------------------------------------------------------------------------
CDB       YES Linux x86 64-bit

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 MASTER                         MOUNTED

SQL> create pluggable database soe_pdb admin user pdb_admin
  2  identified by secretpwd;

Pluggable database created.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 MASTER                         MOUNTED
         4 SOE_PDB                        MOUNTED

SQL> alter pluggable database SOE_PDB open ;

Pluggable database altered.

Since the database is using ASM the data files first have to be moved into ASM before they can be made known to the destination. The files originate from little endian Solaris/Intel so no conversion is needed. There are many ways to move data files to ASM, and asmcmd is one of them. The file naming convention on ASM for PDBs is diskgroupName/CDBName/PDBGUID/datafile/fileName.dbf’. The GUID can be found in v$pdbs for example.

SQL> select guid, name from v$pdbs where name = 'SOE_PDB';

GUID                             NAME
-------------------------------- ------------------------------
218B3FD3B43B146EE0531438A8C06EE5 SOE_PDB

[oracle@oraclelinux7 full_tts_dir]$ asmcmd cp /m/oracle/full_tts_dir/o1_mf_soe_tbs_bomhd219_.dbf  +data/cdb/218B3FD3B43B146EE0531438A8C06EE5/datafile/soe_tbs.dbf
copying /m/oracle/full_tts_dir/o1_mf_soe_tbs_bomhd219_.dbf -> +data/cdb/218B3FD3B43B146EE0531438A8C06EE5/datafile/soe_tbs.dbf
[oracle@oraclelinux7 full_tts_dir]$ asmcmd cp /m/oracle/full_tts_dir/o1_mf_users_bolbn3sn_.dbf +data/cdb/218B3FD3B43B146EE0531438A8C06EE5/datafile/users.dbf
copying /m/oracle/full_tts_dir/o1_mf_users_bolbn3sn_.dbf -> +data/cdb/218B3FD3B43B146EE0531438A8C06EE5/datafile/users.dbf
[oracle@oraclelinux7 full_tts_dir]$ 

Although the data files are now in ASM, they are not part of the database yet. This is done in the next step.

[oracle@oraclelinux7 full_tts_dir]$ impdp system/secretpwd@localhost/SOE_PDB full=y \
> dumpfile=exp_full_transportable.dmp directory=FULL_TTS_DIR \
> transport_datafiles= \
> '+data/cdb/218B3FD3B43B146EE0531438A8C06EE5/datafile/soe_tbs.dbf', \
> '+data/cdb/218B3FD3B43B146EE0531438A8C06EE5/datafile/users.dbf' \
> logfile=imp_full_transportable.log

Import: Release 12.1.0.2.0 - Production on Wed Oct 7 22:40:55 2015

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Source time zone is +00:00 and target time zone is -07:00.
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@localhost/SOE_PDB 
  full=y dumpfile=exp_full_transportable.dmp directory=FULL_TTS_DIR 
  transport_datafiles=+data/cdb/218B3FD3B43B146EE0531438A8C06EE5/datafile/soe_tbs.dbf, 
  +data/cdb/218B3FD3B43B146EE0531438A8C06EE5/datafile/users.dbf  
  logfile=imp_full_transportable.log 
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-39083: Object type TABLESPACE:"UNDOTBS1" failed to create with error:
ORA-01516: nonexistent log file, data file, or temporary file "/u01/oradata/SOL11203/datafile/o1_mf_undotbs1_bolbn3ro_.dbf"
Failing sql is:
 ALTER DATABASE DATAFILE '/u01/oradata/SOL11203/datafile/o1_mf_undotbs1_bolbn3ro_.dbf' RESIZE 844103680
ORA-31684: Object type TABLESPACE:"TEMP" already exists
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
...
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOU/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
ORA-39360: Table "SOE"."ORDERS" was skipped due to transportable import and TSLTZ issues resulting from time zone mismatch.
ORA-39151: Table "ORDDATA"."ORDDCM_INTERNAL_TAGS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "ORDDATA"."ORDDCM_DOC_TYPES" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "OLAPSYS"."XML_LOAD_LOG" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39360: Table "MARTIN"."TZTEST" was skipped due to transportable import and TSLTZ issues resulting from time zone mismatch.
ORA-39151: Table "ORDDATA"."ORDDCM_MAPPING_DOCS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39083: Object type TABLE:"MARTIN"."XMLTEST" failed to create with error:
ORA-39945: Token conflicting with existing tokens.
Failing sql is:
CREATE TABLE "MARTIN"."XMLTEST" OF XMLTYPE  OID '165A17E90A5B0857E054080027F26450'  XMLTYPE STORE AS SECUREFILE BINARY XML ( TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 NOCACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES  STORAGE(SEG_FILE 4 SEG_BLOCK 177 OBJNO_REUSE 76699 INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL DEFAU
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "APEX_030200"."WWV_FLOW_PAGE_PLUGS"         3.834 MB    7416 rows


Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
ORA-39112: Dependent object type INDEX:"SOE"."ORDER_PK" skipped, base object type TABLE:"SOE"."ORDERS" creation failed
ORA-39112: Dependent object type INDEX:"SOE"."ORD_SALES_REP_IX" skipped, base object type TABLE:"SOE"."ORDERS" creation failed
ORA-39112: Dependent object type INDEX:"SOE"."ORD_CUSTOMER_IX" skipped, base object type TABLE:"SOE"."ORDERS" creation failed
ORA-39112: Dependent object type INDEX:"SOE"."ORD_ORDER_DATE_IX" skipped, base object type TABLE:"SOE"."ORDERS" creation failed
ORA-39112: Dependent object type INDEX:"SOE"."ORD_WAREHOUSE_IX" skipped, base object type TABLE:"SOE"."ORDERS" creation failed
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX

The import command took a fair amount of time to complete, but this is at least partially so because of the underlying hardware which could do with 2 more cores. Or 4 actually … As expected, some errors were thrown by the process. As stated in the documentation timestamp with local time zone tables will not be transportable using TTS. This is visible from the log, the subsequent errors are a result of this. You can’t create an index on a non-existing table.

[oracle@oraclelinux7 full_tts_dir]$ grep SOE imp_full_transportable.log
ORA-39360: Table "SOE"."ORDERS" was skipped due to transportable import and TSLTZ issues resulting from time zone mismatch.
ORA-39082: Object type PACKAGE:"SOE"."ORDERENTRY" created with compilation warnings
ORA-39112: Dependent object type INDEX:"SOE"."ORDER_PK" skipped, base object type TABLE:"SOE"."ORDERS" creation failed
ORA-39112: Dependent object type INDEX:"SOE"."ORD_SALES_REP_IX" skipped, base object type TABLE:"SOE"."ORDERS" creation failed
ORA-39112: Dependent object type INDEX:"SOE"."ORD_CUSTOMER_IX" skipped, base object type TABLE:"SOE"."ORDERS" creation failed
ORA-39112: Dependent object type INDEX:"SOE"."ORD_ORDER_DATE_IX" skipped, base object type TABLE:"SOE"."ORDERS" creation failed
ORA-39112: Dependent object type INDEX:"SOE"."ORD_WAREHOUSE_IX" skipped, base object type TABLE:"SOE"."ORDERS" creation failed
ORA-39112: Dependent object type CONSTRAINT:"SOE"."ORDER_MODE_LOV" skipped, base object type TABLE:"SOE"."ORDERS" creation failed
ORA-39112: Dependent object type CONSTRAINT:"SOE"."ORDER_PK" skipped, base object type TABLE:"SOE"."ORDERS" creation failed
ORA-39112: Dependent object type CONSTRAINT:"SOE"."ORDER_TOTAL_MIN" skipped, base object type TABLE:"SOE"."ORDERS" creation failed
ORA-39083: Object type REF_CONSTRAINT:"SOE"."ORDER_ITEMS_ORDER_ID_FK" failed to create with error:
ALTER TABLE "SOE"."ORDER_ITEMS" ADD CONSTRAINT "ORDER_ITEMS_ORDER_ID_FK" FOREIGN KEY ("ORDER_ID") REFERENCES "SOE"."ORDERS" ("ORDER_ID") ON DELETE CASCADE ENABLE NOVALIDATE
ORA-39112: Dependent object type REF_CONSTRAINT:"SOE"."ORDERS_CUSTOMER_ID_FK" skipped, base object type TABLE:"SOE"."ORDERS" creation failed
ORA-39082: Object type PACKAGE BODY:"SOE"."ORDERENTRY" created with compilation warnings

In addition to that some of the objects created in the MARTIN schema did not survive the operation either:

ORA-31684: Object type USER:"MARTIN" already exists
ORA-39360: Table "MARTIN"."TZTEST" was skipped due to transportable import and TSLTZ issues resulting from time zone mismatch.
ORA-39083: Object type TABLE:"MARTIN"."XMLTEST" failed to create with error:
CREATE TABLE "MARTIN"."XMLTEST" OF XMLTYPE  OID '165A17E90A5B0857E054080027F26450'  XMLTYPE
 STORE AS SECUREFILE BINARY XML ( TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 NOCACHE
 LOGGING  NOCOMPRESS  KEEP_DUPLICATES  STORAGE(SEG_FILE 4 SEG_BLOCK 177 OBJNO_REUSE 76699
 INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL
 DEFAU
ORA-39112: Dependent object type CONSTRAINT:"MARTIN"."SYS_C0011162" skipped, base object
 type TABLE:"MARTIN"."TZTEST" creation failed

You can’t blame Oracle for that, this is expected behaviour! On the other hand it does also imply that the process is indeed a wrapper around (cross-platform) transportable tablespaces. Two out of four tables from the MARTIN schema made it across: LOBTEST and IOT_TEST. The XML table did not, and neither did the one with the timestamp with local time zone. When using the new Full Tranportable Export/Import, be aware of what the tool can and cannot do!

The white paper at http://www.oracle.com/technetwork/database/enterprise-edition/full-transportable-wp-12c-1973971.pdf also states that beginning with 12c administrative information stored on SYSTEM and SYSAUX is neither exported nor imported. This probably applies to a 12c->12c migration. In my log file I can see a number of account creation commands, or rather, how they failed.

[oracle@oraclelinux7 ~]$ egrep -i "object type user.*already exists" imp_full_transportable.log
ORA-31684: Object type USER:"OUTLN" already exists
ORA-31684: Object type USER:"ORDDATA" already exists
ORA-31684: Object type USER:"OLAPSYS" already exists
ORA-31684: Object type USER:"MDDATA" already exists
ORA-31684: Object type USER:"SPATIAL_WFS_ADMIN_USR" already exists
ORA-31684: Object type USER:"SPATIAL_CSW_ADMIN_USR" already exists
ORA-31684: Object type USER:"FLOWS_FILES" already exists
ORA-31684: Object type USER:"APEX_PUBLIC_USER" already exists

Time permitting I will try again with a 12c non-CDB and import into a 12c PDB.

Posted in 11g Release 2, 12c Release 1 | Tagged: | 2 Comments »

CloneDB in Oracle 12.1.0.2

Posted by Martin Bach on August 4, 2015

I personally really like CloneDB, a way to thin-clone an Oracle database over NFS. This can be quite interesting, and I wanted to update my blog for 12.1.0.2.3 (April PSU). Tim Hall has a good example for 11.2.0.2 and later with further references.

My setup is as follows:

  • server3 (Oracle Linux 7.1) uses Oracle Restart and has database CDB1 registered. I would like to use this as the source for the clone
  • The backup I will take of CDB1 resides in /u01/oraback/CDB1
  • /u01/oraback is NFS-exported on server3 to server4 (Oracle Linux 7.1)
  • This directory is mounted on server4 as /u01/oraback
  • Oracle 12.1.0.2.3 (April 2015 PSU) is used throughout
  • The Oracle accounts on server3 and server4 have been created using the preinstall RPM, and have the same user and group IDs. You will find this important, and your Oracle installation standards document should enforce common user and group IDs not only for RAC where this is mandatory

Step 1: backing up CDB1

In the first step I am taking a full cold backup of CDB1. Data files are in ASM, and there is 1 PDB defined in the CDB. Here is the script I used for the backup:

[oracle@server3 ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Tue Aug 4 11:15:11 2015

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

connected to target database: CDB1 (DBID=853309103, not open)

RMAN> run {
2> allocate channel c1 device type disk format '/u01/oraback/CDB1/%U';
3> allocate channel c2 device type disk format '/u01/oraback/CDB1/%U';
4> set nocfau;
5> backup as copy database;
6> }

allocated channel: c1
channel c1: SID=15 device type=DISK

allocated channel: c2
channel c2: SID=252 device type=DISK

executing command: SET NOCFAU

Starting backup at 04-AUG-15
channel c1: starting datafile copy
input datafile file number=00001 name=+DATA/CDB1/DATAFILE/system.273.879591527
...
Finished backup at 04-AUG-15
released channel: c1
released channel: c2

CloneDB requires me to provide the init.ora file as well, which I also place on the NFS mount (“create pfile=’/u01/oraback/CDB1/initCDB1.orig’ from spfile” does the trick)

That should be enough for now, I can resume work on server4.

Setup of server4

The first step is to ensure that dNFS is set up. Instead of repeating myself here I’ll simply link to a previous post where I explained how I did this for 12c. The actual line in /etc/fstab is this:

server3:/u01/oraback/   /u01/oraback    nfs     rw,bg,hard,nointr,tcp,vers=3,timeo=600,rsize=32768,wsize=32768 1 2

Check MOS and your NFS appliance vendor’s support site for your specific settings.

The oranfstab’s contents is shown here for reference:

[oracle@server4 lib]$ cat $ORACLE_HOME/dbs/oranfstab
server: server1
local: 192.168.100.13
path:  192.168.100.12
export: /u01/oraback mount: /u01/oraback

Prepare for cloning

The clonedb script requires a few environment variables to be set, as in this example:

export MASTER_COPY_DIR=/u01/oraback/CDB1
export CLONE_FILE_CREATE_DEST=/u01/oradata/CLONEDB
export CLONEDB_NAME=CLONEDB

The MASTER_COPY_DIR indicates where the backup resides, the CLONE_FILE_CREATE_DEST marks the directory where the CLONEDB data files are going to be located, and finally the CLONEDB_NAME is the database name.

With that in place, directories created and permissions set properly, it’s time to call the script, and here’s the first surprise:

[oracle@server4 ~]$ perl $ORACLE_HOME/rdbms/install/clonedb.pl
Missing braces on \o{} at /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/install/clonedb.pl line 245, near "$lne' , '$clonedbdir"

I tried with $ORACLE_HOME/perl/bin/perl and the Oracle-Linux 7.1 version of perl and both showed the same behaviour (perl provided by the RDBMS home is v5.14.1, the default is v5.16.3). I corrected the script in line 245 and was able to produce the required scripts:

[oracle@server4 ~]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/install/clonedb.pl \
> /u01/oraback/CDB1/initCDB1.orig /tmp/script1.sql /tmp/script2.sql

[oracle@server4 ~]$ cat /tmp/script1.sql

SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100

STARTUP NOMOUNT PFILE=/u01/oradata/CLONEDB/initCLONEDB.ora
CREATE CONTROLFILE REUSE SET DATABASE CLONEDB RESETLOGS
    MAXLOGFILES 32
    MAXLOGMEMBERS 2
    MAXINSTANCES 1
    MAXLOGHISTORY 908
LOGFILE
  GROUP 1 '/u01/oradata/CLONEDB/CLONEDB_log1.log' SIZE 100M BLOCKSIZE 512,
  GROUP 2 '/u01/oradata/CLONEDB/CLONEDB_log2.log' SIZE 100M BLOCKSIZE 512
DATAFILE
'/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSAUX_FNO-3_0mqdp1mb',
'/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSAUX_FNO-7_0oqdp1n5',
'/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSAUX_FNO-9_0nqdp1n5',
'/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSTEM_FNO-1_0lqdp1mb',
'/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSTEM_FNO-5_0rqdp1o4',
'/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSTEM_FNO-8_0qqdp1nl',
'/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-UNDOTBS1_FNO-4_0pqdp1nk',
'/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-USERS_FNO-10_0sqdp1o4',
'/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-USERS_FNO-6_0tqdp1oc'
CHARACTER SET WE8DEC;

[oracle@server4 ~]$ cat /tmp/script2.sql
declare
begin
dbms_dnfs.clonedb_renamefile('/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSAUX_FNO-3_0mqdp1mb' , '/u01/oradata/CLONEDB//ora_data_CLONEDB0.dbf');
dbms_dnfs.clonedb_renamefile('/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSAUX_FNO-7_0oqdp1n5' , '/u01/oradata/CLONEDB//ora_data_CLONEDB1.dbf');
dbms_dnfs.clonedb_renamefile('/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSAUX_FNO-9_0nqdp1n5' , '/u01/oradata/CLONEDB//ora_data_CLONEDB2.dbf');
dbms_dnfs.clonedb_renamefile('/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSTEM_FNO-1_0lqdp1mb' , '/u01/oradata/CLONEDB//ora_data_CLONEDB3.dbf');
dbms_dnfs.clonedb_renamefile('/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSTEM_FNO-5_0rqdp1o4' , '/u01/oradata/CLONEDB//ora_data_CLONEDB4.dbf');
dbms_dnfs.clonedb_renamefile('/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSTEM_FNO-8_0qqdp1nl' , '/u01/oradata/CLONEDB//ora_data_CLONEDB5.dbf');
dbms_dnfs.clonedb_renamefile('/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-UNDOTBS1_FNO-4_0pqdp1nk' , '/u01/oradata/CLONEDB//ora_data_CLONEDB6.dbf');
dbms_dnfs.clonedb_renamefile('/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-USERS_FNO-10_0sqdp1o4' , '/u01/oradata/CLONEDB//ora_data_CLONEDB7.dbf');
dbms_dnfs.clonedb_renamefile('/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-USERS_FNO-6_0tqdp1oc' , '/u01/oradata/CLONEDB//ora_data_CLONEDB8.dbf');
end;
/
show errors;
alter database open resetlogs;
drop tablespace TEMP;
create temporary tablespace TEMP;[oracle@server4 ~]$

[oracle@server4 tmp]$ cat /u01/oradata/CLONEDB/initCLONEDB.ora
CDB1.__data_transfer_cache_size=0
CDB1.__db_cache_size=478150656
CDB1.__java_pool_size=4194304
CDB1.__large_pool_size=125829120
CDB1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
CDB1.__pga_aggregate_target=268435456
CDB1.__sga_target=805306368
CDB1.__shared_io_pool_size=0
CDB1.__shared_pool_size=188743680
CDB1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/CDB1/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
control_files=/u01/oradata/CLONEDB/CLONEDB_ctl.dbf
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
db_name=CLONEDB
*.db_recovery_file_dest='+RECO'
*.db_recovery_file_dest_size=4560m

*.dispatchers='(PROTOCOL=TCP) (SERVICE=CDB1XDB)'
*.enable_pluggable_database=true
*.open_cursors=300
*.pga_aggregate_target=256m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=768m
*.undo_tablespace='UNDOTBS1'
db_create_file_dest=/u01/oradata/CLONEDB/
log_archive_dest=/u01/oradata/CLONEDB/
clonedb=TRUE

The scripts in that form won’t work- bear with me a second.

Fixing the scripts

First of all the init.ora script, it needs mending. The final initCLONEDB.ora, located in $CLONE_FILE_CREATE_DEST file looks like this (mandatory directories have to be created):

*.audit_file_dest='/u01/app/oracle/admin/CLONEDB/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files=/u01/oradata/CLONEDB/CLONEDB_ctl.dbf
*.db_block_size=8192
*.db_domain=''
*.db_name=CLONEDB
*.db_recovery_file_dest='/u01/fra'
*.db_recovery_file_dest_size=4560m
*.dispatchers='(PROTOCOL=TCP) (SERVICE=CLONEDBXDB)'
*.enable_pluggable_database=true
*.open_cursors=300
*.pga_aggregate_target=256m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=768m
*.undo_tablespace='UNDOTBS1'
*.db_create_file_dest=/u01/oradata/CLONEDB/
*.log_archive_dest=/u01/oradata/CLONEDB/
*.clonedb=TRUE

I took out the ASM related parameters and made sure the FRA was usable. I also tidied everything up a little bit. Take a backup of this file, it will be overwritten next time you run clonedb.pl

Next check script1.sql for problems. I initially forgot to remove the reference to the init.ora I placed in /u01/oraback/CDB1 (I thought it was a good idea. It’s not):

SQL> @/tmp/script1
SQL> SET FEEDBACK 1
SQL> SET NUMWIDTH 10
SQL> SET LINESIZE 80
SQL> SET TRIMSPOOL ON
SQL> SET TAB OFF
SQL> SET PAGESIZE 100
SQL>
SQL> STARTUP NOMOUNT PFILE=/u01/oradata/CLONEDB/initCLONEDB.ora
ORACLE instance started.

Total System Global Area  805306368 bytes
Fixed Size                  2929552 bytes
Variable Size             318770288 bytes
Database Buffers          478150656 bytes
Redo Buffers                5455872 bytes
SQL> CREATE CONTROLFILE REUSE SET DATABASE CLONEDB RESETLOGS
  2      MAXLOGFILES 32
  3      MAXLOGMEMBERS 2
  4      MAXINSTANCES 1
  5      MAXLOGHISTORY 908
  6  LOGFILE
  7    GROUP 1 '/u01/oradata/CLONEDB/CLONEDB_log1.log' SIZE 100M BLOCKSIZE 512,
  8    GROUP 2 '/u01/oradata/CLONEDB/CLONEDB_log2.log' SIZE 100M BLOCKSIZE 512
  9  DATAFILE
 10  '/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSAUX_FNO-3_04qdogkp',
 11  '/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSAUX_FNO-7_06qdogmu',
 12  '/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSAUX_FNO-9_05qdogmq',
 13  '/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSTEM_FNO-1_03qdogkp',
 14  '/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSTEM_FNO-5_09qdogpr',
 15  '/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSTEM_FNO-8_08qdogon',
 16  '/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-UNDOTBS1_FNO-4_07qdogom',
 17  '/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-USERS_FNO-10_0aqdogpt',
 18  '/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-USERS_FNO-6_0bqdogqn',
 19  '/u01/oraback/CDB1/initCDB1.orig'
 20  CHARACTER SET WE8DEC;
CREATE CONTROLFILE REUSE SET DATABASE CLONEDB RESETLOGS
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file '/u01/oraback/CDB1/initCDB1.orig'
ORA-17503: ksfdopn:7 Failed to open file /u01/oraback/CDB1/initCDB1.orig
ORA-27047: unable to read the header block of file
Additional information: 7
Additional information: 210592808
Additional information: 140184619486368

Took me 5 minutes to work out that there isn’t a problem with the pfile for starting the database. There is a problem with the reference to the pfile in line 19…

In a second attempt I was luckier.

[oracle@server4 CLONEDB]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Aug 4 16:09:50 2015

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

Connected to an idle instance.

SQL> @/tmp/script1
SQL> SET FEEDBACK 1
SQL> SET NUMWIDTH 10
SQL> SET LINESIZE 80
SQL> SET TRIMSPOOL ON
SQL> SET TAB OFF
SQL> SET PAGESIZE 100
SQL>
SQL> STARTUP NOMOUNT PFILE=/u01/oradata/CLONEDB/initCLONEDB.ora
ORACLE instance started.

Total System Global Area  805306368 bytes
Fixed Size                  2929552 bytes
Variable Size             318770288 bytes
Database Buffers          478150656 bytes
Redo Buffers                5455872 bytes
SQL> CREATE CONTROLFILE REUSE SET DATABASE CLONEDB RESETLOGS
  2      MAXLOGFILES 32
  3      MAXLOGMEMBERS 2
  4      MAXINSTANCES 1
  5      MAXLOGHISTORY 908
  6  LOGFILE
  7    GROUP 1 '/u01/oradata/CLONEDB/CLONEDB_log1.log' SIZE 100M BLOCKSIZE 512,
  8    GROUP 2 '/u01/oradata/CLONEDB/CLONEDB_log2.log' SIZE 100M BLOCKSIZE 512
  9  DATAFILE
 10  '/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSAUX_FNO-3_0mqdp1mb',
 11  '/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSAUX_FNO-7_0oqdp1n5',
 12  '/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSAUX_FNO-9_0nqdp1n5',
 13  '/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSTEM_FNO-1_0lqdp1mb',
 14  '/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSTEM_FNO-5_0rqdp1o4',
 15  '/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSTEM_FNO-8_0qqdp1nl',
 16  '/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-UNDOTBS1_FNO-4_0pqdp1nk',
 17  '/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-USERS_FNO-10_0sqdp1o4',
 18  '/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-USERS_FNO-6_0tqdp1oc'
 19  CHARACTER SET WE8DEC;

Control file created.

Elapsed: 00:00:02.22

Success! Now I need to continue with script2:

SQL> @/tmp/script2
SQL> declare
  2  begin
  3  dbms_dnfs.clonedb_renamefile('/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSAUX_FNO-3_0mqdp1mb' , '/u01/oradata/CLONEDB//ora_data_CLONEDB0.dbf');
  4  dbms_dnfs.clonedb_renamefile('/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSAUX_FNO-7_0oqdp1n5' , '/u01/oradata/CLONEDB//ora_data_CLONEDB1.dbf');
  5  dbms_dnfs.clonedb_renamefile('/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSAUX_FNO-9_0nqdp1n5' , '/u01/oradata/CLONEDB//ora_data_CLONEDB2.dbf');
  6  dbms_dnfs.clonedb_renamefile('/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSTEM_FNO-1_0lqdp1mb' , '/u01/oradata/CLONEDB//ora_data_CLONEDB3.dbf');
  7  dbms_dnfs.clonedb_renamefile('/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSTEM_FNO-5_0rqdp1o4' , '/u01/oradata/CLONEDB//ora_data_CLONEDB4.dbf');
  8  dbms_dnfs.clonedb_renamefile('/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSTEM_FNO-8_0qqdp1nl' , '/u01/oradata/CLONEDB//ora_data_CLONEDB5.dbf');
  9  dbms_dnfs.clonedb_renamefile('/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-UNDOTBS1_FNO-4_0pqdp1nk' , '/u01/oradata/CLONEDB//ora_data_CLONEDB6.dbf');
 10  dbms_dnfs.clonedb_renamefile('/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-USERS_FNO-10_0sqdp1o4' , '/u01/oradata/CLONEDB//ora_data_CLONEDB7.dbf');
 11  dbms_dnfs.clonedb_renamefile('/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-USERS_FNO-6_0tqdp1oc' , '/u01/oradata/CLONEDB//ora_data_CLONEDB8.dbf');
 12  end;
 13  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.05
SQL> show errors;
No errors.
SQL> alter database open resetlogs;

Database altered.

Elapsed: 00:00:22.60
SQL> drop tablespace TEMP;
drop tablespace TEMP
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace

Elapsed: 00:00:00.01
SQL> create temporary tablespace TEMP;
create temporary tablespace TEMP
*
ERROR at line 1:
ORA-01543: tablespace 'TEMP' already exists

Elapsed: 00:00:00.02

Not quite success but close. I added a temp file to the TEMP tablespace and was ready. A quick test revealed that I could open the PDB, too:

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
SQL> alter pluggable database PDB1 open;

Pluggable database altered.

Elapsed: 00:00:01.46
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
SQL>

Sparse Files

The real beauty lies in the fact that I have space efficient snapshots:

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/oradata/CLONEDB/ora_data_CLONEDB3.dbf
/u01/oradata/CLONEDB/ora_data_CLONEDB0.dbf
/u01/oradata/CLONEDB/ora_data_CLONEDB6.dbf
/u01/oradata/CLONEDB/ora_data_CLONEDB4.dbf
/u01/oradata/CLONEDB/ora_data_CLONEDB8.dbf
/u01/oradata/CLONEDB/ora_data_CLONEDB1.dbf
/u01/oradata/CLONEDB/ora_data_CLONEDB5.dbf
/u01/oradata/CLONEDB/ora_data_CLONEDB2.dbf
/u01/oradata/CLONEDB/ora_data_CLONEDB7.dbf

9 rows selected.

Elapsed: 00:00:00.01
SQL> !ls -lsh /u01/oradata/CLONEDB/
total 211M
   0 drwxr-x---. 4 oracle asmadmin   37 Aug  4 16:14 CLONEDB
8.9M -rw-r-----. 1 oracle asmadmin 8.9M Aug  4 16:16 CLONEDB_ctl.dbf
101M -rw-r-----. 1 oracle asmadmin 101M Aug  4 16:16 CLONEDB_log1.log
101M -rw-r-----. 1 oracle asmadmin 101M Aug  4 16:10 CLONEDB_log2.log
4.0K -rw-r--r--. 1 oracle oinstall  613 Aug  4 16:09 initCLONEDB.ora
4.0K -rw-r--r--. 1 oracle oinstall  613 Aug  4 16:00 initCLONEDB.ora.orig
460K -rw-r-----. 1 oracle asmadmin 761M Aug  4 16:15 ora_data_CLONEDB0.dbf
 64K -rw-r-----. 1 oracle asmadmin 491M Aug  4 16:10 ora_data_CLONEDB1.dbf
 64K -rw-r-----. 1 oracle asmadmin 521M Aug  4 16:15 ora_data_CLONEDB2.dbf
420K -rw-r-----. 1 oracle asmadmin 791M Aug  4 16:15 ora_data_CLONEDB3.dbf
 64K -rw-r-----. 1 oracle asmadmin 251M Aug  4 16:10 ora_data_CLONEDB4.dbf
 64K -rw-r-----. 1 oracle asmadmin 261M Aug  4 16:15 ora_data_CLONEDB5.dbf
540K -rw-r-----. 1 oracle asmadmin 361M Aug  4 16:15 ora_data_CLONEDB6.dbf
 64K -rw-r-----. 1 oracle asmadmin 201M Aug  4 16:15 ora_data_CLONEDB7.dbf
 64K -rw-r-----. 1 oracle asmadmin 5.1M Aug  4 16:10 ora_data_CLONEDB8.dbf

The first column in the output is the actual size on disk, for example 64k for ora_data_CLONEDB4.dbf. A “regular” ls output will show the file as 251M, reflected in the above output as well. It’s the original file size.

Posted in 12c Release 1, Linux | Tagged: , | Leave a Comment »

Fixing a problem with the ASM spfile preventing RAC 12c from starting

Posted by Martin Bach on July 31, 2015

This is a little note to myself on how to fix a corrupt spfile in clustered ASM. I hope you find it useful, too.

Let’s assume you made a change to the ASM (server) parameter file that causes an issue. You are most likely to notice this once CRS is restarted but parts of the stack fail to come up. If “crsctl check crs” mentions any component not started you can try to find out where in the bootstrap process you are stuck. Here is the output from my system.

[root@rac12pri1 ~]# crsctl stat res -t -init
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.asm
      1        ONLINE  OFFLINE                               STABLE
ora.cluster_interconnect.haip
      1        ONLINE  OFFLINE      rac12pri1                STARTING
ora.crf
      1        ONLINE  OFFLINE                               STABLE
ora.crsd
      1        ONLINE  OFFLINE                               STABLE
ora.cssd
      1        ONLINE  ONLINE       rac12pri1                STABLE
ora.cssdmonitor
      1        ONLINE  ONLINE       rac12pri1                STABLE
ora.ctssd
      1        ONLINE  ONLINE       rac12pri1                OBSERVER,STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.drivers.acfs
      1        ONLINE  ONLINE       rac12pri1                STABLE
ora.evmd
      1        ONLINE  INTERMEDIATE rac12pri1                STABLE
ora.gipcd
      1        ONLINE  ONLINE       rac12pri1                STABLE
ora.gpnpd
      1        ONLINE  ONLINE       rac12pri1                STABLE
ora.mdnsd
      1        ONLINE  ONLINE       rac12pri1                STABLE
ora.storage
      1        ONLINE  OFFLINE                               STABLE
--------------------------------------------------------------------------------
[root@rac12pri1 ~]#

I noticed that lots of components are not started. If you are interested in the startup order and dependencies between processes you can find this documented in the Clusterware Administration and Deployment Guide – Chapter 1, Figure 1-2

Another useful piece of information is the Clusterware alert.log. Unlike Oracle Clusterware version 11.2 where log information was in the $GRID_HOME the 12c CRS logs moved to the ADR. A quick look at the alert.log showed


2015-07-28 09:16:51.247 [OCSSD(11611)]CRS-8500: Oracle Clusterware OCSSD process is starting with operating system process ID 11611
2015-07-28 09:16:52.347 [OCSSD(11611)]CRS-1713: CSSD daemon is started in hub mode
2015-07-28 09:16:57.974 [OCSSD(11611)]CRS-1707: Lease acquisition for node rac12pri1 number 1 completed
2015-07-28 09:16:59.076 [OCSSD(11611)]CRS-1605: CSSD voting file is online: /dev/vdc1; details in /u01/app/oracle/diag/crs/rac12pri1/crs/trace/ocssd.trc.
2015-07-28 09:16:59.089 [OCSSD(11611)]CRS-1672: The number of voting files currently available 1 has fallen to the minimum number of voting files required 1.
2015-07-28 09:17:08.198 [OCSSD(11611)]CRS-1601: CSSD Reconfiguration complete. Active nodes are rac12pri1 .
2015-07-28 09:17:10.276 [OCTSSD(11694)]CRS-8500: Oracle Clusterware OCTSSD process is starting with operating system process ID 11694
2015-07-28 09:17:11.261 [OCTSSD(11694)]CRS-2403: The Cluster Time Synchronization Service on host rac12pri1 is in observer mode.
2015-07-28 09:17:11.469 [OCTSSD(11694)]CRS-2407: The new Cluster Time Synchronization Service reference node is host rac12pri1.
2015-07-28 09:17:11.469 [OCTSSD(11694)]CRS-2401: The Cluster Time Synchronization Service started on host rac12pri1.
2015-07-28 09:17:43.016 [ORAROOTAGENT(11376)]CRS-5019: All OCR locations are on ASM disk groups [CHM], and none of these disk groups are
   mounted. Details are at "(:CLSN00140:)" in "/u01/app/oracle/diag/crs/rac12pri1/crs/trace/ohasd_orarootagent_root.trc".
2015-07-28 09:18:05.139 [OCSSD(11611)]CRS-1625: Node rac12pri2, number 2, was shut down
2015-07-28 09:18:05.139 [OCSSD(11611)]CRS-1625: Node rac12pri3, number 3, was shut down
2015-07-28 09:18:05.139 [OCSSD(11611)]CRS-1625: Node rac12pri4, number 4, was shut down

In other words, CSSD has found the block device I use for the voting files, and concludes its initial work. However, the oracle ROOT agent (orarootagent) cannot proceed since none of the OCR locations on ASM can be opened. Checking the log file at that particular time I can see where the problem is:

2015-07-28 09:17:42.989946*:kgfo.c@2846: kgfoCheckMount dg=CHM ok=0
2015-07-28 09:17:42.990045 : USRTHRD:3741497088: {0:9:3} -- trace dump on error exit --

2015-07-28 09:17:42.990057 : USRTHRD:3741497088: {0:9:3} Error [kgfoAl06] in [kgfokge] at kgfo.c:2850

2015-07-28 09:17:42.990067 : USRTHRD:3741497088: {0:9:3} ORA-15077: could not locate ASM instance serving a
  required diskgroup

2015-07-28 09:17:42.990077 : USRTHRD:3741497088: {0:9:3} Category: 7

2015-07-28 09:17:42.990115 : USRTHRD:3741497088: {0:9:3} DepInfo: 15077

2015-07-28 09:17:42.990382 : USRTHRD:3741497088: {0:9:3} -- trace dump end --

2015-07-28 09:17:42.990408 :CLSDYNAM:3741497088: [ora.storage]{0:9:3} [start] retcode = 7, kgfoCheckMount(CHM)
2015-07-28 09:17:42.990423 :CLSDYNAM:3741497088: [ora.storage]{0:9:3} [start] (null) category: 7, operation:
 kgfoAl06, loc: kgfokge, OS error: 15077,
 other: ORA-15077: could not locate ASM instance serving a required diskgroup

So there is not a single ASM instance that could serve the required diskgroup. Hmmm… So maybe I have to back out the change I just made. I have developed a habit of creating backups (pfiles) of spfiles prior to implementing changes. But even if there is no backup of the spfile I can still get the system back, and here are the steps I used. Just as with the database, I need to

  1. Create a temporary pfile on the file system
  2. Start ASM using this temporary pfile
  3. Create a backup my (bad) spfile from the ASM disk group
  4. Extract all parameters
  5. Create a proper pfile that I use to start the cluster with
  6. Convert that to a spfile in ASM

Fixing the problem

The first step is to create a temporary pfile. Using the ASM instance’s alert.log I can go scroll up to a point in time before the change I made to check which parameters are needed. These following are just an example, your settings are different!

...
Using parameter settings in server-side spfile +CHM/rac12pri/ASMPARAMETERFILE/registry.253.885820125
System parameters with non-default values:
  large_pool_size          = 12M
  remote_login_passwordfile= "EXCLUSIVE"
  asm_diskstring           = "/dev/vd*1"
  asm_diskgroups           = "DATA"
  asm_diskgroups           = "RECO"
  asm_power_limit          = 1
NOTE: remote asm mode is remote (mode 0x202; from cluster type)
Cluster communication is configured to use the following interface(s) for this instance
  169.254.106.70
  169.254.184.41
cluster interconnect IPC version: Oracle UDP/IP (generic)
IPC Vendor 1 proto 2
...

The new pfile, /tmp/init+ASM1.ora, has the following contents:

  large_pool_size          = 12M
  remote_login_passwordfile= "EXCLUSIVE"
  asm_diskstring           = "/dev/vd*1"
  asm_diskgroups           = "DATA"
  asm_diskgroups           = "RECO"
  asm_power_limit          = 1

I can now start the first ASM instance:

[oracle@rac12pri1 ~]$ sqlplus / as sysasm

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 28 09:23:23 2015

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

Connected to an idle instance.

SQL> startup pfile='/tmp/init+ASM1.ora'
ASM instance started

Total System Global Area 1140850688 bytes
Fixed Size                  2933400 bytes
Variable Size            1112751464 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted
SQL>

The alert.log also records the location of the spfile-you should back this up now (using asmcmd or any other tool). Using the backup, you should be able to reconstruct your spfile, but make sure to take the offending parameter out.

I decided to create the spfile as spfileASM.ora in ASM. I amended my temporary pfile with the settings from the recovered spfile and put it back into the cluster.


SQL> create spfile='+CHM/rac12pri/spfileASM.ora' from pfile='/tmp/init+ASM1.ora';

File created.

Why the name change? You cannot create files in ASM that have OMF names. Trying to create the spfile with the original name will cause an error:


SQL> create spfile='+CHM/rac12pri/ASMPARAMETERFILE/registry.253.885820125' from pfile='/tmp/init+ASM1.ora';
create spfile='+CHM/rac12pri/ASMPARAMETERFILE/registry.253.885820125' from pfile='/tmp/init+ASM1.ora'
*
ERROR at line 1:
ORA-17502: ksfdcre:4 Failed to create file
+CHM/rac12pri/ASMPARAMETERFILE/registry.253.885820125
ORA-15177: cannot operate on system aliases

The really nice thing is that this is reflected in the Grid Plug And Play (GPNP) profile immediately. The ASM alert.log showed:

2015-07-28 09:25:01.323000 +01:00
NOTE: updated gpnp profile ASM SPFILE to
NOTE: header on disk 0 advanced to format #2 using fcn 0.0
2015-07-28 09:25:58.332000 +01:00
NOTE: updated gpnp profile ASM diskstring: /dev/vd*1
NOTE: updated gpnp profile ASM diskstring: /dev/vd*1
NOTE: updated gpnp profile ASM SPFILE to +CHM/rac12pri/spfileASM.ora

And the XML profile is updated too (reformatted for better readability)


[oracle@rac12pri1 ~]$ gpnptool get -o-

<?xml version="1.0" encoding="UTF-8"?>
<gpnp:GPnP-Profile Version="1.0" xmlns="http://www.grid-pnp.org/2005/11/gpnp-profile"
  xmlns:gpnp="http://www.grid-pnp.org/2005/11/gpnp-profile"
  xmlns:orcl="http://www.oracle.com/gpnp/2005/11/gpnp-profile"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.grid-pnp.org/2005/11/gpnp-profile gpnp-profile.xsd"
  ProfileSequence="7" ClusterUId="886a0e42a...5d805357c76a"
  ClusterName="rac12pri" PALocation="">
  <gpnp:Network-Profile>
    <gpnp:HostNetwork id="gen" HostName="*">
     <gpnp:Network id="net1" IP="192.168.100.0" Adapter="eth0" Use="public"/>
     <gpnp:Network id="net2" IP="192.168.101.0" Adapter="eth1" Use="cluster_interconnect"/>
     <gpnp:Network id="net3" IP="192.168.102.0" Adapter="eth2" Use="asm,cluster_interconnect"/>
    </gpnp:HostNetwork>
  </gpnp:Network-Profile>
  <orcl:CSS-Profile id="css" DiscoveryString="+asm" LeaseDuration="400"/>
  <orcl:ASM-Profile id="asm" DiscoveryString="/dev/vd*1" SPFile="+CHM/rac12pri/spfileASM.ora" Mode="remote"/>
  <ds:Signature xmlns:ds="http://www.w3.org/2000/09/xmldsig#">...</ds:Signature>
</gpnp:GPnP-Profile>

This should be it-the correct values have been restored, the spfile is back on shared storage, and I should be able to start with this combination. After having issued the stop/start commands to CRS it was indeed all well:

[root@rac12pri1 ~]# crsctl check cluster
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online   

[root@rac12pri1 ~]# crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr
               ONLINE  ONLINE       rac12pri1                STABLE
ora.CHM.dg
               ONLINE  ONLINE       rac12pri1                STABLE
ora.DATA.dg
               ONLINE  ONLINE       rac12pri1                STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       rac12pri1                STABLE
ora.RECO.dg
               ONLINE  ONLINE       rac12pri1                STABLE
ora.net1.network
               ONLINE  ONLINE       rac12pri1                STABLE
ora.ons
               ONLINE  ONLINE       rac12pri1                STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       rac12pri1                STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       rac12pri1                STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       rac12pri1                STABLE
ora.MGMTLSNR
      1        ONLINE  ONLINE       rac12pri1                169.254.1.137 192.16
                                                             8.101.10 192.168.102
                                                             .10,STABLE
ora.asm
      1        ONLINE  ONLINE       rac12pri1                STABLE
      2        ONLINE  OFFLINE                               STABLE
      3        ONLINE  OFFLINE                               STABLE
ora.cdb.db
      1        OFFLINE OFFLINE                               Instance Shutdown,ST
                                                             ABLE
      2        OFFLINE OFFLINE                               STABLE
      3        OFFLINE OFFLINE                               STABLE
      4        OFFLINE OFFLINE                               STABLE
ora.cvu
      1        ONLINE  ONLINE       rac12pri1                STABLE
ora.mgmtdb
      1        ONLINE  ONLINE       rac12pri1                Open,STABLE
ora.ncdb.db
      1        ONLINE  ONLINE       rac12pri1                Open,STABLE
      2        ONLINE  OFFLINE                               STABLE
      3        ONLINE  OFFLINE                               STABLE
      4        ONLINE  OFFLINE                               STABLE
ora.ncdb.fotest.svc
      1        ONLINE  OFFLINE                               STABLE
      2        ONLINE  ONLINE       rac12pri1                STABLE
ora.oc4j
      1        ONLINE  ONLINE       rac12pri1                STABLE
ora.rac12pri1.vip
      1        ONLINE  ONLINE       rac12pri1                STABLE
ora.rac12pri2.vip
      1        ONLINE  INTERMEDIATE rac12pri1                FAILED OVER,STABLE
ora.rac12pri3.vip
      1        ONLINE  INTERMEDIATE rac12pri1                FAILED OVER,STABLE
ora.rac12pri4.vip
      1        ONLINE  INTERMEDIATE rac12pri1                FAILED OVER,STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       rac12pri1                STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       rac12pri1                STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       rac12pri1                STABLE
--------------------------------------------------------------------------------

Time to start Clusterware on the other nodes and to report “We are back and running” :)

Reference

Posted in 12c Release 1, Automatic Storage Management, RAC | Leave a Comment »

Little things worth knowing: Data Guard Broker Setup changes in 12c

Posted by Martin Bach on July 27, 2015

One of the problems I have seen when deploying Data Guard for systems such as RAC One Node and policy managed databases was the static listener configuration you needed in 11.2. This has changed with 12c for the better if you are using Grid Infrastructure.

http://docs.oracle.com/database/121/DGBKR/install.htm

In the section about static listener registration a little addendum can be found (thanks to Patrick Hurley/@phurley for pointing this out to me!):

“A static service needs to be defined and registered only if Oracle Clusterware or Oracle Restart is not being used.”

This is good news, let’s put it to the test; I’m a great fan of Oracle Restart. If I ever find the time I’d like to repeat this test with clustered Grid Infrastructure. I think the quote mentioned earlier still stands true but I would like to see it with my own eyes.

The Setup

To start with I used a dbca-created database named “NCDB” on my server named “server1”. It uses the DATA and RECO disk groups and is a non-CDB (although I’m quite sure that doesn’t matter). The patch level is current at the time of writing, I’m on 12.1.0.2.4 (which is the OJVM and PSU bundle patch for July 2015):

[oracle@server1 ~]$  /u01/app/oracle/product/12.1.0.2/dbhome_1/OPatch/opatch lspatches
21068507;Database PSU 12.1.0.2.4, Oracle JavaVM Component (Jul2015)
20831113;OCW Patch Set Update : 12.1.0.2.4 (20831113)
20831110;Database Patch Set Update : 12.1.0.2.4 (20831110)

OPatch succeeded.

[oracle@server1 ~]$  /u01/app/oracle/product/12.1.0.2/grid/OPatch/opatch lspatches
20831113;OCW Patch Set Update : 12.1.0.2.4 (20831113)
20831110;Database Patch Set Update : 12.1.0.2.4 (20831110)
20299018;ACFS Patch Set Update : 12.1.0.2.3 (20299018)
19872484;WLM Patch Set Update: 12.1.0.2.2 (19872484)

OPatch succeeded.

The future standby database will be called STDBY. This is for demonstration purposes only, I’m not a fan of using db_unique_name implying roles. Imagine you switch over to STDBY and run it as primary database for extended periods of time-that would be potentially confusing.

With that in mind, I created/updated a common tnsnames.ora on server1 and server2:

[oracle@server1 ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: 
# /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

NCDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server1.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = NCDB)
    )
  )

STDBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server2.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = STDBY)
    )
  )

The duplicate command was a simple “duplicate target database for standby” after creating a simple pfile and auxiliary structures (audit_dump_dest, large pages etc) were in place. For this to work you need a copy of the controlfile for the standby in place as well as a backup of the primary database).

RMAN> duplicate target database for standby;

Starting Duplicate Db at 27-JUL-15
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2

contents of Memory Script:
{
   sql clone "alter system set  control_files =
  ''+DATA/STDBY/CONTROLFILE/current.258.886176617'', 
  ''+RECO/STDBY/CONTROLFILE/current.257.886176617'' comment=
 ''Set by RMAN'' scope=spfile";
   restore clone standby controlfile;
}
executing Memory Script

sql statement: alter system set  control_files =   ''+DATA/STDBY/CONTROLFILE/current.258.886176617'', 
 ''+RECO/STDBY/CONTROLFILE/current.257.886176617'' comment= ''Set by RMAN'' scope=spfile

Starting restore at 27-JUL-15
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece +RECO/NCDB/AUTOBACKUP/2015_07_27/s_886176583.273.886176583
channel ORA_AUX_DISK_1: ORA-19870: error while restoring backup piece +RECO/NCDB/AUTOBACKUP/2015_07_27/s_886176583.273.886176583
ORA-19505: failed to identify file "+RECO/NCDB/AUTOBACKUP/2015_07_27/s_886176583.273.886176583"
ORA-17503: ksfdopn:2 Failed to open file +RECO/NCDB/AUTOBACKUP/2015_07_27/s_886176583.273.886176583
ORA-15012: ASM file '+RECO/NCDB/AUTOBACKUP/2015_07_27/s_886176583.273.886176583' does not exist

failover to previous backup

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/oraback/NCDB/09qd3uq4_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/oraback/NCDB/09qd3uq4_1_1 tag=TAG20150727T160940
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+DATA/STDBY/CONTROLFILE/current.259.886176619
output file name=+RECO/STDBY/CONTROLFILE/current.258.886176619
Finished restore at 27-JUL-15
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 clone tempfile  1 to new;
   switch clone tempfile all;
   set newname for clone datafile  1 to new;
   set newname for clone datafile  3 to new;
   set newname for clone datafile  4 to new;
   set newname for clone datafile  5 to new;
   set newname for clone datafile  6 to new;
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DATA in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 27-JUL-15
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00005 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00006 to +DATA
channel ORA_AUX_DISK_1: reading from backup piece /u01/oraback/NCDB/05qd3uas_1_1
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00001 to +DATA
channel ORA_AUX_DISK_2: restoring datafile 00003 to +DATA
channel ORA_AUX_DISK_2: reading from backup piece /u01/oraback/NCDB/06qd3uat_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/oraback/NCDB/05qd3uas_1_1 tag=TAG20150727T160132
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_2: piece handle=/u01/oraback/NCDB/06qd3uat_1_1 tag=TAG20150727T160132
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:15
Finished restore at 27-JUL-15

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=886176640 file name=+DATA/STDBY/DATAFILE/system.261.886176625
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=886176641 file name=+DATA/STDBY/DATAFILE/sysaux.263.886176625
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=886176641 file name=+DATA/STDBY/DATAFILE/undotbs1.262.886176625
datafile 5 switched to datafile copy
input datafile copy RECID=9 STAMP=886176641 file name=+DATA/STDBY/DATAFILE/example.260.886176625
datafile 6 switched to datafile copy
input datafile copy RECID=10 STAMP=886176641 file name=+DATA/STDBY/DATAFILE/users.264.886176625
Finished Duplicate Db at 27-JUL-15

RMAN>

That’s a working standby database. I will have to register it with Grid Infrastructure next.

[oracle@server2 ~]$ srvctl add database -db STDBY -oraclehome /u01/app/oracle/product/12.1.0.2/dbhome_1 \
> -role physical_standby -startoption MOUNT -policy automatic -diskgroup data,reco

Now all I need to do is set standby_file_management to auto, add standby redo logs to both databases and enable the broker. I’ll not show these here.

Broker Configuration

The next step in the deployment of my standby database is the creation of a Data Guard Broker configuration. I always create it using dgmgrl.

[oracle@server1 ~]$ dgmgrl
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@ncdb
Password:
Connected as SYSDBA.

DGMGRL> CREATE CONFIGURATION brokertest AS
>  PRIMARY DATABASE IS 'NCDB'
>  CONNECT IDENTIFIER IS 'NCDB';
Configuration "brokertest" created with primary database "NCDB"

DGMGRL>  add database 'STDBY' as connect identifier is 'STDBY';
Database "STDBY" added

DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;

Configuration - brokertest

  Protection Mode: MaxPerformance
  Members:
  NCDB  - Primary database
    STDBY - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 5 seconds ago)

That’s looking good so far! I can see archived redo logs being exchanged, and I can see MRP0 applying data in real time on the standby.

Recap

This is a reference for the settings in the Data Guard Broker. I have not modified any listener.ora file on any host, all I did was to ensure that the tnsnames.ora file has identical contents on both sides. I have listed the database configuration for later reference here:

DGMGRL> show database verbose 'NCDB';

Database - NCDB

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    NCDB

  Properties:
    DGConnectIdentifier             = 'NCDB'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    RedoRoutes                      = ''
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyLagThreshold               = '0'
    TransportLagThreshold           = '0'
    TransportDisconnectedThreshold  = '30'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server1.example.com)
           (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=NCDB_DGMGRL)
           (INSTANCE_NAME=NCDB)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS

DGMGRL> show database verbose 'STDBY';

Database - STDBY

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 5.00 KByte/s
  Active Apply Rate:  0 Byte/s
  Maximum Apply Rate: 0 Byte/s
  Real Time Query:    OFF
  Instance(s):
    STDBY

  Properties:
    DGConnectIdentifier             = 'STDBY'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    RedoRoutes                      = ''
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyLagThreshold               = '0'
    TransportLagThreshold           = '0'
    TransportDisconnectedThreshold  = '30'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.52)
          (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STDBY_DGMGRL)(INSTANCE_NAME=STDBY)
          (SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS

DGMGRL> show configuration verbose;

Configuration - brokertest

  Protection Mode: MaxPerformance
  Members:
  NCDB  - Primary database
    STDBY - Physical standby database 

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'
    ExternalDestination1            = ''
    ExternalDestination2            = ''
    PrimaryLostWriteAction          = 'CONTINUE'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Now the big question is: will this allow me to switch over? For the record I am still using my SSH connection (in screen!) on server1, but am now connected to the standby database in dgmgrl.

DGMGRL> validate database 'STDBY'

  Database Role:     Physical standby database
  Primary Database:  NCDB

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    NCDB:   Off
    STDBY:  Off

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (NCDB)                  (STDBY)
    1         3                       2                       Insufficient SRLs

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (STDBY)                 (NCDB)
    1         3                       0                       Insufficient SRLs
    Warning: standby redo logs not configured for thread 1 on NCDB

DGMGRL> switchover to 'STDBY';
Performing switchover NOW, please wait...
New primary database "STDBY" is opening...
Oracle Clusterware is restarting database "NCDB" ...
Switchover succeeded, new primary is "STDBY"
DGMGRL> show configuration

Configuration - brokertest

  Protection Mode: MaxPerformance
  Members:
  STDBY - Primary database
    NCDB  - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 13 seconds ago)

DGMGRL>

Well that seems to have worked!

It seems straight forward though: the SSH connection was made to the future standby host, which could have helped with Oracle Restart restarting the database. What if I try to switch back, but this time connect to server1 (currently in the standby role) and issue the switchover command? Here is the output:

[oracle@server1 ~]$ dgmgrl
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@ncdb
Password:
Connected as SYSDBA.
DGMGRL> show configuration

Configuration - brokertest

  Protection Mode: MaxPerformance
  Members:
  STDBY - Primary database
    NCDB  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 2 seconds ago)

DGMGRL> validate database 'NCDB';

  Database Role:     Physical standby database
  Primary Database:  STDBY

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    STDBY:  Off
    NCDB:   Off

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status       
              (STDBY)                 (NCDB)                               
    1         3                       2                       Insufficient SRLs

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status       
              (NCDB)                  (STDBY)                              
    1         3                       2                       Insufficient SRLs

Ready to switch over:

DGMGRL> switchover to 'NCDB'
Performing switchover NOW, please wait...
New primary database "NCDB" is opening...
Oracle Clusterware is restarting database "STDBY" ...
Switchover succeeded, new primary is "NCDB"
DGMGRL> show configuration

Configuration - brokertest

  Protection Mode: MaxPerformance
  Members:
  NCDB  - Primary database
    STDBY - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 54 seconds ago)

DGMGRL> 

OK, that worked too-very nice. It’s safe to say that in my lab conditions restarting of databases works pretty well.

Posted in 12c Release 1, Data Guard, Linux | 2 Comments »

JSON support in Exadata 12.1.2.1.0 and later

Posted by Martin Bach on July 8, 2015

Some time ago Oracle announced that RDBMS 12.1.0.2 has built-in support for JSON processing. A little later it was also mentioned that you have support for JSON in the Exadata storage servers for offloading. This is probably a lot more exciting to users of JSON than it is to me as I’m not a developer. However, whenever an announcement such as the one I’m referring to is made I would like to see for myself how much of it is implemented in software. Like I said, I’m not a developer so apologies for a silly example: what I’m showing you here can probably done differently and is not the best use of an Exadata. But all I really wanted to test is if JSON support actually exists. I am using cellsrv 12.1.2.1.0 and RDBMS 12.1.0.2.2 for this test.

JSON

I have to say I struggled a little bit to understand the use case for JSON and therefore did what probably everyone does and consulted the official documentation and oracle-base.com for Tim’s views on JSON. Here’s a summary of links I found useful to get started:

The Test

Ok so that was enough to get me started. I needed data, and a table to store this in. It appeared to me that an apache log could be a useful source for JSON records, so I converted my webserver’s log file to JSON using libee0 on OpenSuSE (yes I know, but it’s a great virtualisation platform). The converted file was named httpd_access_log.json and had records such as these:

{"host": "192.168.100.1", "identity": "", "user": "", "date": "05/Feb/2015:12:13:05 +0100", "request": "HEAD /ol70 HTTP/1.1", "status": "404", "size": "", "f1": "", "useragent": "Python-urllib/2.7"}
{"host": "192.168.100.1", "identity": "", "user": "", "date": "05/Feb/2015:12:13:25 +0100", "request": "GET / HTTP/1.1", "status": "403", "size": "989", "f1": "", "useragent": "Mozilla/5.0 (X11; Linux x86_64; rv:35.0) Gecko/20100101 Firefox/35.0"}

Sorry for the wide output-it’s an Apache log…

I then created the table to store the data. JSON appears to be pretty unstructured, so this will do:

SQL> create table jsontest (id number,
  2   jdata clob,
  3   constraint jsontest check (jdata is json)
  4  )
  5  lob (jdata) store as securefile (
  6   enable storage in row
  7  );

SQL> create sequence s_jsontest;

Sequence created

If you look closely then you’ll see that the JSON data is stored in an inline CLOB-that’s one of the pre-requisites for offloading LOBs in 12c.

Loading JSON

Now I needed a way to get the data into the table. I think I could have used SQLLDR but since I have rusty perl scripting skills I gave DBD::Oracle on 12.1.0.2 a go. The following script inserts records slow-by-slow or row-by-row into the table and is probably not the most efficient way to do this. But one of the reasons I blog is so that I don’t have to remember everything. If you ever wondered how to write a DBI/DBD::Oracle script here’s a starting point. Note the emphasis on “starting point” since the script has been trimmed for readability-essential error checking is not shown. Whenever you work with data make sure that your error handling is top-notch!

#!/usr/bin/perl

use strict;
use warnings;

use DBI;
use DBD::Oracle;
use Getopt::Long;

# these will be set by GetOpt::Long
my $service;            # has to be in tnsnames.ora
my $username;
my $jsonfile;

GetOptions (
  "service=s"   => \$service,
  "user=s"      => \$username,
  "jsonfile=s"  => \$jsonfile
);
die "usage: load_json.pl --service <servicename> --jsonfile [--user username] " if (!defined ($service ) || !defined ($jsonfile));

die "$jsonfile is not a file" unless ( -f $jsonfile );

print "connecting to service $service as user $username to load file $jsonfile\n";

# about to start...
my $dbh = DBI->connect ("dbi:Oracle:$service", "$username", "someCleverPasswordOrCatName")
  or die ("Cannot connect to service $service: DBI:errstr!");

print "connection to the database established, trying to load data...\n";

# prepare a cursor to loop over all entries in the file
my $sth = $dbh->prepare(q{
 insert into jsontest (id, jdata) values(s_jsontest.nextval, :json)
});

if (! open JSON, "$jsonfile")  {
  print "cannot open $jsonfile: $!\n";
  $dbh->disconnect();
  die "Cannot continue\n";
}

while (<JSON>) {
  chomp;
  $sth->bind_param(":json", $_);
  $sth->execute();
}

$dbh->disconnect();

close JSON;

print "done\n";

This script read the file and inserted all the data into the table. Again, essential error checking must be added, the script is far from being complete. You also need to set the Perl environment variables to the perl installation in $ORACLE_HOME for it to find the DBI and DBD::Oracle drivers.

Offloading or not?

It turned out that the data I inserted was of course not enough to trigger a direct path read that could turn into a Smart Scan. A little inflation of the table was needed. Once that was done I started to get my feet wet with JSON queries:

SQL> select jdata from jsontest where rownum < 6;

JDATA
--------------------------------------------------------------------------------
{"host": "192.168.100.1", "identity": "", "user": "", "date": "05/Feb/2015:12:26
{"host": "192.168.100.156", "identity": "", "user": "", "date": "05/Feb/2015:12:
{"host": "192.168.100.156", "identity": "", "user": "", "date": "05/Feb/2015:12:
{"host": "192.168.100.156", "identity": "", "user": "", "date": "05/Feb/2015:12:
{"host": "192.168.100.156", "identity": "", "user": "", "date": "05/Feb/2015:12:

Interesting. Here are a few more examples with my data set. Again, refer to oracle-base.com and the official documentation set for more information about JSON and querying it in the database. It’s by no means an Exadata only feature.

SQL> select count(*) from jsontest where json_exists(jsontest.jdata, '$.host' false on error);

  COUNT(*)
----------
   2195968

SQL> select count(*) from jsontest where not json_exists(jsontest.jdata, '$.host' false on error);

  COUNT(*)
----------
         0

And finally, here is proof that you can offload JSON data in Exadata; at least for some of the operations it should  be possible judging by the information in v$sqlfn_metadata:

SQL> select name,offloadable from v$sqlfn_metadata where name like '%JSON%'
  2  order by offloadable,name;

NAME                                               OFF
-------------------------------------------------- ---
JSON_ARRAY                                         NO
JSON_ARRAYAGG                                      NO
JSON_EQUAL                                         NO
JSON_OBJECT                                        NO
JSON_OBJECTAGG                                     NO
JSON_QUERY                                         NO
JSON_SERIALIZE                                     NO
JSON_TEXTCONTAINS2                                 NO
JSON_VALUE                                         NO
JSON                                               YES
JSON                                               YES
JSON_EXISTS                                        YES
JSON_QUERY                                         YES
JSON_VALUE                                         YES

14 rows selected.

The two entries named “JSON” are most likely “is JSON” and “is not JSON”.

And now with real data volumes on a real system using JSON_EXISTS:

SQL> select /*+ monitor am_I_offloaded */ count(*)
  2  from jsontest where json_exists(jsontest.jdata, '$.host' false on error);                                                                                        

   COUNT(*)
-----------
    2195968

Elapsed: 00:00:04.96

SQL> select * from table(dbms_xplan.display_cursor);                                                                                                        

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
SQL_ID  6j73xcww7hmcw, child number 0
-------------------------------------
select /*+ monitor am_I_offloaded */ count(*) from jsontest where
json_exists(jsontest.jdata, '$.host' false on error)

Plan hash value: 568818393

---------------------------------------------------------------------------------------
| Id  | Operation                  | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |          |       |       | 91078 (100)|          |
|   1 |  SORT AGGREGATE            |          |     1 |   610 |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL| JSONTEST | 21960 |    12M| 91078   (1)| 00:00:04 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - storage(JSON_EXISTS2("JSONTEST"."JDATA" FORMAT JSON , '$.host' FALSE ON
              ERROR)=1)
       filter(JSON_EXISTS2("JSONTEST"."JDATA" FORMAT JSON , '$.host' FALSE ON
              ERROR)=1)

So the execution plan looks promising-I can see “table access storage full” and a storage() predicate. Looking at V$SQL I get:

SQL> select sql_id, child_number,
  2  case when io_cell_offload_eligible_bytes = 0 then 'NO' else 'YES' end offloaded,
  3  io_cell_offload_eligible_bytes/power(1024,2) offload_eligible_mb,
  4  io_interconnect_bytes/power(1024,2) interconnect_mb,
  5  io_cell_offload_returned_bytes/power(1024,2) returned_mb,
  6  io_cell_offload_returned_bytes/io_cell_offload_eligible_bytes*100 offload_pct
  7   from v$sql where sql_id = '6j73xcww7hmcw';                                                                                                                                                   

SQL_ID        CHILD_NUMBER OFF OFFLOAD_ELIGIBLE_MB INTERCONNECT_MB RETURNED_MB OFFLOAD_PCT
------------- ------------ --- ------------------- --------------- ----------- -----------
6j73xcww7hmcw            0 YES         2606.695313     1191.731941 1191.724129 45.71781455

And to avoid any doubt, I have the SQL Trace as well:

PARSING IN CURSOR #140370400430072 len=120 dep=0 uid=65 oct=3 lid=65 tim=1784977054418 hv=1750582781 ad='5bfcebed8' sqlid='bfwd4t5n5gjgx'
select /*+ monitor am_I_offloaded */ count(*)   from jsontest where json_exists(jsontest.jdata, '$.host' false on error)
END OF STMT
PARSE #140370400430072:c=103984,e=272239,p=909,cr=968,cu=0,mis=1,r=0,dep=0,og=1,plh=568818393,tim=1784977054417
EXEC #140370400430072:c=0,e=105,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=568818393,tim=1784977054587
WAIT #140370400430072: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=96305 tim=1784977054666
WAIT #140370400430072: nam='reliable message' ela= 826 channel context=27059892880 channel handle=27196561216 broadcast message=26855409216 obj#=96305 tim=1784977055727
WAIT #140370400430072: nam='enq: KO - fast object checkpoint' ela= 159 name|mode=1263468550 2=65629 0=1 obj#=96305 tim=1784977055942
WAIT #140370400430072: nam='enq: KO - fast object checkpoint' ela= 229 name|mode=1263468545 2=65629 0=2 obj#=96305 tim=1784977056265
WAIT #140370400430072: nam='cell smart table scan' ela= 196 cellhash#=3249924569 p2=0 p3=0 obj#=96298 tim=1784977057370
WAIT #140370400430072: nam='cell smart table scan' ela= 171 cellhash#=822451848 p2=0 p3=0 obj#=96298 tim=1784977057884
WAIT #140370400430072: nam='cell smart table scan' ela= 188 cellhash#=674246789 p2=0 p3=0 obj#=96298 tim=1784977058461
WAIT #140370400430072: nam='cell smart table scan' ela= 321 cellhash#=3249924569 p2=0 p3=0 obj#=96298 tim=1784977061623
WAIT #140370400430072: nam='cell smart table scan' ela= 224 cellhash#=822451848 p2=0 p3=0 obj#=96298 tim=1784977062053
WAIT #140370400430072: nam='cell smart table scan' ela= 254 cellhash#=674246789 p2=0 p3=0 obj#=96298 tim=1784977062487
WAIT #140370400430072: nam='cell smart table scan' ela= 7 cellhash#=3249924569 p2=0 p3=0 obj#=96298 tim=1784977062969
WAIT #140370400430072: nam='cell smart table scan' ela= 25 cellhash#=822451848 p2=0 p3=0 obj#=96298 tim=1784977063016
WAIT #140370400430072: nam='cell smart table scan' ela= 81 cellhash#=674246789 p2=0 p3=0 obj#=96298 tim=1784977063115
WAIT #140370400430072: nam='cell smart table scan' ela= 1134 cellhash#=674246789 p2=0 p3=0 obj#=96298 tim=1784977065442
WAIT #140370400430072: nam='cell smart table scan' ela= 6 cellhash#=3249924569 p2=0 p3=0 obj#=96298 tim=1784977065883
WAIT #140370400430072: nam='cell smart table scan' ela= 14 cellhash#=822451848 p2=0 p3=0 obj#=96298 tim=1784977065917
WAIT #140370400430072: nam='cell smart table scan' ela= 105 cellhash#=674246789 p2=0 p3=0 obj#=96298 tim=1784977066037
WAIT #140370400430072: nam='cell smart table scan' ela= 12 cellhash#=674246789 p2=0 p3=0 obj#=96298 tim=1784977066207
WAIT #140370400430072: nam='cell smart table scan' ela= 6605 cellhash#=3249924569 p2=0 p3=0 obj#=96298 tim=1784977072866
WAIT #140370400430072: nam='cell smart table scan' ela= 27 cellhash#=674246789 p2=0 p3=0 obj#=96298 tim=1784977073877
WAIT #140370400430072: nam='cell smart table scan' ela= 29 cellhash#=3249924569 p2=0 p3=0 obj#=96298 tim=1784977074903
WAIT #140370400430072: nam='cell smart table scan' ela= 907 cellhash#=674246789 p2=0 p3=0 obj#=96298 tim=1784977077783
WAIT #140370400430072: nam='cell smart table scan' ela= 28 cellhash#=674246789 p2=0 p3=0 obj#=96298 tim=1784977078753
WAIT #140370400430072: nam='cell smart table scan' ela= 24 cellhash#=3249924569 p2=0 p3=0 obj#=96298 tim=1784977080860
WAIT #140370400430072: nam='cell smart table scan' ela= 1077 cellhash#=674246789 p2=0 p3=0 obj#=96298 tim=1784977082935
...

Summary

So yet, it would appear as if JSON is offloaded.

Posted in 12c Release 1, Exadata | Tagged: | Leave a Comment »

 
Follow

Get every new post delivered to your Inbox.

Join 3,184 other followers