Advertisement

Showing posts with label RAC. Show all posts
Showing posts with label RAC. Show all posts

Friday, June 14, 2019

Oracle Database 19c: Silent Database Deletion

In this blog I am going to present on how to delete database silently (RAC / Non RAC)


$ export ORACLE_BASE=/u01/app/oracle
$export ORACLE_HOME=/u01/app/oracle/product/190/db/
$ export PATH=$ORACLE_HOME/bin:$PATH
$ dbca -silent -deleteDatabase -sourceDB  ORPL
Enter SYS user password:

[WARNING] [DBT-19202] The Database Configuration Assistant will delete the Oracle instances and datafiles for your database. All information in the database will be destroyed.
Prepare for db operation
32% complete
Connecting to database
39% complete
42% complete
45% complete
48% complete
52% complete
55% complete
58% complete
65% complete
Updating network configuration files
68% complete
Deleting instances and datafiles
77% complete
87% complete
97% complete
100% complete
Database deletion completed.
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/orpl/orpl.log" for further details.

Oracle Database - 19c DB Creation (RAC)

In this blog, I present on how to create a RAC DB for 19c. 
I have already put down in my previous blogs on how to create a RAC DB in silent mode. 
In this we will look into the Graphical Mode.

Login as Oracle user and go to Oracle home directory

$ cd $ORACLE_HOME/bin # (/u01/app/oracle/product/190/db/bin)
# Run insalller
$./dbca

Next follow the instructions as are in the screenshots





















this completes the installation.

Oracle Database - 19c - RAC Silent Disk Group Creation

In this blog, I am going to talk about Disk Group Creation in RAC 18c in silent mode. (no UI)

First I am going to create the disks at the OS level and discover it in oracleasm. 


1. Create Partition for New Disks. Do this for all the disks you have attached to your system. Make sure disks are attached to both nodes and partition is created on only one Node. 
Details on Creating the partition are present in end of the blog.

2.  Create Disks using oracleasm on Node 1

$ oracleasm createdisk FRA001 /dev/xvdf1
Writing disk header: done
Instantiating disk: done

$ oracleasm createdisk FRA002 /dev/xvdg1
Writing disk header: done
Instantiating disk: done

$ oracleasm createdisk FRA003 /dev/xvdh1
Writing disk header: done

Instantiating disk: done

3.  Scan for disks using oracleasm on Node 2

$ oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Scanning system for ASM disks...
Instantiating disk "FRA001"
Instantiating disk "FRA002"

Instantiating disk "FRA003"

4. Login as Grid and set the environment run asmca  and create Disk Group 

$  asmca -silent -sysAsmPassword Oracle123 -asmsnmpPassword Oracle123 -createDiskGroup -diskGroupName FRA -diskList  /dev/oracleasm/disks/FRA001,/dev/oracleasm/disks/FRA002,/dev/oracleasm/disks/FRA003 -redundancy EXTERNAL -au_size 4 -compatible.asm 19.0.0.0.0 -compatible.rdbms 19.0.0.0.0


[INFO] [DBT-30001] Disk groups created successfully. Check /u01/app/grid/cfgtoollogs/asmca/asmca-190614AM043742.log for details.


Disk group creation complete. Verify using asmcmd (lsdg command)

New Disk Partition Creation 
$ fdisk /dev/xvdf
Welcome to fdisk (util-linux 2.23.2).

Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.

Device does not contain a recognized partition table
Building a new DOS disklabel with disk identifier 0xc0515184.

Command (m for help): n
Partition type:
   p   primary (0 primary, 0 extended, 4 free)
   e   extended
Select (default p): p
Partition number (1-4, default 1):
First sector (2048-251658239, default 2048):
Using default value 2048
Last sector, +sectors or +size{K,M,G} (2048-251658239, default 251658239):
Using default value 251658239
Partition 1 of type Linux and of size 120 GiB is set

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.


Syncing disks.

Thursday, June 13, 2019

Oracle Database 19c: Create RAC DB in silent mode

In this blog I am going to cover how to create a new database using dbca in silent mode. 

In a lot of environments it is required to create database in silent mode because there is no UI available due to security reasons.

So here I have given below on how to create a database online 
The key thing to note is , it is better to have a database created using response files rather than created using commands given on the cmd. 

In below I have created a RAC database which is specified by the parameter 
databaseConfigType=RAC

I create database using single command below and configure everything in my response file for ease of usage and re-usability.
Once the database is created you should add the corresponding /etc/oratab entries.

(you will have to enter password for sys, sytem and dbsnmp)
$ /u01/app/oracle/product/190/db/bin/dbca -silent -createDatabase \ -responseFile /home/oracle/19c_db.rsp


Enter SYS user password:

Enter SYSTEM user password:


Enter DBSNMP user password:


Prepare for db operation
4% complete
Creating and starting Oracle instance
5% complete
6% complete
8% complete
Creating database files
9% complete
13% complete
Creating data dictionary views
14% complete
16% complete
17% complete
18% complete
20% complete
21% complete
23% complete
25% complete
Oracle JVM
31% complete
38% complete
44% complete
46% complete
Oracle Text
47% complete
49% complete
50% complete
Oracle Multimedia
63% complete
Oracle OLAP
64% complete
65% complete
66% complete
67% complete
Oracle Spatial
68% complete
75% complete
Creating cluster database views
76% complete
83% complete
Completing Database Creation
85% complete
87% complete
88% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:
 /u01/app/oracle/cfgtoollogs/dbca/orpl.
Database Information:
Global Database Name:orpl
System Identifier(SID) Prefix:orpl

Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/orpl/orpl.log" for further details.



List of all parameters which were used 
responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v12.2.0 
gdbName=orpl 
sid=orpl 
databaseConfigType=RAC 
RACOneNodeServiceName= 
policyManaged=false 
createServerPool=false 
serverPoolName= 
cardinality= 
force=false 
pqPoolName= 
pqCardinality= 
createAsContainerDatabase=false 
numberOfPDBs=0 
pdbName= 
useLocalUndoForPDBs=true 
pdbAdminPassword= 
nodelist=oelrac19c01,oelrac19c02 
templateName=/u01/app/oracle/product/190/db/assistants/dbca/templates/New_Database.dbt 
sysPassword= 
systemPassword= 
serviceUserPassword= 
emConfiguration= 
emExpressPort=5500 
runCVUChecks=TRUE 
dbsnmpPassword= 
omsHost= 
omsPort=0 
emUser= 
emPassword= 
dvConfiguration=false 
dvUserName= 
dvUserPassword= 
dvAccountManagerName= 
dvAccountManagerPassword= 
olsConfiguration=false 
datafileJarLocation= 
datafileDestination=+DATA/{DB_UNIQUE_NAME}/ 
recoveryAreaDestination= 
storageType=ASM 
diskGroupName=+DATA/{DB_UNIQUE_NAME}/ 
asmsnmpPassword= 
recoveryGroupName= 
characterSet=AL32UTF8 
nationalCharacterSet=AL16UTF16 
registerWithDirService=false 
dirServiceUserName= 
dirServicePassword= 
walletPassword= 
listeners=LISTENER 
variablesFile= 
variables=ORACLE_BASE_HOME=/u01/app/oracle/product/190/db,DB_UNIQUE_NAME=orpl,ORACLE_BASE=/u01/app/oracle,PDB_NAME=,DB_NAME=orpl,ORACLE_HOME=/u01/app/oracle/product/190/db,SID=orpl
initParams=orpl1.undo_tablespace=UNDOTBS1,orpl2.undo_tablespace=UNDOTBS2,sga_target=9566MB,db_block_size=8192BYTES,cluster_database=true,family:dw_helper.instance_mode=read-only,nls_language=AMERICAN,dispatchers=(PROTOCOL=TCP) (SERVICE=orplXDB),diagnostic_dest={ORACLE_BASE},remote_login_passwordfile=exclusive,db_create_file_dest=+DATA/{DB_UNIQUE_NAME}/,audit_file_dest={ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/adump,processes=320,pga_aggregate_target=3189MB,orpl1.thread=1,orpl2.thread=2,nls_territory=AMERICA,local_listener=-oraagent-dummy-,open_cursors=300,compatible=19.0.0,db_name=orpl,orpl1.instance_number=1,orpl2.instance_number=2,audit_trail=db
sampleSchema=false 
memoryPercentage=40 
databaseType=MULTIPURPOSE 
automaticMemoryManagement=false 
totalMemory=0 


Oracle Database - 19c - RAC Database Silent Installation

In this blog I am going to discuss on silent installation of RAC DB. 
This is in continuation with my blog of RAC 19c installation. 

You can find the response file I used in the end of the blog. 

Step 1. [As oracle] Set Passwordless for oracle user
$ cd /u01/app/190/grid/deinstall 

$ ./sshUserSetup.sh -user oracle -hosts "oelrac19c01 oelrac19c02" -noPromptPassphrase -confirm -advanced


Step 2. [As root ] Create Required Directories (ON Both Nodes)

$ mkdir -p /u01/app/oracle/product/190/db
$ chown -R oracle:oinstall /u01/app/oracle

Step 3. [As oracle] Unzip the Database Binary (ON Node 1)
$ cd /u01/app/oracle/product/190/db
$ unzip -qq /tmp/LINUX.X64_193000_db_home.zip

Step 4. Pre-req check

Connect (or switch to) as Oracle User to the system
$ cd /u01/app/oracle/product/190/db 
$ unset ORACLE_BASE ORACLE_HOME ORA_NLS10

$ ./runInstaller -silent  -executePrereqs -responseFile /home/oracle/19c_RACDB.rsp

Ensure here the pre-req are completed sucessfully. else fix the issues 


Step 5. Start with installation
$ ./runInstaller -silent -responsefile /home/oracle/19c_RACDB.rsp

Launching Oracle Database Setup Wizard...

The response file for this session can be found at:
 /u01/app/oracle/product/190/db/install/response/db_2019-06-13_08-27-54AM.rsp

You can find the log of this install session at:

 /u01/app/oraInventory/logs/InstallActions2019-06-13_08-27-54AM/installActions2019-06-13_08-27-54AM.log

As a root user, execute the following script(s):
        1. /u01/app/oracle/product/190/db/root.sh

Execute /u01/app/oracle/product/190/db/root.sh on the following nodes:
[oelrac19c01, oelrac19c02]


Successfully Setup Software.

Step 6. Execute the  root.sh scripts (and Installation is complete)

$ /u01/app/oracle/product/190/db/root.sh

Check /u01/app/oracle/product/190/db/install/root_oelrac19c01.novalocal_2019-06-13_08-35-11-823823824.log for the output of root script

$ /u01/app/oracle/product/190/db/root.sh

Check /u01/app/oracle/product/190/db/install/root_oelrac19c02.novalocal_2019-06-13_08-35-18-015821291.log for the output of root script

Response file for reference

oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v19.0.0
oracle.install.option=INSTALL_DB_SWONLY
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
ORACLE_BASE=/u01/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.OSDBA_GROUP=dba
oracle.install.db.OSOPER_GROUP=oper
oracle.install.db.OSBACKUPDBA_GROUP=backupdba
oracle.install.db.OSDGDBA_GROUP=dgdba
oracle.install.db.OSKMDBA_GROUP=kmdba
oracle.install.db.OSRACDBA_GROUP=racdba
oracle.install.db.rootconfig.executeRootScript=false
oracle.install.db.CLUSTER_NODES=oelrac19c01,oelrac19c02
oracle.install.db.config.starterdb.type=GENERAL_PURPOSE
oracle.install.db.ConfigureAsContainerDB=false
oracle.install.db.config.starterdb.characterSet=
oracle.install.db.config.starterdb.memoryOption=false
oracle.install.db.config.starterdb.memoryLimit=
oracle.install.db.config.starterdb.installExampleSchemas=false
oracle.install.db.config.starterdb.managementOption=DEFAULT
oracle.install.db.config.starterdb.enableRecovery=false

Oracle Database - 19c - RAC 19c Database Installation

This blog goes in continuation with my 18c installation. 
I install 18c RAC Database on my cluster.


You can download Oracle Database 18c from Database Downloads page from Oracle and hence sftp to the required server. 


Step 1. [As oracle] Set Passwordless for oracle user
$ cd /u01/app/190/grid/deinstall 

$ ./sshUserSetup.sh -user oracle -hosts "oelrac19c01 oelrac19c02" -noPromptPassphrase -confirm -advanced



Step 2. [As root ] Create Required Directories (ON Both Nodes)

$ mkdir -p /u01/app/oracle/product/190/db
$ chown -R oracle:oinstall /u01/app/oracle

Step 3. [As oracle] Unzip the Database Binary (ON Node 1)
$ cd /u01/app/oracle/product/190/db
$ unzip -qq /tmp/LINUX.X64_193000_db_home.zip

Step 4. [As oracle on Node 1] Installation
Connect as Oracle User to the system

$ cd /u01/app/oracle/product/190/db
$ ./runInstaller





Run the root script and Press OK

[root@oelrac19c01 ~]# /u01/app/oracle/product/190/db/root.sh
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/oracle/product/190/db

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.


[root@oelrac19c02 ~]# /u01/app/oracle/product/190/db/root.sh
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/oracle/product/190/db

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed

AFter Pressing OK, the installation is complete.


After you are finished ,ensure the patch information on both nodes

$ cd OPatch/
$ ./opatch lspatches
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)

29517242;Database Release Update : 19.3.0.0.190416 (29517242)

Friday, May 31, 2019

Oracle Database: How to relocate tablespaces to different Diskgroup (except SYS)

in this blog, I list out steps for Oracle RAC relocating tablespaces to different diskgroups with partial downtime only 

Step1 - 
backup tablespaces to new diskgroup - 

run 
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
allocate channel c6 type disk;
allocate channel c7 type disk;
allocate channel c8 type disk;
backup as copy tablespace TB1,TB2,TB3 format '+DATA2';
}

test

Repeat for each tablespaces
1. SQL> 
     alter tablespace TB1 offline;
2. rman  
     switch tablespace TB1 to copy;
     run {
    allocate channel c1 type disk;
    recover tablespace TB1;
    }
3. SQL> 
    alter tablespace TB1 online;

This completes the relocation.
If you want to do it for datafile level (in case your tabelspace is large)
You can follow same steps (with minor modifications from tablespace to datafile )

Oracle Database: RAC - Relocate UNDO to different Diskgroup (and Standby)

In this blog I discuss steps to relocate Undotablespaces on a 2 node rac cluster.

Currently tablespaces are
Node 1 - undotbs1
Node 2 - undotbs2


Step 1 - Create Undotablespaces (as per the size required) 
(Note the name difference) 

 CREATE UNDO TABLESPACE "UNDOTBS11" DATAFILE '+DATA2' SIZE 209715200 AUTOEXTEND ON NEXT 52428800 MAXSIZE 32767M,
  BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

 CREATE UNDO TABLESPACE "UNDOTBS22" DATAFILE '+DATA2' SIZE 209715200 AUTOEXTEND ON NEXT 52428800 MAXSIZE 32767M,

  BLOCKSIZE 8192

  EXTENT MANAGEMENT LOCAL AUTOALLOCATE;


Next set the undo tablespace parameter 

  alter system set undo_tablespace=UNDOTBS11 scope=spfile sid='NODE1';  
  alter system set undo_tablespace=UNDOTBS22 scope=spfile sid='NODE2';


Next drop the the undo tablespace after undo retention has expired
Drop tablespace UNDOTBS1 including contents and datafiles;
Drop tablespace UNDOTBS2 including contents and datafiles;

This completes the relocation.

In case you are running a standby RAC, then you will need to set these a well, however these parameters are not dynamic, so you will have to set it to spfile and restart the standby DB (not just instance)

Tuesday, May 7, 2019

Oracle Database: Updating Grid / Oracle Inventory Manually

In this small blog, I will tell you how to update the inventory file with node names manually.

This is useful in many cases
1.  Inventory does not have node names
2. RAC Node addition / deletion - automatic update does not happen
3. During Installation - inventory does not have node names


Case 1
Update Inventory RAC Node - Add Node names
(To be run as grid installation owner)
$ /opt/oracle/product/180/grid/oui/bin/runInstaller -silent -ignoreSysPrereqs -updateNodeList ORACLE_HOME=/opt/oracle/product/180/grid "CLUSTER_NODES={Node1,Node2}" CRS=true LOCAL_NODE=Node1

This will update the inventory on all cluster nodes (2 in this case)

Case 2
Update only on one node in case of cluster installation
(To be run as grid installation owner)
$ /opt/oracle/product/180/grid/oui/bin/runInstaller -updateNodeList -noClusterEnabled ORACLE_HOME=/opt/oracle/product/180/grid "CLUSTER_NODES={Node1,Node2}" CRS=true  LOCAL_NODE=Node2

Oracle Database: VOTE Disk (Closed) / Not Available

In this blog I am going to discuss an issue which I faced during my maintenance on cluster. 
One of the votedisk became unavailable and was dropped from the system in my cluster.


crsctl will only show me 2 Vote disks


$ crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   578049fd7bd34f51bfdf9cd7d2e02205 (/dev/oracleasm/disks/OCR_VOTE1) [OCR_VOTE]
 3. ONLINE   232d262659714f84bfbede64f662f566 (/dev/oracleasm/disks/OCR_VOTE2) [OCR_VOTE]

From V$asm_disk I try to see the status of disk. 

SQL> select mount_status, path, header_status, mode_status from V$asm_disk  where path like '%OCR_VOTE%'


MOUNT_S PATH                                     HEADER_STATU MODE_ST
------- ---------------------------------------- ------------ -------
CLOSED  /dev/oracleasm/disks/OCR_VOTE2           MEMBER       ONLINE
CACHED  /dev/oracleasm/disks/OCR_VOTE3           MEMBER       ONLINE
CACHED  /dev/oracleasm/disks/OCR_VOTE1           MEMBER       ONLINE

Solution  - I add back the Disk to Diskgroup
SQL> alter diskgroup OCR_VOTE add disk '/dev/oracleasm/disks/OCR_VOTE2' force;
(Reference - 
OCR / Vote disk Maintenance Operations: (ADD/REMOVE/REPLACE/MOVE) (Doc ID 428681.1)

and then I check the status again from v$asm_disk and crsctl

SQL> select mount_status, path, header_status, mode_status from V$asm_disk  where path like '%OCR_VOTE%' ;

MOUNT_S PATH                                     HEADER_STATU MODE_ST
------- ---------------------------------------- ------------ -------
CACHED  /dev/oracleasm/disks/OCR_VOTE3           MEMBER       ONLINE
CACHED  /dev/oracleasm/disks/OCR_VOTE2           MEMBER       ONLINE
CACHED  /dev/oracleasm/disks/OCR_VOTE1           MEMBER       ONLINE

$ crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   578049fd7bd34f51bfdf9cd7d2e02205 (/dev/oracleasm/disks/OCR_VOTE1) [OCR_VOTE]
 2. ONLINE   65cc344fc7da4fbbbf22772629b12203 (/dev/oracleasm/disks/OCR_VOTE3) [OCR_VOTE]
 3. ONLINE   232d262659714f84bfbede64f662f566 (/dev/oracleasm/disks/OCR_VOTE2) [OCR_VOTE]

Wednesday, April 10, 2019

Oracle Database: kggpnpInit: failed to init gpnp (Only one Instance running) 11g,12c,18c,19c

Symptom - 
1.Only one instance of Oracle DB can run on a RAC node (1 or 2)
2. Public interface used as interconnect interface as seen in gv$cluster_interconnects

In Alert log you can see - 
[USER(153710)]CRS-2316:Fatal error: cannot initialize GPnP, CLSGPNP_ERR (Generic GPnP error).
kggpnpInit: failed to init gpnp
  WARNING: No cluster interconnect has been specified. Depending on
           the communication driver configured Oracle cluster traffic
           may be directed to the public interface of this machine.
           Oracle recommends that RAC clustered databases be configured
           with a private interconnect for enhanced security and

           performance.


Reason - From October 2018 PSU of Oracle RAC you must apply OCW component of the GI patch as well to the Oracle Home. 

Example - 
Current  Patch Level 
28790660;OJVM PATCH SET UPDATE 11.2.0.4.190115
28729262;Database Patch Set Update : 11.2.0.4.190115 (28729262)

Solution - Apply OCW Patch
OCW Patch is available as part of GI PSU Bundle, you cannot download it individually (in general)
But you can apply it individually to DB Home
This can be really an issue if you have done newer versions of Oracle install on higher version of Cluster

Patch Apply Process
Step 1 - Stop all Database instances running from the home

Step 2 - Create the ocm file 
$ cd $ORACLE_HOME/ccr/bin
$ ./emocmrsp
(respond <Enter> and then 'Y'>
$ cp ocm.rsp /home/oracle/

Step 3 - Patch Apply 
PrePatch
<Patch Unzipped Location>/<patch_number>/custom/server/<patch_number>/custom/scripts/prepatch.sh -dbhome <oh>
Patch
<oh>/OPatch/opatch apply -oh <oh> -local <Patch Unzipped Location>/<patch_number>/custom/server/<patch_number>
Post Patch
<Patch Unzipped Location>/<patch_number>/custom/server/<patch_number>/custom/scripts/postpatch.sh -dbhome <oh>

Example for Jan 2019 Patch
$ /images/oracle/11.2.0.4/p28729234/28729234/custom/server/28729234/custom/scripts/prepatch.sh -dbhome /opt/oracle/product/112/db
$ /opt/oracle/product/112/db/OPatch/opatch napply -oh /opt/oracle/product/112/db -local /images/oracle/11.2.0.4/p28729234/28729234/custom/server/28729234 -ocmrf /home/oracle/ocm.rsp
$ /images/oracle/11.2.0.4/p28729234/28729234/custom/server/28729234/custom/scripts/postpatch.sh -dbhome /opt/oracle/product/112/db


Final Patch Level
28729234;OCW Patch Set Update : 11.2.0.4.190115 (28729234)
28790660;OJVM PATCH SET UPDATE 11.2.0.4.190115
28729262;Database Patch Set Update : 11.2.0.4.190115 (28729262

Reference Doc ID - 
Customer Recommended11.2.0.4 RAC Database Instance Fails to Start with "No connectivity to other instances in the cluster during startup" After Applying 11.2.0.4 OCT 2018 DB PSU (11.2.0.4.181016 DB PSU) (Patch# 28204707) (Doc ID 2471441.1)