Online DBA

<<Back to Oracle ASM Main Page How to Copy File from One Server to Another in ASM Diskgroup As you already know, we can even store the DB export dumpfile in ASM diskgroup itself. Often it becomes challenging to find space in file system to perform export and we decide to use ASM diskgroup to hold the export dump files If you are performing the export to import it on some 3rd server you need to transfer these export dumpfiles to the target server (Again in target server we have space in ASM diskgroup only), so in this scenarios we need some method to copy the export dump file from source ASM DG to target ASM DG directly. No need to worry the Answer is asmcmd cp command. I want to copy the export dumpfile from source to target Environment Setup Source Server =>  q-orcl-test01 ASM DG=>          +DATAC2 Exp dumpfile=>    QA01_01.dmp ASM Instance=>   +ASM1 Target Server =>   d-orcl-test01 ASM DG=>          +RECOC1 ASM Instance=>   +ASM1 $

<<Back to DB Administration Main Page ORA-46630: keystore cannot be created at the specified location CDB011> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '+DATAC4/CDB01/wallet/' IDENTIFIED BY "xxxxxxx"; ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '+DATAC4/CDB01/wallet/' IDENTIFIED BY "EncTest123" * ERROR at line 1: ORA-46630: keystore cannot be created at the specified location Cause  Creating a keystore at a location where there is already a keystore exists Solution To solve the problem, use a different location to create a keystore (use ENCRYPTION_WALLET_LOCATION in sqlnet.ora file to specify the keystore location), or move this ewallet.p12 file to some other location. Note: Oracle does not recommend deleting keystore file (ewallet.p12) that belongs to a database. If you have multiple keystores, you can choose to merge them rather than deleting either of them.

<<Back to DB Administration Main Page ORA-28336: cannot encrypt SYS owned objects during create Table Solution: The operation must not be performed as sysdba. Always use user with dba privileges to perform the task Proof CDB01> show user; USER is "SYS" CDB01> show con_name CON_NAME ------------------------------ PDB01 CDB01> CREATE TABLE employee (      first_name VARCHAR2(128),      last_name VARCHAR2(128),      empID NUMBER,      salary NUMBER(6) ENCRYPT );   2    3    4    5    6      salary NUMBER(6) ENCRYPT      * ERROR at line 5: ORA-28336: cannot encrypt SYS owned objects SQL> show con_name CON_NAME ------------------------------ PDB01 SQL> show user USER is "PDBDBA" SQL> CREATE TABLE tde_test1 (   id    NUMBER(10),   data  VARCHAR2(50) ENCRYPT )   ; Table created.

<<Back to DB Administration Main Page Unplugging and Plugging Oracle PDB from One Container to Another Note: T he procedure is valid if even if the root container and others are using TDE but the container (PDB) you are unplugging is not having TDE and wallet keys Setup Overview Source Container: CDB01 No of PDB's in Source Container 2 (PDB01 and PDB02) PDB01 using TDE PDB02 don't Target Container: CDB02 No of PDB's 0 TDE not used Task: In this post I will unplug the PDB02 from CDB01 and Plug it in CDB002 on the same host without copying the datafile (using NOCOPY Method). Precheck  1) Perform Full PDB backup of PDB02 database. 2) Create PDB description XML file for PDB(PDB02) SQL> exec dbms_pdb.describe (PDB_DESCR_FILE=>'/u01/app/oracle/PDB02.xml', PDB_NAME=>'PDB02'); 3) Verify the status of the PDB. SQL> select pdb_name, status from cdb_pdbs where pdb_name in ('PDB02'); 4)  Checking the Compatibility of t

<<Back to DB Administration Main Page Configure Multiple Wallet Location in sqlnet.ora for SI DBs Sharing Same Oracle Home Configuring multiple wallet location in sqlnet.ora is super easy. Just use the entry like below $ORACLE_SID is translated automatically. Starting from 12c you can configure wallet location in ASM diskgroup. ENCRYPTION_WALLET_LOCATION=  (SOURCE=(METHOD=FILE)    (METHOD_DATA=     (DIRECTORY=+DATAC4/ $ORACLE_SID /wallet)    )  )

<<Back to DB Administration Main Page How to configure TDE in pluggable database in 12c for Standalone and RAC environment Assumption : You have already created a Container Database (CDB01 in my case) You already have created a pluggable Database (PDB01 in my case) Step1> Configure sqlnet.ora, Add the below entry ## COMMON WALLET LOCATION ENCRYPTION_WALLET_LOCATION=  (SOURCE=(METHOD=FILE)    (METHOD_DATA=     (DIRECTORY=+DATAC4/$ORACLE_SID/wallet)    )  ) Step2> Create Key store on CDB database and generate master encryption key for CDB SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '+DATAC4/CDB01/wallet' IDENTIFIED BY "xxxxxxx"; SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY " xxxxxxx "; SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY " xxxxxxx " WITH BACKUP; Step3> Verify the wallet has been opened in CDB database SQL> select * from v$encryption_wallet; Step4> Open

<<Back to DB Administration Main Page Hot to Fix ORA-15173: entry 'wallet' does not exist in directory CDB01> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '+DATAC4/CDB01/wallet' IDENTIFIED BY "xxxxx"; ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '+DATAC4/CDB01/wallet' IDENTIFIED BY "xxxxxx" ERROR at line 1: ORA-46640: incorrect file name for exporting keys ORA-17502: ksfdcre:4 Failed to create file +DATAC4/CDB01/wallet/ewallet.p12 ORA-15173: entry 'wallet' does not exist in directory 'CDB01' Solution: Ensure the keystore path specified in the path exists. If not create it and re-run the create keystore command ASMCMD > mkdir +DATAC4/CDB01/walle CDB01> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '+DATAC4/CDB01/wallet' IDENTIFIED BY "xxxxxx"; keystore altered.

<<Back to DB Administration Main Page Find out Which Database Components and Options are Installed on Oracle Database List Components and status col COMP_ID for a10       col COMP_NAME for a40 col VERSION  for a15     col STATUS   for a5     col MODIFIED for a30     col CONTROL     for a5     col SCHEMA      for a15     select COMP_ID, COMP_NAME, VERSION, STATUS, MODIFIED, CONTROL,   SCHEMA from DBA_REGISTRY; List down the Options Value=>TRUE=Installed and FALSE=Not Installed col PARAMETER for a40 col VALUE for a30 select * from v$option Features Used on Oracle Database SET LINESIZE 180; SET PAGESIZE 1000; SET FEEDBACK OFF; set heading ON col Name for a60 select version, name, detected_usages, currently_used, first_usage_date, last_usage_date from DBA_FEATURE_USAGE_STATISTICS where detected_usages > 0 order by 1, 2 /

<<Back to MySQL & MariaDB How To Page How to Grant Execute Procedure to User in MySQL or MariaDB Granting Execute on a Selected Procedure GRANT execute  ON procedure DB_Name.ProcName TO 'User_Name'; MySQL>GRANT EXECUTE ON PROCEDURE intdb.myproc TO 'app_test'@'localhost'; Query OK, 0 rows affected (0.00 sec) Granting Execute for all Procedure in a Database GRANT execute  ON `DB_Name`.* TO 'User_Name'; mysql>  GRANT execute  ON `testdb`.* TO  'app_test'@'localhost'; Query OK, 0 rows affected (0.00 sec)

<<Back to Oracle ASM Main Page Understanding ASM Disk:Diskgroup and Diskgroup Attributes In this post I will give you a brief idea about ASM components. The post is helpful for beginners to understand the ASM and Its components ASM Disks To create ASM disk group you need valid ASM disks. Valid ASM disks can be A Disk Partition A Logical Unit Number (LUN) A Logical Volume A Network File System (NFS) Here is an Example of Valid ASM Disk. You need correct permissions on these disks so that ASM can read them. I have ASM  AFD configured in my environment and therefore the below permissions are correct for me [root@TEST05]# ls -lrt /dev/xvd* brw-rw---- 1 root disk 202,  64 Jun  4 12:41 /dev/xvde brw-rw---- 1 root disk 202,  48 Jun  4 12:41 /dev/xvdd brw-rw---- 1 root disk 202,  32 Jun  4 12:41 /dev/xvdc brw-rw---- 1 root disk 202,  80 Jun  4 12:41 /dev/xvdf brw-rw---- 1 root disk 202, 160 Jun  4 12:41 /dev/xvdk ASM Diskgroup ASM groups the disks in Diskgroup

<<Back to DB Administration Main Page OPW-00014: Could not delete password file $orapwd FILE='+DG_TEST_DATA/testa/orapwtesta' delete=y OPW-00014: Could not delete password file +DG_TEST_DATA/testa/orapwtesta. RESOURCE LIST: ora.testa.db Solution Use the below command to delete the Password File $orapwd delete=y dbuniquename=testa <= for database $orapwd delete=y asm=y   <= for ASM OPW-00029: Password complexity failed for SYS user $orapwd FILE='+DG_TEST_DATA/testa/orapwtesta' ENTRIES=4 DBUNIQUENAME='testa' FORMAT=12.2 Enter password for SYS: OPW-00029: Password complexity failed for SYS user : Password must contain at least 8 characters. Solution 1) Use Complex password Password must contain at least 8 characters Password must not contain double quotes Password must contain at least 1 letter Password must contain at least 1 digit Password must contain at least 1 special character Password must not contain the username Pa

<<Back to DB Administration Main Page Password File Administration In this post I will discuss about the password file, its usages and how to maintain it. Starting from 12c it is possible to store the password file in a Diskgroup. The benefit of storing a password file in DG is, its shared among the instances in case of RAC and ease the administration The COMPATIBLE.ASM disk group attribute must be set to at least 12.1 for the disk group where the password is to be located. The SYSASM privilege is required to manage the Oracle ASM password file. The SYSDBA privilege on Oracle ASM is required to manage the database password file. The shared password file in a disk group is managed by ASMCMD commands, the ORAPWD tool, and SRVCTL commands. ORAPWD supports the creation of password files on an Oracle ASM disk group. All other password file manipulation is performed with ASMCMD or SRVCTL commands. Important Note :- Before running commands, such as ORAPWD, to create a password

<<Back to Oracle ASM Main Page ASM User Management You can create a personalized user in ASM and grant privileges to administer ASM Instance. This works similar to database instance except one thing When you revoke the last privilege of a user in an Oracle ASM password file, the user is not automatically deleted as is done in the Oracle Database password file. You must run DROP USER to delete a user with no privileges in an Oracle ASM password file Creating a New Personalized ASM User SQL> set line 200 SQL>col USERNAME for a15 SQL> select USERNAME,SYSDBA,SYSOPER,SYSASM,SYSBACKUP,SYSDG,SYSKM from v$pwfile_users; USERNAME        SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM --------------- ----- ----- ----- ----- ----- ----- SYS             TRUE  TRUE  TRUE  FALSE FALSE FALSE ASMSNMP         TRUE  FALSE FALSE FALSE FALSE FALSE SQL> CREATE USER ASM_PER1 IDENTIFIED by XXXXX; User created. SQL>select USERNAME,SYSDBA,SYSOPER,SYSASM,SYSBACKUP,SYSDG,SYSKM from v$pwfile_u