Database Technologies...!

The easiest method to start/stop AWS RDS instance based on schedule is by using AWS CLI inside a bash/bat script. Download and configure the AWS CLI. Install AWS CLI – https://docs.aws.amazon.com/cli/latest/userguide/install-windows.html Configure AWS CLI for the VPC – https://docs.aws.amazon.com/cli/latest/userguide/cli-chap-configure.html CLI Commands. Use “rds stop-db-instance” and “rds start-db-instance” commands in a simple bat file as below, set userprofile=C:\Users\07766 aws rds stop-db-instance --db-instance-identifier qa-rds-ban aws rds stop-db-instance --db-instance-identifier dev-rds-ban aws rds stop-db-instance --db-instance-identifier uat-rds-ban It is required to set the userprofile of the user who configured the AWS CLI in the bat script set userprofile=C:\Users\<username> If you have not configured the AWS CLI using the security parameters, it is required to add below configuration to the script,         aws configure set AWS_ACCES

After applying RU (28828733) patch and running datapatch -verbose on two node RAC cluster, which contains 3 database instances, PDB$SEED of one of the databases was on restricted mode. Also, below sys objects in CDB level got invalidated causing the RMAN backups to fail.  Querying pdb_plug_in_violations showed datapatch errors and recommended to rerun the datapatch verbos. But datapath re run only updated the same error in pdb_plug_in_violations view and the PDB$SEED remained in restricted mode. Invalidted sys objects --> SQL> select owner, object_name, object_type, status from dba_objects where status='INVALID'; OWNER OBJECT_NAME OBJECT_TYPE STATUS -------- -------------------- --------------- -------- SYS DBMS_RCVMAN PACKAGE BODY INVALID SYS INITJVMAUX PACKAGE BODY INVALID PUBLIC SDO_VERSION SYNONYM INVALID WMSYS LT_EXPORT_PKG PACKAGE BODY INVALID MDSYS SDO_VERSION FUNCTION INVALID MDSYS SDO_3GL PACKAGE BODY INVALID MDSYS SDO_NETWORK_

Oracle RMAN or the Recovery Manager is the tool provided by Oracle to perform all its database related data protection operations. This include Rman Backups, Restorations and recovery, Flashback, Backup encryption, Duplication and other numerous options. Although a comprehensive article on Rman can be a complex topic, idea of this post is to provide basic understanding of configuring a Oracle rman backup with minimal database administration knowledge. Oracle Backup Types – Online (Hot) Backups and Offline (Cold) Backups Rman backup can be either Online backup where backups is taken while the database is open for users and applications to connect and work. Offline backups are a dying concept as more and more systems are not tolerating downtimes to take backups. In order to take online rman backup the database must be in archive log mode. SQL> select log_mode from v$database; LOG_MODE ------------ ARCHIVELOG SQL> Setting the Oracle database to archivelog mo

What is oracle core factor? Oracle core factor refers to a weightage of licensing that oracle enforce on its software, based on the underlying server infrastructure it is running on. A simple example is when running Oracle database on 4 core AIX POWER 8 system customer should license for 4 database core licenses where as if the database software is running on 4 core SPARC T4 server, customer should only license for 2 database core licenses. This is because core factor for SPARC is 0.5 and it is for POWER is 1. Below is the full core factor table published by Oracle on 29 th July 2019. http://www.oracle.com/us/corporate/contracts/processor-core-factor-table-070634.pdf Why core factor is important? Given the enormous cost involved with Oracle licensing (e.g List price for Oracle enterprise edition database per core is $47,500), it is vital to plan the correct hardware infrastructure considering all the aspects of a deployment. Even with the engineered systems such as Exada

Oracle - 12.2.0.1 When applying RU JAN_2019 DB/GI patch on 2 node RAC using rolling method, apply phase on node 1 failed with below errors, CRS-4698: Error code 2 in retrieving the patch level CRS-1154: There was an error setting Oracle ASM to rolling patch mode. CRS-4000: Command Start failed, or completed with errors. 2019/08/28 01:18:08 CLSRSC-430: Failed to start rolling patch mode All prerequisite and verification phases were successful and a quick search showed that this error (CRS-1154) can occur  if ASM rebalance operation was running during the patch apply. In our scenario no REBAL was going and the systems were almost idle as it was off peak hours. Strange enough second attempt with " opatchauto resume" went through without any errors. Also haven't come across this error code during the patch apply on node 2 of same cluster or any of the other clusters patched.

Below is a quick way to access all client databases to get the output of a specific query. This can be really helpful during audits where particular parameter or configuration is checked throughout all the databases. In the example cloud control host (Running RHEL 7.5) is used to check whether flashback is enabled on every client database. First generate TNS names of client databases based on tnsnames.ora, cat tnsnames.ora | grep -iv "(" | grep -i "=" | cut -d = -f 1 > db_list.out Then run the simple bash script below, #!/bin/bash export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1 export PATH=/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin:/u01/app/oracle/product/12.1.0.2/db_1/bin:/bin LOC=/home/oracle/audit_outs cat $LOC/db_list.txt | while read line do export ORACLE_SID=$line sqlplus -S -M "HTML ON" dbsnmp/"<password>"@"$line"<<E

After the restore of rman backup the recover session might return errors similar to below, This is  a common scenario but still it seems there are difficulties in understanding the reason why rman is asking for more logs.  So when rman complains on set of missing logs, find the oldest log it is asking and check the first_time/next_time values of particular log from (g)v$archived_log .  If the value returned is older than the restored control file time ( select checkpoint_time from v$datafile ) and between the backup start time, it is required to provide a valid backup of the archive log or in a situation where backup is not available consider opening the database with inconsistencies. In the scenario shown in above image, the minimum log rman was asking was sequence 777682 of thread 1. For this sequence this was a archive log generated at 11.10 AM 26th July while the controlfile restored was from 11.50 AM 26th July. So we need to provide the correct backup. Depending

During the apply of RU patch on Oracle 12.2 opatchauto apply was failing with below errors. Environment was single instance ASM standby database. Execution of [OPatchAutoBinaryAction] patch action failed, check log for more details. Failures: Patch Target : racadrdb1->/u01/app/oracle/product/12.2.0/dbhome_1 Type[sidb] Details: [ ---------------------------Patching Failed--------------------------------- Command execution failed during patching in home: /u01/app/oracle/product/12.2.0/dbhome_1, host: racadrdb1. Command failed: /u01/app/oracle/product/12.2.0/dbhome_1/OPatch/opatchauto apply /tmp/JAN2019/28828733 -oh /u01/app/oracle/product/12.2.0/dbhome_1 -target_type oracle_database -binary -invPtrLoc /u01/app/12.2.0.1/grid12c/grid/oraInst.loc -jre /u01/app/12.2.0.1/grid12c/grid/OPatch/jre -persistresult /u01/app/oracle/product/12.2.0/dbhome_1/OPatch/auto/dbsessioninfo/sessionresult_racadrdb1_sidb.ser -analyzedresult /u01/app/oracle/product/12.2.0/dbhome_1/OPatch/a

When monitoring Oracle database dataguard targets on AIX using Cloud Control 13C the metric collection was failing with below error. The same metric " Data Guard Status " was collected successfully from same db targets with Cloud Control 12c. Lookup on error points towards requirement of enabling IOCP (I/O completion ports) on target AIX servers. You can check the status of IOCP using below command, bash-3.2$ lsdev -Cc iocp iocp0 Defined I/O Completion Ports bash-3.2$ If the status is defined as above it is required to use smitty and change the IOCP setting to available .

There are scenarios where it is required to add additional public network to a RAC cluster in order to segregate the network traffic from the default public network. In our case the requirement was to come up with a separate network which is dedicated to carry the backup traffic between the RAC servers and the enterprise backup server. Below are the steps which were followed during the setup on Oracle 11.2 cluster (2 nodes) running on top of Solaris 10. Before the start, make sure the new IPs are assigned in the network level and available on all nodes of the cluster. In our case below are the test IPs assigned. node 1 - IP --> 172.20.60.46 | VIP --> 172.20.60.146 node 2 - IP --> 172.20.60.47 | VIP --> 172.20.60.147 Step 1 - Add new IP entries to host file. ##Backup Network 172.20.60.46 cbbacknode1 172.20.60.47 cbbacknode2 ##Backup VIP 172.20.60.146 vip-cbbacknode1 172.20.60.147 vip-cbbacknode2 Step 2 - Check currently assigned networks

During Oracle RAC db installation prerequisite check, there is a validation raised on NTP configuration. Oracle needs NTP to be configured with " slew_always " option enabled, So what is this slew setting in NTP? NTP can operate in two modes,                 1. Slew mode -- NTP will adjust time drift at a maximum rate of 500 microseconds persecond. This ensures that the server will not see any quick/large time differences which Oracle consider as a requirement.                 2. Step mode -- In this mode, clock can suddenly change time in large quantities to make any adjustment. Apparently this behavior can cause issues in db server operations. Below is the configuration change required to enable slew_always on Solaris 11.3, svccfg -s svc:/network/ntp:default setprop config/slew_always = true