DBA Blogs

Script to recompile the synonyms in a schema

Tom Kyte - 5 hours 12 min ago
Hi I have written the below script to recompile the synonyms in all the schemas but I am getting invalid character error. Could you check is there any wrong with the script: spool 'c:synonyms.txt' begin for i in (select object_name,ow...
Categories: DBA Blogs

Join (or equivalent) a collection to a table.

Tom Kyte - 5 hours 12 min ago
I need to build a procedure that will accept a collection of numbers that I need to then find matches in a table. If the elements of the collection were in another table, then it would be a simple case to join the tables. How can I accomplish this ...
Categories: DBA Blogs

Dedicated and Shared Mode

Tom Kyte - 5 hours 12 min ago
Hi Tom, How can we know that our database is running on shared mode or dedicated mode. Can we configure the database so that we can change the mode according to our need. Thanks, Snehasish Das.
Categories: DBA Blogs

Exceptions handling - how to rollback correctly

Tom Kyte - 5 hours 12 min ago
Hi everyone, my question is about how to correctly handling exception in a pl/sql procedure: I need to rollback everything made in a begin-end block if there's any kind of exception. Here's the example code: <code>create table prova (cod ...
Categories: DBA Blogs

Log_checkpoint_interval and timeout

Tom Kyte - 5 hours 12 min ago
Hi, It is rather confusing from documentation that the meaning of Log_checkpoint_interval and log_checkpoint_timeout between Oracle 8 and 8i versions. I believe that even though the definitions changed in 8i meaning is same,if so why did the...
Categories: DBA Blogs

Backup Oracle Databases to AWS S3

Pythian Group - Tue, 2017-02-21 10:17

There are different options for backing up Oracle databases to Cloud, but using Oracle Secure Backup module to take backups into AWS S3 is one of the most efficient methods in terms of costs and backup/restore performance.

In this post I will show you how to install, configure and use Oracle Secure Backup to take your Oracle database backups to AWS S3. This method can be used for Oracle database version 9.2 or higher.

In this example, database version is 12c and platform is Linux x86_64.

Oracle Secure Backup module must be installed into database Oracle Home. Using installed libraries you can then take backups via RMAN into AWS S3 the same way you backup to sbt_tape.

Requirements:

1- An AWS account and an IAM user with access to S3:

For setting up backups to AWS you will require an AWS account and an IAM user with full access to AWS S3. During setup Access Keys and Secret Access Key of this IAM user will be used. There is no need to have access to AWS Console.

You can use AWS Free tire for test purposes.

2- Oracle Secure Backup module for AWS:
You can download Oracle Secure Backup module for AWS from here

3- OTN account:
During installation you need to provide an OTN account.

4- Java 1.7 or higher:
Java 1.7 or higher must be installed on your server before you can proceed.

Installation:

1- Create Oracle Wallet Directory:

If Oracle Wallet directory does not exist, create one. This folder will be used to store AWS Access Keys and Secret Access Key.
Create this directory in $ORACLE_HOME/dbs/:


   $ cd $ORACLE_HOME/dbs/
   $ mkdir osbws_wallet

2- Download osbws_installer.zip from the link provided above and put in your installation folder, in this example /mnt/stage/osb , unzip the compressed file and you will have two files as shown below:


   $ pwd
   /mnt/stage/osb
   $ unzip osbws_installer.zip
   Archive:  osbws_installer.zip
     inflating: osbws_install.jar
     inflating: osbws_readme.txt
   $ ls
   osbws_installer.zip  osbws_install.jar  osbws_readme.txt

3- Install OSB Cloud Module for Amazon S3 into your Oracle Home:


   $ cd /mnt/stage/osb
   $ java -jar osbws_install.jar -AWSID XxXxX -AWSKey XxXxX -walletDir $ORACLE_HOME/osbws_wallet -libDir $ORACLE_HOME/lib -location ap-southeast-2 -awsEndPoint  s3-ap-southeast-2.amazonaws.com  -otnUser bakhshandeh@pythian.com -otnPass

Parameters that you will need to set for installation are as below:


  -AWSID:       AWS Access Key

  -AWSKey:      AWS Secret Access Key

  -walletDir:   Location where Backup Module will store AWS keys

  -libDir:      Location where Backup Module libraries will be installed

  -location:    This is AWS S3 location where you want to put your backups into. 
                Value for this parameter must be a valid Region from Amazon Regions.
                In this example "ap-southeast-2" which is region for "Asia Pacific (Sydney)" has been used

  -awsEndPoint: This should be valid end-point from location AWS region specified by "location" parameter
                In this example "s3-ap-southeast-2.amazonaws.com" has been used which is one of the end-points in ""Asia Pacific (Sydney)""

  -otnUser:     OTN Account

  -otnPass:     OTN Password

In my example I did not pass any value for -otnPass parameter and this was the only workaround I found for the error noted below during my tests:


   Downloading Oracle Secure Backup Web Service Software Library from file osbws_linux64.zip.
   Error: Library download failed. Please check your network connection to Oracle Public Cloud.

When I encountered this error I could only fix the issue by passing no value for otnPass, but it might work for you.

Running Backup using RMAN:

Installation will create a file in $ORACLE_HOME/dbs which is usually named osb<SID>.ora and you need to use full path of this file in your allocate channel command in RMAN.

In my example SID is KAMRAN


   $ cd $ORACLE_HOME/dbs
   $ pwd
   /apps/oracle/product/12.1.0.2/db_1/dbs
   $ ls -al osb*.ora
   -rw-r--r-- 1 oracle oinstall 194 Jan  5 11:31 osbwsKAMRAN.ora
   $

Content of this file is as below:


   $ cat osbwsKAMRAN.ora
   OSB_WS_HOST=http://s3-ap-southeast-2.amazonaws.com
   OSB_WS_LOCATION=ap-southeast-2
   OSB_WS_WALLET='location=file:/apps/oracle/product/12.1.0.2/db_1/dbs/osbws_wallet CREDENTIAL_ALIAS=gordon-s_aws'
   $

This file can be used for any other database in same Oracle Home. For this reason,I renamed it to osbwsCONFIG.ora so that name is generic and there is no dependency to any of databases.

mv osbwsKAMRAN.ora osbwsCONFIG.ora

I will use osbwsCONFIG.ora in RMAN channel settings.

Now you just need to allocate a channel for your backup/restore commands as below using above file as below:


   allocate channel c1 device type sbt parms='SBT_LIBRARY=libosbws.so,SBT_PARMS=(OSB_WS_PFILE=/apps/oracle/product/12.1.0.2/db_1/dbs/osbwsCONFIG.ora)';

This is a complete example which shows backup piece details and how they have been located in AWS S3 regions you specified during installation:


   $ . oraenv
   KAMRAN
   $ rman target /

   Recovery Manager: Release 12.1.0.2.0 - Production on Wed Dec 28 11:21:48 2016

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

   connected to target database: KAMRAN (DBID=283560064)

   RMAN>run{
   2> allocate channel c1 device type sbt parms='SBT_LIBRARY=libosbws.so,SBT_PARMS=(OSB_WS_PFILE=/apps/oracle/product/12.1.0.2/db_1/dbs/osbwsCONFIG.ora)';
   3> backup datafile 1;
   4> }

   released channel: ORA_DISK_1
   allocated channel: c1
   channel c1: SID=374 instance=KAMRAN device type=SBT_TAPE
   channel c1: Oracle Secure Backup Web Services Library VER=3.16.11.11

   Starting backup at 28-DEC-16
   channel c1: starting full datafile backup set
   channel c1: specifying datafile(s) in backup set
   input datafile file number=00001 name=+DATA/KAMRAN/DATAFILE/system.258.887023011
   channel c1: starting piece 1 at 28-DEC-16
   channel c1: finished piece 1 at 28-DEC-16
   piece handle=09rojka7_1_1 tag=TAG20161228T112807 comment=API Version 2.0,MMS Version 3.16.11.11
   channel c1: backup set complete, elapsed time: 00:00:45
   channel c1: starting full datafile backup set
   channel c1: specifying datafile(s) in backup set
   including current control file in backup set
   including current SPFILE in backup set
   channel c1: starting piece 1 at 28-DEC-16
   channel c1: finished piece 1 at 28-DEC-16
   piece handle=0arojkbl_1_1 tag=TAG20161228T112807 comment=API Version 2.0,MMS Version 3.16.11.11
   channel c1: backup set complete, elapsed time: 00:00:07
   Finished backup at 28-DEC-16
   released channel: c1

   RMAN> list backup tag TAG20161228T112807;


   List of Backup Sets
   ===================


   BS Key  Type LV Size       Device Type Elapsed Time Completion Time
   ------- ---- -- ---------- ----------- ------------ ---------------
   9       Full    741.75M    SBT_TAPE    00:00:38     28-DEC-16
           BP Key: 9   Status: AVAILABLE  Compressed: NO  Tag: TAG20161228T112807
           Handle: 09rojka7_1_1   Media: s3-ap-southeast-2.amazonaws.com/oracle-data-gordonsm-ap1
     List of Datafiles in backup set 9
     File LV Type Ckp SCN    Ckp Time  Name
     ---- -- ---- ---------- --------- ----
     1       Full 58915843   28-DEC-16 +DATA/KAMRAN/DATAFILE/system.258.887023011

   BS Key  Type LV Size       Device Type Elapsed Time Completion Time
   ------- ---- -- ---------- ----------- ------------ ---------------
   10      Full    22.50M     SBT_TAPE    00:00:01     28-DEC-16
           BP Key: 10   Status: AVAILABLE  Compressed: NO  Tag: TAG20161228T112807
           Handle: 0arojkbl_1_1   Media: s3-ap-southeast-2.amazonaws.com/oracle-data-gordonsm-ap1
     SPFILE Included: Modification time: 26-DEC-16
     SPFILE db_unique_name: KAMRAN
     Control File Included: Ckp SCN: 58915865     Ckp time: 28-DEC-16

   RMAN>
Some performance statistics:

I used a 340G database for testing performance and tried full backups into AWS S3 using different number of channels.
First, I allocated two channels and backup to AWS was complete in 48 minutes. I then tried four channels, and the backup to AWS was completed in 27 minutes.

I predicted that by increasing the number of channels to eight, would make backup complete faster. Surprisingly, with 8 channels backup completed in 27 minutes (which was exactly the same result when I used four channels).
So in my case, the optimum number of channels for taking backups to AWS S3 was four.

I should mention that same database when backed up to NFS disks using four channels it completed in 22 minutes, so backup time of 27 minutes to AWS was acceptable.

Restore was even faster. I tried restore without recovering the database, same 340G database full restore of databases from AWS backups completed in 22 minutes which again is acceptable.

Categories: DBA Blogs

TKPROF analysis: parses and executions

Tom Kyte - Tue, 2017-02-21 07:26
Hi Tom, Below is the execution plan for one of the sql. When i verify the query execution plan it is using index range scan and index unique scan. By seeing the below output how we will start the analysis. and can you advise on why is parse co...
Categories: DBA Blogs

Uncommitted transactions are committed after running DDL

Tom Kyte - Tue, 2017-02-21 07:26
Hi Tom, Why uncommitted DML transactions are committing after DDL transaction in oracle? Please help me to understand.
Categories: DBA Blogs

Log List of columns updated

Tom Kyte - Tue, 2017-02-21 07:26
Hi Chris/Connor, Please have a look at below scenario - Table ----- tb_temp_0001 Columns ------- order_id number -- PK cust_fname cust_lname dob address_1 address_2 address_3 debit_amnt credit_amnt Table ----- tb_app_0001 ...
Categories: DBA Blogs

Oracle utl_smtp to send e-mail Issue while increasing the number of emails in CC

Tom Kyte - Tue, 2017-02-21 07:26
<code>Recently I moved to oracle database Release 12.1.0.2.0, while using below mentioned code for send email through utl_smtp it works fine but when the number of emails in cc increase it generate the error ora-29278 smtp transient error 421 service...
Categories: DBA Blogs

12c Pro*C precompiler no longer supports ezconnect syntax?

Tom Kyte - Tue, 2017-02-21 07:26
Hi Tom, Did Oracle remove support for the ezconnect connect syntax in the userid= option on the 12c Pro*C precompiler? I recently attempted to update a Pro*C application that is compiled using the instant client and precompiler (instantclient-p...
Categories: DBA Blogs

Assessment for platform that uses both Oracle and MongoDB

Tom Kyte - Tue, 2017-02-21 07:26
Team, Very recently I got this question, not sure how to respond. could you help us on this ? <code>We are going to do an assessment of a platform that uses both Oracle and MongoDB. Can you please list down the artifacts that are required f...
Categories: DBA Blogs

SEQUENCE COntention

Tom Kyte - Tue, 2017-02-21 07:26
I need a query to find the sequence contention in oracle. Can you please provide it? Thanks in advance. Sam
Categories: DBA Blogs

Advantages and disadvantages of using Shareplex vs Dataguard for disaster recovery

Tom Kyte - Tue, 2017-02-21 07:26
Hi AskTom, Just need some advise on the setup of our database environment. We will have production instances A and B. A is live for external business and B is an exact copy of A but is used as operation data storage (B is live datawarehouse env)....
Categories: DBA Blogs

Latches and mutex

Tom Kyte - Tue, 2017-02-21 07:26
Dear Team, Can you please let me know few demonstration of latches and mutex types. one more thing can we take any preliminary action to avoid latches and mutex in future. want to know exact logic behind this. Thank's Pradeep
Categories: DBA Blogs

Using bitmap indexes in OLTP database with mostly inserts

Tom Kyte - Mon, 2017-02-20 13:06
Hi, We have a table for logging metadata about processed messages in a production system. The table have approx 32M rows today and 25 columns. The <b>total number of rows is expected to be around 100M</b> in the future. When the processing of a...
Categories: DBA Blogs

Trigger based on set of data

Tom Kyte - Mon, 2017-02-20 13:06
Hi All, i have a scenario where I want to create a trigger which will generate a flat file whenever a set of data like department number's(10,20,30,40,50...) changes(insert/updates) on a particular date(sysdate).
Categories: DBA Blogs

How to fetch up to 5MB of text/string/data from a larger a clob in oracle

Tom Kyte - Mon, 2017-02-20 13:06
Hi Oracle, <code> CREATE TABLE XMLISSUE ( xmltablecolumn clob ); </code> Created a table As shown below code I have inserted some data to clob <code> DECLARE XMLCLO...
Categories: DBA Blogs

when I drop a plsql function, 1 view goes invalid, 1 view and 1 procedure remain valid

Tom Kyte - Mon, 2017-02-20 13:06
We recently upgraded to database version 12c... We found an anomaly that we can't explain and wondering if this is a bug or a misunderstanding of new functionality within 12c. We can reproduce this issue on demand with the following example....
Categories: DBA Blogs

use of Block change tracking file for offline incremental backup

Tom Kyte - Mon, 2017-02-20 13:06
Hi , If block change tracking is ENABLED, Does oracle uses this file when we take an offline incremental backup ( in mount mode ) ? As the Db is not open , I think it can not access the file ( change tracking file), I believe the feature is only...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs