How to drop and recreate Oracle ASM Disk Groups in RAC

This post covers the simple commands on ow to drop and recreate Oracle ASM Disk Groups in RAC.
Warning: Running these commands shall result in Data Loss.
SQL> create pfile from spfile;
$ asmtool -delete \\.\ASMDISK01
$ asmtool -delete \\.\ASMDISK02
$ asmtool -delete \\.\ASMDISK03
$ asmcmd
ASMCMD> lsdg
$ srvctl status diskgroup -g DATA01
$ srvctl status diskgroup -g RECO01
$ srvctl stop diskgroup -g DATA01 -f
$ srvctl stop diskgroup -g RECO01 -f
SQL> drop diskgroup RECO01 force including contents;
Diskgroup dropped.
SQL> drop diskgroup DATA01 force including contents;
Diskgroup dropped.
Now that the ASM Disk Groups and constituent Disks have been dropped/erased, the new ASM Disk Groups can be recreated.
CREATE DISKGROUP DATA01 EXTERNAL REDUNDANCY
  DISK '/dev/asm/asmdisk*';
Resources:
How To Drop and Recreate ASM Diskgroup (Doc ID 563048.1)

My New book coming out - Building Database Clouds in Oracle Database 12c

After a lot of hard work, I am thrilled to announce that my new book on Oracle-centric Cloud Computing is becoming available in a matter of a few days.


I had been contemplating authoring a book within the Oracle Cloud domain for a few years—the project finally started in 2013. I am very proud of my coauthors’ industry credentials and the depth of experience that they brought to this endeavor. From inception to writing to technical review to production, authoring a book is a lengthy labor of love and at times a painful process; this book would not have been possible without the endless support of the awesome Addison-Wesley team.

 

You can order the book online at:
Amazon - Building Database Clouds in Oracle Database 12c

 

I have copied the Preface from the "Publisher's Website: Addison Wesley" to provide a brief overview to the Reader:

Preface

Cloud Computing is all the rage these days. This book focuses on DBaaS (database-as-a-service) and Real Application Clusters (RAC), one of Oracle’s newest cutting-edge technologies within the Cloud Computing world.

Authored by a world-renowned, veteran author team of Oracle ACEs/ACE directors with a proven track record of multiple best-selling books and an active presence in the Oracle speaking circuit, this book is intended to be a blend of real-world, hands-on operations guide and expert handbook for Oracle 12c DBaaS within Oracle 12c Enterprise Manager (OEM 12c) as well as provide a segue into Oracle 12c RAC.

Targeted for Oracle DBAs and DMAs, this expert’s handbook is intended to serve as a practical, technical, go-to reference for performing administration operations and tasks for building out, managing, monitoring, and administering DB Clouds with the following objectives:

 

• Practical, technical guide for building Oracle Database Clouds in 12c
• Expert, pro-level DBaaS handbook
• Real-world DBaaS hands-on operations guide
• Expert deployment, management, administration, support, and monitoring guide for Oracle DBaaS
• Practical best-practices advice from real-life DBaaS architects/administrators
• Guide to setting up virtualized DB Clouds based on Oracle RAC clusters

In this technical, everyday, hands-on, step-by-step book, the authors aim for an audience of intermediate-level, power, and expert users of Oracle 12c DBaaS and RAC.

This book covers the 12c version of the Oracle DB software.

How to move an Oracle Table with LOBs and Indexes temporarily out of a TableSpace and then back in again?

On occasion, it becomes necessary to move an Oracle Table temporarily out of a TableSpace and then back in again. The following sequence of steps shows how to accomplish this task along with rebuilding associated Indexes and Large Objects (LOBs) as well.


=> Created new tablespace to move table ORACLE_TABLE_NAME temporarily out of OLDTBS_01 tablespace.
create tablespace NEWTBS_01 datafile '+DATA1' size 1G autoextend on maxsize unlimited;


=>Move Table,Index & LOB to temporary tablespace.
alter table "DBUSER"."ORACLE_TABLE_NAME" move tablespace NEWTBS_01;
alter index DBUSER.ORACLE_TABLE_NAME_COL1 rebuild tablespace NEWTBS_01;
alter index DBUSER.ORACLE_TABLE_NAME_COL2 rebuild tablespace NEWTBS_01;
alter index DBUSER.ORACLE_TABLE_NAME_COL3 rebuild tablespace NEWTBS_01;
Alter table DBUSER.ORACLE_TABLE_NAME move lob(LOBNAME_1) store as (tablespace NEWTBS_01);

=>Move ORACLE_TABLE_NAME table, indexes & LOB back to original tablespace  OLDTBS_01.
alter table "DBUSER"."ORACLE_TABLE_NAME" move tablespace OLDTBS_01;
alter index DBUSER.ORACLE_TABLE_NAME_COL1 rebuild tablespace OLDTBS_01;
alter index DBUSER.ORACLE_TABLE_NAME_COL2 rebuild tablespace OLDTBS_01;
alter index DBUSER.ORACLE_TABLE_NAME_COL3 rebuild tablespace OLDTBS_01;
Alter table DBUSER.ORACLE_TABLE_NAME move lob(LOBNAME_1) store as (tablespace OLDTBS_01);


=>Validate that no data was left was left on NEWTBS_01 tablespace before dropping it.
select * from dba_segments where tablespace_name='NEWTBS_01';
drop tablespace NEWTBS_01 including contents and datafiles;


And there you have it, a table with Indexes and LOBs was temporarily moved to a new tablespace and then back for administrative purposes.

How to modify an Oracle RAC Service to startup in a specific DB Mode (For Data Guard)?

Below, I have mentioned commands to modify an Oracle RAC Cluster Managed DB Service to startup in a specific DB Mode (for Data Guard).




Modify the Service to startup in a Primary Mode:
srvctl modify service -d RACDB1 -s RACDB1SERV1 -l primary


Modify the Service to startup in Physical Standby Mode:
srvctl modify service -d RACDB1 -s RACDB1SERV1 -l physical_standby
Modify the Service to o startup in a Snapshot Standby Mode:
srvctl modify service -d RACDB1 -s RACDB1SERV1 -l snapshot_standby


How to Check the configuration/status of the Cluster Managed DB Service:srvctl config service -d RACDB1 -s RACDB1SERV1




Note: Please remember to change the name of the DB Names/Services with your own.

Creating and Changing Encryption Wallets/Passwords in Oracle

This post covers the commands to create and then change Wallet Files/Passwords for Oracle databases using the ORAPKI utility.

$ orapki help
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
orapki [crl|wallet|cert|help] <-nologo>
Syntax :
[-option [value]]     : mandatory, for example [-wallet [wallet]]
[-option <value>]     : optional, but when option is used its value is mandatory.
<option>              : optional, for example <-summary>, <-complete>
[option1] | [option2] : option1 'or' option2
In this example the -auto_login switch enables the Oracle database to automatically startup with the Wallet file.
$ orapki wallet create -wallet /u01/wallet/DBNAME -pwd "insert_pwd_here" -auto_login
This command shows how to change the existing Wallet Password utilizing the ORAPKI utility.
orapki wallet change_pwd -wallet /u01/DBNAME/wallet -oldpwd insert_old_password -newpwd insert_new_password
The following SQL commands show how to open, close, authenticate and query Encryption Wallet Passwords and status.
alter system set wallet open identified by "xxxxxx";
alter system set wallet close identified by "xxxxxxxx";
alter system set encryption key authenticated by "xxxxxxx";
select * from v$encryption_wallet;

RAC Installs: root.sh fails after installation on the first node

This post covers RAC Install failures: root.sh fails after installation on the first node


Excerpt from Problem Logs:
Disk Group GRID01 creation failed with the following message:
ORA-15018: diskgroup cannot be created
ORA-15017: diskgroup "GRID01" cannot be mounted
ORA-15003: diskgroup "GRID01" already mounted in another lock name space
Configuration of ASM ... failed

see asmca logs at /u01/app/grid/cfgtoollogs/asmca for details
Did not succssfully configure and start ASM at /u01/app/11.2.0/grid/crs/install/crsconfig_lib.pm line 6912.
/u01/app/11.2.0/grid/perl/bin/perl -I/u01/app/11.2.0/grid/perl/lib -I/u01/app/11.2.0/grid/crs/install /u01/app/11.2.0/grid/crs/install/rootcrs.pl execution failed


Cause: "This particular issue arises when root.sh is run concurrently on the first and other nodes on the RAC cluster. The correct approach is to run root.sh on the first node followed by the other nodes of the RAC cluster.\

Solution:
root.sh Fails on the First Node for 11gR2 Grid Infrastructure Installation (Doc ID 1191783.1)
Run the following as the root OS user on Node 1.
"$GRID_HOME/crs/install/rootcrs.pl -deconfig -force -verbose"
Followed by this command on the other nodes:
"$GRID_HOME/crs/install/rootcrs.pl -verbose -deconfig -force -lastnode"


How to Proceed from Failed 11gR2 Grid Infrastructure (CRS) Installation (Doc ID 942166.1)
Here is the master note for troubleshooting/fixing Grid Infrastructure Startup issues.
Troubleshoot Grid Infrastructure Startup Issues (Doc ID 1050908.1)

Useful OS Commands/Utilities for Oracle Databases on IBM AIX environments

In this post, I have compiled some useful Commands/Utilities for running Oracle Databases on IBM AIX environments.


Gives useful resource/info about the LPAR/Virtual Server:
$ lparstat -I


The following utilities (topas and nmon) Gives useful resource consumption about the LPAR/Virtual Server (:
$ nmon
       -> h
┌─HELP─────────most-keys-toggle-on/off───────────────────────────────────────────────────────────────────┐
│h = Help information     q = Quit nmon             0 = reset peak counts                                │
│+ = double refresh time  - = half refresh          r = ResourcesCPU/HW/MHz/AIX                          │
│c = CPU by processor     C=upto 1024 CPUs          p = LPAR Stats (if LPAR)                             │
│l = CPU avg longer term  k = Kernel Internal       # = PhysicalCPU if SPLPAR                            │
│m = Memory & Paging      M = Multiple Page Sizes  P = Paging Space                                      │
│d = DiskI/O Graphs       D = DiskIO +Service times o = Disks %Busy Map                                  │
│a = Disk Adapter         e = ESS vpath stats       V = Volume Group stats                               │
│^ = FC Adapter (fcstat)  O = VIOS SEA (entstat)    v = Verbose=OK/Warn/Danger                           │
│n = Network stats        N=NFS stats (NN for v4)   j = JFS Usage stats                                  │
│A = Async I/O Servers    w = see AIX wait procs   "="= Net/Disk KB<-->MB                                │
│b = black&white mode     g = User-Defined-Disk-Groups (see cmdline -g)                                  │
│t = Top-Process --->     1=basic 2=CPU-Use 3=CPU(default) 4=Size 5=Disk-I/O                             │
│u = Top+cmd arguments    U = Top+WLM Classes       . = only busy disks & procs                          │
│W = WLM Section          S = WLM SubClasses        @=Workload Partition(WPAR)                           │
│[ = Start ODR            ] = Stop ODR              i = Top-Thread                                       │
│~ = Switch to topas screen 

$ topas
       -> h
One-character commands:
  @ - Pressing the '@' key repeatedly toggles to wpar and normal mode
  a - Show all the variable subsections being monitored. Pressing the
      the 'a' key always returns topas to the main initial display.
  c - Pressing the 'c' key repeatedly toggles the CPU subsection
      between the cumulative report, off, and a list of busiest CPUs.
  d - Pressing the 'd' key repeatedly toggles the disk subsection between
      total disk, off, and busiest disks list activity for the system.
  t - Pressing the 't' key repeatedly toggles the tape subsection between
      total tape, off, and busiest tape list activity for the system.
  f - Pressing the 'f' key repeatedly toggles the file system subsection
      between total file system, off, and busiest file system list activity
      for the system.Also Moving the cursor over a WLM class and pressing 'f'
      shows the list of top processes in the class on the bottom of the
      screen(WLM Display Only).Similarly moving the cursor over a WPAR name and
      pressing'f' shows the list of top file system belonging to that wpar
      on the bottom of the screen(FS Display with @ option only)
  e - Pressing the 'e' key repeatedly toggles between the AME and NFS
      subsections. The key has no effect if AME(Active memory expansion)
      is not enabled in the machine.
  n - Pressing the 'n' key repeatedly toggles the network interfaces subsection
      between total network, off, and busiest interfaces list activity.
  p - Pressing the 'p' key toggles the hot processes subsection on and off.
  P - Toggle to the Full Screen Process Display
  q - Quit the program                                                           r - Refresh the screenGives useful resource consumption about the System Memory info (Including Large Page Consumption/Information):
$ svmon
Gives Disk Usage Information on/attached-to the LPAR:
$ df -g