Hello again, Oracle Flashback Captains!!

Here’s a Friday lunchtime special. Since my lunchtime is being interrupted by creating a restore point for a customer, I thought I would share my fun with you! There’s nothing earth shaking or innovative here. Just some good, old fashioned syntax to store away for later use.

  • First make sure archivelog is set:

select log_mode,flashback_on from v$database;

LOG_MODE     FLASHBACK_ON

———— ——————

ARCHIVELOG   NO

1 row selected.

  • Next turn on flashback:

Alter database flashback ON;

LOG_MODE     FLASHBACK_ON

———— ——————

ARCHIVELOG   YES

 

  • Next see if any restore points are still out there.  Drop them if they exist:

 

SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT;

no rows selected

  • Now create restore point with the date time and db name

CREATE RESTORE POINT restorePointName_todaysDate_timeOfRestorePoint GUARANTEE FLASHBACK DATABASE;

  • Make sure the sql stmt worked.

SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT;

  • Should you need to use this restore point…
    • flashback database to restore point restorePointName_todaysDate_timeOfRestorePoint
    • On a RAC Database…

>>> srvctl stop database -d dbname
set env
. oraenv
sqlplus / as sysdba
startup mount;
flashback database to restore point restorePointName_todaysDate_timeOfRestorePoint;
alter database open resetlogs;
drop restore point before_upgrade;
shutdown immediate;
exit;
>>> srvctl start database -d dbname
Alter database flashback off;

  • After Deployment set Flashback off

DROP RESTORE POINT restorePointName_todaysDate_timeOfRestorePoint;

Alter database flashback off;

  • Verify flashback is off

select log_mode,flashback_on from v$database;

Thanks for reading!

Jason

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…