Archive for the ‘RAC’ Category

Good afternoon, Oracle Pioneers!

A few months ago (maybe a year ago), I published an article on how to convert a single instance database into a RAC database.  Converting a Single Instance To RAC DB However, that article assumed this was already a RAC database at some prior to its migration and subsequent (RACifying) **Yes RACifying is a word I made up or stole from someone**

If this database is and has always been a single instance, there are likely a few missing parameters that are needed in order to make it into a multiple instance database.

So here is my updated version of how to convert a single instance database into a RAC Database using command line and srvctl. These steps will work for 11g or 12c databases.

After single instance is available do this:
If this is a single instance you are migrating and “racifying”, you must create undotbs2 and logfiles for thread 2 to match number and size of thread 1.

  1. SQL> create undo tablespace undotbs2 datafile ‘+DATA’ size 4G;
  2. SQL> alter database add logfile thread 2 group 5 size 4096M;   
  3. SQL> alter database enable public thread 2; 
  4. Verify these parameters are set in your pfile:

control_files **Update location info from +data and +reco Location         

db_name1.thread=1                                                                                                                                 db_name2.thread=2                                                                                                           db_name1.undo_tablespace=’UNDOTBS1′                                                                 db_name2.undo_tablespace=’UNDOTBS2′                                                                   db_name1.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=xdt*client01-vip)(PORT=9653))’ db_name2.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=xdt*client02-vip)(PORT=9653))’ db_name1.instance_number=1 db_name2.instance_number=2 *.remote_listener=’xdt*-scan:9653’5.

5. edit $ORACLE_HOME/dbs/init<db_name>.ora to a one line file reading location of new spfile:

6. copy file to $ORACLE_HOME/dbs/init<db_name><inst#>.ora on all nodes

7.   shutdown immediate;

8. Create srvctl setup. See my previous article on converting a single instance to a RAC DB.

9. Log into instance and set cluster database to true:

srvctl start instance -d db_name -i db_name1

SQL> alter system set cluster_database=true scope=spfile sid=’*’;

10. Shutdown and restart database with srvctl:

srvctl stop database -d db_name -o immediate -f

srvctl start database -d db_name

Good luck and let me know if you have any issues! Keep on RACifying out there!

Good morning fellow DBA’s and Backup Admins! Yesterday, Jasonbrownsite reached a significant milestone! We surpassed 10,000 total views! That doesn’t include those who follow us through Twitter, Google Plus and LinkedIn. Regardless, thank you for continuing to read our weekly/monthly blogs and adventures. Without readership, it would make more sense for me just to keep a journal…

So, today’s scenario involves migrating a single instance Oracle database off of an old Solaris server to a newer Exadata Server that happens to be a four node RAC. We attempted to accomplish this with RMAN Duplicate and it did not work to our satisfaction. So here is what we did with success. An alternative method might be to utilize RMAN Transportable Tablespaces but that involves an outage to the source database. That method was not seriously considered. As always, there may be a better way so don’t take this as gospel truth. Just something to consider…

In summary, my partner and I utilized Data Pump to export all of the non system type users from the database on the Solaris Server. From there, we copied the dump files to the Exadata node 1. Step three was to create a clean four node RAC database with DBCA, the trusty yet out of date database creation tool. Next we created the new users and ran some DDL to create procedures and functions which I will summarize in this post. The final step was to import the dumpfiles copied from the Solaris side.

  • Create a parfile for your export. Determine which schemas own objects and need to be exported. Due to the size of the db, we created parallelism=25. Your parfile should be adjusted according to your needs:
    • userid=system
    • directory=dbname_dir
    • dumpfile=dbname_%u.dmp
    • logfile=dbname.log
    • filesize=15g
    • parallel=25
    • exclude=SCHEMA: “IN(‘OUTLN’,’PUBLIC’,’SYS’,’SYSTEM’,’WMSYS’,’ETC’)”
    • or schemas=()
  • Run the export.  expdp parfile=dbname.par
  • Copy the dumpfiles to the Exadata server.We used an EXFER utility that collected all the dumpfiles and transferred them simultaneously.
  • Create a four node RAC database on the Exadata server using DBCA.
    • Create a custom database.
    • Make sure to match the blocksize of your source database.
    • I make it a habit to not include OEM setup or extra components during DB creation. It is not needed in this migration.
    • Multiplex your redo logs and controlfiles.
    • Specify your fast recovery area and size, i.e. +RECO_Exadata_Name.
    • Specify your memory settings, archivelog destinations, processes, redo log groups, etc.
    • Create a template for future use. It is much faster to use this if it is needed later.
  • Create tnsnames entries , password file, wallet/s, and adump directory for all four nodes. I assume you already know how to do this.
  • Validate all nodes!
    • srvctl remove database -d dbname
      srvctl add database -d dbname -c RAC \
      -o /u01/app/oracle/product/ \
      -p +DATA_XDT*/DBNAME/PARAMETERFILE/spfiledbname.ora \
      -r PRIMARY -s OPEN -t IMMEDIATE -n dbname

      srvctl add instance -d dbname-i dbname1 -n xdt*dbadm01
      srvctl add instance -d dbname-i dbname2 -n xdt*dbadm02
      srvctl add instance -d dbname-i dbname3 -n xdt*dbadm03
      srvctl add instance -d dbname-i dbname4 -n xdt*dbadm04

  • Before commencing with the Import, try to eliminate as much up front errors as possible:
    • Port over the verify_functions for password management:
      • set lines 150 pages 50000 echo on feedback on long 200000000 serveroutput on size unlimited timing off trim on trims on verify off
        SQL> select dbms_metadata.get_ddl(p.object_type,p.object_name,p.owner) ddl_text from dba_procedures p where object_name like ‘VERIFY%’;
      • Run the resultant SQL on the Exadata database.
    • Do the same thing with Profiles, Tablespaces, Users, Roles, and directories and run the resultant SQL:
      • SQL> select dbms_metadata.get_ddl(‘PROFILE’,c.profile) ddl_text from (select distinct profile from dba_profiles) c ;
      • set echo on feedback on lines 150 pages 50000 serveroutput on size unlimited trim on trims on verify off long 2000000
        col ddl_text for a150
        select dbms_metadata.get_ddl(‘TABLESPACE’,t.tablespace_name) ddl_text from (select tablespace_name from dba_tablespaces t order by 1) t;
      • select dbms_metadata.get_ddl(‘USER’,u.username) ddl_text from dba_users u ;
      • select role from dba_roles;
      • select * from dba_directories ;
    • Run the import with the same parameters as the export data pump job.
    • Query dba_objects for invalid objects and recompile them.
    • At this point your shiny, new 4 node RAC database should be ready to turn over to the customer!

As always, please test this process in your own sandbox before deploying to production.

Thanks for reading and making a success!


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/ –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!


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/
    • $ scp orapwdb xdt*dbadm02:/u01/app/oracle/product/
  • 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 =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xdt**client01-vip)(PORT = ****))
      (SID = db1)
    • DB2 =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xdt**client02-vip)(PORT = ****))
      (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/
  • 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!!