Pages

Advertisement

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 Important Documentation and Patch Information

 
In this blog I discuss important documentation on Database 19c. 
It lists the important links which you require when thinking of Database 19c. 
This list is quite handy for Database Administrators of 19c when planning new installation, upgrade or finding about new features of 19c.

 The Patch information in the end (point 5) is a quick link to find and download the Patches for this release. 
  1. Oracle Database 19c Documentation 
  2. Oracle Database 19c tutorials
  3. Oracle 19c - Complete Checklist for Manual Upgrades to Non-CDB Oracle Database 19c - DocID 2539778.1
  4. Oracle 19c - Complete Checklist for upgrading Oracle 12c, 18c Container Database (CDB) to Oracle 19c Release using DBUA - Doc ID 2543981.1
  5. Oracle 19c - Complete Checklist for Upgrading to Oracle Database 19c (19.x) using DBUA - Doc ID 2545064.1
  6. 18c & 19c Physical Standby Switchover Best Practices using SQL*Plus -  Doc ID 2485237.1
  7. DBCA Silent Mode New features in Database 19C - Doc ID 2477805.1
  8. Assistant: Download Reference for Oracle Database/GI Update, Revision, PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases Doc ID 2118136.2
  9. Desupport of Oracle Real Application Clusters (RAC) with Oracle Database Standard Edition 19c - Doc ID 2504078.1

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)

Wednesday, June 12, 2019

Oracle Database: Duplicate Specific Tablespaces only

 
Oracle allows you to duplicate a database using specific tablespaces only, .

 The command is same as the duplicate command 

Mention the tablespace comma separated names (highlighted in bold)
Ofcourse temp, system,sysaux, undo will be duplicated along
  • rman run at the new database site
  • target is source
  • auxiliary is the new db
  • list of tablespaces is comma seperated.
  • if you want to skip a specific tablespace use 'skip <tablespace_name>' 


rman 
connect TARGET sys/passs@PROD;
connect AUXILIARY sys/pass@XDB1_STDBY;
run 
{
set newname for database to '+FRA';
DUPLICATE TARGET DATABASE 
  TO XDB
  FROM ACTIVE DATABASE
  TABLESPACE users
  NOFILENAMECHECK;

}

The highlight here is that oracle will internally check during duplicate that if the tablespaces are linked (dependent) or not.

You will have to ensure that static entries is present for the new database in the listener, as it will need to be shut down and started by the rman. 

Oracle Database: Planning for New Storage

 
In this blog I cover topics which need to be considered when doing a new storage design for your Oracle Database. 

 1. Review the current storage speed using the storage consoles which you have and find out the iops happening on the SSD Cluster + HDD Cluster, you can get iops and data transferred from the storages itself. From the current design, you must add the iops of SSD and HDD cluster and the data transfer as well

  • design should include at least these much iops and probably a 20-30% more.
  • design should be done considering the peak hours storage iops and the data transfer rates.
2. Design should/not enforce redundancy. 

  • data is better handled by ASM redundancy, in case using ASM use ASM redundancy - Normal is preferred for DATA and FRA, High for REDO, CF and OCR.
  • in case you are not using ASM then ask storage to include external parity and redundancy
  • the effective storage capacity should be given as per the approach followed. 
3. A design having multiple disks performs better in oracle, as when you give the luns to oracle, it will stripe the data across the disks, thus maximizing performance. 

  • design with many small disks can mean additional controllers and more management and more sockets to insert to.
  • design with big disks again can reduce performance, so you should have a tradeoff with respect to cost and design.
4.  Latency which you plan should be as seen by Oracle and not from storage perspective

  • from the AWR find out the current latency, min, max and average.
  • In the AWR, you will have details of what you want or you can use my blog to find out latency of the events. 
  • latency introduced by networking components should be considered
  • networking should be such that it does not introduces a significant amount of latency
  • latency planned should be the same at the maximum iops and maximum data transfer
  • prefer to have redo, controlfile, ocr on fastest disks as these require less storage area.
  • if you have a read intensive and dw application, you can have temp to on faster storage area. 

5. Any storage design should exclude any type of inline compression or storage optimization, unless the storage compression (at storage level) is certified by Oracle.

6. Migration to new storage depends on what type of design you use.
  • when migrating to new storage always follow standby first approach
  • when using ASM, use drop and add disks in the same operation
  • these operations are resumable after restart of the cluster also
  • when not using ASM, use methods such as duplicate and rolling switch of data files to minimize downtime.

Tuesday, June 11, 2019

Oracle Database: Flashback RAC Primary and Standby

 
In this blog I am going to discuss on how to flashback a RAC Primary and RAC Standby both and ensure Standby is in sync. 
These are for some maintenance activities you might want to perform on Primary and to have a rollback plan. 
You can re-order few steps if you ant
My primary Database name is PRIM and Standby is STDBY.

Step 1 and 2 are setup for the flashback. 

 Step 1  - Stop the apply on STDBY
dgmgrl /
edit datbase 'STDBY' set state 'APPLY-OFF'

 Step 2 - Create GRP (Guranteed Restore Point)
i. Switch logfile on Primary (alter system archive log current) 
ii. Create GRP on Standby (Create Restore  Point BEFORE_RELOAD GUARANTEE FLASHBACK DATABASE)
iii. Create GRP on Primary  (Create Restore  Point BEFORE_RELOAD GUARANTEE FLASHBACK DATABASE)
iv. take output of select * from v$restore_point and note the scn


 Now do your changes (patching etc), assuming your changes were not successful and you want to rollback.  

 Step 3 - Do the Flashback
i. Stop Database Primary (srvctl stop database -d PRIM)
ii. Start one instance in mount (startup mount exclusive)
iii. Flashback Database (flashback database to restore point BEFORE_RELOAD);
iv. alter database open reset logs

 Step 4 - Start Primary (all nodes)
i. srvctl start database -d PRIM


 Step 5 - Flashback Standby 
i. Check the current_scn (select current_scn from V$database)
ii. if current_scn of Standby is less than 2.iv, i.e scn of GRP on primary then you need not to do any flashback and can skip
iii. If current_scn is greater then you have 2 options
First - srvctl stop database -d STDBY and then startup mount exclusive for 1 instance
Then - 
    a. Flashback to scn of the GRP of primary (flashback database to scn <> )
    b. Flashback to scn of the GRP of standby (flashback database to restore point BEFORE_RELOAD). This assumes the scn of standby GRP is less than scn of primary GRP
iv. start DB (srvctl stop database -d CHMSTDBY, and srvctl start database -d CHMSTDBY)
v. Ensure apply is working fine. (edit database 'STDBY' set state='APPLY-ON')

 Step 6 - Finally Drop Restore Points
i. Primary - Drop restore point BEFORE_RELOAD
ii. Standby - for this you will have to have database is mount mode (in case your standby was read only) and then 'drop restore point BEFORE_RELOAD'

 References: How To Flashback Primary Database In Standby Configuration (Doc ID 728374.1)

Popular Posts

© Copyright 2019 DBA to DA. . Powered by Blogger.