Monthly Archives: August 2017

How to update OPatch

Posted on by Standard Reply

When applying patches, such as PSUs or one-offs, you may need to update OPatch to meet the minimum OPatch version.  It is also recommended to update OPatch when applying any patch.

To see your current OPatch version:

[oracle@v1ex1dbadm01 ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1
[oracle@v1ex1dbadm01 ~]$ $ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.1.0.1.3

OPatch succeeded.
[oracle@v1ex1dbadm01 ~]$

Backup existing OPatch:

[oracle@v1ex1dbadm01 ~]$ cd $ORACLE_HOME
[oracle@v1ex1dbadm01 dbhome_1]$ tar -cvf OPatch_backup.tar OPatch/*
OPatch/datapatch
OPatch/datapatch.bat
OPatch/docs/
...
OPatch/oplan/README.txt
OPatch/oplan/README.html
OPatch/oplan/oplan
[oracle@v1ex1dbadm01 dbhome_1]$

Check the backup of OPatch:

[oracle@v1ex1dbadm01 dbhome_1]$ ls -lh | grep OPatch_backup.tar
-rw-rw-r--. 1 oracle oracle 6.7M Aug 29 11:51 OPatch_backup.tar
[oracle@v1ex1dbadm01 dbhome_1]$

Remove the existing OPatch:

[oracle@v1ex1dbadm01 dbhome_1]$ rm -rf OPatch

Unzip the latest OPatch:

[oracle@v1ex1dbadm01 dbhome_1]$ unzip -d $ORACLE_HOME ~/sw/p6880880_122010_Linux-x86-64.zip
Archive: /home/oracle/sw/p6880880_122010_Linux-x86-64.zip
creating: /u01/app/oracle/product/12.1.0/dbhome_1/OPatch/
inflating: /u01/app/oracle/product/12.1.0/dbhome_1/OPatch/datapatch
...
inflating: /u01/app/oracle/product/12.1.0/dbhome_1/OPatch/docs/cversion.txt
inflating: /u01/app/oracle/product/12.1.0/dbhome_1/OPatch/docs/FAQ
inflating: /u01/app/oracle/product/12.1.0/dbhome_1/OPatch/opatch.bat
[oracle@v1ex1dbadm01 dbhome_1]$

Which can be found here:
OPatch – Where Can I Find the Latest Version of OPatch(6880880)? [Video] (Doc ID 224346.1)
OPATCH PLACEHOLDER Patch 6880880

To see your newOPatch version:

[oracle@v1ex1dbadm01 dbhome_1]$ $ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.2.0.1.9

OPatch succeeded.
[oracle@v1ex1dbadm01 dbhome_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)

How to Checksum Downloads

Posted on by Standard Reply

As a DBA, in your time, you will need to download files such as Oracle binaries, patches, etc.  As a rule of thumb, you should always checksum your downloads, to ensure they are free of corruption.  In my example, I needed to download:
Patch 25440397: QUARTERLY FULL STACK DOWNLOAD PATCH FOR EXADATA (Apr 2017 – 12.1.0.2)

This is the Quarterly Full Stack Download Patch for Exadata, which is 10 zip files that total 13Gb.  That’s a lot of bytes and although highly unlikely, it’s possible for one of them to become corrupt.  Therefore, to avoid downstream pain, check the downloads’ md5 checksum on your machine.  The Microsoft File Checksum Integrity Verifier (FCIV), can be found here:
How to compute the MD5 or SHA-1 cryptographic hash values for a file

Below shows the downloads’ MD5 checksum using FCIV:

Microsoft Windows [Version 10.0.14393]
(c) 2016 Microsoft Corporation. All rights reserved.

C:\Users\anwarz>C:\Users\anwarz\Downloads\md5\fciv.exe "C:\Users\anwarz\Exadata Patching 2017\p25440397_121020_Linux-x86-64_1of10.zip"
//
// File Checksum Integrity Verifier version 2.05.
//
855c3d128adb7f87eee04d7615f20e2b c:\users\anwarz\exadata patching 2017\p25440397_121020_linux-x86-64_1of10.zip

C:\Users\anwarz>C:\Users\anwarz\Downloads\md5\fciv.exe "C:\Users\anwarz\Exadata Patching 2017\p25440397_121020_Linux-x86-64_2of10.zip"
//
// File Checksum Integrity Verifier version 2.05.
//
93cea0dddd371e148f6cb3e35f9faa6a c:\users\anwarz\exadata patching 2017\p25440397_121020_linux-x86-64_2of10.zip

C:\Users\anwarz>C:\Users\anwarz\Downloads\md5\fciv.exe "C:\Users\anwarz\Exadata Patching 2017\p25440397_121020_Linux-x86-64_3of10.zip"
//
// File Checksum Integrity Verifier version 2.05.
//
22318acb7fa36154f0bb6d02115daba4 c:\users\anwarz\exadata patching 2017\p25440397_121020_linux-x86-64_3of10.zip

C:\Users\anwarz>C:\Users\anwarz\Downloads\md5\fciv.exe "C:\Users\anwarz\Exadata Patching 2017\p25440397_121020_Linux-x86-64_4of10.zip"
//
// File Checksum Integrity Verifier version 2.05.
//
28957a7ac3f2b05625cf3058fc97d981 c:\users\anwarz\exadata patching 2017\p25440397_121020_linux-x86-64_4of10.zip

C:\Users\anwarz>C:\Users\anwarz\Downloads\md5\fciv.exe "C:\Users\anwarz\Exadata Patching 2017\p25440397_121020_Linux-x86-64_5of10.zip"
//
// File Checksum Integrity Verifier version 2.05.
//
a1cdad9db470bb10eb14d85c3240fa64 c:\users\anwarz\exadata patching 2017\p25440397_121020_linux-x86-64_5of10.zip

C:\Users\anwarz>C:\Users\anwarz\Downloads\md5\fciv.exe "C:\Users\anwarz\Exadata Patching 2017\p25440397_121020_Linux-x86-64_6of10.zip"
//
// File Checksum Integrity Verifier version 2.05.
//
f358ebbf9e2fc222795176091939054c c:\users\anwarz\exadata patching 2017\p25440397_121020_linux-x86-64_6of10.zip

C:\Users\anwarz>C:\Users\anwarz\Downloads\md5\fciv.exe "C:\Users\anwarz\Exadata Patching 2017\p25440397_121020_Linux-x86-64_7of10.zip"
//
// File Checksum Integrity Verifier version 2.05.
//
3d20be91084b10fcb6fe3aa045346bf1 c:\users\anwarz\exadata patching 2017\p25440397_121020_linux-x86-64_7of10.zip

C:\Users\anwarz>C:\Users\anwarz\Downloads\md5\fciv.exe "C:\Users\anwarz\Exadata Patching 2017\p25440397_121020_Linux-x86-64_8of10.zip"
//
// File Checksum Integrity Verifier version 2.05.
//
7cebaca222e384957b8e65df0de6e6b1 c:\users\anwarz\exadata patching 2017\p25440397_121020_linux-x86-64_8of10.zip

C:\Users\anwarz>C:\Users\anwarz\Downloads\md5\fciv.exe "C:\Users\anwarz\Exadata Patching 2017\p25440397_121020_Linux-x86-64_9of10.zip"
//
// File Checksum Integrity Verifier version 2.05.
//
5a79f025564427a14ab7adce31d1a8bb c:\users\anwarz\exadata patching 2017\p25440397_121020_linux-x86-64_9of10.zip

C:\Users\anwarz>C:\Users\anwarz\Downloads\md5\fciv.exe "C:\Users\anwarz\Exadata Patching 2017\p25440397_121020_Linux-x86-64_10of10.zip"
//
// File Checksum Integrity Verifier version 2.05.
//
34b8bfe3792fa9dbd0cd13ba61bc5696 c:\users\anwarz\exadata patching 2017\p25440397_121020_linux-x86-64_10of10.zip

C:\Users\anwarz>

The MD5 checksums then can be verified with what Oracle Support list under the patch, click ‘Download’, then ‘View Digest Details’:

Patch_MD5

Once checked on your machine and then you transfer to server (assuming you didn’t download directly to server using WGET or something), then check again using md5sum:

[oracle@v1oex1dbadm01 25440397]$ md5sum p25440397_121020_linux-x86-64_1of10.zip
 855c3d128adb7f87eee04d7615f20e2b p25440397_121020_linux-x86-64_1of10.zip
[oracle@v1oex1dbadm01 25440397]$ md5sum p25440397_121020_linux-x86-64_2of10.zip
93cea0dddd371e148f6cb3e35f9faa6a p25440397_121020_linux-x86-64_2of10.zip
[oracle@v1oex1dbadm01 25440397]$ md5sum p25440397_121020_linux-x86-64_3of10.zip
22318acb7fa36154f0bb6d02115daba4 p25440397_121020_linux-x86-64_3of10.zip
[oracle@v1oex1dbadm01 25440397]$ md5sum p25440397_121020_linux-x86-64_4of10.zip
28957a7ac3f2b05625cf3058fc97d981 p25440397_121020_linux-x86-64_4of10.zip
[oracle@v1oex1dbadm01 25440397]$ md5sum p25440397_121020_linux-x86-64_5of10.zip
a1cdad9db470bb10eb14d85c3240fa64 p25440397_121020_linux-x86-64_5of10.zip
[oracle@v1oex1dbadm01 25440397]$ md5sum p25440397_121020_linux-x86-64_6of10.zip
f358ebbf9e2fc222795176091939054c p25440397_121020_linux-x86-64_6of10.zip
[oracle@v1oex1dbadm01 25440397]$ md5sum p25440397_121020_linux-x86-64_7of10.zip
3d20be91084b10fcb6fe3aa045346bf1 p25440397_121020_linux-x86-64_7of10.zip
[oracle@v1oex1dbadm01 25440397]$ md5sum p25440397_121020_linux-x86-64_8of10.zip
7cebaca222e384957b8e65df0de6e6b1 p25440397_121020_linux-x86-64_8of10.zip
[oracle@v1oex1dbadm01 25440397]$ md5sum p25440397_121020_linux-x86-64_9of10.zip
5a79f025564427a14ab7adce31d1a8bb p25440397_121020_linux-x86-64_9of10.zip
[oracle@v1oex1dbadm01 25440397]$ md5sum p25440397_121020_linux-x86-64_10of10.zip
34b8bfe3792fa9dbd0cd13ba61bc5696 p25440397_121020_linux-x86-64_10of10.zip
[oracle@v1oex1dbadm01 25440397]$

Once all checked, you’re free to unzip, knowing you’re free from corruption 🙂

Mind you, that doesn’t guarantee you wouldn’t get corruption from I/O but that would be extremely unlucky and you’ll have bigger problems.

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 obtain the Historical Database Total Used and Allocated Size from OEM Repository

Posted on by Standard 3

From time to time, it’s useful to know the total allocated size of a database at OS level, how much of it has been used and what the maximum total size the database can grow to at OS level, see blog post:
How to obtain the Database Total Used, Allocated and Max Size

However, it’s also good to know the historical size.  The below query will give you this from the Oracle Enterprise Manager (OEM) repository:

SELECT Database,
Month_Date,
round(sum(decode(metric_column, 'spaceUsed', maximum))/1024/1024, 3) Used_Size_Tb,
round(sum(decode(metric_column, 'spaceAllocated', maximum))/1024/1024, 3) Allocated_Size_Tb
FROM
(
SELECT target_name Database, trunc(rollup_timestamp, 'MONTH') Month_Date, key_value TB, metric_column, round(max(maximum),0) maximum
FROM mgmt$metric_daily
WHERE target_type = 'rac_database'
and metric_name = 'tbspAllocation'
and metric_column in ('spaceAllocated', 'spaceUsed')
and target_name in ('VERS')
GROUP BY target_name, key_value, trunc(rollup_timestamp, 'MONTH'), metric_column
)
GROUP BY Database, Month_Date
ORDER BY Database, Month_Date
/

Output:

DATABASE   MONTH_DAT USED_SIZE_TB ALLOCATED_SIZE_TB
---------- --------- ------------ -----------------
VERS       01-SEP-15        1.198             1.554
VERS       01-OCT-15        1.209             1.652
VERS       01-NOV-15          1.3             1.805
...
VERS       01-MAY-17        6.526             7.226
VERS       01-JUN-17        7.085             8.528
VERS       01-JUL-17        7.136             7.569

23 rows selected.

SQL>

The unit is in Tb, which should be suitable for most, however this can be changed by add/removing division of 1024.

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 obtain the Database Total Used, Allocated and Max Size

Posted on by Standard 3

From time to time, it’s useful to know the total allocated size of a database at OS level, how much of it has been used and what the maximum total size the database can grow to at OS level.

The below query will give you this:

SELECT round(sum(used_ts_size)/1024/1024, 2) total_used_db_size_tb,
 round(sum(curr_ts_size)/1024/1024, 2) total_current_db_size_tb,
 round(sum(max_ts_size)/1024/1024, 2) total_max_allocated_db_size_tb
FROM
(SELECT df.tablespace_name, (df.bytes - sum(fs.bytes)) / (1024 * 1024) used_ts_size,
df.bytes / (1024 * 1024) curr_ts_size,
df.maxbytes / (1024 * 1024) max_ts_size
FROM dba_free_space fs,
 (select tablespace_name,
 sum(bytes) bytes,
 sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes
 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);

Output:

SQL> SELECT round(sum(used_ts_size)/1024/1024, 2) total_used_db_size_tb,
 2 round(sum(curr_ts_size)/1024/1024, 2) total_current_db_size_tb,
 3 round(sum(max_ts_size)/1024/1024, 2) total_max_allocated_db_size_tb
 4 FROM
 5 (SELECT df.tablespace_name, (df.bytes - sum(fs.bytes)) / (1024 * 1024) used_ts_size,
 6 df.bytes / (1024 * 1024) curr_ts_size,
 7 df.maxbytes / (1024 * 1024) max_ts_size
 8 FROM dba_free_space fs,
 9 (select tablespace_name,
 10 sum(bytes) bytes,
 11 sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes
 12 from dba_data_files
 13 group by tablespace_name) df
 14 WHERE fs.tablespace_name (+) = df.tablespace_name
 15 GROUP BY df.tablespace_name,df.bytes,df.maxbytes);

TOTAL_USED_DB_SIZE_TB TOTAL_CURRENT_DB_SIZE_TB TOTAL_MAX_ALLOCATED_DB_SIZE_TB
--------------------- ------------------------ ------------------------------
                 7.15                     7.36                           9.04

SQL>

The unit is in Tb, which should be suitable for most, however this can be changed by add/removing division of 1024.

Related Post:
How to obtain the Historical Database Total Used and Allocated Size from OEM Repository

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)

KBHS-00600: Internal Error, Arguments [1] [kbhshtCreateDataBucket] Error During Backup To Oracle Cloud

Posted on by Standard 1

When setting up Oracle Cloud Backup Service for first time and the Oracle Database Cloud Backup Module has been installed successfully using:
Installing the Oracle Database Cloud Backup Module

Your RMAN session will hang and eventually give the following error:

Starting backup at 2017/08/10 12:56:58 
current log archived 
RMAN-00571: =========================================================== 
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== 
RMAN-00571: =========================================================== 
RMAN-03002: failure of backup plus archivelog command at 08/10/2017 16:20:05 
ORA-19554: error allocating device, device type: SBT_TAPE, device name: 
ORA-27023: skgfqsbi: media manager protocol error 
ORA-19511: non RMAN, but media manager or vendor specific failure, error text: 
KBHS-00600: internal error, arguments [1] [kbhshtCreateDataBucket] [] [] []

If the replication policy has not been set for this account, as explained in the MOS Note:
KBHS-00600: Internal Error, Arguments [1] [kbhshtCreateDataBucket] Error During Backup To Cloud (Doc ID 2232778.1)

Details of the replication policy can be found in the following documentation:
Selecting a Replication Policy for Your Service Instance

“Policies that have no georeplication:

These policies specify only the primary data center (DC) that hosts your service instance.

All read and write requests go to the primary DC, always. If the primary DC is unavailable, then the requests fail.

 Such a policy may be adequate if you have standard data-durability requirements and if an occasional failure of read requests (when the primary DC is down) is acceptable.

The Georeplication policies:

These policies specify a primary DC that hosts your service instance as well as a geographically distant, georeplication DC.

Write requests that you send to the global namespace URL are routed to the primary DC. Data that you write is replicated automatically, but asynchronously, to the georeplication DC. The primary and secondary DCs are eventually consistent.

 If the primary DC is unavailable, then write requests fail with the 403 – Forbidden error, but read requests are routed to the georeplication DC. When the primary DC is available again, requests to the global namespace URL are routed to the primary DC.

 You’ll be billed for the sum of the capacities used in both DCs and for the data transfer from the primary to the georeplication DC.

 A policy that has a georeplication DC is ideal if you have advanced durability requirements for your data or if read requests must succeed always regardless of the state of the primary DC.”

The first option, there’s a possibility of not being able to reach your backups but has the benefit of 1:1 ratio on storage used.   The second option has the benefit of guarantees being able to reach your backups but double storage usage and you are charged for data transfer between DC.

Once you select your georeplication policy, your backup will work 🙂

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)