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

Advertisements

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

Happy Friday, Students of the Oracle!

This is a quick post because I know it’s Friday and who wants to read lengthy technical writing right before cutting loose from work for two days?

In the past I have bounced the OEM Agent. This stops and starts the whole Agent. I have been getting an alert for the agent on one node of one Exadata Database Server. Along with that, all of the targets on that node are unreachable as well.

  1. I just patched that server last Friday. I had performed an OEM Blackout on the entire server, bounced the cluster for the Grid Patch set, and bounced RAC for the Database Patch set. I then lifted the blackout and went to bed. So my first inclination was to perform another blackout and then cancel it after ten minutes. That has worked for me in the past. No such luck this time.
  2. I logged into the offending node as Oracle. I then changed my environment and navigated to the agent home.
    • $ . oraenv
    • ORACLE_SID = [] ? agnt12c
    • $ cd $ORACLE_HOME
    • oracle@xdt**:/u01/app/oracle/agent/core/12.1.0.4.0 agnt12
  3. Next, I check the status of the agent just like I would on the OEM Server.
    • Oracle Enterprise Manager Cloud Control 12c Release 4
    • $ emctl status agent
      Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
      —————————————————————
      Agent is Not Running
    • Hmm. Let’s start the agent.
  4. Start the agent.
    • $ emctl start agent
      Oracle Enterprise Manager Cloud Control 12c Release 4
      Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
      Starting agent …………………… started.
  5. Check status again…
    • $ emctl status agent
      Oracle Enterprise Manager Cloud Control 12c Release 4
      Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
      —————————————————————Agent is Running and Ready
  6. I then refreshed my OEM–>Summary page. After 30 seconds, all targets on that node are up and running.

Easy Peasy. There is a way to do this inside of OEM but it is restricted in our environment.

Thanks for reading!

Jason Brown

 

Good afternoon, Oracle Aficionados!

This is another installment in response to my post on Making a Test Restore of a RAC DB Using CommVault and Putty SSH Session. Our beloved RMAN tool is a bit fussy at times. If you don’t have every little detail right, it will just quit. Now add CommVault, the GUI of choice for many organizations, and the details become even more critical.

Today’s subject involves another failure message that came up on one of the test restores I have been performing on our Exadata Database RAC Instances. Mr. Tim Dexter, my backup partner in chief, is experimenting on a RAC in place restore. I used his restore to perform this latest test restore.

  • After meticulously following all of the steps to make a test restore, I kicked off the clone job.
  • Within about five minutes, the restore job failed. The abbreviated error stack follows:

ORA-00245: control file backup failed; target is likely on a local file system

  • My first thought was the location of the control file was mixed up. I opened the pfile and looked for the control file location. It was in +DATA/…/… Right where it was supposed to be.
  • Next, I headed out to ASM and looked for that same location. Yep, right where it was supposed to be.
  • This probably didn’t matter, but I next went into RMAN and looked at the configuration settings.

RMAN> SHOW ALL;

CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘+DATA_XDT*/dbm01.f’;

This just didn’t look right. It turns out CommVault didn’t think so either so it decided to fail and assume the snaphot controlfile was on the local file system.

  • I looked into my ASMCMD file system and, sure enough, this was not a valid location. I changed the location to a folder just above the control file location.

CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘+DATA_XDT*/DBM01/spfiledbm01*.f’;

  • I killed the running clone and started a new one with all of the same settings.
  • Voila! It finished without error and the new test instance was created!

Thanks for reading! Again, please respond with your concerns, findings or challenges.

Warm regards (whatever that means),

Jason Brown