Adding a Datafile to Tablespace

When monitoring Tablespace Usage (see my Tablespace Usage blog post for more info), there comes a point when you need to add a datafile to a tablespace to allow for growth and more importantly get below a monitoring threshold for example in OEM or OpsView.

Query to see Current Datafiles State

To see the current state of the datafiles:

set pages 999
set lines 400
col FILE_NAME format a75
select d.TABLESPACE_NAME, d.FILE_NAME, d.BYTES/1024/1024 SIZE_MB, d.AUTOEXTENSIBLE, d.MAXBYTES/1024/1024 MAXSIZE_MB, d.INCREMENT_BY*(v.BLOCK_SIZE/1024)/1024 INCREMENT_BY_MB
from dba_data_files d,
 v$datafile v
where d.FILE_ID = v.FILE#
order by d.TABLESPACE_NAME, d.FILE_NAME;

Output from Current Datafiles State

This is the output you will get from the query:

TABLESPACE_NAME FILE_NAME                                      SIZE_MB AUT MAXSIZE_MB INCREMENT_BY_MB
--------------- ---------------------------------------------- ------- --- ---------- ---------------
VERS_DATA       +DATAC1/VERS/DATAFILE/vers_data.395.891367103    32767 YES      32767            1024
...
VERS_DATA       +DATAC1/VERS/DATAFILE/vers_data.772.947087161     4096 YES      32767            1024
VERS_INDEX      +DATAC1/VERS/DATAFILE/vers_index.434.891367657    3072 YES      32767            1024
VERS_INDX       +DATAC1/VERS/DATAFILE/vers_indx.422.891367801    32767 YES      32767            1024
...
VERS_INDX       +DATAC1/VERS/DATAFILE/vers_indx.783.947764359    10240 YES      32767            1024
SNP_DATA        +DATAC1/VERS/DATAFILE/snp_data.421.891367815      1024 YES      32767            1024
SYSAUX          +DATAC1/VERS/DATAFILE/sysaux.488.891349085       31500 YES 32767.9844              10
SYSAUX          +DATAC1/VERS/DATAFILE/sysaux.577.909753421       12288 YES      12288              10
SYSTEM          +DATAC1/VERS/DATAFILE/system.487.891349083         890 YES 32767.9844              10
UNDOTBS1        +DATAC1/VERS/DATAFILE/undotbs1.489.891349085     30565 YES 32767.9844               5
UNDOTBS2        +DATAC1/VERS/DATAFILE/undotbs2.491.891349091     20555 YES 32767.9844               5
USERS           +DATAC1/VERS/DATAFILE/users.492.891349091            5 YES 32767.9844            1.25

273 rows selected.

SQL>

The … represents the several lines removed to make the output readable 🙂

Add Datafile to Tablespace

To add a datafile to ‘VERS_DATA‘ to be initially 10G, auto extendable by 1G to maxsize of 32Gb:

ALTER TABLESPACE VERS_DATA ADD DATAFILE '+DATAC1' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 32767M;

See Oracle Documentation for more info and Syntax:

https://docs.oracle.com/database/121/SQLRF/statements_3002.htm

Once added you’ll see the datafile in the above query:

TABLESPACE_NAME FILE_NAME                                      SIZE_MB AUT MAXSIZE_MB INCREMENT_BY_MB
--------------- ---------------------------------------------- ------- --- ---------- ---------------
VERS_DATA       +DATAC1/VERS/DATAFILE/vers_data.395.891367103    32767 YES      32767            1024
...
VERS_DATA       +DATAC1/VERS/DATAFILE/vers_data.772.947087161     4096 YES      32767            1024
VERS_DATA       +DATAC1/VERS/DATAFILE/vers_data.774.947426643    10240 YES      32767            1024
VERS_INDEX      +DATAC1/VERS/DATAFILE/vers_index.434.891367657    3072 YES      32767            1024
VERS_INDX       +DATAC1/VERS/DATAFILE/vers_indx.422.891367801    32767 YES      32767            1024
...
VERS_INDX       +DATAC1/VERS/DATAFILE/vers_indx.783.947764359    10240 YES      32767            1024
SNP_DATA        +DATAC1/VERS/DATAFILE/snp_data.421.891367815      1024 YES      32767            1024
SYSAUX          +DATAC1/VERS/DATAFILE/sysaux.488.891349085       31500 YES 32767.9844              10
SYSAUX          +DATAC1/VERS/DATAFILE/sysaux.577.909753421       12288 YES      12288              10
SYSTEM          +DATAC1/VERS/DATAFILE/system.487.891349083         890 YES 32767.9844              10
UNDOTBS1        +DATAC1/VERS/DATAFILE/undotbs1.489.891349085     30565 YES 32767.9844               5
UNDOTBS2        +DATAC1/VERS/DATAFILE/undotbs2.491.891349091     20555 YES 32767.9844               5
USERS           +DATAC1/VERS/DATAFILE/users.492.891349091            5 YES 32767.9844            1.25

274 rows selected.

SQL>

Related Blog Posts:
Querying Tablespace Usage

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

Advertisements

Querying Tablespace Usage

Tablespace management in an Oracle database is important and something a DBA will need to do quite frequently.  Therefore I wrote a query, which I believe I originally got from the internet and then it evolved by me adding in temp tablespace and few other things 🙂

Query for Tablespace Usage

Query to obtain Tablespace Usage:

set pages 999
set lines 400
SELECT df.tablespace_name tablespace_name,
 max(df.autoextensible) auto_ext,
 round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
 round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
 round(df.bytes / (1024 * 1024), 2) curr_ts_size,
 round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
 round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
 round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
 nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
FROM dba_free_space fs,
 (select tablespace_name,
 sum(bytes) bytes,
 sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
 max(autoextensible) autoextensible
 from dba_data_files
 group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
UNION ALL
SELECT df.tablespace_name tablespace_name,
 max(df.autoextensible) auto_ext,
 round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
 round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
 round(df.bytes / (1024 * 1024), 2) curr_ts_size,
 round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
 round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
 round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
 nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
FROM (select tablespace_name, bytes_used bytes
 from V$temp_space_header
 group by tablespace_name, bytes_free, bytes_used) fs,
 (select tablespace_name,
 sum(bytes) bytes,
 sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
 max(autoextensible) autoextensible
 from dba_temp_files
 group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
ORDER BY 4 DESC;

Output from Tablespace Usage Query

This is the output you will get from the query:

TABLESPACE_NAME AUT MAX_TS_SIZE MAX_TS_PCT_USED CURR_TS_SIZE USED_TS_SIZE TS_PCT_USED FREE_TS_SIZE TS_PCT_FREE
--------------- --- ----------- --------------- ------------ ------------ ----------- ------------ -----------
VERS_DATA       YES     6061895           85.81      5270187   5201788.31        98.7     68398.69           1
VERS_INDX       YES  2391991.98           85.55   2101084.98   2046384.98        97.4        54700           3
SYSAUX          YES    45055.98           73.43        43788     33084.44       75.56     10703.56          24
UNDOTBS1        YES    32767.98           53.95        30565     17678.44       57.84     12886.56          42
VERS_INDEX      YES       32767            6.95         3072      2278.88       74.18       793.13          26
TEMP            YES  1245165.69            4.72       134141        58728       43.78        75413          56
SYSTEM          YES    32767.98            2.69          890       882.19       99.12         7.81           1
UNDOTBS2        YES    32767.98              .2        20555        64.38         .31     20490.63         100
SNP_DATA        YES       32767               0         1024            1          .1         1023         100
USERS           YES    32767.98               0            5         1.38        27.5         3.63          73

10 rows selected.

Column definitions

TABLESPACE_NAME: This is the Tablespace Name.

AUTO_EXT: If the datafiles are ‘Auto Extendable’ or not.

Please Note: This is using a max function, so if all are ‘NO’, then the ‘NO’ is true for all datafiles, however if one is ‘YES’, then the ‘YES’ is possible for one through to all of the datafiles.

MAX_TS_SIZE: This is the maximum Tablespace Size if all the datafile reach their max size.

MAX_TS_PCT_USED: This is the percent of MAX_TS_SIZE reached and is the most important value in the query, as this reflects the true usage before DBA intervention is required.

CURR_TS_SIZE: This is the current size of the Tablespace.

USED_TS_SIZE: This is how much of the CURR_TS_SIZE is used.

TS_PCT_USED: This is the percent of CURR_TS_SIZE which if ‘Auto Extendable’ is on, is a little meaningless.  Use MAX_TS_PCT_USED for actual usage.

FREE_TS_SIZE: This is how much is free in CURR_TS_SIZE.

TS_PCT_FREE: This is how much is free in CURR_TS_SIZE as a percent.

Please Note: All sizes are in Megabytes, this can be changed to Gigabytes by added a ‘/1024’ to the columns.

Related Blog Posts
Adding a datafile to tablespace

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

Extending a Non-root LVM Partition on Exadata

On an Oracle Exadata Database Machine, the ‘/u01’ is defaulted to a size of 100Gb, which can easily fill up.  Luckily this is just a Logical Volume and there’s normally lots of space available on the Logical Volume Group which is usually untapped.

 

Extending ‘/u01’

Identify how much space is used and free on ‘/u01’ using df:

[root@v1ex1dbadm01 ~]# df -h /u01
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VGExaDb-LVDbOra1
99G 78G 17G 83% /u01
[root@v1ex1dbadm01 ~]#

Display the current logical volume configuration using the lvs command:

[root@v1ex1dbadm01 ~]# lvs -o lv_name,lv_path,vg_name,lv_size
LV Path VG LSize
LVDbOra1 /dev/VGExaDb/LVDbOra1 VGExaDb 100.00g
LVDbSwap1 /dev/VGExaDb/LVDbSwap1 VGExaDb 24.00g
LVDbSys1 /dev/VGExaDb/LVDbSys1 VGExaDb 30.00g
LVDbSys2 /dev/VGExaDb/LVDbSys2 VGExaDb 30.00g
LVDoNotRemoveOrUse /dev/VGExaDb/LVDoNotRemoveOrUse VGExaDb 1.00g
[root@v1ex1dbadm01 ~]#

Check the online resize option is available using the tune2fs command:

[root@v1ex1dbadm01 ~]# tune2fs -l /dev/VGExaDb/LVDbOra1 | grep resize_inode
Filesystem features: has_journal ext_attr resize_inode dir_index filetype needs_recovery extent flex_bg sparse_super large_file huge_file uninit_bg dir_nlink extra_isize
[root@v1ex1dbadm01 ~]#

If not available then the file system needs to be unmounted before resizing.  Refer to documentation:

Extending a Non-root LVM Partition on Systems Running Oracle Exadata Storage Server Software Earlier than Release 11.2.3.2.1

Verify there’s space available in the Logical Volume Group using vgdisplay command:

[root@v1ex1dbadm01 ~]# vgdisplay -s
"VGExaDb" 1.63 TiB [185.00 GiB used / 1.45 TiB free]
[root@v1ex1dbadm01 ~]#

Finally if there’s enough space, then extend the Logical Volume using lvextend command:

[root@v1ex1dbadm01 ~]# lvextend -L +100G /dev/VGExaDb/LVDbOra1
Size of logical volume VGExaDb/LVDbOra1 changed from 100.00 GiB (25600 extents) to 200.00 GiB (51200 extents).
Logical volume LVDbOra1 successfully resized.
[root@v1ex1dbadm01 ~]#

Followed by a resize of the file system using resize2fs command:

[root@v1ex1dbadm01 ~]# resize2fs /dev/VGExaDb/LVDbOra1
resize2fs 1.43-WIP (20-Jun-2013)
Filesystem at /dev/VGExaDb/LVDbOra1 is mounted on /u01; on-line resizing required
old_desc_blocks = 7, new_desc_blocks = 13
Performing an on-line resize of /dev/VGExaDb/LVDbOra1 to 52428800 (4k) blocks.
The filesystem on /dev/VGExaDb/LVDbOra1 is now 52428800 blocks long.
[root@v1ex1dbadm01 ~]#

You should now see ‘/u01’ with additional 100Gb less formatting:

[root@v1ex1dbadm01 ~]# df -h /u01
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VGExaDb-LVDbOra1
197G 78G 110G 42% /u01
[root@v1ex1dbadm01 ~]#

Also see the Logical Volume is now 200Gb from 100Gb:

[root@v1ex1dbadm01 ~]# lvs -o lv_name,lv_path,vg_name,lv_size
LV Path VG LSize
LVDbOra1 /dev/VGExaDb/LVDbOra1 VGExaDb 200.00g
LVDbSwap1 /dev/VGExaDb/LVDbSwap1 VGExaDb 24.00g
LVDbSys1 /dev/VGExaDb/LVDbSys1 VGExaDb 30.00g
LVDbSys2 /dev/VGExaDb/LVDbSys2 VGExaDb 30.00g
LVDoNotRemoveOrUse /dev/VGExaDb/LVDoNotRemoveOrUse VGExaDb 1.00g
[root@v1ex1dbadm01 ~]#

 

Documentation for reference:

Extending a Non-root LVM Partition on Systems Running Oracle Exadata Storage Server Software Release 11.2.3.2.1 or Later

 

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

Cloning an Oracle Home

You may wish to clone an Oracle Home, for example you have all your databases on a single Oracle Home but you want to separate Development from Test.  This could be so you can soak test Patch Set Updates (PSU) on Development before applying to Test and then Production.  Or you might wish to have 2 Oracle Homes, so you can patch one and then switch all databases to the patched Oracle Home for minimal downtime.

 

Copying the Oracle Home

First you need to copy the Oracle Home at file level using cp as user root as shown below:

[root@v1ex2dbadm01 ~]# cp -Rp /u01/app/oracle/product/12.1.0.2/dbhome_1 /u01/app/oracle/product/12.1.0.2/dbhome_2

Then check the Oracle Home and the Cloned Oracle Home are the same size:

[root@v1ex2dbadm01 ~]# du -h /u01/app/oracle/product/12.1.0.2/dbhome_1 --max-depth=0
12G /u01/app/oracle/product/12.1.0.2/dbhome_1
[root@v1ex2dbadm01 ~]# du -h /u01/app/oracle/product/12.1.0.2/dbhome_2 --max-depth=0
12G /u01/app/oracle/product/12.1.0.2/dbhome_2

Then repeat on all the other nodes:

[root@v1ex2dbadm02 ~]# cp -Rp /u01/app/oracle/product/12.1.0.2/dbhome_1 /u01/app/oracle/product/12.1.0.2/dbhome_2
[root@v1ex2dbadm02 ~]# du -h /u01/app/oracle/product/12.1.0.2/dbhome_1 --max-depth=0
12G /u01/app/oracle/product/12.1.0.2/dbhome_1
[root@v1ex2dbadm02 ~]# du -h /u01/app/oracle/product/12.1.0.2/dbhome_2 --max-depth=0
12G /u01/app/oracle/product/12.1.0.2/dbhome_2

 

Cloning the Oracle Home

Now we have a copy of the Oracle Home, we next need to clone using the clone.pl perl script as shown below:

/usr/bin/perl $ORACLE_HOME/clone/bin/clone.pl \
'-O"CLUSTER_NODES={v1ex2dbadm01,v1ex2dbadm02}"' \
'-O"LOCAL_NODE=v1ex2dbadm01"' ORACLE_BASE=/u01/app/oracle \
ORACLE_HOME=$ORACLE_HOME ORACLE_HOME_NAME=OraDB12Home2 '-O-noConfig'

CLUSTER_NODES = all the nodes in the cluster
LOCAL_NODE = The node you are running the command on
ORACLE_BASE = The Oracle Base defined on the Server
ORACLE_HOME = The Cloned Oracle Home, already exported
ORACLE_HOME_NAME = The name you wish to give to the Cloned Oracle Home

[root@v1ex2dbadm01 ~]# su - oracle
[oracle@v1ex2dbadm01 ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_2
[oracle@v1ex2dbadm01 ~]$ /usr/bin/perl $ORACLE_HOME/clone/bin/clone.pl \
> '-O"CLUSTER_NODES={v1ex2dbadm01,v1ex2dbadm02}"' \
> '-O"LOCAL_NODE=v1ex2dbadm01"' ORACLE_BASE=/u01/app/oracle \
> ORACLE_HOME=$ORACLE_HOME ORACLE_HOME_NAME=OraDB12Home2 '-O-noConfig'
./runInstaller -clone -waitForCompletion "CLUSTER_NODES={v1ex2dbadm01,v1ex2dbadm02}" "LOCAL_NODE=v1ex2dbadm01" "ORACLE_BASE=/u01/app/oracle" "ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_2" "ORACLE_HOME_NAME=OraDB12Home2" -noConfig -silent -paramFile /u01/app/oracle/product/12.1.0.2/dbhome_2/clone/clone_oraparam.ini
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 500 MB. Actual 5392 MB Passed
Checking swap space: must be greater than 500 MB. Actual 24532 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2017-06-21_02-57-14PM. Please wait ...You can find the log of this install session at:
/u01/app/oraInventory/logs/cloneActions2017-06-21_02-57-14PM.log
 .................................................. 5% Done.
 .................................................. 10% Done.
 .................................................. 15% Done.
 .................................................. 20% Done.
 .................................................. 25% Done.
 .................................................. 30% Done.
 .................................................. 35% Done.
 .................................................. 40% Done.
 .................................................. 45% Done.
 .................................................. 50% Done.
 .................................................. 55% Done.
 .................................................. 60% Done.
 .................................................. 65% Done.
 .................................................. 70% Done.
 .................................................. 75% Done.
 .................................................. 80% Done.
 .................................................. 85% Done.
 ..........
Copy files in progress.

Copy files successful.

Link binaries in progress.

Link binaries successful.

Setup files in progress.

Setup files successful.

Setup Inventory in progress.

Setup Inventory successful.

Finish Setup successful.
The cloning of OraDB12Home2 was successful.
Please check '/u01/app/oraInventory/logs/cloneActions2017-06-21_02-57-14PM.log' for more details.

Setup Oracle Base in progress.

Setup Oracle Base successful.
 .................................................. 95% Done.

As a root user, execute the following script(s):
1. /u01/app/oracle/product/12.1.0.2/dbhome_2/root.sh

Execute /u01/app/oracle/product/12.1.0.2/dbhome_2/root.sh on the following nodes:
[v1ex2dbadm01]

.................................................. 100% Done.

[oracle@v1ex2dbadm01 ~]$

Next check the re-linking is RDS not UDP:

[oracle@v1ex2dbadm01 ~]$ /u01/app/oracle/product/12.1.0.2/dbhome_2/bin/skgxpinfo
rds
[oracle@v1ex2dbadm01 ~]$

If UDP, then relink using command below:

cd $ORACLE_HOME/rdbms/lib; ORACLE_HOME=$ORACLE_HOME make -f ins_rdbms.mk ipc_rds ioracle

Then repeat on all the other nodes, remember to change LOCAL_NODE:

[root@v1ex2dbadm02 ~]# su - oracle
[oracle@v1ex2dbadm02 ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_2
[oracle@v1ex2dbadm02 ~]$ /usr/bin/perl $ORACLE_HOME/clone/bin/clone.pl \
> '-O"CLUSTER_NODES={v1ex2dbadm01,v1ex2dbadm02}"' \
> '-O"LOCAL_NODE=v1ex2dbadm02"' ORACLE_BASE=/u01/app/oracle \
> ORACLE_HOME=$ORACLE_HOME ORACLE_HOME_NAME=OraDB12Home2 '-O-noConfig'
./runInstaller -clone -waitForCompletion "CLUSTER_NODES={v1ex2dbadm01,v1ex2dbadm02}" "LOCAL_NODE=v1ex2dbadm02" "ORACLE_BASE=/u01/app/oracle" "ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_2" "ORACLE_HOME_NAME=OraDB12Home2" -noConfig -silent -paramFile /u01/app/oracle/product/12.1.0.2/dbhome_2/clone/clone_oraparam.ini
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 500 MB. Actual 10725 MB Passed
Checking swap space: must be greater than 500 MB. Actual 24565 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2017-06-21_03-05-37PM. Please wait ...You can find the log of this install session at:
/u01/app/oraInventory/logs/cloneActions2017-06-21_03-05-37PM.log
 .................................................. 5% Done.
 .................................................. 10% Done.
 .................................................. 15% Done.
 .................................................. 20% Done.
 .................................................. 25% Done.
 .................................................. 30% Done.
 .................................................. 35% Done.
 .................................................. 40% Done.
 .................................................. 45% Done.
 .................................................. 50% Done.
 .................................................. 55% Done.
 .................................................. 60% Done.
 .................................................. 65% Done.
 .................................................. 70% Done.
 .................................................. 75% Done.
 .................................................. 80% Done.
 .................................................. 85% Done.
 ..........
Copy files in progress.

Copy files successful.

Link binaries in progress.

Link binaries successful.

Setup files in progress.

Setup files successful.

Setup Inventory in progress.

Setup Inventory successful.

Finish Setup successful.
The cloning of OraDB12Home2 was successful.
Please check '/u01/app/oraInventory/logs/cloneActions2017-06-21_03-05-37PM.log' for more details.

Setup Oracle Base in progress.

Setup Oracle Base successful.
 .................................................. 95% Done.

As a root user, execute the following script(s):
1. /u01/app/oracle/product/12.1.0.2/dbhome_2/root.sh

Execute /u01/app/oracle/product/12.1.0.2/dbhome_2/root.sh on the following nodes:
[v1ex2dbadm02]

.................................................. 100% Done.

[oracle@v1ex2dbadm02 ~]$ /u01/app/oracle/product/12.1.0.2/dbhome_2/bin/skgxpinfo
rds
[oracle@v1ex2dbadm02 ~]$

Next need to run root.sh as shown below:

[oracle@v1ex2dbadm01 ~]$ exit
logout
[root@v1ex2dbadm01 ~]# /u01/app/oracle/product/12.1.0.2/dbhome_2/root.sh
Check /u01/app/oracle/product/12.1.0.2/dbhome_2/install/root_v1ex2dbadm01.v1.com_2017-06-21_15-04-16.log for the output of root script
[root@v1ex2dbadm01 ~]#

Then repeat on all the other nodes:

[oracle@v1ex2dbadm02 ~]$ exit
logout
[root@v1ex2dbadm02 ~]# /u01/app/oracle/product/12.1.0.2/dbhome_2/root.sh
Check /u01/app/oracle/product/12.1.0.2/dbhome_2/install/root_v1ex2dbadm02.v1.com_2017-06-21_15-06-49.log for the output of root script
[root@v1ex2dbadm02 ~]#

Verify that the Cloned Oracle Home comprises of all the nodes in the cluster:

[root@v1ex2dbadm01 ~]# su - oracle
[oracle@v1ex2dbadm01 ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_2
[oracle@v1ex2dbadm01 ~]$ $ORACLE_HOME/OPatch/opatch lsinventory -oh $ORACLE_HOME | grep node
Rac system comprising of multiple nodes
Local node = v1ex2dbadm01
Remote node = v1ex2dbadm02
[oracle@v1ex2dbadm01 ~]$

Then repeat on all the other nodes:

[root@v1ex2dbadm02 ~]# su - oracle
[oracle@v1ex2dbadm02 ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_2
[oracle@v1ex2dbadm02 ~]$ $ORACLE_HOME/OPatch/opatch lsinventory -oh $ORACLE_HOME | grep node
Rac system comprising of multiple nodes
Local node = v1ex2dbadm02
Remote node = v1ex2dbadm01
[oracle@v1ex2dbadm02 ~]$

 

Switching Databases to Cloned Oracle Home

Change the Oracle Home using server control:

[oracle@v1ex2dbadm01 ~]$ srvctl config database -d V1DEV -a
Database unique name: V1DEV
Database name:
Oracle home: /u01/app/oracle/product/12.1.0.2/dbhome_1
Oracle user: oracle
Spfile: +DATAC1/V1DEV/PARAMETERFILE/spfileV1DEV.ora
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATAC1,RECOC1
Mount point paths:
Services: v1jobservice
Type: RAC
Start concurrency:
Stop concurrency:
Database is enabled
Database is individually enabled on nodes:
Database is individually disabled on nodes:
OSDBA group: dba
OSOPER group: dba
Database instances: V1DEV1,V1DEV2
Configured nodes: v1ex2dbadm01,v1ex2dbadm02
Database is administrator managed
[oracle@v1ex2dbadm01 ~]$ srvctl modify database -d V1DEV -o /u01/app/oracle/product/12.1.0.2/dbhome_2
[oracle@v1ex2dbadm01 ~]$ srvctl config database -d V1DEV -a
Database unique name: V1DEV
Database name:
Oracle home: /u01/app/oracle/product/12.1.0.2/dbhome_2
Oracle user: oracle
Spfile: +DATAC1/V1DEV/PARAMETERFILE/spfileV1DEV.ora
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATAC1,RECOC1
Mount point paths:
Services: v1jobservice
Type: RAC
Start concurrency:
Stop concurrency:
Database is enabled
Database is individually enabled on nodes:
Database is individually disabled on nodes:
OSDBA group: dba
OSOPER group: dba
Database instances: V1DEV1,V1DEV2
Configured nodes: v1ex2dbadm01,v1ex2dbadm02
Database is administrator managed
[oracle@v1ex2dbadm01 ~]$

Next change /etc/oratab to reflect the new Oracle Home:

[oracle@v1ex2dbadm01 ~]$ vi /etc/oratab
[oracle@v1ex2dbadm01 ~]$ more /etc/oratab | grep dbhome_2
V1DEV1:/u01/app/oracle/product/12.1.0.2/dbhome_2:N # line added by Agent

Then repeat on all the other nodes:

[oracle@v1ex2dbadm02 ~]$ vi /etc/oratab
[oracle@v1ex2dbadm02 ~]$ more /etc/oratab | grep dbhome_2
V1DEV2:/u01/app/oracle/product/12.1.0.2/dbhome_2:N # line added by Agent

Now we rolling bounce the database:

[oracle@v1ex2dbadm01 ~]$ srvctl status database -d V1DEV -v
Instance V1DEV1 is running on node v1ex2dbadm01. Instance status: Open,Running from Old Oracle Home.
Instance V1DEV2 is running on node v1ex2dbadm02 with online services v1jobservice. Instance status: Open,Running from Old Oracle Home.
[oracle@v1ex2dbadm01 ~]$ srvctl stop instance -d V1DEV -i V1DEV1 -f
[oracle@v1ex2dbadm01 ~]$ srvctl start instance -d V1DEV -i V1DEV1
[oracle@v1ex2dbadm01 ~]$ srvctl stop instance -d V1DEV -i V1DEV2 -f
[oracle@v1ex2dbadm01 ~]$ srvctl start instance -d V1DEV -i V1DEV2
[oracle@v1ex2dbadm01 ~]$ srvctl status database -d V1DEV -v
Instance V1DEV1 is running on node v1ex2dbadm01. Instance status: Open.
Instance V1DEV2 is running on node v1ex2dbadm02 with online services v1jobservice. Instance status: Open.
[oracle@v1ex2dbadm01 ~]$

 

Related My Oracle Support (MOS) notes:

Master Note For Cloning Oracle Database Server ORACLE_HOME’s Using the Oracle Universal Installer (OUI) (Doc ID 1154613.1)

Cloning An Existing Oracle Database 12c Release 1 (12.1.0.x) RDBMS Installation Using OUI (Doc ID 1493677.1)

Minimal downtime patching via cloning 11gR2 ORACLE_HOME directories (Doc ID 1136544.1)

 

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

Assess Performance using Calibrate on Exadata

For those who are fortunate to have an Oracle Exadata Database Machine, may wonder if their Exadata meets the IOPS/MBPS as per the technical specifications.  Well with the command CALIBRATE in CellCLI, you can run raw performance tests on the cells’ hard disks and flash drives, enabling you to verify the disk/drive performance:

[root@v1ex1celadm01 ~]# cellcli
CellCLI: Release 12.1.2.3.4 - Production on Tue Jun 13 19:02:05 IST 2017

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

CellCLI> calibrate force;
Calibration will take a few minutes...
Aggregate random read throughput across all hard disk LUNs: 1823 MBPS
Aggregate random read throughput across all flash disk LUNs: 9973 MBPS
Aggregate random read IOs per second (IOPS) across all hard disk LUNs: 3002
Calibrating hard disks (read only) ...
LUN 0_0 on drive [8:0 ] random read throughput: 152.00 MBPS, and 243 IOPS
LUN 0_1 on drive [8:1 ] random read throughput: 157.00 MBPS, and 246 IOPS
LUN 0_10 on drive [8:10 ] random read throughput: 161.00 MBPS, and 253 IOPS
LUN 0_11 on drive [8:11 ] random read throughput: 157.00 MBPS, and 251 IOPS
LUN 0_2 on drive [8:2 ] random read throughput: 157.00 MBPS, and 244 IOPS
LUN 0_3 on drive [8:3 ] random read throughput: 158.00 MBPS, and 245 IOPS
LUN 0_4 on drive [8:4 ] random read throughput: 156.00 MBPS, and 248 IOPS
LUN 0_5 on drive [8:5 ] random read throughput: 161.00 MBPS, and 250 IOPS
LUN 0_6 on drive [8:6 ] random read throughput: 159.00 MBPS, and 252 IOPS
LUN 0_7 on drive [8:7 ] random read throughput: 158.00 MBPS, and 251 IOPS
LUN 0_8 on drive [8:8 ] random read throughput: 157.00 MBPS, and 251 IOPS
LUN 0_9 on drive [8:9 ] random read throughput: 159.00 MBPS, and 254 IOPS
Calibrating flash disks (read only, note that writes will be significantly slower) ...
LUN 1_1 on drive [FLASH_1_1] random read throughput: 2,157.00 MBPS, and 280525 IOPS
LUN 2_1 on drive [FLASH_2_1] random read throughput: 2,156.00 MBPS, and 274304 IOPS
LUN 4_1 on drive [FLASH_4_1] random read throughput: 2,158.00 MBPS, and 282083 IOPS
LUN 5_1 on drive [FLASH_5_1] random read throughput: 2,160.00 MBPS, and 287786 IOPS
CALIBRATE results are within an acceptable range.
Calibration has finished.

CellCLI>

 

The CALIBRATE FORCE, allows the test to run when CELLSRV is still up, this is acceptable if there is no user workload.  It is therefore recommended to not run during normal operations.  Without the FORCE, the CELLSRV must be shut down.

PLEASE NOTE: This is a single run on a single storage cell, you will need to run on all storage cells in the Exadata Machine to get the total IOPS/MBPS.  You can use dcli to run this across all the cells 🙂

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

SNMP unresponsive on Storage Cell after Exadata Patching

After a round of Exadata patching, the Storage Cells become unresponsive to SNMP for monitoring tools like OpsViews:

check_snmp_sysinfo CRITICAL - Agent not responding, tried SNMP v1 and v2c

This is because the Storage Cells get re-imaged as part of the Exadata patching, confirmed by imagehistory:

[root@v1ex1celadm01 ~]# imagehistory
Version : 12.1.2.1.1.150316.2
Image activation date : 2015-05-01 16:10:10 -0700
Imaging mode : fresh
Imaging status : success

Version : 12.1.2.1.3.151021
Image activation date : 2015-12-16 05:25:21 +0000
Imaging mode : out of partition upgrade
Imaging status : success

Version : 12.1.2.2.1.160330
Image activation date : 2016-05-12 14:02:43 +0100
Imaging mode : out of partition upgrade
Imaging status : success

Version : 12.1.2.3.2.160721
Image activation date : 2016-10-05 01:45:42 +0100
Imaging mode : out of partition upgrade
Imaging status : success

Version : 12.1.2.3.3.161208
Image activation date : 2017-01-18 02:53:10 +0000
Imaging mode : out of partition upgrade
Imaging status : success

Version : 12.1.2.3.4.170111
Image activation date : 2017-05-23 10:58:24 +0100
Imaging mode : out of partition upgrade
Imaging status : success

[root@v1oex1celadm01 ~]#

Which knocks out the configuration in iptables and snmpd.conf.

To resolve add back in the lines for your primary and secondary monitoring servers, for example:

rocommunity V12V1 192.168.0.31
rocommunity V12V1 192.168.0.32

So looks like this:

[root@v1ex1celadm01 ~]# more /etc/snmp/snmpd.conf
trapcommunity public
trapsink 127.0.0.1 public
rocommunity public 127.0.0.1
rocommunity V12V1 192.168.0.31
rocommunity V12V1 192.168.0.32
rwcommunity public 127.0.0.1

access RWGroup "" any noauth exact all all all
com2sec snmpclient 127.0.0.1 public
group RWGroup v1 snmpclient

pass .1.3.6.1.4.1.4413.4.1 /usr/bin/ucd5820stat
pass .1.3.6.1.4.1.3582 /usr/sbin/lsi_mrdsnmpmain

syscontact Root <root@localhost> (configure /etc/snmp/snmp.local.conf)
syslocation Unknown (edit /etc/snmp/snmpd.conf)

view all included .1 80

[root@v1ex1celadm01 ~]#

Then reload snmp using the user root:

[root@v1ex2celadm01 ~]# service snmpd reload
Reloading snmpd: [ OK ]
[root@v1ex2celadm01 ~]#

Next add the monitoring servers primary and secondary to iptables as follows:

[root@v1ex1celadm01 ~]# iptables -I INPUT -s 192.168.0.31 -p udp --dport 161 -j ACCEPT
[root@v1ex1celadm01 ~]# iptables -I INPUT -s 192.168.0.32 -p udp --dport 161 -j ACCEPT

And make permanent by saving to firewall rules:

[root@v1ex1dbadm01 ~]# /etc/init.d/iptables save
iptables: Saving firewall rules to /etc/sysconfig/iptables:[ OK ]
[root@v1ex1dbadm01 ~]#

Now your monitoring should return as SNMP connectivity is restored 🙂 :

SNMP OK - "Linux v1ex1celadm01.v1.com 2.6.39-400.294.1.el6uek.x86_64 #1 SMP Wed Jan 11 08:46:38 PST 2017 x86_64"

 

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

How to easily delete files in the Oracle Cloud using CloudBerry Explorer

So you have some Oracle Cloud storage, which was probably thrown in as a freebie initially by Oracle 🙂  Now your freebie is expiring and you decide you want to retain the service but now as metered, so you want to delete all the unnecessary files, which in my case are old Oracle database backups.

As you can see here I have 50k plus files using 897Gb:

Oracle Cloud Web Console Details

Here are the files listed in the Web Console:

Oracle Cloud Web Console List Objects

To delete them one by one isn’t feasible.

So the solution is to use a 3rd party file explorer, in my case CloudBerry Explorer from CloudBerry Labs:

https://www.cloudberrylab.com/explorer/openstack.aspx

The freeware version is perfectly fine, no need to purchase Pro or use the trial.  Just click ‘Cancel’ on the Register Product dialogue and then the application will load.

Once installed, to connect to your Oracle Cloud storage, you can follow this link:

https://www.cloudberrylab.com/blog/how-to-use-cloudberry-explorer-with-oracle-cloud-storage/

However, the key to connecting is to have the username in the format of:

<Your Oracle Cloud Service Instance Name>-<Your Oracle Cloud Identity Domain>:<Your Oracle Cloud User Name>

e.g. zeddbacloud-zeddba:oraclecloudbackup@zeddba.com

Also select the correct ‘Account location’ which will fill the ‘Authentication Service’:

CloudBerry Login

Keystone, set to ‘Do not use’.

When you finally manage to get connected, you’ll see something like this:

CloudBerry Explorer

Now you have the freedom, to browse your files and delete at leisure 🙂

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)