11g ASM – Inside story – Part3 (Export Datapump)
Introduction
In the previous post of “11gR2 ASM – Inside story – Part2″, we have seen how to restore a diskgroup after a failure.
In this part 3, we will create datapump export dumpsets within ASM diskgroup.
The procedure is valid for 10g Oracle database,
We should follow the steps below:
1- Create a directory from ASM.
2- Create a directory object in the database.
3- Create log file directory. The datapump log file cannot be stored within ASM.
4- Run the datapump export.
5- Verify the file within the ASM diskgroup.
1- Create a directory from ASM:
From ASM instance:
1
2
3
SQL> ALTER DISKGROUP DATA_REPORTING ADD DIRECTORY '+DATA_REPORTING/oraw/dumpsets';
Diskgroup altered.
2- Create a directory object in the database:
From database instance:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
SQL> CREATE DIRECTORY DUMP_SETS AS '+DATA_REPORTING/oraw/dumpsets';
Directory created.
SQL>
SQL> create user wissem identified by wissem;
User created.
SQL> grant read,write on directory DUMP_SETS to wissem;
Grant succeeded.
SQL> grant EXP_FULL_DATABASE to wissem;
Grant succeeded.
SQL>
SQL> create table wissem.dump (ID number);
Table created.
SQL> alter user wissem quota 1M on USERS;
User altered.
SQL> insert into wissem.dump values(1);
1 row created.
SQL> commit;
Commit complete.
SQL>
3- Create log file directory:
1
2
3
4
5
6
7
8
9
10
11
SQL> CREATE DIRECTORY LOGFILE_DEST AS '/tmp';
Directory created.
SQL>
SQL> grant read,write on directory LOGFILE_DEST to wissem;
Grant succeeded.
SQL>
4- Run the datapump export:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
[oracle@wissem ~]$ expdp wissem/wissem directory=DUMP_SETS dumpfile=wissem_dump.dmp TABLES=DUMP LOGFILE=LOGFILE_DEST:wissem_dump.dmp
Export: Release 11.2.0.1.0 - Production on Sat Mar 5 05:56:57 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "WISSEM"."SYS_EXPORT_TABLE_01": wissem/******** directory=DUMP_SETS dumpfile=wissem_dump.dmp TABLES=DUMP LOGFILE=LOGFILE_DEST:wissem_dump.dmp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "WISSEM"."DUMP" 5.007 KB 1 rows
Master table "WISSEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for WISSEM.SYS_EXPORT_TABLE_01 is:
+DATA_REPORTING/oraw/dumpsets/wissem_dump.dmp
Job "WISSEM"."SYS_EXPORT_TABLE_01" successfully completed at 05:57:04
[oracle@wissem ~]$
5- Verify the file within the ASM diskgroup:
You can verify the file is created within the ASM diskgroup by query the view v$ASM_FILE where TYPE=’DUMPSET’.
Introduction
In the previous post of “11gR2 ASM – Inside story – Part2″, we have seen how to restore a diskgroup after a failure.
In this part 3, we will create datapump export dumpsets within ASM diskgroup.
The procedure is valid for 10g Oracle database,
In the previous post of “11gR2 ASM – Inside story – Part2″, we have seen how to restore a diskgroup after a failure.
In this part 3, we will create datapump export dumpsets within ASM diskgroup.
The procedure is valid for 10g Oracle database,
We should follow the steps below:
1- Create a directory from ASM.
2- Create a directory object in the database.
3- Create log file directory. The datapump log file cannot be stored within ASM.
4- Run the datapump export.
5- Verify the file within the ASM diskgroup.
1- Create a directory from ASM:
1- Create a directory from ASM.
2- Create a directory object in the database.
3- Create log file directory. The datapump log file cannot be stored within ASM.
4- Run the datapump export.
5- Verify the file within the ASM diskgroup.
1- Create a directory from ASM:
From ASM instance:
1
2
3
| SQL> ALTER DISKGROUP DATA_REPORTING ADD DIRECTORY '+DATA_REPORTING/oraw/dumpsets';Diskgroup altered. |
2- Create a directory object in the database:
From database instance:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
| SQL> CREATE DIRECTORY DUMP_SETS AS '+DATA_REPORTING/oraw/dumpsets';Directory created.SQL> SQL> create user wissem identified by wissem;User created.SQL> grant read,write on directory DUMP_SETS to wissem;Grant succeeded.SQL> grant EXP_FULL_DATABASE to wissem;Grant succeeded.SQL> SQL> create table wissem.dump (ID number);Table created.SQL> alter user wissem quota 1M on USERS;User altered.SQL> insert into wissem.dump values(1);1 row created.SQL> commit;Commit complete.SQL> |
3- Create log file directory:
1
2
3
4
5
6
7
8
9
10
11
| SQL> CREATE DIRECTORY LOGFILE_DEST AS '/tmp';Directory created.SQL> SQL> grant read,write on directory LOGFILE_DEST to wissem;Grant succeeded.SQL> |
4- Run the datapump export:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
| [oracle@wissem ~]$ expdp wissem/wissem directory=DUMP_SETS dumpfile=wissem_dump.dmp TABLES=DUMP LOGFILE=LOGFILE_DEST:wissem_dump.dmpExport: Release 11.2.0.1.0 - Production on Sat Mar 5 05:56:57 2011Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing optionsStarting "WISSEM"."SYS_EXPORT_TABLE_01": wissem/******** directory=DUMP_SETS dumpfile=wissem_dump.dmp TABLES=DUMP LOGFILE=LOGFILE_DEST:wissem_dump.dmp Estimate in progress using BLOCKS method...Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 64 KBProcessing object type TABLE_EXPORT/TABLE/TABLE. . exported "WISSEM"."DUMP" 5.007 KB 1 rowsMaster table "WISSEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded******************************************************************************Dump file set for WISSEM.SYS_EXPORT_TABLE_01 is: +DATA_REPORTING/oraw/dumpsets/wissem_dump.dmpJob "WISSEM"."SYS_EXPORT_TABLE_01" successfully completed at 05:57:04[oracle@wissem ~]$ |
5- Verify the file within the ASM diskgroup:
You can verify the file is created within the ASM diskgroup by query the view v$ASM_FILE where TYPE=’DUMPSET’.
No comments:
Post a Comment