Pages

Advertisement

Showing posts with label 19c. Show all posts
Showing posts with label 19c. Show all posts

Sunday, May 17, 2020

Oracle Database (All) : Archive Log Generation (Distribution - hourly/daily/size)

 
In this blog I am going to cover on how to find out 
1. total number of archives generated per hour & per day 
2. The size of the archives MB/ hour & MB per day



Output Sample for redo switches / hour

Redo Switches / hour /day
Redo Size / hour /day

The complete script can be download as a GIST from here 


Friday, September 6, 2019

Oracle Database: Process RSM0, PID = , will be killed

 
When you check status of database using dgmgrl and you get the error message in alert log - 
'Process RSM0, PID = 1524144984, will be killed'

 that means that dgmgrl is not able to get the response within stipulated amount of  time. 
This can be because of many reasons such has high CPU, Load Average , network etc. 
So, what you can do is increase the overall timeout period using 

 'EDIT CONFIGURATION SET PROPERTY OperationTimeout=90'

 another symptom of this is when you will check the database details, there will be no metrics which will be populated. 

Tuesday, August 27, 2019

Oracle Exadata: chrony Daemon (release 19.1)

 
Release 19.1 of Exadata has taken a leap in using chrony daemon instead of age old written NTP daemon and protocol.

 You will not find any NTP daemon installed once you upgrade to this release and note in this release you are at Linux 7. 

 To check status of chrony daemon here are few commands which will help. 

 1. Check Sources

 # chronyc sources
210 Number of sources = 3
MS Name/IP address         Stratum Poll Reach LastRx Last sample
===============================================================================
^? aa-bbb-ccc-1100.dddddddd>     3   4   377     3  -2328ms[-2328ms] +/- 8332ms
^? bb-bbb-ccc-1100.dddddddd>     3   4   377     4  -2358ms[-2358ms] +/- 8681ms

^? cc-bbb-ccc-1100.dddddddd>     3   4   377     7  -2321ms[-2321ms] +/- 8696ms


2. Check Source stats

# chronyc sourcestats
210 Number of sources = 3
Name/IP Address            NP  NR  Span  Frequency  Freq Skew  Offset  Std Dev
==============================================================================
aa-bbb-ccc-1100.dddddddd>   6   3    81   +951.004   4830.397  -2334ms    31ms
bb-bbb-ccc-1100.dddddddd>   9   4   129   -438.641    216.568  -2372ms  4677us
cc-bbb-ccc-1100.dddddddd>  26  11   421   -381.175     20.365  -2332ms  3727us



3. Check sync

# chronyc tracking
Reference ID    : 0A901009 (cc-bbb-ccc-1100.dddddddd)
Stratum         : 4
Ref time (UTC)  : Mon Aug 26 03:19:47 2019
System time     : 0.000000304 seconds fast of NTP time
Last offset     : -0.000190595 seconds
RMS offset      : 0.012864766 seconds
Frequency       : 84.308 ppm slow
Residual freq   : -0.004 ppm
Skew            : 0.720 ppm
Root delay      : 0.140609756 seconds
Root dispersion : 1.491145134 seconds
Update interval : 16.1 seconds
Leap status     : Normal


You can find more details on chrony commands here 

Wednesday, August 21, 2019

Oracle Database: TFACTL Blackout ORA-600/7445/700

 
tfactl or oracle's trace file analyzer utility can be really helpful sometimes, and also a lot of pain in many, when it sends a lot of alerts some of which you know and it's a known issue or you don't want to do anything about it. 

 Fortunately, you can blackout such events from tfactl 

 To supppress a specific string ORA-0600 this is what I would do. 
tfactl blackout add -targettype database -target mydb -c -event  "kewrspbr_2"  -timeout none -reason "skipping ORA-0600 Doc ID 2313603.1" 

 where 
-target : dbname
-c : clusterwide
-event: String contained in the event (find it from the email you got)

-timeout : no timeout

 Similarly, you can play around with the parameters and blackout a lot of unwanted emails.
You can find them documentation below. 
Reference - https://docs.oracle.com/en/engineered-systems/health-diagnostics/trace-file-analyzer/tfaug/running-administration-commands.html#GUID-56A03EC7-3324-48BD-9701-2411626D16CB

Friday, August 2, 2019

Oracle Database: Permissions to Execute Job Class

 
There is a great mis conception in DBA's who primarily work on OLTP about Job classes.

 Job classes are one of the essentially used components in job definitions which people seldom use and seldom know about. 

 This blog however is a small and nice one which talks about how to see permissions to execute a job class.

 Yes you read that right. You need execute permission for a job class. 
Only if you have execute permission to job class, you will be able to add it to your create job. 

 the way to grant is same 

 Grant execute 'on job class' to <user>';

 to verify you should use dba_tab_privs to know if your user has execute privilege on job class or not. 

 select * from dba_tab_privs where grantee = '<your user>' and type = 'JOB CLASS';

 Example output below (from TOAD)

By default permission to execute 'DEFAULT JOB CLASS' is granted to PUBLIC, so you if you do not specify any job class name, default job class will be taken and it will be okay. 

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. 

Popular Posts

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