Skip navigation.

DBA Blogs

Pretty close to getting out speaker notifications for GLOC 2014 ...

Grumpy old DBA - Thu, 2014-02-27 18:51
Our official notifications should be out by mid week ( next Wednesday ) and we appreciate all of the people submitting abstracts.  Thank everyone of you very much from all of us involved in the conference.

Wow this is going to be another outstanding conference.  Darn going to have to not accept a large number of quality presentations also.  We do not have the time or room to run all the sessions that we would like to present.  So my apologies for that ... our only good choice is to grow the conference even larger in the future!

Now me personally just hoping for a semi quiet weekend to finish final preparations for Dallas/Hotsos 2014 and also hoping to have no problems flying down there sunday morning!

Stay tuned for more news on GLOC 2014!
Categories: DBA Blogs

Production RMAN recovery

Bobby Durrett's DBA Blog - Thu, 2014-02-27 10:31

I was on call again last week and again had to do a database recovery using RMAN.  I thought I might as well document some lessons learned even though it went quite smoothly.

First, here are the steps I took.  I’ve modified the commands and script so that our real usernames, passwords,  database names, and host names are obscured, but otherwise it is exactly what I ran.

Environment wise we are on 11.2.0.3 Oracle database running on HP-UX 11.31 on 64 bit Itanium.

First I had to build the Unix script that I would run to do the recovery:

rman target / catalog CATUSER/CATPASSWORD@CATDB <<EOF

run {
allocate channel 'dev_0' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=OURDB,OB2BARLIST=VTL_ourhost_OURDB_ora4)';
allocate channel 'dev_1' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=OURDB,OB2BARLIST=VTL_ourhost_OURDB_ora4)';
allocate channel 'dev_2' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=OURDB,OB2BARLIST=VTL_ourhost_OURDB_ora4)';
allocate channel 'dev_3' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=OURDB,OB2BARLIST=VTL_ourhost_OURDB_ora4)';
allocate channel 'dev_4' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=OURDB,OB2BARLIST=VTL_ourhost_OURDB_ora4)';
set until time "to_date('Feb 20 2014 21:38:10','Mon DD YYYY HH24:MI:SS')";
restore database;
recover database;
}

exit
EOF

In my previous recovery scenario that I blogged about I didn’t use the catalog because I was recovering to a different database just to retrieve data from before the failure I was trying to recover from.  In this case I was recovering the production database in place to our Thursday night backup that we took just before embarking on a PeopleTools upgrade.  So, first thing I had to do was look up the user, password, and connect string for the catalog.  Then I just tested connecting without doing anything.

rman target / catalog CATUSER/CATPASSWORD@CATDB
exit

Next I had to figure out the allocate channel commands.  In our case we have a graphical front end to our data protector backup system and I was able to extract the log from Thursday night’s backup.  I copied the allocate channel commands unchanged from the log to my script.

The hardest part of setting up this script was knowing what time to use in the set until time command.  I decided to use the last time that appeared on the backup output.  I knew that the application was down and so not much updating was occurring so I wasn’t worried about going past the earliest possible point that I could use.  I knew that our upgrade didn’t start until after the backup finished so I reasoned that the last backup message would be safe.  But, the backup program spits out messages in a different time zone than our database is in.  (I’m in Arizona, but my database is on Chicago time…).  So, getting the timezone right was key otherwise I could be off by an hour.  The date and time in the last backup message looked like this in Arizona time:

Time: 2/20/2014 8:38:10 PM

I had to translate it to

Feb 20 2014 21:38:10

This got it into central time and 24 hour format.

Lastly, I considered whether we had enough space for all the archive logs that would be retrieved for the recovery.  Since we were recovering from just after a hot backup and with the application down I decided that it couldn’t be more than one or two archive logs.  As it turns out the archive logs were on the disk when the recovery ran so they didn’t even need to be restored from tape.

To actually run the script I first manually put the database in mount mode:

sqlplus / as sysdba
shutdown immediate
startup mount

Then I ran the recovery nohup in the background:

nohup ./recover.sh > recover.out &

Lastly, after carefully reviewing the output from the recovery to make sure it looked right I opened the database resetting logs:

sqlplus / as sysdba
alter database open resetlogs;

It went very smoothly.  It was a little nerve-racking recovering the production system in place, but we did make two different backups of it before doing the recovery so presumably we could have gone back to the way it was before the recovery.

- Bobby

 

 

 

 

 

 

 

 

Categories: DBA Blogs

Modify Primary Key Using Index (Learning To Fly)

Richard Foote - Thu, 2014-02-27 00:34
One of the things I love about working with Oracle Database technologies is that there’s just so much one can learn. I make it an active goal of mine to try to learn something new at work each and every day, no matter how big or small. One of these days, I might blog about a presentation I put together […]
Categories: DBA Blogs

Explaining Foreign Key Index in One Illustration

Pythian Group - Wed, 2014-02-26 08:45

Think of the leash as an index:

fk

Categories: DBA Blogs

SYSBACKUP, SYSDG and SYSKM Administrative Users in Oracle 12c

VitalSoftTech - Wed, 2014-02-26 07:00

Large enterprise database environments have dedicated DBAs performing specific database roles. Some of these specific roles include database storage management, tuning, backup and recovery, etc. Inside these organizations there has been growing concern to further ensure the use of the SOD (Segregation of Duties) concept, to fulfill duties for these roles.

The post SYSBACKUP, SYSDG and SYSKM Administrative Users in Oracle 12c appeared first on VitalSoftTech.

Categories: DBA Blogs

WebLogic Suite: Foundation Infrastructure for Oracle iAS (Internet App Server) Implementations

WebLogic has become the strategic application server infrastructure for Oracle Fusion Middleware and Applications. It is designed to provide a standards-based, mission critical and secure...

We share our skills to maximize your revenue!
Categories: DBA Blogs

OEM Agent Core directory deleted/re-add–What Happen? (EXADATA)

DBASolved - Mon, 2014-02-24 14:43

Ever have one of those days when someone calls and says “We/I accidently deleted the whole directory; can you get it back for me”?  Well,  over the weekend I had that happen with an OEM 12c agent on an Exadata, where the core directory for the agent was deleted by mistake.  Before I could evaluate the situation, I had to reassure the end user that the removal of the core directory under the agent home wasn’t a major issue.  The agent was still running in memory and reporting to OEM.  For all intensive-purposes the agent was still monitoring the Exadata node. 

After the end user was assured that the problem could be fixed, the question became:  How can the missing core directory be replaced?

The simplest way is to do a reinstall of the agent silently; however, this takes a lot of time and effort to get it working again.  I wanted the shortest possible way to recover the directory so there would not be a huge window of unmonitored time. 

In this post, what I want to show you how I recovered the agent’s core directory and didn’t loose any targets or have to resync the agent from OEM afterwards.

Note:  I have a good post on agent silent installs located here which is helpful to understand some of the process that was used.  Although, I cover every environment in my post for silent installs, Maaz Anjum covers silent installs for windows pretty well too; check it out here

As I mention in the note above, I needed to pull the correct agent binaries from the OMS library (outlined in the silent install post).  Once I had the binaries extracted to a temporary location, I needed to edit the response file (agent.rsp).  The response file was edited according to the silent install post.

The values that were changed within the response file were for:

OMS_HOST
EM_UPLOAD_PORT
AGENT_REGISTRATION_PASSWORD
AGENT_INSTANCE_HOME
AGENT_PORT
ORACLE_HOSTNAME
s_agentHomeName

All the values for these variables need to match what the existing agent had (information can be found in OEM under Setup –> Manage Cloud Control –> Agents).

image

With the response file ready, before I can run a silent install; the currently running agent needs to be stopped.  With the core directory gone, the only way to stop the running agent is to using “ps –ef | grep agent” and “kill –9 <process id>”. 

Reminder: there will be two (2) process that need to be killed; one which is a Perl process and the other a Java process.  Once the processes are killed the agent that was running is down.

Once the agent is down, installing the agent software using the silent install method can be done.  Now, here is where the install process becomes different from a normal agent installation.  Earlier I said the core directory under the agent home was deleted.  This means that everything else is still in place; only have to relink the core directory of the binaries.   How can I get only the core directory out of the binaries? 

In trying to answer this question, I used the -help option with the agentDeploy.sh script.  The –help option provides a few examples on how to use the agentDeploy.sh script.  I have listed these examples below:

Example1:
         agentDeploy.sh AGENT_BASE_DIR=/scratch/agent OMS_HOST=hostname.domain.com EM_UPLOAD_PORT=1000 INVENTORY_LOCATION=/scratch AGENT_REGISTRATION_PASSWORD=2Bor02B4
        This command is expected to do the complete agent install and configuration with the provided inputs.

Example2:
         agentDeploy.sh AGENT_BASE_DIR=/scratch/agt RESPONSE_FILE=/scratch/agent.rsp -softwareOnly -invPtrLoc /scratch/agent/oraInst.loc -debug
        This command is expected to copy the agent bits to the agent base directory.

Example3:
         agentDeploy.sh AGENT_BASE_DIR=/scratch/agent OMS_HOST=hostname.domain.com EM_UPLOAD_PORT=1000 -forceConfigure
        This command is expected to do the agent install and also force the agent configuration even though the oms host and port are not available.

Example4:
         agentDeploy.sh AGENT_BASE_DIR=/scratch/agent AGENT_INSTANCE_HOME=/scratch/agent/agent_inst -configOnly
        This command is expected to do the agent configuration only with the provided inputs.

Example5:
         agentDeploy.sh AGENT_BASE_DIR=/scratch/agent s_agentHomeName=myAgent -ignorePrereqs
        This command is expected to skip the prereqs and then continue with the agent deployment also notice in the inventory that instead of the default home name, myAgent home name will be assigned for the agent home.

As I looked at the examples, I noticed Example 2; a software only install.  I decided to give that a try.  Keep in mind all I needed was the core directory.  The command I used to do a software only install was:

./agentDeploy.sh AGENT_BASE_DIR=/u01/app/oracle/product/agent12c RESPONSE_FILE=/tmp/agent_12030/agent.rsp –softwareOnly

As the deployment started, I noticed that the rebuilds and relinks for all the binaries was be performed on the the agent home.  Once the deployment is done updating all the dependences, the deployment completes successfully and returned me to the command problem.

image

The software only deployment of the silent install replaced the missing core directory in the agent home.  Now the only question left was will the agent start?

To test if the agent would start, I needed to go into the agent home:

$ cd /u01/app/oracle/product/agent12c/core/12.1.0.3.0/bin

$ ./emctl status agent

In running the above commands, I was expecting to see the agent status as being down since I have just completed the agent deployment.  What I received instead is an unusual error.  The error was:

$ ./emctl status agent

EM Configuration issue. #DEFAULT_EMSTATE# not found.

In researching this error (#DEFAULT_EMSTATE#) in My Oracle Support (MOS), there were only two notes that were found (1607805.1/1543473.1).  From reading the notes and reviewing the emctl file under the core directory, I identified that the problem was a configuration problem.  In order to fix this configuration problem, what needed to be done?

To make a long story short, the simplest way to fix this issue was to copy an emctl from another Exadata node.    The reason why this was the simplest is due to all the nodes have the same agent home configurations.  Once the updated emctl was put in place, I was able to start and get all the information I wanted from the agent.

image

With the agent running, my next question was what was OEM’s reaction to the agent being reconfigured/built this way?   To my surprise, OEM didn’t have a problem. The agent was able to upload with no issues and OEM reported that no re-syncing was needed.  The only thing I can conclude from this is that the configuration files were never deleted and when the core directory was relinked, OEM thinks everything is as it was before the core directory was deleted.

Enjoy!

twitter: @dbasolved

blog: http://dbasolved.com


Filed under: Exadata, Golden Gate
Categories: DBA Blogs

Nothing to Blog About? Think Again!

Pythian Group - Mon, 2014-02-24 08:46

How often do you run off to your favourite sites and devour information put together by others? It’s quite remarkable how hard stuff is until you’ve done it one or more times. The seemingly insurmountable task becomes second nature once mastered. Hey, Alex Gorbachev or Jonathan Lewis were once beginners just like you and me. In the days when I got started there was no internet, no Twitter, no Metalink (MOS), and little, if no email. We used the good old-fashioned phone … a traditional landline at that. They used to have  round apparatus with holes in them called “dials.”

Something you have done may be a mystery to others and the seemingly most menial tasks (to you) may be like a vertical wall to others. Think back to 10 things you have done in the past few weeks. Estimate the number of those 10 things that would be “news” to others… Got that right… All 10. We owe it to others to blog about what we do on a daily basis and go out of our ways to find time to educate the masses.

To shed some light on an example that went down in the early to mid ’90s, picture the following (purveyors of SQL*Forms 2 and 3 may remember this). Triggers were used in a way similar to they are today. Events occurred as we moved around a screen (character-based at that :)). A common trigger was called POST-CHANGE and I became frustrated as we went to Forms 3 remembering that this trigger would not fire until the cursor left a field. I needed a way to execute a trigger while the cursor still resided in a field. Along comes a developer with 3 months experience. She suggests coding an ON-VALIDATE-FIELD trigger. Swell I said to myself knowing well that this trigger as well would not fire until the cursor left the field. So as not to offend here, I did just that. She also suggested placing the text “ENTER;” in the trigger code and all would proceed exactly as hoped.

I tried it out after chuckling to myself, based on what I already knew about Forms and it WORKED. There is the rub… No matter how little you may know, your expertise may lie in a corner of technology, others have not experienced yet. Your experiences are valuable to others and it is your obligation to blog. Nothing to blog about — think again.

Categories: DBA Blogs

Log Buffer #360, A Carnival of the Vanities for DBAs

Pythian Group - Mon, 2014-02-24 08:40

This week, there are some announcements from the database arena and then there are some tips and tricks coupled with some cool techniques, and this Log Buffer covers some of them.

Oracle:

startCD options to install Oracle E-Business Suite Release 12.2.

IDC Big Data in the Enterprise: When Worlds Collide.

Friday prank: select from join join join.

Friday Spotlight: Vitual Sysadmin Day and Hands On Labs

Are You “Millennial Ready”?

SQL Server:

Show the results for a date even if there is no data.

Hosting SQL Server in Hyper-V Replica Environment.

SQL Saturday is coming to Vienna on March 6 with a full-day of technical training and networking, featuring international speakers.

Understanding Graphical Execution Plans – Part 2: How to Create One.

SQL Server security related tasks can be divided into four main categories.

MySQL:

Before every release: A glimpse into Percona XtraDB Cluster CI testing.

MySQL Connector/Python v1.1.6 GA

No Hadoop Fun for Me at SCaLE 12X :(

FromDual.en: Why FromDual customers are using Galera Cluster for MySQL

Percona XtraDB Cluster 5.6.15-25.4 is now available.

Categories: DBA Blogs

RMAN Image Copy File Names

Hemant K Chitale - Sun, 2014-02-23 09:38
RMAN provides a convenient way to name Image Copies (backups) of datafiles to retain the same file name without the path.  This is possible with the "%b" FORMAT modifier.

Here's a demo :

[oracle@localhost ~]$ cd /IMA*/ORCL*
[oracle@localhost ORCL_DB]$ pwd
/IMAGE_BACKUP/ORCL_DB
[oracle@localhost ORCL_DB]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Sun Feb 23 23:24:55 2014

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

connected to target database: ORCL (DBID=1229390655)

RMAN> backup as copy database format '/IMAGE_BACKUP/ORCL_DB/%b';


Starting backup at 23-FEB-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=37 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
output file name=/IMAGE_BACKUP/ORCL_DB/sysaux01.dbf tag=TAG20140223T232527 RECID=3 STAMP=840324368
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/home/oracle/app/oracle/oradata/orcl/system01.dbf
output file name=/IMAGE_BACKUP/ORCL_DB/system01.dbf tag=TAG20140223T232527 RECID=4 STAMP=840324400
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/home/oracle/app/oracle/oradata/orcl/users01.dbf
output file name=/IMAGE_BACKUP/ORCL_DB/users01.dbf tag=TAG20140223T232527 RECID=5 STAMP=840324414
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00011 name=/home/oracle/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_hemant_8pnowslc_.dbf
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 02/23/2014 23:26:55
ORA-01276: Cannot add file /IMAGE_BACKUP/ORCL_DB/o1_mf_hemant_8pnowslc_.dbf. File has an Oracle Managed Files file name.
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
output file name=/IMAGE_BACKUP/ORCL_DB/undotbs01.dbf tag=TAG20140223T232527 RECID=6 STAMP=840324420
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/home/oracle/app/oracle/oradata/orcl/example01.dbf
output file name=/IMAGE_BACKUP/ORCL_DB/example01.dbf tag=TAG20140223T232527 RECID=7 STAMP=840324428
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
copying current control file
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 02/23/2014 23:27:12
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 ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 23-FEB-14
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 02/23/2014 23:27:12
ORA-19715: invalid format b for generated name
ORA-27302: failure occurred at: slgpn

RMAN>
RMAN> quit


Recovery Manager complete.
[oracle@localhost ORCL_DB]$ pwd
/IMAGE_BACKUP/ORCL_DB
[oracle@localhost ORCL_DB]$ pwd
/IMAGE_BACKUP/ORCL_DB
[oracle@localhost ORCL_DB]$ ls -l
total 2641496
-rw-rw---- 1 oracle oracle 85991424 Feb 23 23:27 example01.dbf
-rw-rw---- 1 oracle oracle 1320165376 Feb 23 23:26 sysaux01.dbf
-rw-rw---- 1 oracle oracle 881860608 Feb 23 23:26 system01.dbf
-rw-rw---- 1 oracle oracle 178266112 Feb 23 23:27 undotbs01.dbf
-rw-rw---- 1 oracle oracle 235937792 Feb 23 23:26 users01.dbf
[oracle@localhost ORCL_DB]$


However, this fails with OMF files and controlfile auto backups.

Also, you have to be careful to check that you do not have two datafiles with the same name in two different folders. RMAN recognises the presence of the first datafile copied and raises an error on the second datafile

For example :

SQL> create tablespace test_tbs datafile '/home/oracle/app/oracle/oradata/orcl/test_tbs_01.dbf' size 100M;

Tablespace created.

SQL> alter tablespace test_tbs add datafile '/tmp/test_tbs_01.dbf' size 50M;

Tablespace altered.

SQL>
SQL> !ls -l /home/oracle/app/oracle/oradata/orcl/test_tbs_01.dbf
-rw-rw---- 1 oracle oracle 104865792 Feb 23 23:34 /home/oracle/app/oracle/oradata/orcl/test_tbs_01.dbf

SQL> !ls -l /tmp/test_tbs_01.dbf
-rw-rw---- 1 oracle oracle 52436992 Feb 23 23:34 /tmp/test_tbs_01.dbf

SQL> exit

RMAN> backup as copy tablespace test_tbs format '/IMAGE_BACKUP/ORCL_DB/%b';

Starting backup at 23-FEB-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=47 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/orcl/test_tbs_01.dbf
output file name=/IMAGE_BACKUP/ORCL_DB/test_tbs_01.dbf tag=TAG20140223T233608 RECID=8 STAMP=840324970
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/tmp/test_tbs_01.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 02/23/2014 23:36:13
ORA-19504: failed to create file "/IMAGE_BACKUP/ORCL_DB/test_tbs_01.dbf"
ORA-27038: created file already exists
Additional information: 1

RMAN>

The %b format for datafiles is also supposed to be usable with the SET NEWNAME clause in a RESTORE run.

.
.
.
Categories: DBA Blogs

emergency monitoring and forcing a flush of the shared pool ... use with caution ONLY if really needed

Grumpy old DBA - Sat, 2014-02-22 13:11
Flushing the shared pool has an impact on systems forcing at least the re parsing of sql statements and new ( perhaps even changed ) execution plans.  Use with caution on any production environment and test/test before deploying anything like this.

The usual approach for a system suffering from shared pool fragmentation and/or 4031 is to identify sql not using bind variables and consider implementing ( after logon session based database trigger best ) CURSOR_SHARING = FORCE ... but at times even that may need to be supplemented with some emergency monitoring and flushing.

This code below needs to be looked at and tested in your environment ( test system first ) ... may need setup and grants put in place to get it operational.  It currently is hard coded to flush when less than 256 mb is available as free memory ... could be kicked off every minute by some kind of scheduler or cron job or database job.

CREATE OR REPLACE PROCEDURE FLUSH_SHARE_POOL is
   v_free_space_meg NUMBER;
BEGIN
   SELECT round(bytes/1024/1024,0) INTO v_free_space_meg FROM v$sgastat
    WHERE name = 'free memory' AND pool =  'shared pool';
   IF ( v_free_space_meg < 256 ) THEN
      EXECUTE IMMEDIATE 'alter system flush shared_pool';
   END IF;    
END FLUSH_SHARE_POOL;
/
Categories: DBA Blogs

Oracle SOA Suite 11g - The Ingredient for Innovation: New Integration Adapters

Enterprise of all sizes, are evaluating cloud computing. Many businesses are making new economic investments in public cloud, while they continue to rely on their existing on-premise IT investments....

We share our skills to maximize your revenue!
Categories: DBA Blogs

SQL Analytics

Hemant K Chitale - Fri, 2014-02-21 22:56
Oracle Learning Library video series on SQL Analytics
SQL Analytics Overview Video Series.
.
.
Categories: DBA Blogs

How many times can you practice and revise a presentation?

Grumpy old DBA - Fri, 2014-02-21 18:51
Well the obvious answers are "it depends" and "how far away is the actual presentation"?  For Hotsos 2014 I had the vast majority of the work done in early December 2013 ... not bad for a presentation not delivered until March 3 2014.

That was after a couple of series of revisions and after all this was based on a presentation originally done at Oracle Open World 2011.

It makes even an experienced and grumpy old Oracle professional more than a little nervous delivering something at Hotsos however.  After another series of changes and improvements in January I put it away for a while.  Just back at it again about 10 days ago.  I sent in Hotsos "yet another" final version but is it actually the final version ... heck no!

Last week on Wednesday I delivered a dry run of the presentation to several of my co-workers along with a couple of NEOOUG fellow board members.  Big thanks to Pete Dinin from Sherwin Williams for some great suggestions and comments.  He was typing away during the run ... I thought he was doing work related email ... but no ... comments and suggestions on the presentation.  So Pete as always I owe you!

Two more weekends before I actually do the presentation.  Going to alternate days when I do not look at it at all and other days when I run through the material and the slides.

Working on some dry DBA humor to get some laughs ... wish me luck!

Categories: DBA Blogs

Oracle JDBC Connection Tester

DBASolved - Thu, 2014-02-20 12:08

From time to time, when working at various sites I have to check connections to a database.  If you have been an Oracle DBA for some time, you already know that the listener has to be up and services have to be registered before you can connect from a remote host.  If the listener is up and services are registered, then you should be able to get to the database; so why the need for a connection tester?

Recently, I’ve been asked to verify connections for a few databases to provide management a “warm-and-fuzzy” about their connections to environments; also helps in troubleshooting if you know the service name.  I have been asked to verify connections more than once in my career and always wanted a simple way that I could test without logging into the database host or needing to use an Oracle client.  Hence the reason I wrote this simple JDBC connection tool.

This tool is simple to use, it takes the following to build the URL for connection to Oracle:

  • Hostname/IP Address
  • Listener Port
  • SID/Service Name
  • Username
  • Password

Note: The password will be passed in clear text!

Once the requirements for the URL are provided, the tool attempts to login to the database with the username and password provided.  If the listener and services are working correctly, the tool will return a successful message.  If not, the tool will provide an error stack with the reason why it failed to connect.

I am making the tool available here if you would like to test or use it.   

In order to run the tool, it will need to be ran from the command prompt and Java should be in your path.  The tool was written against JDK 1.7 and uses the ojdbc6.jar which is provided with Oracle 11g.  Also, the ojdbc6.jar file is provided in the lib directory after unzipping the tool.  The README.txt file that comes with the tool explains how to run the tool.

Here is a screenshot of a run done previously.

image

Enjoy!

twitter: @dbasolved

blog: http://dbasolved.com


Filed under: Database, General
Categories: DBA Blogs

An SQL Performance Quiz

Hemant K Chitale - Thu, 2014-02-20 09:19
Markus Winand has written a very small SQL Performance Quiz.  (I scored 5 out of 5 on Oracle).
He has published a review of the results.  A very large number of visitors have failed.

.
.
.


Categories: DBA Blogs

What Happens When Active DB Duplication Goes Wrong?

Pythian Group - Tue, 2014-02-18 13:57

There are many blog posts out there about active database duplication. However, they were all tested in an ideal environment or condition. What happens when a tablespace is created during the middle of active duplication and how to resolve the error? Read on if you would like to know.

For my test case, I created database db01 using OMF and will perform active duplication to db02 using OMF as well on the same host. While duplication was running, I created a new tablespace. Here are the details of the steps performed:

Review files and pfile for TARGET database:

[oracle@arrow:db02]/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs
$ ll
total 7692
-rw-rw----. 1 oracle oinstall    1544 Feb 14 13:06 hc_db01.dat
-rwxr-x---. 1 oracle oinstall     590 Feb 13 08:14 initdb01.ora
-rwxr-x---. 1 oracle oinstall     590 Feb 13 08:14 initdb02.ora
-rw-r-----. 1 oracle oinstall      24 Feb 13 08:18 lkDB01
-rw-r-----. 1 oracle oinstall       0 Feb 14 13:06 lkinstdb01
-rw-r-----. 1 oracle oinstall    2048 Feb 11 14:00 orapwdb01
-rw-r-----. 1 oracle oinstall    2048 Feb 11 15:48 orapwdb02
-rw-r-----. 1 oracle oinstall 7847936 Feb 11 18:06 snapcf_db01.f
-rw-r-----. 1 oracle oinstall    3584 Feb 14 13:05 spfiledb01.ora

[oracle@arrow:db02]/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs
$ cat initdb02.ora
*.audit_file_dest='/u01/app/oracle/admin/adump'
*.audit_trail='none'
*.compatible='11.2.0.4.0'
*.db_block_size=8192
*.db_create_file_dest='/oradata'
*.db_domain=''
*.db_name='db02'
*.db_recovery_file_dest='/oradata/fra'
*.db_recovery_file_dest_size=4g
*.diagnostic_dest='/u01/app/oracle'
*.event='10795 trace name context forever, level 2'
*.fast_start_mttr_target=300
*.java_pool_size=0
*.local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1531))'
*.pga_aggregate_target=268435456
*.processes=100
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=805306368
*.undo_tablespace='UNDOTBS'

[oracle@arrow:db02]/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs
$ diff ./initdb01.ora ./initdb02.ora
7c7
< *.db_name='db01' --- > *.db_name='db02'
[oracle@arrow:db02]/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs
$

Notice there is only one difference between in the pfile for db01 and db02

Create same directory structures for TARGET database:

[oracle@arrow:]/oradata
$ ls DB*
DB01:
controlfile  datafile  onlinelog

DB02:
controlfile  datafile  onlinelog
[oracle@arrow:]/oradata
$

Startup NOMOUNT TARGET database:

[oracle@arrow:db02]/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 14 13:11:13 2014

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

Connected to an idle instance.

SYS@db02> startup nomount;
ORACLE instance started.

Total System Global Area  801701888 bytes
Fixed Size                  2257520 bytes
Variable Size             222301584 bytes
Database Buffers          570425344 bytes
Redo Buffers                6717440 bytes
SYS@db02>

Start active database duplication:

[oracle@arrow:db01]/media/sf_linux_x64/rman
$ rman @dupdbomf.rman

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Feb 14 13:12:04 2014

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

RMAN> connect target *
2> connect auxiliary *
3> run {
4> allocate channel c1 type disk maxopenfiles 1;
5> allocate auxiliary channel a1 type disk;
6> duplicate target database to db02
7>   from active database nofilenamecheck
8>   spfile
9>   PARAMETER_VALUE_CONVERT ('DB01','DB02')
10> ;
11> }
12> exit;
connected to target database: DB01 (DBID=1470673955)

connected to auxiliary database: DB02 (not mounted)

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=14 device type=DISK

allocated channel: a1
channel a1: SID=96 device type=DISK

Starting Duplicate Db at 14-FEB-2014 13:12:07

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfiledb01.ora' auxiliary format
 '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfiledb02.ora'   ;
   sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfiledb02.ora''";
}
executing Memory Script

Starting backup at 14-FEB-2014 13:12:08
Finished backup at 14-FEB-2014 13:12:09

sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfiledb02.ora''

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''DB02'' comment=
 ''duplicate'' scope=spfile";
   sql clone "alter system set  control_files =
 ''/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl'', ''/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.ctl'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''DB02'' comment= ''duplicate'' scope=spfile

sql statement: alter system set  control_files =  ''/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl'', ''/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.c                                                                              tl'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     801701888 bytes

Fixed Size                     2257520 bytes
Variable Size                222301584 bytes
Database Buffers             570425344 bytes
Redo Buffers                   6717440 bytes
allocated channel: a1
channel a1: SID=95 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  control_files =
  ''/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl'', ''/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.ctl'' comment=
 ''Set by RMAN'' scope=spfile";
   sql clone "alter system set  db_name =
 ''DB01'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''DB02'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  '/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl';
   restore clone controlfile to  '/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.ctl' from
 '/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl';
   sql clone "alter system set  control_files =
  ''/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl'', ''/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.ctl'' comment=
 ''Set by RMAN'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  control_files =   ''/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl'', ''/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.ctl'' comment= ''Set by RMAN'' scope=spfile

sql statement: alter system set  db_name =  ''DB01'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''DB02'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     801701888 bytes

Fixed Size                     2257520 bytes
Variable Size                222301584 bytes
Database Buffers             570425344 bytes
Redo Buffers                   6717440 bytes
allocated channel: a1
channel a1: SID=95 device type=DISK

Starting backup at 14-FEB-2014 13:12:55
channel c1: starting datafile copy
copying current control file
output file name=/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/snapcf_db01.f tag=TAG20140214T131255 RECID=1 STAMP=839509978
channel c1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 14-FEB-2014 13:13:03

Starting restore at 14-FEB-2014 13:13:03

channel a1: copied control file copy
Finished restore at 14-FEB-2014 13:13:04

sql statement: alter system set  control_files =   ''/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl'', ''/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.ctl'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     801701888 bytes

Fixed Size                     2257520 bytes
Variable Size                222301584 bytes
Database Buffers             570425344 bytes
Redo Buffers                   6717440 bytes
allocated channel: a1
channel a1: SID=95 device type=DISK

database mounted

contents of Memory Script:
{
   set newname for clone datafile  1 to new;
   set newname for clone datafile  2 to new;
   set newname for clone datafile  3 to new;
   set newname for clone datafile  4 to new;
   backup as copy reuse
   datafile  1 auxiliary format new
   datafile  2 auxiliary format new
   datafile  3 auxiliary format new
   datafile  4 auxiliary format new
   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 14-FEB-2014 13:13:21

----------------------------------------------------------------------
-- While duplication was running, create new tablespace at source
--
[oracle@arrow:db01]/home/oracle
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 14 13:13:22 2014

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ARROW:(SYS@db01):PRIMARY> create tablespace mdinh;

Tablespace created.

ARROW:(SYS@db01):PRIMARY>
----------------------------------------------------------------------

channel c1: starting datafile copy
input datafile file number=00001 name=/oradata/DB01/datafile/o1_mf_system_9hsw4shz_.dbf
output file name=/oradata/DB02/datafile/o1_mf_system_02p0jpvh_.dbf tag=TAG20140214T131321
channel c1: datafile copy complete, elapsed time: 00:01:26
channel c1: starting datafile copy
input datafile file number=00002 name=/oradata/DB01/datafile/o1_mf_sysaux_9hsw63d2_.dbf
output file name=/oradata/DB02/datafile/o1_mf_sysaux_03p0jq27_.dbf tag=TAG20140214T131321
channel c1: datafile copy complete, elapsed time: 00:00:35
channel c1: starting datafile copy
input datafile file number=00003 name=/oradata/DB01/datafile/o1_mf_undotbs_9hsw75h4_.dbf
output file name=/oradata/DB02/datafile/o1_mf_undotbs_04p0jq3a_.dbf tag=TAG20140214T131321
channel c1: datafile copy complete, elapsed time: 00:00:35
channel c1: starting datafile copy
input datafile file number=00004 name=/oradata/DB01/datafile/o1_mf_users_9hsw880k_.dbf
output file name=/oradata/DB02/datafile/o1_mf_users_05p0jq4d_.dbf tag=TAG20140214T131321
channel c1: datafile copy complete, elapsed time: 00:00:35
Finished backup at 14-FEB-2014 13:16:32

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "/oradata/fra/DB01/archivelog/2014_02_14/o1_mf_1_10_9hx1xkn1_.arc" auxiliary format
 "/oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_10_%u_.arc"   ;
   catalog clone recovery area;
   switch clone datafile all;
}
executing Memory Script

Starting backup at 14-FEB-2014 13:16:34
channel c1: starting archived log copy
input archived log thread=1 sequence=10 RECID=2 STAMP=839510193
output file name=/oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_10_06p0jq5i_.arc RECID=0 STAMP=0
channel c1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 14-FEB-2014 13:16:35

searching for all files in the recovery area

List of Files Unknown to the Database
=====================================
File Name: /oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_10_06p0jq5i_.arc
File Name: /oradata/fra/DB02/archivelog/2014_02_12/o1_mf_1_10_0cp0ekfe_.arc
File Name: /oradata/fra/DB02/controlfile/o1_mf_9hoq0kmv_.ctl
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_10_06p0jq5i_.arc
File Name: /oradata/fra/DB02/archivelog/2014_02_12/o1_mf_1_10_0cp0ekfe_.arc

List of Files Which Where Not Cataloged
=======================================
File Name: /oradata/fra/DB02/controlfile/o1_mf_9hoq0kmv_.ctl
  RMAN-07518: Reason: Foreign database file DBID: 1470537681  Database Name: DB01

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=839510196 file name=/oradata/DB02/datafile/o1_mf_system_02p0jpvh_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=839510196 file name=/oradata/DB02/datafile/o1_mf_sysaux_03p0jq27_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=839510197 file name=/oradata/DB02/datafile/o1_mf_undotbs_04p0jq3a_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=839510197 file name=/oradata/DB02/datafile/o1_mf_users_05p0jq4d_.dbf

contents of Memory Script:
{
   set until scn  227291;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 14-FEB-2014 13:16:39
Oracle instance started

Total System Global Area     801701888 bytes

Fixed Size                     2257520 bytes
Variable Size                222301584 bytes
Database Buffers             570425344 bytes
Redo Buffers                   6717440 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''DB02'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
}
executing Memory Script

sql statement: alter system set  db_name =  ''DB02'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 02/14/2014 13:17:01
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06094: datafile 5 must be restored

Recovery Manager complete.
[oracle@arrow:db01]/media/sf_linux_x64/rman
$

Remove spfile and misc files for TARGET database:

Startup NOMOUNT TARGET database:

[oracle@arrow:db02]/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs
$ rm spfiledb02.ora lkDB02 hc_db02.dat
[oracle@arrow:db02]/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 14 13:18:43 2014

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

Connected to an idle instance.

SYS@db02> startup nomount;
ORACLE instance started.

Total System Global Area  801701888 bytes
Fixed Size                  2257520 bytes
Variable Size             222301584 bytes
Database Buffers          570425344 bytes
Redo Buffers                6717440 bytes
SYS@db02> exit

RESTART active database duplication:

[oracle@arrow:db01]/media/sf_linux_x64/rman
$ rman @dupdbomf.rman

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Feb 14 13:18:52 2014

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

RMAN> connect target *
2> connect auxiliary *
3> run {
4> allocate channel c1 type disk maxopenfiles 1;
5> allocate auxiliary channel a1 type disk;
6> duplicate target database to db02
7>   from active database nofilenamecheck
8>   spfile
9>   PARAMETER_VALUE_CONVERT ('DB01','DB02')
10> ;
11> }
12> exit;
connected to target database: DB01 (DBID=1470673955)

connected to auxiliary database: DB02 (not mounted)

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=14 device type=DISK

allocated channel: a1
channel a1: SID=10 device type=DISK

Starting Duplicate Db at 14-FEB-2014 13:18:54

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfiledb01.ora' auxiliary format
 '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfiledb02.ora'   ;
   sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfiledb02.ora''";
}
executing Memory Script

Starting backup at 14-FEB-2014 13:18:54
Finished backup at 14-FEB-2014 13:18:55

sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfiledb02.ora''

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''DB02'' comment=
 ''duplicate'' scope=spfile";
   sql clone "alter system set  control_files =
 ''/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl'', ''/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.ctl'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''DB02'' comment= ''duplicate'' scope=spfile

sql statement: alter system set  control_files =  ''/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl'', ''/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.ctl'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     801701888 bytes

Fixed Size                     2257520 bytes
Variable Size                222301584 bytes
Database Buffers             570425344 bytes
Redo Buffers                   6717440 bytes
allocated channel: a1
channel a1: SID=95 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  control_files =
  ''/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl'', ''/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.ctl'' comment=
 ''Set by RMAN'' scope=spfile";
   sql clone "alter system set  db_name =
 ''DB01'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''DB02'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  '/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl';
   restore clone controlfile to  '/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.ctl' from
 '/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl';
   sql clone "alter system set  control_files =
  ''/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl'', ''/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.ctl'' comment=
 ''Set by RMAN'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  control_files =   ''/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl'', ''/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.ctl'' comment= ''Set by RMAN'' scope=spfile

sql statement: alter system set  db_name =  ''DB01'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''DB02'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     801701888 bytes

Fixed Size                     2257520 bytes
Variable Size                222301584 bytes
Database Buffers             570425344 bytes
Redo Buffers                   6717440 bytes
allocated channel: a1
channel a1: SID=95 device type=DISK

Starting backup at 14-FEB-2014 13:19:11
channel c1: starting datafile copy
copying current control file
output file name=/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/snapcf_db01.f tag=TAG20140214T131912 RECID=2 STAMP=839510353
channel c1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 14-FEB-2014 13:19:15

Starting restore at 14-FEB-2014 13:19:15

channel a1: copied control file copy
Finished restore at 14-FEB-2014 13:19:16

sql statement: alter system set  control_files =   ''/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl'', ''/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.ctl'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     801701888 bytes

Fixed Size                     2257520 bytes
Variable Size                222301584 bytes
Database Buffers             570425344 bytes
Redo Buffers                   6717440 bytes
allocated channel: a1
channel a1: SID=95 device type=DISK

database mounted

Using previous duplicated file /oradata/DB02/datafile/o1_mf_system_02p0jpvh_.dbf for datafile 1 with checkpoint SCN of 226956
Using previous duplicated file /oradata/DB02/datafile/o1_mf_sysaux_03p0jq27_.dbf for datafile 2 with checkpoint SCN of 227250
Using previous duplicated file /oradata/DB02/datafile/o1_mf_undotbs_04p0jq3a_.dbf for datafile 3 with checkpoint SCN of 227262
Using previous duplicated file /oradata/DB02/datafile/o1_mf_users_05p0jq4d_.dbf for datafile 4 with checkpoint SCN of 227275

contents of Memory Script:
{
   set newname for datafile  1 to
 "/oradata/DB02/datafile/o1_mf_system_02p0jpvh_.dbf";
   set newname for datafile  2 to
 "/oradata/DB02/datafile/o1_mf_sysaux_03p0jq27_.dbf";
   set newname for datafile  3 to
 "/oradata/DB02/datafile/o1_mf_undotbs_04p0jq3a_.dbf";
   set newname for datafile  4 to
 "/oradata/DB02/datafile/o1_mf_users_05p0jq4d_.dbf";
   set newname for clone datafile  5 to new;

   backup as copy reuse
   datafile  5 auxiliary format new
   ;
   
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 14-FEB-2014 13:19:31
channel c1: starting datafile copy
input datafile file number=00005 name=/oradata/DB01/datafile/o1_mf_mdinh_9hx1qqko_.dbf
output file name=/oradata/DB02/datafile/o1_mf_mdinh_08p0jqb4_.dbf tag=TAG20140214T131931
channel c1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 14-FEB-2014 13:19:47

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "/oradata/fra/DB01/archivelog/2014_02_14/o1_mf_1_10_9hx1xkn1_.arc" auxiliary format
 "/oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_10_%u_.arc"   archivelog like
 "/oradata/fra/DB01/archivelog/2014_02_14/o1_mf_1_11_9hx23s3n_.arc" auxiliary format
 "/oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_11_%u_.arc"   ;
   catalog clone recovery area;
   catalog clone datafilecopy  "/oradata/DB02/datafile/o1_mf_system_02p0jpvh_.dbf",
 "/oradata/DB02/datafile/o1_mf_sysaux_03p0jq27_.dbf",
 "/oradata/DB02/datafile/o1_mf_undotbs_04p0jq3a_.dbf",
 "/oradata/DB02/datafile/o1_mf_users_05p0jq4d_.dbf";
   switch clone datafile  1 to datafilecopy
 "/oradata/DB02/datafile/o1_mf_system_02p0jpvh_.dbf";
   switch clone datafile  2 to datafilecopy
 "/oradata/DB02/datafile/o1_mf_sysaux_03p0jq27_.dbf";
   switch clone datafile  3 to datafilecopy
 "/oradata/DB02/datafile/o1_mf_undotbs_04p0jq3a_.dbf";
   switch clone datafile  4 to datafilecopy
 "/oradata/DB02/datafile/o1_mf_users_05p0jq4d_.dbf";
   switch clone datafile all;
}
executing Memory Script

Starting backup at 14-FEB-2014 13:19:53
channel c1: starting archived log copy
input archived log thread=1 sequence=10 RECID=2 STAMP=839510193
output file name=/oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_10_09p0jqbq_.arc RECID=0 STAMP=0
channel c1: archived log copy complete, elapsed time: 00:00:01
channel c1: starting archived log copy
input archived log thread=1 sequence=11 RECID=3 STAMP=839510393
output file name=/oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_11_0ap0jqbr_.arc RECID=0 STAMP=0
channel c1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 14-FEB-2014 13:19:56

searching for all files in the recovery area

List of Files Unknown to the Database
=====================================
File Name: /oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_11_0ap0jqbr_.arc
File Name: /oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_10_06p0jq5i_.arc
File Name: /oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_10_09p0jqbq_.arc
File Name: /oradata/fra/DB02/archivelog/2014_02_12/o1_mf_1_10_0cp0ekfe_.arc
File Name: /oradata/fra/DB02/controlfile/o1_mf_9hoq0kmv_.ctl
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_11_0ap0jqbr_.arc
File Name: /oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_10_06p0jq5i_.arc
File Name: /oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_10_09p0jqbq_.arc
File Name: /oradata/fra/DB02/archivelog/2014_02_12/o1_mf_1_10_0cp0ekfe_.arc

List of Files Which Where Not Cataloged
=======================================
File Name: /oradata/fra/DB02/controlfile/o1_mf_9hoq0kmv_.ctl
  RMAN-07518: Reason: Foreign database file DBID: 1470537681  Database Name: DB01

cataloged datafile copy
datafile copy file name=/oradata/DB02/datafile/o1_mf_system_02p0jpvh_.dbf RECID=2 STAMP=839510398
cataloged datafile copy
datafile copy file name=/oradata/DB02/datafile/o1_mf_sysaux_03p0jq27_.dbf RECID=3 STAMP=839510398
cataloged datafile copy
datafile copy file name=/oradata/DB02/datafile/o1_mf_undotbs_04p0jq3a_.dbf RECID=4 STAMP=839510399
cataloged datafile copy
datafile copy file name=/oradata/DB02/datafile/o1_mf_users_05p0jq4d_.dbf RECID=5 STAMP=839510399

datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=839510398 file name=/oradata/DB02/datafile/o1_mf_system_02p0jpvh_.dbf

datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=839510398 file name=/oradata/DB02/datafile/o1_mf_sysaux_03p0jq27_.dbf

datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=839510399 file name=/oradata/DB02/datafile/o1_mf_undotbs_04p0jq3a_.dbf

datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=839510399 file name=/oradata/DB02/datafile/o1_mf_users_05p0jq4d_.dbf

datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=839510401 file name=/oradata/DB02/datafile/o1_mf_mdinh_08p0jqb4_.dbf

contents of Memory Script:
{
   set until scn  227620;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 14-FEB-2014 13:20:02

starting media recovery

archived log for thread 1 with sequence 10 is already on disk as file /oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_10_06p0jq5i_.arc
archived log for thread 1 with sequence 11 is already on disk as file /oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_11_0ap0jqbr_.arc
archived log file name=/oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_10_06p0jq5i_.arc thread=1 sequence=10
archived log file name=/oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_11_0ap0jqbr_.arc thread=1 sequence=11
media recovery complete, elapsed time: 00:00:09
Finished recover at 14-FEB-2014 13:20:14
Oracle instance started

Total System Global Area     801701888 bytes

Fixed Size                     2257520 bytes
Variable Size                222301584 bytes
Database Buffers             570425344 bytes
Redo Buffers                   6717440 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''DB02'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''DB02'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     801701888 bytes

Fixed Size                     2257520 bytes
Variable Size                222301584 bytes
Database Buffers             570425344 bytes
Redo Buffers                   6717440 bytes
allocated channel: a1
channel a1: SID=95 device type=DISK
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DB02" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      2
  MAXDATAFILES       30
  MAXINSTANCES     1
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1  SIZE 100 M ,
  GROUP   2  SIZE 100 M ,
  GROUP   3  SIZE 100 M
 DATAFILE
  '/oradata/DB02/datafile/o1_mf_system_02p0jpvh_.dbf'
 CHARACTER SET AL32UTF8

contents of Memory Script:
{
   set newname for clone tempfile  1 to new;
   switch clone tempfile all;
   catalog clone datafilecopy  "/oradata/DB02/datafile/o1_mf_sysaux_03p0jq27_.dbf",
 "/oradata/DB02/datafile/o1_mf_undotbs_04p0jq3a_.dbf",
 "/oradata/DB02/datafile/o1_mf_users_05p0jq4d_.dbf",
 "/oradata/DB02/datafile/o1_mf_mdinh_08p0jqb4_.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /oradata/DB02/datafile/o1_mf_temp_%u_.tmp in control file

cataloged datafile copy
datafile copy file name=/oradata/DB02/datafile/o1_mf_sysaux_03p0jq27_.dbf RECID=1 STAMP=839510428
cataloged datafile copy
datafile copy file name=/oradata/DB02/datafile/o1_mf_undotbs_04p0jq3a_.dbf RECID=2 STAMP=839510428
cataloged datafile copy
datafile copy file name=/oradata/DB02/datafile/o1_mf_users_05p0jq4d_.dbf RECID=3 STAMP=839510428
cataloged datafile copy
datafile copy file name=/oradata/DB02/datafile/o1_mf_mdinh_08p0jqb4_.dbf RECID=4 STAMP=839510429

datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=839510429 file name=/oradata/DB02/datafile/o1_mf_mdinh_08p0jqb4_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=5 STAMP=839510430 file name=/oradata/DB02/datafile/o1_mf_sysaux_03p0jq27_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=839510431 file name=/oradata/DB02/datafile/o1_mf_undotbs_04p0jq3a_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=839510432 file name=/oradata/DB02/datafile/o1_mf_users_05p0jq4d_.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 14-FEB-2014 13:21:48
released channel: c1
released channel: a1

Recovery Manager complete.
[oracle@arrow:db01]/media/sf_linux_x64/rman
$

Did you noticed duplication reused previous duplicated file versus duplicating it again?

Categories: DBA Blogs

linkedin endorsements make me ... grumpy ... sometimes

Grumpy old DBA - Tue, 2014-02-18 12:59
Well I don't really mind endorsements for skills that are in my current toolset.  It is a nice compliment to get endorsements from people that you know well or have work with or are working with.

On the other hand endorsements for things that you have never worked on in your life seem a little strange.  I now have several endorsements for Oracle HR ... never ever in my life touched that not even peoplesoft.  Even stranger is the recent endorsement in that from a system admin in my previous job where guess what ... we never ran that software there at all.

Just received another strange endorsement for SQL Server from someone that I do not remember how they are connected to me via linkedin.  I guess SQL Server is much improved these days ( needed to change obviously ) but wow have not touched anything on that platform since like 2000 and that was a 3 month project.

Yikes!
Categories: DBA Blogs

Speaking at Collaborate14 in Las Vegas

Pakistan's First Oracle Blog - Mon, 2014-02-17 18:47
12c Multi-Tenancy and Exadata IORM: An Ideal Cloud Based Resource Management



Cloud database enables administrators to scale up and down the resource usage according to the business requirements. Oracle 12c renders multi-tenancy to manage multiple pluggable databases within a multi-tenant container database. In an Exadata, at the compute nodes, the resource manager controls the resources among pluggable databases, and all this resource management is trickled down to the storage servers, where IORM controls the resources. This presentation lucidly explains as how a business can leverage the benefits of 12C multi-tenancy, DBRM, and IORM in an Exadata realm to have an ideal cloud based resource management.

 
Following is the welcome page for the Collaborate14.
Categories: DBA Blogs

SQL Developer’s PL/SQL Debugger and Oracle 12c

Galo Balda's Blog - Mon, 2014-02-17 17:54

The PL/SQL Debugger works pretty much out of the box when used with a previous Oracle version. These are the things we needed in place before we could start debugging PL/SQL:

  1. A grant of the DEBUG CONNECT SESSION privilege.
  2. EXECUTE privilege on DBMS_DEBUG_JDWP.
  3. EXECUTE privilege on the stored procedure you want to debug.
  4. Make sure the stored procedure is “Compiled for Debug”.

Jeff Smith talks about it in this post.

But what happens when you use Oracle 12c? You still need all the stuff that I mentioned but that’s not enough. See the error I got when I was trying to debug a procedure.

Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( ’192.168.0.10′, ’49428′ )
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at “SYS.DBMS_DEBUG_JDWP”, line 68
ORA-06512: at line 1
Process exited.
Disconnecting from the database SCOTT – ORA12CPDB1

Starting with Oracle 12c, if you want to debug PL/SQL stored procedures in the database through a Java Debug Wire Protocol (JDWP)-based debugger, such as SQL Developer or JDeveloper, then you must be granted the jdwp ACL privilege to connect your database session to the debugger at a particular host.

This is one way you can configure network access for JDWP operations:

BEGIN
 DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE
 (
 host => '192.168.0.10',
 lower_port => null,
 upper_port => null,
 ace => xs$ace_type(privilege_list => xs$name_list('jdwp'),
 principal_name => 'scott',
 principal_type => xs_acl.ptype_db)
 );
END;
Host can can be a host name, domain name, IP address, or subnet.

Principal name in the access control entry (ACE) section is the schema that holds the stored procedures you want to debug.

Now the error goes away and you can start your debugging task.

Connecting to the database SCOTT – ORA12CPDB1.
Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( ’192.168.0.10′, ’49428′ )
Debugger accepted connection from database on port 49428.
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.DISCONNECT()
Inside the Procedure
Process exited.
Disconnecting from the database SCOTT – ORA12CPDB1.
Debugger disconnected from database.

I hope this post saves you some time when you migrate to 12c.


Filed under: 12C, SQL Developer Tagged: 12C, SQL Developer
Categories: DBA Blogs