Now we've got all these wonderful ASM infrastructure - can we start using it to start storing datapump exports too? In theory this should improve export speeds as writes to ASM infrastructure should be faster that to a straight filesystem.
Lets give it a try and see if it's possible - first up lets create a directory as normal but pointing to an ASM location
[ oracle@server]:TEST :[ ~ ]# s
SQL*Plus: Release 12.1.0.1.0 Production on Fri Feb 28 11:24:50 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> create directory datapump_asm as '+DATA';
Directory created.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Simple enough - lets now try an export using that location
[ oracle@server]:TEST :[ ~ ]# expdp / full=y directory=datapump_asm dumpfile=asmtest.dmp
Export: Release 12.1.0.1.0 - Production on Fri Feb 28 11:26:12 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
Hmm that didn;t work that well - doesn;t seem to be able to write the logfile - which makes sense when you read the restrictions on the files that can be located in ASM (well ignoring acfs etc for now). A plain text logfile does not qualify. So how do we get round that?
Again - quite easy - using a lesser used syntax where we specify an explicit (different) directory for the logfile
oracle@server]:TEST :[ ~ ]# expdp / full=y directory=datapump_asm dumpfile=asmtest.dmp logfile=DATA_PUMP_DIR:test.l
Export: Release 12.1.0.1.0 - Production on Fri Feb 28 11:26:55 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Starting "OPS$ORACLE"."SYS_EXPORT_FULL_01": /******** full=y directory=datapump_asm dumpfile=asmtest.dmp logfile=DATA_PUMP_DIR:test.log
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 312 KB
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/RADM_FPTM
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOU/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/STATISTICS/MARKER
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
. . exported "SYS"."KU$_USER_MAPPING_VIEW" 5.757 KB 19 rows
. . exported "SYSTEM"."REDO_DB" 23.42 KB 1 rows
. . exported "SYS"."DAM_CONFIG_PARAM$" 6.507 KB 14 rows
. . exported "SYS"."TSDP_PARAMETER$" 5.921 KB 1 rows
. . exported "SYS"."TSDP_POLICY$" 5.890 KB 1 rows
. . exported "SYS"."TSDP_SUBPOL$" 6.304 KB 1 rows
. . exported "SYS"."AUD$" 0 KB 0 rows
. . exported "SYS"."DAM_CLEANUP_EVENTS$" 0 KB 0 rows
. . exported "SYS"."DAM_CLEANUP_JOBS$" 0 KB 0 rows
. . exported "SYS"."TSDP_ASSOCIATION$" 0 KB 0 rows
. . exported "SYS"."TSDP_CONDITION$" 0 KB 0 rows
. . exported "SYS"."TSDP_FEATURE_POLICY$" 0 KB 0 rows
. . exported "SYS"."TSDP_PROTECTION$" 0 KB 0 rows
. . exported "SYS"."TSDP_SENSITIVE_DATA$" 0 KB 0 rows
. . exported "SYS"."TSDP_SENSITIVE_TYPE$" 0 KB 0 rows
. . exported "SYS"."TSDP_SOURCE$" 0 KB 0 rows
. . exported "SYSTEM"."REDO_LOG" 0 KB 0 rows
. . exported "SYSTEM"."SCHEDULER_PROGRAM_ARGS" 9.468 KB 12 rows
. . exported "SYS"."AUDTAB$TBS$FOR_EXPORT" 5.929 KB 2 rows
. . exported "SYS"."NACL$_ACE_EXP" 9.890 KB 1 rows
. . exported "SYS"."NACL$_HOST_EXP" 6.890 KB 1 rows
. . exported "SYS"."DBA_SENSITIVE_DATA" 0 KB 0 rows
. . exported "SYS"."DBA_TSDP_POLICY_PROTECTION" 0 KB 0 rows
. . exported "SYS"."FGA_LOG$FOR_EXPORT" 0 KB 0 rows
. . exported "SYS"."NACL$_WALLET_EXP" 0 KB 0 rows
. . exported "SYSTEM"."SCHEDULER_JOB_ARGS" 0 KB 0 rows
. . exported "DEMO"."DEMO" 37.46 KB 1 rows
. . exported "DEMO"."DEMO2" 0 KB 0 rows
Master table "OPS$ORACLE"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for OPS$ORACLE.SYS_EXPORT_FULL_01 is:
+DATA/asmtest.dmp
Job "OPS$ORACLE"."SYS_EXPORT_FULL_01" successfully completed at Fri Feb 28 11:28:51 2014 elapsed 0 00:01:55
OK - so that's now worked fine - we have to have the log file on the filesystem but the dumpfile can be in ASM.
The files in this case are just created directly under +DATA (but we could easily create the directories in ASM to logically separate them out)
The 12c export also looks interesting with lots of SYS owned stuff and also new export types mentioned - all this is related to new stuff in 12c and CDB/PDB stuff.
Comments
Post a Comment