Online DBA

<<Back to DB Administration Main Page /u01/oragrid/12.2.0.1/grid/lib/libclntshcore.so.12.1: file too short $ ./asmcmd Error: /u01/oragrid/12.2.0.1/grid/lib/libclntshcore.so.12.1: file too short at /u01/oragrid/12.2.0.1/grid/bin/asmcmdcore line 300.  at /u01/oragrid/12.2.0.1/grid/bin/asmcmdcore line 301. Cause The installation has been done with -ignorePrereq option Solution 1> Clean deinstall this oracle home Do ensure to run the cluster verification utility and fix all Warning and Failures reported by cluvfy utility and re-run the installation you can run  cluvfy for single instance as shown below $GRID_HOME//runcluvfy.sh stage -pre hacfg and for RAC $GRID_HOME//runcluvfy.sh stage -pre crsinst -n node1,node......noden Note:(node1,node2...noden are the list of RAC nodes)

<<Back to Exadata Main Page Exadata The disk has been confined and is in an offline state Error Description Data hard disk entered confinement offline status. The LUN 0_7 changed status to warning - confinedOffline. CellDisk changed status to normal - confinedOffline. All subsequent I/Os on this disk are failed immediately. Confinement tests will be run on the disk to determine if the disk should be dropped Checked the AlerHistory from the Cell # cellcli -e list alerthistory          2_1     2018-07-27T01:56:15+02:00       warning         "Data hard disk entered confinement offline status. The LUN 0_7 changed status to warning - confinedOffline. CellDisk changed status to normal - confinedOffline. All subsequent I/Os on this disk are failed immediately. Confinement tests will be run on the disk to determine if the disk should be dropped. Status                      : WARNING - CONFINEDOFFLINE  Manufacturer                : HGST  Model Number                :   

<<Back to DB Administration Main Page Creation of a Password-based keystore is Failing  CDB0012> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/oracle/stage/wallet' IDENTIFIED BY "xxxxx"; ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/oracle/stage/wallet' IDENTIFIED BY "xxxx" * ERROR at line 1: ORA-46633: creation of a password-based keystore failed Cause  Password-based keystore could not be created at the specified location due to either incorrect file permissions for Oracle on the specified location or due to the nonexistence of the specified location. Solution 1> Check  ENCRYPTION_WALLET_LOCATION in SQLNET.ORA and ensure the  keystore location is correct. ENCRYPTION_WALLET_LOCATION=  (SOURCE=(METHOD=FILE)    (METHOD_DATA=     (DIRECTORY= /u01/app/oracle/stage/wallet )    )  ) 2> Ensure the PATH specified in SQLNET.ORA exists and oracle user has proper privileges on this directory. 3> Ensure the

<<Back to Oracle DATAPUMP Main Page How to Resolve ORA-39181: Only partial table data may be exported due to fine grain access control Cause This is expected behavior ORA-39181 is caused by an unprivileged user who tries to export a table with a fine grain access control policiy applied. Solution To avoid this:  Grant the privilege EXEMPT ACCESS POLICY to the exporting user  -or- Perform the export with privileges user  -or- Disable the VPD policy Note :- Carefully analyze what is best for you. Do ensure to revoke   EXEMPT ACCESS POLICY privilege once export is done Extract of Logs expdp admin@ORCL1 parfile=ORCL_TEST_EXP.par Export: Release 12.1.0.2.0 - Production on Thu Jul 26 12:47:19 2018 Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved. Password: Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Advanced Analyt

<<Back to DB Administration Main Page How to Configure Auto Login Wallet in Oracle 12c Step1> Check the Wallet's Current Status As you can see I have already password wallet in place. I will configure this password wallet to Auto_Login Wallet. To Configure Wallet from scratch check out my post  How to configure TDE Using Wallet in pluggable database in 12c SQL> set linesize 200 col WALLET_DIR for a32 col status for a21 select STATUS,WRL_PARAMETER WALLET_DIR,WALLET_TYPE from V$ENCRYPTION_WALLET; STATUS                WALLET_DIR                       WALLET_TYPE --------------------- -------------------------------- ------------------------------------------------------------ OPEN                  +DATAC4/CDB001/wallet/           PASSWORD 1 row selected. Step2> Create Local Auto Login KeyStore from Existing KeyStore SQL> ADMINISTER KEY MANAGEMENT CREATE LOCAL AUTO_LOGIN KEYSTORE FROM KEYSTORE  '+DATAC4/CDB01/wallet/' IDENTIFIED BY &qu

<<Back to DB Administration Main Page ORA-28365: wallet is not open Encountered while Starting the Database $ srvctl start instance -d CDB001 -i CDB0011 PRCR-1013 : Failed to start resource ora.cdb001.db PRCR-1064 : Failed to start resource ora.cdb001.db on node node1.oracle.com CRS-5017: The resource action "ora.cdb001.db start" encountered the following error: ORA-28365: wallet is not open . For details refer to "(:CLSN00107:)" in "/u01/app/oracle/diag/crs/node1.oracle.com/crs/trace/crsd_oraagent_oracle.trc". CRS-2674: Start of 'ora.cdb001.db' on 'node1.oracle.com' failed Solution : Start the instance in mount mode SQL> startup mount; ORACLE instance started. Total System Global Area 2147483648 bytes Fixed Size                  2926472 bytes Variable Size            1392511096 bytes Database Buffers          738197504 bytes Redo Buffers               13848576 bytes Database mounted. Check Wallet status set linesiz

<<Back to DB Administration Main Page How to Resolve ORA-12578: TNS: Wallet Open Failed $ sqlplus /@ORCL1 as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 24 17:51:12 2017 Copyright (c) 1982, 2016, Oracle.  All rights reserved. ERROR: ORA-12578: TNS:wallet open failed Easiest way to resolve the issue by changing WALLET_OVERRIDE = TRUE to FALSE in SQLNET.ORA edit your sqlnet.ora file and modify SQLNET.WALLET_OVERRIDE parameter value from TRUE to FALSE . Change this: SQLNET.WALLET_OVERRIDE = TRUE to this: SQLNET.WALLET_OVERRIDE = FALSE Try Again.

<<Back to MySQL & MariaDB Main Page mysqldump Error 2013: Lost connection to MySQL server during query when dumping table 'string' at row Solution: Increase the value of net_read_timeout = 120 net_write_timeout = 900 and max_allowed_packet=256 or even 1GB and try mysqldump again. Default value for me was net_read_timeout            60 net_write_timeout           60 max_allowed_packet      4194304 Changing the value as mentioned below solved my Issue. net_read_timeout = 120 net_write_timeout = 900 max_allowed_packet=256  MB You can Change the value of These variables either in my.cnf file which requires restart of the MYSQL sever or Just session or Global Level using set command set global net_write_timeout=900; Query OK, 0 rows affected (0.00 sec) set global net_read_timeout=120; Query OK, 0 rows affected (0.00 sec) set global max_allowed_packet=268435456; Query OK, 0 ro

<<Back to DB Administration Main Page Dumping Oracle Database Blocks As we already know the data in oracle database resides in Tablespace (Logical Container) and each Tablespace has one or more datafile (Physical storage) so lets start with this Step1> Create a tablespace SQL> create tablespace TEST_AU_OS datafile '/u01/dbatest1/stage/database/db01.dbf' size 100M; Tablespace created. Step2> Find the Detail of your Tablespace and Datafile SQL> select f.FILE#, f.NAME "File", t.NAME "Tablespace" from V$DATAFILE f, V$TABLESPACE t where t.NAME='TEST_AU_OS' and f.TS# = t.TS#;      FILE# File                                                         Tablespace ---------- ------------------------------------------------------------ ---------------         27 /u01/dbatest1/stage/database/db01.dbf                        TEST_AU_OS Step3> Lets Create A Table and Populate Some Data SQL> create table test (n number, name varch

<<Back to Oracle ASM Main Page How to Calculate the ASM rebalance Operation Cost In this post I will demonstrate you, how you can find out the estimated amount of data that will be relocated by ASM rebalance activity triggered due to your action. This you can estimate in advance before actually executing the actual operation. The benefit of calculating the Cost upfront is, that you can decide the suitable time period to execute the task and a suitable value of POWER which you should use to finish the task in expected time frame. Example1:- In this example I want to drop a disk from my disk group TEST_DG but before executing the drop disk command I wanted to know the amount of data ASM has to transfer as part of rebalance activity. SQL> EXPLAIN WORK SET STATEMENT_ID='DROP_DISK' FOR ALTER DISKGROUP TEST_DG DROP DISK TEST08; Explained. SQL> SELECT * FROM V$ASM_ESTIMATE WHERE STATEMENT_ID='DROP_DISK' ; GROUP_NUMBER   STATEMENT        TIMESTAMP   EST_W

<<Back to Oracle ASM Main Page Alter Diskgroup There are many occasions where one need to alter the Diskgroup. In this post I will show you only those which are most frequently required. You can alter a disk group with SQL*Plus, ASMCA, or ASMCMD commands. Adding Disks to a Disk Group Dropping Disks from Disk Group Replacing Disks in Disk Group Renaming Disks in Disk Group Resizing Disks in Disk Group Changing Diskgroup Attributes Undropping Disks in Disk Group Creating ADVM Volumes How to Add Disk in a Diskgroup I have already created a DG "TEST_DG"  Click Here to See How to Create ASM Diskgroup . The Diskgroup Configuration for  TEST_DG is shown below Let us find the available which we can add to TEST_DG diskgroup $ asmcmd lsdsk --candidate -p Group_Num  Disk_Num      Incarn  Mount_Stat  Header_Stat  Mode_Stat  State   Path         0         0           0  CLOSED      PROVISIONED  ONLINE     NORMAL  AFD:TEST08         0         1           0  CLOSE