See What we are Talking About!
Sean's DBA Blog for Dec 2011
Sean Howe, CommitDBA Lead DBA
Hello, I am Sean Howe, Lead DBA on the CommitDBA team here at Ross Group Inc. I am going to be blogging about DBA topics, things I see as I work on client databases, solve issues, etc. Enjoy!
Dec 5, 2011
Using Native Window’s Files for Oracle ASM
By Sean Howe
You want to standardize your Oracle installations or want to take advantage of the benefits of ASM but you do not have an adequate number of disks, access to a SAN, or access to another type of uncooked filesystem to support the installation.
You don’t want to invest in the additional hardware required to host the ASM disk groups nor do you want to reorganize existing disks that, do indeed, have adequate disk space for the Oracle development environments.
Create your ASM Disk Groups on NTFS. This will allow you to take advantage of ASM features and potentially standardize your installations of development or test database environments.
- On your ASM Instance set the following init parameters:
SQL> alter system set asm_diskstring='g:\disks\_file*' scope=both;
SQL> alter system set "_asm_allow_only_raw_disks"=false scope=spfile;
- Create a perl script called CreateASMFiles.pl with the following code:
my $s='0' x 2**20;
open(File1,">g:/disks/_file_disk5") || die "Incorrect Path - $!\n";
open(File2,">g:/disks/_file_disk6") || die "Incorrect Path - $!\n";
open(File3,">g:/disks/_file_disk7") || die "Incorrect Path - $!\n";
for (my $i=1; $i print File1 $s;
print File2 $s;
print File3 $s;
- Now execute the perl script with a perl interpreter. If you don’t already have one then Oracle provides one and here is an example of its location. This script will create 3 files each 2048MB in size. You can adjust this to fit your requirements.
- Now you are ready to bring your devices into the Oracle ASM family. You may do this via DBCA (Database Configuration Assistant) or via SQL*Plus commands used for administrating ASM (create diskgroup).
You may use a similar approach within Unix by using the dd command to pre-create your disk file devices.
Dec 7, 2011
By Sean Howe
You have been using an Oracle database to contain your businesses’ data for many years. You have a production database, and periodically you move the data to a development or test database to meet development or testing needs.
You have always used Oracle’s exp and imp tools to achieve moving data between Oracle systems. You hear that these tools will be obsolete in future Oracle versions.
Use Oracle’s expdp and impdp tools to replace what you’ve customarily done with exp/imp. This enables you to continue to meet the needs of your business; in addition, you are able to achieve this task more quickly.
Some advantages of Data Pump:
- perform fine-grained object selection
- ability to estimate jobs times
- ability to restart failed jobs
- monitor running jobs
- directly load a database from a remote instance via the network
- remapping capabilities
- improved performance using parallel executions
Oracle Data Pump uses Oracle directory objects. One of the parameters for the expdp/impdp is DIRECTORY=. You must create an Oracle directory or it will default to a directory. This default directory can be obtained with the following query:
SELECT directory_path FROM dba_directories WHERE directory_name = 'DATA_PUMP_DIR';
Create Oracle Directory SQL Command:
Create a directory on both the source and target databases with the following command.
create or replace directory expdp_dir as 'c:\backup\database\expdp';
Extract DDL for user to be moved (ProdUser) to the Target System:
select dbms_metadata.get_ddl('USER', username) || '/' usercreate from dba_users where username = ‘PRODUSER’;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','PRODUSER') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','PRODUSER') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','PRODUSER') FROM DUAL;
Move the Data
Run export of data
C:\>expdp system directory=expdp_dir dumpfile=export_produser.dmp schemas=PROSUSER logfile=export_produser.log
Create user in Target Database (use output from step #2). If you want to modify the output to change the username, feel free to do so. I will articulate how to accompany that change below.
Move the output from step #3 to the target machine to 'c:\backup\database\expdp' directory folder.
Import Data into the target database.
C:\>impdp system directory=expdp_dir dumpfile=export_produser.dmp schemas=PRODUSER logfile=produser_import.log
If you want to change the name of the user that the data is loaded into then you will use the remap_schema parameter (see below). You may also want to remap_tablespace(s).
Oracle’s Data Pump utility is a much improved tool over the prior tool. Change is difficult, but if you begin to use the new tool, I am confident you will grow to appreciate the Data Pump tool. It has many enhancements that ease the task of moving data.
Complete documentation on Data Pump can be found at: