Online DBA

<<Back to Oracle DATAPUMP Main Page How to Export Database from 12c for 11g Version Using EXPDP VERSION Parameter Syntax and Description VERSION=[COMPATIBLE | LATEST | version_string] Specifies the version of database objects to be exported. Only database objects and attributes that are compatible with the specified release will be exported. This can be used to create a dump file set that is compatible with a previous release of Oracle Database. • COMPATIBLE - This is the default value. The version of the metadata corresponds to the database compatibility level as specified on the COMPATIBLE initialization parameter. Database compatibility must be set to 9.2 or later. • LATEST - The version of the metadata and resulting SQL DDL corresponds to the database release regardless of its compatibility level. • version_string - A specific database release (for example, 11.2.0). In Oracle Database 11g, this value cannot be lower than 9.2. $ cat exp.par DIRECTORY=EXP DUMPFILE=EX

<<Back to Oracle DATAPUMP Main Page How to Export Tablespace using expdp TABLESPACES=<comma seprated list of tablespace> $ cat exp.par DIRECTORY=EXP DUMPFILE=EXP.DMP LOGFILE=EXP.LOG TABLESPACES=TEST REUSE_DUMPFILES=Y FLASHBACK_TIME=SYSTIMESTAMP $ expdp test@PDB01 parfile=exp.par How to Import Individual Tablespace using impdp  $ cat imp.par DIRECTORY=EXP DUMPFILE=EXP.DMP LOGFILE=IMP.LOG TABLESPACES=TEST $impdp system@PDB02 parfile=imp.par During the following import situations, Data Pump automatically creates the tablespaces into which the data will be imported: • The import is being done in FULL or TRANSPORT_TABLESPACES mode • The import is being done in table mode with TRANSPORTABLE=ALWAYS In all other cases, the tablespaces for the selected objects must already exist.

<<Back to Oracle DATAPUMP Main Page ORA-39173: Encrypted data has been stored unencrypted in dump file set $ expdp test@PDB01 parfile=exp.par Export: Release 12.2.0.1.0 - Production on Wed Nov 28 11:30:14 2018 Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved. Password: Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Starting "TEST"."SYS_EXPORT_TABLESPACE_01":  test/********@PDB01 parfile=exp.par Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/COMMENT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT . . exported "T

<<Back to Oracle DB Security Main Page Oracle 12c Wallet/KeyStore Administration Commands Creating a Keystore Examples The following statement creates a password-protected software keystore in directory /etc/ORACLE/WALLETS/orcl: SQL>ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/etc/ORACLE/WALLETS/orcl' IDENTIFIED BY password; The following statement creates an auto-login software keystore from the keystore created in the previous statement: SQL> ADMINISTER KEY MANAGEMENT   CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/etc/ORACLE/WALLETS/orcl'   IDENTIFIED BY password; Opening a Keystore Examples The following statement opens a password-protected software keystore SQL>ADMINISTER KEY MANAGEMENT   SET KEYSTORE OPEN   IDENTIFIED BY password; If you are connected to a CDB, then the following statement opens a password-protected software keystore in the current container: SQL>ADMINISTER KEY MANAGEMENT   SET KEYSTORE OPEN   IDENTIFIED BY password   CONT

<<Back to Oracle DB Security Main Page In many cases you need to export the TDE Master Encryption Key and Import it in same or different database. For Example: If you want to migrate a PDB (Using Encryption) from one CDB to another, you must export the TDE Keys from source CDB and import it in Target CDB. How To Export TDE Master Encryption Key SQL> ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET "any password to protect export file" TO 'file_path' IDENTIFIED BY keystore_password If you run the above statement in PDB it will export the keys for that PDB only SQL> alter session set container=PDB01 ; Session altered. SQL> ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET "mysecret" TO '/home/dbatst1/Wallet/PDB01_TDE.KEY' IDENTIFIED BY keystore_password; keystore altered. NOTE : You can only export the keys if the password based wallet is open in case AUTOLOGIN wallet is open you can export the keys eit

<<Back to Oracle DB Security Main Page How To Find TDE Master Encryption Key That is in Use Find the master key in use in a non-CDB SQL>SELECT KEY_ID FROM V$ENCRYPTION_KEYS WHERE ACTIVATION_TIME = (SELECT MAX(ACTIVATION_TIME) FROM V$ENCRYPTION_KEYS WHERE ACTIVATING_DBID = (SELECT DBID FROM V$DATABASE)); Find the master key in use in a Multitenant-Environment SQL> SELECT KEY_ID,CREATION_TIME,ACTIVATION_TIME,KEY_USE,CREATOR_PDBNAME,ACTIVATING_DBNAME,ACTIVATING_PDBNAME FROM V$ENCRYPTION_KEYS; Find the master key in use in Current PDB SQL>SELECT KEY_ID FROM V$ENCRYPTION_KEYS WHERE ACTIVATION_TIME = (SELECT MAX(ACTIVATION_TIME) FROM V$ENCRYPTION_KEYS WHERE ACTIVATING_PDBID = SYS_CONTEXT('USERENV','CON_ID'));

<<Back to Oracle DB Security Main Page You can create the TDE Master Encryption Keys in two ways   The following statement creates and activates a master encryption key in a password-protected software keystore. First Method: Create and Activate the Key  SQL> ADMINISTER KEY MANAGEMENT SET KEY  IDENTIFIED BY password  WITH BACKUP; But if you are planning to create the TDE Master Encryption Key to use it in future use the below command. This command just creates the TDE Master Encryption Key but does not activate the key Second Method: Only Creates The Key. Later you have to manually activate it if you want to use it S QL> ADMINISTER KEY MANAGEMENT CREATE KEY IDENTIFIED BY password WITH BACKUP; In case you have AUTOLOGIN or LOCAL_AUTOLOGIN Keystore use FORCE KEYSTORE clause to create the Key    SQL> ADMINISTER KEY MANAGEMENT  CREATE KEY FORCE KEYSTORE IDENTIFIED BY Password WITH BACKUP; keystore altered. The Key you create with CREATE KEY  command is not used

<<Back to Oracle DB Security Main Page How to Delete Oracle Wallet or Keystores Oracle strongly recommends that you do not delete keystores, particularly after you have configured Transparent Data Encryption and the keystore is in use. You can find if a keystore is in use by querying the WRL_PARAMETER column of the V$ENCRYPTION_WALLET view after you open the keystore.

<<Back to Oracle DB Security Main Page If you close the Keystore, a database user or application cannot perform any operation involving encrypted data until the keystore is reopened. You can check the Keystore status using following query SQL> select status from V$ENCRYPTION_WALLET; Closing a Software AUTOLOGIN or LOCAL_AUTOLOGIN Keystore SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE ; keystore altered. Although the above query will close the AUTOLOGIN or LOCAL_AUTOLOGIN Keystore, you try to query V$ENCRYPTION_WALLET to check its status, You will see the wallet is open , because auto login wallet is open automatically as soon as you query  V$ENCRYPTION_WALLET view Closing a Software Password Based Keystore SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY xxxx CONTAINER = ALL; keystore altered. OR If you have configured external store for keystore password use below command SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY EXTE

<<Back to Oracle DB Security Main Page Migrating Software Keystore from Automatic Storage Management Step1> Create Empty Keystore SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/dbatst1/admin/wallet/TST1T' IDENTIFIED BY xxxx; keystore altered. Step2> Merge Keystore with Empty Keystore SQL> ADMINISTER KEY MANAGEMENT MERGE KEYSTORE '+DG_TST_DATA/TST1T/wallet/' IDENTIFIED BY xxxx INTO EXISTING KEYSTORE '/u01/dbatst1/admin/wallet/TST1T' IDENTIFIED BY xxxx WITH BACKUP USING 'mig_keystore'; keystore altered. Step3> Close the Keystore SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE; keystore altered. Step4> Modify the sqlnet.ora to point to the new keystore Step5> Open the Keystore SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN identified by xxxx; keystore altered. SQL> select WRL_TYPE,WRL_PARAMETER,STATUS,WALLET_TYPE from v$encryption_wallet; WRL_TYPE   WRL_PARAMETER                                  

<<Back to Oracle DB Security Main Page How To Merge One Software into Another Existing Software Keystore There are many situations when you need to merge the Keystores eg. You added a New key in primary database Keystore having physical standby database to replicate the keys you must merge key stores of primary database with physical standby. You can merge any combination of software keystores, but the merged keystore must be password-based SQL> ADMINISTER KEY MANAGEMENT MERGE KEYSTORE '+DG_TST_DATA/TST1T/wallet/' IDENTIFIED BY xxxx INTO EXISTING KEYSTORE '/u01/dbatst1/admin/wallet/TST1T/' IDENTIFIED BY xxxx WITH BACKUP USING 'Merg_bkp'; keystore altered. Validate the presence of Keys in New Wallet $orapki wallet display -wallet /u01/dbatst1/admin/wallet/TST1T/ Syntax ADMINISTER KEY MANAGEMENT MERGE KEYSTORE 'keystore1_location' [IDENTIFIED BY software_keystore1_password] INTO EXISTING KEYSTORE 'keystore2_location' IDENTIFIED BY

<<Back to DB Administration Main Page SQL> ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE USING 'nondefault_dir' FORCE KEYSTORE IDENTIFIED BY xxxx TO '/u01/dbatst1/stage/'; ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE USING 'nondefault_dir' FORCE KEYSTORE IDENTIFIED BY xxxx TO '/u01/dbatst1/stage/' * ERROR at line 1: ORA-46620: backup for the keystore cannot be taken Cause : Either the specified backup location was not an ASM path name when the configured keystore location was an ASM path or the specified backup location was an ASM path name when the configured keystore location was not an ASM path. Solution : Either specify an ASM path name for the backup location if the keystore location is an ASM path or specify a non-ASM path name for the backup location if the keystore location is not an ASM path. SQL>  ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE USING 'nondef_dir' FORCE KEYSTORE IDENTIFIED BY xxxx TO '+DG_TEST'; keystore a

<<Back to DB Administration Main Page Backup Database Wallet (Password-Based Software Keystores) A backup of the keystore contains all of the keys contained in the original keystore. After you complete the backup operation, the keys in the original keystore are marked as "backed up". You can check this in FULLY_BACKED_UP column in V$ENCRYPTION_WALLET view ; SQL> select FULLY_BACKED_UP from V$ENCRYPTION_WALLET; FULLY_BAC --------- NO SQL> ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE USING 'Key_bkp' FORCE KEYSTORE IDENTIFIED BY xxxx; keystore altered. SQL> select FULLY_BACKED_UP from V$ENCRYPTION_WALLET; FULLY_BAC --------- YES NOTE: If you modify the keystore (modify a key or a secret etc) and the modification does not exists in the backup copy the value of FULLY_BACKED_UP column in V$ENCRYPTION_WALLET changes to NO even if it was yes before. Oracle Database prefixes the software keystore password file name with the file creation time stamp in

<<Back to Oracle DB Security Main Page Changing the Software Keystore (TDE Wallet) Password Changing the Password of a Software Keystore is fully online. You can change the Software Keystore password (rotate) at any time. To change the password you must specify the WITH BACKUP clause, which backup the current keystore. Syntax: ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD [FORCE KEYSTORE] IDENTIFIED BY old_password SET new_password [WITH BACKUP [USING 'backup_identifier']]; SQL> ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD FORCE KEYSTORE IDENTIFIED BY xxxx set xxxx with backup using 'Key_bkp'; keystore altered. NOTE1: If AUTO LOGIN wallet is open you must either specify the FORCE KEYSTORE to temporarily open the keystore to change the password OR follow these steps close the auto login wallet SQL> ADMINISTER KEY MANAGEMENT set keystore close; keystore altered. open the password wallet  SQL> ADMINISTER KEY MANAGEMENT  set keystore ope

<<Back to Oracle DB Security Main Page SQL>  ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD FORCE KEYSTORE IDENTIFIED BY old_password set new_password with backup using 'Keystore_bkp_before_key_rotate';  ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD FORCE KEYSTORE IDENTIFIED BY old_password set new_password  with backup using 'Keystore_bkp_before_key_rotate' * ERROR at line 1: ORA-46677: specified backup identifier exceeds maximum length Cause : The backup identifier within the ASM file name of the keystore exceeded the maximum length of 12 characters. Solution : specify  backup identifier for keystore <= 12 character long. SQL> ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD FORCE KEYSTORE IDENTIFIED BY xxxx set xxxx with backup using 'Key_bkp'; keystore altered.

<<Back to Oracle DB Security Main Page ORA-46638: merging of the two keystores failed SQL> administer key management merge keystore  '/u01/app/Keystore/backup' identified by xxxx into existing keystore '+DG_TST_DATA/TST2T/wallet' identified by xxxx with backup; ORA-46638: merging of the two keystores failed Cause : There could be various reasons to this error. Check the trace file to find, what causing the issue in your case. In my case I found below in trace file. kztsmcombine: could not load the source with error 29106kzckmcom: keystore combine error 29106 Solution: Was using wrong password of the keystore from the source keystore. Corrected the password to fix the issue

<<Back to Oracle DB Security Main Page How to Copy Keystore (Wallet) stored in ASM from Primary to Physical Standby Assumptions:  It is assumed that the Keystore is already configured on Primary as well as on Standby Database Keys in Primary Database Keystore are not synchronized with the Physical Standby Database Keystore Step1: Create empty keystore in any other location on File system on primary SQL> administer key management create keystore '/u01/app/Keystore/backup' identified by xxxx; Step2:Merge both keystores, Kestore in ASM and Kestore created in file system SQL> administer key management merge keystore '+DG_TST_DATA/TST1T/wallet' identified by xxxx into existing keystore '/u01/app/Keystore/backup' identified by xxxx with backup; NOTE: First password is the keystore password for Keystore on ASM and second password is the keystore password for Keystore in file system Step3:Copy the file to standby Database U se your favorite