Happy Friday, Students of the Oracle!!
I had the opportunity to take over a failed clone job on CommVault. The immediate solution was to accomplish the clone using RMAN Duplicate. I believe the steps were to run a full backup on RMAN and then duplicate this to the target database. This succeeded and was definitely a solution that satisfied the customer.My job was to figure out why it failed. This is the error stack I received. The last three lines were the comments from my coworker as he left the building to go out of town on holiday.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 06/01/2016 13:22:54
RMAN-05501: aborting duplication of target database
RMAN-05556: not all datafiles have backups that can be recovered to SCN 7447956627439
RMAN-03015: error occurred in stored script Memory Script
RMAN-06026: some targets not found – aborting restore
RMAN-06023: no backup or copy of datafile 18 found to restore
RMAN-06023: no backup or copy of datafile 16 found to restore
RMAN-06023: no backup or copy of datafile 14 found to restore
RMAN-06023: no backup or copy of datafile 12 found to restore
RMAN-06023: no backup or copy of datafile 9 found to restore
RMAN-06023: no backup or copy of datafile 7 found to restore
RMAN-06023: no backup or copy of datafile 6 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore
RMAN>
Recovery Manager complete.
]
That may point out a CV issue.
Why would the backups be reporting success yet be unrestorable?
Past reasons dealt with interface card setup in the CV infrastructure.
 This sounds really bad but it’s actually quite simple. The worst case scenario is we have a corrupted database. The best case scenario is the backup we chose to duplicate with is incomplete.
  1. My first step is to look at the complete RMAN log for the backup. I’ll spare you the complete script but it was attempting a Full (level 1) backup. In other words, an incremental full. When it came across missing datafiles, it defaulted to a Full (level 0) backup and that succeeded. This answered my coworker’s first two questions. It reported success because it was a successful backup despite failing to be an incremental backup. Also, this log gave me 90% certainty it is not a CommVault(CV) issue. **Hint** If we are going to use a past backup to clone with, make sure there are no warnings in the RMAN log and ensure it is a full backup. It was actually a stroke of good fortune that the clone failed.
  2. From CommVault, I ran a full (level 0) backup on both the source and target databases. In our setup, CommVault will not recognize backups run outside of the CommVault tool. Upon further investigation, there had not been a full (level 0) backup run on either database for some time. Our backup strategy and schedule is designed to run a full(level 0) backup once a week. This needs to be looked at. Both backups succeeded and the log showed no RMAN-06023 errors. This full backup can now be used to clone in the future.
  3. In CommVault, I navigated to my schedule policy for these databases. The schedule showed a full backup scheduled once a week. However, in the Backup Options tab, the Incremental box was selected. I quickly changed this to Full and saved my changes.
  4. I verified the logs for last night’s incremental backups on both databases. No errors. Problem solved!!

The moral of the story is don’t trust “Recovery Manager complete” as proof the backup succeeded without warnings. Also, I advise against using a Full (Level 1) backup to clone with.

Thanks for reading!

Jason

MongoDB: To Blog or Not to Blog

Posted: June 1, 2016 in MongoDB
Tags: ,

Good morning, Oracle Elitists!

I have a quick question for all of you who read my posts. I have recently been given the opportunity at work to learn and support our MongoDB project. For those of you who are unfamiliar with Mongo, here is Wikipedia’s definition:

MongoDB (from humongous) is a free and open-source cross-platform document-oriented database. Classified as aNoSQL database, MongoDB avoids the traditional table-based relational database structure in favor of JSON-like documents with dynamic schemas (MongoDB calls the format BSON), making the integration of data in certain types of applications easier and faster. MongoDB is developed by MongoDB Inc. and is free and open-source, published under a combination of the GNU Affero General Public License and the Apache License. As of July 2015, MongoDB is the fourth most popular type of database management system, and the most popular for document stores.

Since I exploit my work experiences to find subjects to blog about, I need to hear from you. Should I blog about this subject? Are you interested in Mongo or are you exclusively Oracle DBA’s who have no desire to hear about new trends? Believe me, I understand that point of view. Most of my DBA coworkers are in that camp.

Please let me know. A lack of response means I will have to taint this otherwise exclusively Oracle blog page with a test post with a MongoDB theme.

Thanks, in advance, for your thoughtful responses.

Jason (should I change my name to Json?) Brown

Good afternoon, Oracle Olympians! Since I will be out of town for Memorial Day, I thought I would publish a little lunchtime special in lieu of a Friday post. If you are looking to hit a home run on your next Oracle RAC job interview, study these commands. These are the basic list of commands that I have used. I make it a practice to not blog about things I have never used. Here you go! Enjoy!

To run these commands, you need to be Oracle and your environment needs to be set to a database instance.

$ srvctl stop database -d dbname –shutdown normal

$ srvctl start database -d dbname –startup normal

$ srvctl stop database -d dbname -o immediate -f –shutdown immediate

$ srvctl start instance -d dbname -i db1 –start individual instance

$ srvctl status instance -d dbname -i db1 –check status of individual instance

$ srvctl remove database -d dbname –database must be shutdown

$ srvctl add database -d dbname -o /u01/app/oracle/product/11.2.0.4/dbhome_1 –add database to srvctl

$ srvctl add instance -d dbname -i db1 -n srvrname01 –instance 1, node 1 –add instance1 to the database in srvctl

$ srvctl add instance -d dbname -i db2 -n srvrname02 –instance 2, node 2 –add instance2 to the database in srvctl

$ srvctl remove instance -d dbname -i db1 –removes instance 1

$ srvctl remove instance -d dbname -i db2 –removes instance 2

$ srvctl disable instance -d dbname -i “db1,db2,db3,etc” –disables management by Oracle restart.

$ srvctl enable instance -d dbname -i “db1,db2,db3,etc” –enables management by Oracle restart.

$ srvctl config database -d dbname –lists configuration information for the specified database and lists all databases managed in the srvctl.

Thanks for reading! Please send me your questions or comments. Otherwise, have a great Memorial Day Weekend!

Jason

Guten Tag, Oracle -Enthusiasten!!

Today’s post is really a continuation from May 9th’s post on Cloning a RAC instance to a single instance database. I have looked into the steps involved in restoring RAC to RAC verses RAC to Single Instance and then converting it to a RAC database. As of yet, there is not a huge time or effort gap between the two approaches. I will eventually post the steps to perform a RAC to RAC restore but today’s focus is to finish what we started on May 9th.

The setup pieces can either all be done at the beginning or the end. They are ordered as I performed them so feel free to test and experiment. My motto is KISS… “Keep it Simple… Sir.”  Or you may choose the gender neutral, Marine Corps version “Keep it simple, stupid.” The first one sounds a bit more elegant.

  • Copy your password file from the source database to both nodes. The steps work the same, regardless of the number of nodes.
    • $ scp orapwdb xdt*dbadm01:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs
    • $ scp orapwdb xdt*dbadm02:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs
  • Create oratab entry on both nodes.
    • $ oracle@xdt*dbadm01: vi $ORACLE_HOME/network/admin/etc/oratab
    • $ oracle@xdt*dbadm02: vi $ORACLE_HOME/network/admin/etc/oratab
  • Add tnsnames entries on both nodes.
    • DB1 =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xdt**client01-vip)(PORT = ****))
      (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = db1)
      )
      )
    • DB2 =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xdt**client02-vip)(PORT = ****))
      (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = db2)
      )
      )
  • Shutdown the single instance database.
    • SQL> shutdown immediate;
    • SQL> exit
  • Set environment to node 1.
    • $ . oraenv
    • ORACLE_SID = [db] ? db1
  • Edit original pfile to make it a cluster database. Remove [*.interconnects] lines to prevent an attempt to connect to the source database.
    • *.cluster_database=true
  • Startup nomount with your original pfile.
    • SQL> startup nomount pfile=’$ORACLE_HOME/dbs/inintdb.ora’
  • Create spfile from pfile. This creates a one line, pointer file in the /dbs directory.
    • SQL> create spfile=’+DATA_XDT**/DB/PARAMETERFILE/spfiledb.ora’
  • Shutdown database.
  • Startup database. This will startup normal with the spfile on ASM.
    • SQL> startup;
    • SQL> select * from v$instance; –Check to see the instance properties.
    • SQL> exit
  • Copy the init.ora file to node 2. Change the name to node2 database.
    • $ scp initdb1.ora xdt*dbadm02:$ORACLE_HOME/dbhome_1/dbs/initdb2.ora
  • Add the database to Server Control where -d =database and -o =Oracle Home.
    • srvctl add database -d db -o /u01/app/oracle/product/11.2.0.4/dbhome_1
  • Add the instances to the new database where -i = Instance Name and -n = Nodename.
    • $ srvctl add instance -d db -i db1 -n xdt*dbadm01
    • $ srvctl add instance -d db -i db1 -n xdt3dbadm01
  • Comment out the tnsnames entry for the single node database. This will prevent connection to the database outside of the RAC environment.

Congratulations!! You just converted a single instance database into a RAC database. Please let me know if you have any issues or questions and, as always…

Thanks for reading!!

Jason

 

OUI-67073:UtilSession failed: Prerequisite check “CheckActiveFilesAndExecutables” failed.

Good afternoon/evening/morning to Oracle Enthusiasts Across our Great Globe!

One of my biggest pet peeves in network television is when a season ends with too many questions and won’t pick up again until next year. For those of you who read my post earlier today, I am sparing you the agony of waiting all weekend to see how my patching turned out!

Earlier today, I published an article on Upgrading to the Latest Patch (OPatch) Utility. This was the primary error I received while trying to apply the bugfix patch for Class of Secure Transport (COST). Since the post ended in an error, I needed to update you with the solution. In truth, I almost backed out and turned the databases back over to the DEV and QA users. However, since these users reside in Europe, they won’t be on shift for several hours. So I kept digging. As usual, the solution was very close.

The logs stated…

OUI-67073:UtilSession failed: Prerequisite check “CheckActiveFilesAndExecutables” failed.

OPatch failed with error code 73

There are two steps to Applying COST to Restrict Instance Registration(of course you need a subscription to get here) The steps are abbreviated on purpose. I am not Oracle Support and patching should be done by strictly following the procedures documented on that page. However, as life turns out, we don’t always get the results that the documentation and the Read Me page presents.

  1. Obtain and Apply bugfix patch 12880299.
    • Download and unzip the patch
    • Upgrade to the latest version of OPatch for your database. Mine was 11.2.0.1. Yes it is an old version.
    • Apply the patch.
      • In the Read Me file, the instructions are to stop all of the Oracle database instances. They are all single instance databases so I shut all of them down.
      • Next, I was to apply the patch. The log stated the above errors.
      • I took to Google and found an interesting article. by an anonymous blogger who calls himself acehints. Thank you, Mr. Acehints. In his situation, he found Enterprise Manager still running and subsequently killed the process. That got me to thinking. This patch is to restrict the listener. Wouldn’t it make sense to stop the listener before applying the patch?
      • Even though the Read Me omitted this step, I stopped the listener.
        • $ lsnrctl stop listener
      • $ opatch apply
      • Start the database instances.
    • Hooray! OPatch was applied successfully!!
  2. Add the COST TCP protocol restriction to the listener.ora to restrict usage to TCP Protocol only.
    • SECURE_REGISTER_LISTENER = (TCP)
  3. Start the listener.
  4. Connect to the databases using the listener instead of straight up SQL Plus.
    • $ sqlplus / as sysdba@db_name;
  5. Inform Database users to start their applications.

All appears to be well!

Thanks for reading!

Jason

Happy Friday, Oracle bloggers and blog followers alike!

Today’s installment comes on the heels of testing the bugfix for BUG:12880299. I will refer to Doc ID 1453883.1 on Oracle Support and maybe a couple other Doc ID’s. I recommend you have these documents open as you read. I ran into a couple issues while following the document. The purpose of this article is to show how I solved the issues I ran into.

One of the first issues I noticed was covered in the Read Me for the patch. Oracle recommends you have the latest version of OPatch installed in your ORACLE HOME. You verify this by running the lsinventory command as oracle in the ORACLE HOME.

$ opatch lsinventory

Invoking OPatch 11.1.0.6.6

Oracle Interim Patch Installer version 11.1.0.6.6
Copyright (c) 2009, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oracle/product/oraInventory
from : /var/opt/oracle/oraInst.loc
OPatch version : 11.1.0.6.6
OUI version : 11.2.0.1.0

I looked past this step, stopped the database instances, and attempted to apply the patch

$ opatch apply

ApplySession failed during prerequisite checks: Prerequisite check “CheckActiveFilesAndExecutables” failed.
System intact, OPatch will not attempt to restore the system

OPatch failed with error code 74

After going back to the read me again, I read Oracle’s “recommendation” to have the latest version of OPatch. It even supplies the document to use.  latest version of OPatch In this document there is a link to a video that shows you how to install the latest version of OPatch. Let me clear something up…

You need to download the OPatch version that matches up to your version of Oracle. For example, don’t use OPatch for 12c if you are using Oracle11g.

Once you have transferred Oracle’s download to your patches directory, follow the document’s easy steps.

cd $ORACLE_HOME
mv OPatch OPatch.bkp
unzip <download directory>/p6880880_<version>_<platform>.zip
cd OPatch
./opatch version

–Include OPatch in PATH

$ export PATH=$ORACLE_HOME/OPatch:$PATH

Now, my version of OPatch is up to date with my database version.

$ opatch lsinventory

OPatch version : 11.2.0.3.12
OUI version : 11.2.0.1.0

Now I can run the patch without fail!!

$ opatch apply

OUI-67073:UtilSession failed: Prerequisite check “CheckActiveFilesAndExecutables” failed.

Ugh. Another stinking issue. The solution will be my next blog topic.

To be continued…

Happy Monday, Oracle Backup Engineers!

Ask a DBA what is the single most important skill that a DBA possess and the majority will tell you Performance Tuning. I must take issue with that opinion. My minority opinion is Backup and Recovery is, without a doubt, the most important skill to master. Without that skill, you may (will) wake up one morning without a database to tune.

Today, I thought it would be fun to take our cloning to the next level. Let’s take our RAC instance and clone it to a different server. This is a common practice and its applications are many. You can use it to restore, migrate or create a database instance. I am still doing test restores so that is the slant in which this and previous articles are pointed.

You will notice a 95% similarity between this article and the one I published two weeks ago. No need to rewrite the entire article but for your benefit, I have recopied all of the steps so you don’t have to bounce back and forth between articles. I merely added a step for tnsnames.ora and modified a couple other steps.

***A little terminology. When I refer to the source, that is the database I am cloning. When I refer to target, that is where I am cloning to.***

  1. Copy the primary database (source) to the clone I’ll make.
    1. Save the oratab with my clone database added.
      1. As Oracle, $ vi /etc/oratab
      2. Add a line with your restore instance name.
      3. $ cat /etc/oratab –to see if your new instance is there.
    2. Change environment to new instance $ . oraenv ?new_instance$ cd $ORACLE_HOME/dbs
    3. Make a pfile from the working instance (source database) you are going to clone.
      1. SQL> create pfile=’/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/initnewinstance.ora’ from spfile;
    4. Edit initnewinstance.ora to make your cloning pfile.
      1. :set ignorecase –makes it easier for searching
      2. Then erase double underscore lines in the pfile.
      3. set cluster_database=false
      4. Set control_files so only the disk groups are set
        1. for example: *.control_files=’+DATA_XDT1′,’+RECO_XDT1′
      5. Set db_recovery_file_dest_size, pga_aggregate, sga_target to appropriate size
      6. Set *.remote_listener to appropriate location.
      7. Change use_large_pages=’ONLY’ to = ‘TRUE’ so it uses them if available but doesn’t fail if none are available.
      8. Change all string=’clone_db’ to become ‘recover_db’
        1. At the vi colon prompt like so   :%s#clonedb#recoverdb#g
      9. Save the new pfile as initrecoverdb.ora.
      10. If you have these backup settings for improved tape backup, keep them.
        1. *._backup_disk_bufcnt=64
        2. *._backup_disk_bufsz=1048576
        3. *._backup_file_bufcnt=64
        4. *._backup_file_bufsz=1048576
        5. *.backup_tape_io_slaves=TRUE.
    5. Edit (vi) tnsnames.ora and add new instance.

:set ignorecase

search for clonedb  [/clonedb]

Copy the single instance “clone_db” to make recover_db then paste it back to create it.

Once edits are done save it then do a tnsping for recover_db.

That now shows network TNS protocol is ready

6. Make a “temporary” copy of your tnsnames.ora file.

a. In this file change the server info to match what exists in all nodes in your target database tnsnames file. You can optionally just edit the tnsnames file but I did this just to stay away from our production tnsnames file since what we are doing is temporary. 

$ cp tnsname.ora $ORACLE_HOME/network/admin/temp

7. Now check existence of $ORACLE_BASE/admin/recover_db/adump and $ORACLE_HOME/admin/recover_db/wallet; further, that wallet has both wallet and single sign on wallet.

  • Neither exist so create both and populate the wallet from our target database (to use RMAN terminology)

mkdir -p $ORACLE_BASE/admin/recover_db/adump

The “-p” tells mkdir to make the absolute path you supply to it

mkdir -p /u01/app/oracle/product/11.2.0.4/dbhome_1/admin/recover_db/wallet

$ cp cwallet.sso /u01/app/oracle/product/11.2.0.4/dbhome_1/admin/recover_db/wallet

$ cp ewallet.p12 /u01/app/oracle/product/11.2.0.4/dbhome_1/admin/recover_db/wallet

    1. As GRID, Add entry to listener.ora

(SID_DESC =

(GLOBAL_NAME=recoverdb)       (ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1)       (SID_NAME=recoverdb)     )

lsnrctl status

lsnrctl reload

lsnrctl status

cd $ORACLE_HOME/network/admin

and edit the file as shown above.

After testing the directories for existence plus doing tnsping for the connection descriptor successfully, we’re ready to start the auxiliary database in nomount mode.

  1. Start SQL*Plus with environment set to recover_db

    Now start the database in nomount mode using the pfile we created. SQL> startup nomount pfile=’/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/initrecoverydb.ora’

Exit to the Unix prompt as we’re done with Unix preparations.

Now comes the CommVault steps

Here we go…

    1. Discover the recovery instance:
      1. Navigate to Single Instance Server (xdt7–>Oracle–>All Tasks–>Discover instance
        1. Discovered Instance!! Refresh the screen to see newly discovered instance.
        2. In the discovered instance in CV, navigate to the Client Name–> Instance(Oracle SID)–> Properties–> Details tab and edit the TNS_ADMIN folder: 

          TNS_ADMIN folder: /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/temp 

  • We are duplicating source database “clone_db” to auxiliary database “recover_db”.
  1. Let’s display the target and list its backups from its backup history view

“clone_db->View->Backup History

  1. Use the advanced option to show All operations, Include Aged Data and Include Backup Copy as NAS Snapshot Catalog Jobs.
  2. Choose okay at each subsequent screen till the Backup Job History is displayed
  3. Double click the latest successful backup and copy and record its:
    1. Job id: 554999 (**Example)
    2. Start Time:
    3. End Time:
    4. SCN Number(S): I use the lower SCN number if there are two displayed.
  4. You’re now ready to begin setting up a clone restore of a backup.
  5. Right click on the backup job # i.e. (553999)
    1. Choose “Browse and Restore”
    2. Next click “View Content” push button control at bottom of the Browse and Restore Options screen.
    3. It displays the tablespaces available in the right pane for selected database in left hand pane.
    4. Click the left hand panes checkbox control to select entire content.
    5. Then “Recover All Selected…”

The “Oracle Restore Options Server_name-clone_db SID: clone_db” screen appears.

Note that its shows Destination Client as being Server_name-clone_db, which will change after you return from the “Advance” options on this screen which we’ll do now:

  1. Select “Duplicate DB”. Now we get 12 tab controls on the Oracle Restore Options screen.
  2. Click on the Advanced push button control. Tab controls appear on the Oracle Advanced Restore Options screen.
  3. Choose the “Duplicate” tab control.
  4. Click its “Duplicate To” checkbox control, and its option become enabled.
  5. In the “Database Name” textbox control, entry the auxiliary database name (i.e. clone you’re making): recover_db.
  6. Now move to the “Recover” tab control, then active its SCN radio button and specify the SCN number we save in our preparation work:
  7. Change to the Duplicate DB Options tab control, check its “No Filename Check” checkbox control.
  8. Finally activate the “Redirect” tab control, uncheck its “Redirect” checkbox control. Remaining tab controls aren’t used for this operation, but you can look at them.

To summarize, the key tab controls were these: Duplicate, Recover, Duplicate DB Options and Redirect.

Now choose “OK” to accept

Notice that our “Destination Client” was automatically filled in after completion of the advanced push button control dialogs.

  1. Now choose “Number of streams to use for restore”, which I usually set at 4 — next accept all entries by clicking “OK” at the bottom of the “Oracle Restore Options” screen.

It immediately gives a warning that, “Data from clone_db on server_name-clone_db will be restored on to recovery_db on server_name.  All data in recovery_db will be over written.  Are you sure you want to continue?”

  1. Take heed of the waning and carefully ensure you want to do this.
  2. Let the restore begin.
  3. Once it completes, you should be able to open the database from the SQL Plus prompt and test if the restore is complete.

At this point, I took the time to delete this recovery instance. Some of the steps I took were to remove listener.ora entry (as Grid), remove its TNS descriptor from tnsnames.ora, and oratab entries. Then shutdown the database, carefully dispose of the datafiles in ASM and remove its statis listener entries. Also, delete the temporary tnsnames.ora file.

Thanks for reading! Again, I greatly appreciate Tim Dexter for his expertise in CommVault and RMAN Duplicate.

Take care,

Jason