Online DBA

<<Back to Oracle ASM Main Page How to Uninstall Oracle Grid Infrastructure for Standalone Server Cleanly Step1> login as oracle grid infrastructure owner (oragrid) Step2> Change directory to $ORACLE_HOME/deinstall $cd $ORACLE_HOME/deinstall Step3> execute ./deinstall.sh and follow the instruction $./deinstall Step4> Answer carefully the questions on the prompt. Step5> When prompted run the command as root user Step6> Clean the leftovers if any manually Deinstallation Logs [oragrid@test1]$./deinstall Checking for required files and bootstrapping ... Please wait ... Location of logs /tmp/deinstall2018-08-31_10-52-00AM/logs/ ############ ORACLE DECONFIG TOOL START ############ ######################### DECONFIG CHECK OPERATION START ######################### ## [START] Install check configuration ## Checking for existence of the Oracle home location /u01/oragrid/12.2.0.1/grid Oracle Home type selected for deinstall is: Oracle Grid Infras

<<Back to Oracle RAC Main Page How to prepare OS disk/LUN for ASM using AFD How to Label OS Disk for ASM using AFD login as root and set ASM environment #export ORACLE_HOME=/u01/oragrid/12.2.0.1/grid #export PATH=$PATH:$ORACLE_HOME/bin #asmcmd afd_label DISK11 /dev/xvdc11 #asmcmd afd_label DISK12 /dev/xvdc12 How to Unlabel Disk using AFD #asmcmd afd_unlabel DISK11 #asmcmd afd_unlabel DISK12

<<Back to Ansible Main Page Failed to set permissions on the temporary files Ansible needs to create when becoming an unprivileged user TASK [ora_gi_si_install : Configure Listener] ************************************************************************************************************************************************************************************************ fatal: [192.9.1.6]: FAILED! => {"msg": "Failed to set permissions on the temporary files Ansible needs to create when becoming an unprivileged user (rc: 1, err: chown: invalid user: ‘/u01/oragrid/12.2.0.1/grid’\n}). For information on working around this, see https://docs.ansible.com/ansible/become.html#becoming-an-unprivileged-user"}         to retry, use: --limit @/home/opc/ansible/playbooks/si_gi_install.retry PLAY RECAP *******************************************************************************************************************************************************************

<<Back to DB Administration Main Page [FATAL] [INS-44001] Reachability check failed from the local node [oragrid@node1 grid] $ ./gridSetup.sh  -ignorePrereq -waitforcompletion -silent -noconfig -responseFile si_gi_install.rsp Launching Oracle Grid Infrastructure Setup Wizard... [FATAL] [INS-44001] Reachability check failed from the local node node1 to the following nodes:  []  These nodes will be ignored and not participate in the configured Grid Infrastructure.    ACTION: Check the network connectivity from the local node node1 to the remote nodes  [] Solution Enter hostname in /etc/hosts If entry in /etc/hosts is already present check if there is no typo and IP/Hostname is correct If DNS is not configured  /etc/hosts should contain the entry for following  SCAN Private Interconnect Public Interconnect VIPs E xample /etc/hosts entries # Public 192.9.1.100                          node1.oracle.com                   node1 192.9.1.101                        

<<Back to DB Administration Main Page How to fix PRVF-0002 [oragrid@node1 grid]$  ./runcluvfy.sh stage -pre hacfg PRVF-0002 : could not retrieve local node name node1: node1: unknown error PRVF-0002 is reported while running cluster ware precheck ( runcluvfy.sh) for SI installation. Solution :  Enter the host name of the server in /etc/hosts and rerun  precheck [opc@node1~]$ cat /etc/hosts 192.9.1.6    node1

<<Back to MySQL & MariaDB Main Page MySQL Replication Overview Replication is a feature allowing the contents of one or more servers (called masters) to be mirrored on one or more servers (called slaves) You can setup the replication at following levels All databases One or more databases Tables within a database Since replication is using binary log to replicate the transactions, it is mandatory requirement to have the database in binary logging mode setup replication. How MySQL Master/Slave Replication Works 1> MySQL master server writes the changes in bnary log in a sequential manner 2> I/O thread of slave MySQL then pulls these changes from Master over the network and writes them in relay log. 3> SQL Thread of slave MySQL then replicates these changes to slave database. The slave server keeps track of the position in the master's binlog of the last event applied on the slave. This allows the slave server to re-connect and resume from whe

<<Back to DB Administration Main Page ORA-00020: maximum number of processes (300) exceeded ............................................................................ ............................................................................ Process m001 submission failed with error = 20 Process m000 submission failed with error = 20 Process m001 submission failed with error = 20 Fri Aug 24 12:21:07 2018 ORA-00020: maximum number of processes (300) exceeded  ORA-20 errors will not be written to the alert log for  the next minute. Please look at trace files to see all  the ORA-20 errors. Process m002 submission failed with error = 20 Solution : Increase the value of processes parameter and restart the database  SQL>alter system set processes=900 scope=spfile sid='*'; System altered. $  srvctl stop database -d ORCL $  srvctl start database -d ORCL

<<Back to Oracle RAC Main Page How to Modify Database Startup Option Using srvctl In this post I will show you, how you can modify the database startup option / Database startup mode in clusterware registry using srvctl. There are some time requirement to change the startup option (from default  OPEN ) to  MOUNT, or "READ ONLY" eg if in case of Physical Standby Configuration Let us Check the Current Configuration $ srvctl config database -d ORCL Database unique name: ORCL Database name: Oracle home: /u01/app/oracle/product/12.1.0.2/dbhome_2 Oracle user: oracle Spfile: +DATA/ORCL/PARAMETERFILE/spfileORCL.ora Password file: +DATA/ORCL/PASSWORD/pwORCL Domain: Start options: open Stop options: immediate Database role: PHYSICAL_STANDBY Management policy: AUTOMATIC Server pools: Disk Groups: DATA,RECO Mount point paths: Services: Type: RAC Start concurrency: Stop concurrency: OSDBA group: oinstall OSOPER group: oinstall Database instances: ORCL1,

<<Back to DB Administration Main Page ORA-01624: log 2 needed for crash recovery of instance ORCL1> alter database drop logfile group 2; alter database drop logfile group 2 * ERROR at line 1: ORA-01624: log 2 needed for crash recovery of instance ORCL1 (thread 1) ORA-00312: online log 7 thread 1: '+DATA/ORCL/ONLINELOG/group_7.1287.984827207' ORA-00312: online log 7 thread 1: '+RECO/ORCL/ONLINELOG/group_7.12707.984827207' ORCL1>  select group#,thread#,status, bytes/1024/1024 "size in MB" from v$log;     GROUP#    THREAD# STATUS               size in MB ---------- ---------- -------------------- ----------          1          1 CURRENT                    2048          2          1 ACTIVE                     2048          6          3 CURRENT                      50          7          1 ACTIVE                     2048 ERROR ORA-01624 is triggered if you are trying to drop a redo log group whose status is still ACTIVE. Although its not curren

<<Back to Oracle RAC Main Page How to Resizing Oracle Redo Log Groups in RAC Resizing Oracle's redo log groups is performed in 2 Steps. Step1> Add new the redo log groups with expected size. Step2> Drop the old redo log groups. In this post I will resize the redo log groups of size 50MB to 2GB in a 2Node RAC cluster Find the redo log's current status, size and member ORCL1> select group#,thread#,status, bytes/1024/1024 "size in MB" from v$log;     GROUP#    THREAD# STATUS               size in MB ---------- ---------- -------------------- ----------          1          1 CURRENT                      50          2          1 INACTIVE                     50          3          2 CURRENT                      50          4          2 INACTIVE                     50 4 rows selected. ORCL1>  select group#, member  from v$logfile order by 1;     GROUP# MEMBER ---------- --------------------------------------------------          1 +DATA/ORCL/

<<Back to DB Administration Main Page ERROR:  Unable to get logical block size for spfile '+DATA/ORCL/spfileORCL.ora' Extract From AlertLog ERROR: Unable to get logical block size for spfile '+DATA/MTSIM1P/spfileORCL.ora'. Wed Aug 22 02:17:22 2018 ERROR: Unable to get logical block size for spfile '+DATA/ORCL/spfileORCL.ora'. Wed Aug 22 02:17:22 2018 ERROR: Unable to get logical block size for spfile '+DATA/ORCL/spfileORCL.ora'. Wed Aug 22 02:17:22 2018 ERROR: Unable to get logical block size for spfile '+DATA/ORCL/spfileORCL.ora'. Wed Aug 22 02:17:22 2018 ERROR: Unable to get logical block size for spfile '+DATA/ORCL/spfileORCL.ora'. Wed Aug 22 02:17:22 2018 ERROR: Unable to get logical block size for spfile '+DATA/ORCL/spfileORCL.ora'. Investigation: ORCL1> show parameter spfile NAME                                 TYPE            VALUE ------------------------------------ --------------- ---------------------

<<Back to Oracle RAC Main Page How to Find Out VIP of an Oracle RAC Cluster Login clusterware owner (oracle) and execute the below command to find out the VIP hostname used in Oracle RAC $ olsnodes -i node1     node1-vip node2     node2-vip OR $ srvctl config nodeapps -viponly Network 1 exists Subnet IPv4: 10.0.0.0/255.255.0.0/bondeth0, static Subnet IPv6: Ping Targets: Network is enabled Network is individually enabled on nodes: Network is individually disabled on nodes: VIP exists: network number 1, hosting node node1 VIP Name: node1-vip VIP IPv4 Address: 10.0.0.1 VIP IPv6 Address: VIP is enabled. VIP is individually enabled on nodes: VIP is individually disabled on nodes: VIP exists: network number 1, hosting node node2 VIP Name: node2-vip VIP IPv4 Address: 10.0.0.2 VIP IPv6 Address: VIP is enabled. VIP is individually enabled on nodes: VIP is individually disabled on nodes:

<<Back to MySQL & MariaDB How To Page Dropping A User in MySQL or MariaDB MariaDB [(none)]> drop user 'test'@'localhost'; Query OK, 0 rows affected (0.07 sec) MariaDB [(none)]> drop user 'test'@'%'; Query OK, 0 rows affected (0.01 sec)

<<Back to MySQL & MariaDB How To Page How to create a user in MySQL or MariaDB User Creation CREATE USER 'myuser' IDENTIFIED BY 'mypassword'; MariaDB [(none)]> CREATE USER 'test' IDENTIFIED BY 'test'; Query OK, 0 rows affected (0.00 sec) Grant Full Permissions from Local Host MariaDB [(none)]> GRANT USAGE ON *.* TO 'test'@'localhost' IDENTIFIED BY 'test'; Query OK, 0 rows affected (0.01 sec ) Grant Full Permissions from Any Computer MariaDB [(none)]> GRANT USAGE ON *.* TO 'test'@'%' IDENTIFIED BY 'test'; Query OK, 0 rows affected (0.01 sec) Grant Full Permissions On a Particular Database from Any Computer MariaDB [(none)]> GRANT ALL privileges ON `agi`.* TO 'test'@'%'; Query OK, 0 rows affected (0.05 sec) Verify use right permissions MariaDB [(none)]> show grants for  'test'@'%'; +---------------------------------------------

<<Back to Oracle ASM Main Page What is ASM  Fast Mirror Resync and How It Works Oracle ASM Fast Mirror Resync is a new feature introduced in 11g,to restore the redundancy faster after a transient disk path failure. What is Transient Failures Any problems that make a failure group temporarily unavailable are considered transient failures. transient failures can be caused by disk path malfunctions, such as cable failures, host bus adapter failures, controller failures, or disk power supply interruptions. How Oracle ASM Fast Mirror Resync Works Oracle ASM fast resync keeps track of pending changes to extents on an offline disk during a disk outage. The extents are resynced when the disk is brought back online. The feature is supported by disk_repair_time attribute of the Diskgroup. ASM keeps track of changed extents until disk_repair_time and apply the changes if the disk is back online with in this period else it drops the disk and initiates rebalance operation. As you

<<Back to Oracle ASM Main Page What is ASM Scrubbing ASM Scrubbing is a process of checking logical data corruption and repair them automatically. obviously this works with normal and high redundancy disks groups. The scrubbing process repairs logical corruptions using the mirror disks ASM disk scrubbing can be done at following levels: - data file level: SQL> alter diskgroup data scrub file '+data/orcl/datafile/example.266.806582193' repair power high force; - specific disk level: SQL> alter diskgroup data scrub disk data_0005 repair power high force; - ASM disk group level: SQL> alter diskgroup data scrub power low; The "scrub" argument takes the following options: scrub  . . . repair:   This option automatically repairs disk corruptions. If the "repair" keywords is not used, Oracle will only identify corruptions and will not fix them: scrub . . . power:  If the "power" argument is specified with data scrubbing, you can have

<<Back to Oracle ASM Main Page Corrupting Oracle Database Block Intensely Note: - The post is only intended for educational and knowledge purpose, must not be tried in Live (prod as well as non-prod) database. CASE1: When ASM is Used Let's say I want to corrupt  Block#132 of  datafile# 9 stored in ASM File#256 and ASM DG#4 I already know DB_BLOCK_SIZE  is 8k and ASM AU_SIZE => 1M for the tablespace holding this block. Check out  Trace Down Your Data from Database to Disk to find out the above details. That's said let's proceed 1M of AU can hold 128 DB Blocks of 8K size and therefore with simple math calculation we can find that the block number 132 is the 4th block in 2nd ASM Extent. So Let us find the Disk holding this block physically and corrupt it using dd. Login to ASM Instance and run below query to find the AU distribution across the disk. SQL> select XNUM_KFFXP, DISK_KFFXP, AU_KFFXP from X$KFFXP where NUMBER_KFFXP=256 AND GROUP_KFFXP=4 AND