Online DBA

<<Back to MySQL & MariaDB Main Page How to Export only Database Structure Excluding data from MySQL or  MariaDB  Database Syntax mysqldump --single-transaction --no-data --databases <dbname> > /stage/tmp/dump.sql Example #mysqldump --single-transaction --no-data --databases testdb > /stage/tmp/testdb.sql  

<<Back to DB Administration Main Page SQL> ALTER SESSION SET CONTAINER=PDB01 SQL>ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET "Password_to_protect_export" TO '/u01/app/stage/exp_tde.exp' FORCE KEYSTORE IDENTIFIED BY "KeyStore_Password"; * ERROR at line 1: ORA-46650: cannot retrieve information for a master key identifier Solution:  To export a master encryption key that is not activated use the EXPORT KEYS command in conjunction with <WITH IDENTIFIER> clause Check if the Master key is activated or not. SQL>SELECT KEY_ID,KEY_USE,KEYSTORE_TYPE,CREATOR_PDBNAME,ACTIVATING_PDBNAME,ACTIVATION_TIME FROM V$ENCRYPTION_KEYS; If Master Key is not activated use below command to export it SQL> ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET "Password_to_protect_export file " TO '/u01/app/stage/exp_tde.exp' FORCE KEYSTORE IDENTIFIED BY "KeyStore_Password" WITH IDENTIFIER <C...

<<Back to DB Administration Main Page ORA-65005: missing or invalid file name pattern for file - +DATA/CDB01/TEMPFILE/temp.1667.989251537 CDB011> create pluggable database PDB01 using '/u01/stage/UNPLUG_DEF_PDB01.xml' SOURCE_FILE_NAME_CONVERT=('+DATA/CDB01/DATAFILE/','/u01/stage/') MOVE FILE_NAME_CONVERT = ('/u01/stage/', '+DATA_TEST'); create pluggable database PDB01 using '/u01/stage/UNPLUG_DEF_PDB01.xml' * ERROR at line 1: ORA-65005: missing or invalid file name pattern for file - +DATA/CDB01/TEMPFILE/temp.1667.989251537 Solution To resolve the issue I modified the UNPLUG_DEF_PDB01.xml manually. Identified and removed the temp file creation clause from XML definition file. Remove the following portion from XMIL file and re-run the create PDB clause. $ diff UNPLUG_DEF_PDB01.xml UNPLUG_DEF_PDB01_tmp.xml <     <name>TEMP</name> <     <type>1</type> <     <tsn>2...

<<Back to Oracle DATAPUMP Main Page How to Perform Encrypted Database Export To take encrypted export either use ENCRYPTION or ENCRYPTION_PASSWORD parameter, or both, Syntax : ENCRYPTION = [ALL | DATA_ONLY | ENCRYPTED_COLUMNS_ONLY | METADATA_ONLY | NONE] To use the ENCRYPTED_COLUMNS_ONLY option, you must have Oracle Advanced Security Transparent Data Encryption (TDE) enabled. Example $ cat exp_full_pdb01.par DIRECTORY=DUMP DUMPFILE=EXP_PDB01_FULL%U.DMP LOGFILE=EXP_PDB01_FULL.LOG SCHEMAS=TEST REUSE_DUMPFILES=Y ENCRYPTION=DATA_ONLY ENCRYPTION_PASSWORD=TESTENCRYPTION $ expdp system@PDB01 parfile=exp_full_pdb01.par Export: Release 12.2.0.1.0 - Production on Fri Oct 12 12:29:54 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 "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@PDB01 parfile=exp_full_pd...

<<Back to Oracle DATAPUMP Main Page $ expdp system@PDB01 parfile=exp_full_pdb01.par Export: Release 12.2.0.1.0 - Production on Fri Oct 12 12:06:23 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 ORA-39002: invalid operation ORA-39188: unable to encrypt dump file set ORA-28365: wallet is not open Cause: Data Pump could not create an encrypted dump file set. The most common reason for this message is that you are trying to create a dump file set that can be transparently decrypted, and the database external security device is not open. As it is clear from the addition message as well ORA-28365: wallet is not open Solution : If the external security device is not open, then open it. If the external security device is not configured (which is in my case ), then the only type of encrypted dump file set that you can create is a passwor...

<<Back to Oracle DATAPUMP Main Page $ expdp system@PDB01 parfile=exp_full_pdb01.par Export: Release 12.2.0.1.0 - Production on Fri Oct 12 12:01:56 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 ORA-39001: invalid argument value ORA-39000: bad dump file specification ORA-31641: unable to create dump file "/u01/dbatst2/stage/dump/EXP_PDB01_FULL01.dmp" ORA-27038: created file already exists Additional information: 1 Issue: dumpfiles generated by expdp operation already present Solution : Solution1 :  use reuse_dumpfiles=y parameter to overwire the dumpfile Solution2 : delete the existing dumpfiles and restart the export again Solution3: Use different directory location to store the generated export file Solution4: Use unique naming convention for export dumpfiles

<<Back to Oracle DATAPUMP Main Page CONTENT parameter of expdp let you select whether you want to export only data or only metadata or both Default : ALL Syntax and Description CONTENT=[ALL | DATA_ONLY | METADATA_ONLY] DATA_ONLY exports only table row data; no database object definitions are exported. METADATA_ONLY exports only database object definitions; no table row data is exported. Exporting metadata only  $ cat exp_full_pdb01.par directory=dump dumpfile=EXP_PDB01_FULL%U.dmp logfile=EXP_PDB01_FULL.log full=y CONTENT=METADATA_ONLY $ expdp system@PDB01 parfile=exp_full_pdb01.par Exporting data only directory=dump dumpfile=EXP_PDB01_FULL%U.dmp logfile=EXP_PDB01_FULL.log full=y CONTENT=DATA_ONLY $ expdp system@PDB01 parfile=exp_full_pdb01.par