Ask Maclean

Backup Script:Expdp Schema to ASM Storage

Below is a shell script  which backup dmpfile to ASM storage everyday:

#!/bin/bash

#asmcmd mkdir DATA/ASM_DATAPUMP_BACKUP
#create directory ASM_DATAPUMP_BACKUP as '+DATA/ASM_DATAPUMP_BACKUP';
#grant read,write on directory ASM_DATAPUMP_BACKUP to system;
#create directory LOGDIR as '/s01/logdir';
#grant read,write on directory LOGDIR to system;
#create directory DMPBACKUP as '/s01/dmpbackup';
#grant read,write on directory DMPBACKUP to system;

export ORACLE_HOME=/s01/oracle/product/11.2.0/dbhome_1;
export ORA_CRS_HOME=/s01/grid;
export ORACLE_SID=PROD1;
export PATH=$ORACLE_HOME/bin:/s01/grid:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/home/oracle/bin:$ORACLE_HOME/OPatch;
thisday=`date "+%Y""%m""%d"`;
expfilename='maclean'$thisday'.dmp';
explogname='maclean'$thisday'.log';
yesterday=`date -d "1 days ago" +%Y%m%d`;
yesterdayfile='maclean'$yesterday'.dmp';
expdp system/system schemas=maclean directory=ASM_DATAPUMP_BACKUP dumpfile=$expfilename logfile=LOGDIR:$explogname;

#TRANSFER FILE FROM ASM TO FILESYSTEM

sqlplus / as sysdba <<EOF
exec DBMS_FILE_TRANSFER.COPY_FILE('ASM_DATAPUMP_BACKUP','$expfilename','DMPBACKUP','$expfilename');
EOF

export ORACLE_SID=+ASM1;
export ORACLE_HOME=/s01/grid;
$ORACLE_HOME/bin/asmcmd rm DATA/ASM_DATAPUMP_BACKUP/$yesterdayfile;
#/usr/bin/find /s01/dmpbackup -mtime 1 -name 'maclean*.dmp'  -exec /bin/rm -rf {} \;
#asmcmd cp DATA/ASM_DATAPUMP_BACKUP/$expfilename /s01/dmpbackup;

Posted

in

by

mac

Tags:

Comments

2 responses to “Backup Script:Expdp Schema to ASM Storage”

  1. maclean Avatar
    maclean
    DBMS_FILE_TRANSFER Feature in Oracle 10G

Applies to:
Oracle Server - Enterprise Edition
Information in this document applies to any platform.
Goal
This bulletin illustrates the new file transfer feature provided within Oracle 10g.

SCOPE & APPLICATION
===================
This feature can be used by Oracle DBA

This is a new feature introduce in Oracle 10G. Using this feature one can move data files from within Oracle without ever 'physically' logging into the operating system itself. This feature provide one more option when doing common database administrative tasks such as simple storage management, where we require to free up space on a disk.
Solution

Steps to copy a file on the local files system:
===================================================

1. Lets take a look at where our current files reside.

SQL> SELECT tablespace_name,file_name FROM dba_data_files;

TABLESPACE_NAME FILE_NAME
--------------- -----------------------------------------------------

SYSTEM /h02/app/oracle/isc101/system01.dbf
SYSAUX /h08/oradata/isc101/sysaux01.dbf
UNDOTBS1 /h08/oradata/isc101/undotbs01.dbf
USERS /h02/app/oracle/isc101/users01.dbf


2. Create a DIRECTORY to our source directory

SQL> CREATE DIRECTORY ORCL_ORA10G AS '/h02/app/oracle/ORCL';
Directory created.

3. Create a DIRECTORY to our destination directory

SQL> CREATE DIRECTORY TEST_ORA10G AS '/h02/app/oracle/TEST';
Directory created.

4. Grant READ Privileges to the source directory

SQL> GRANT READ ON DIRECTORY ORCL_ORA10G TO Kunal;
Grant succeeded.

5. Grant WRITE Privileges to the destination directory

SQL> GRANT WRITE ON DIRECTORY TEST_ora10G TO Kunal;
Grant succeeded.

6. Connect as the user you wish to copy file as

connect Kunal/@ora10g
connected.

7. If you want to copy an existing data file attached to a tablespace, you must put it into read-only mode or take it off-line. A note here to be aware of when working with existing data files that are being used within a database, you cannot copy the data files associated with the SYSTEM, TEMPORARY, or UNDO tablespaces. This is because they cannot be taken off-line or put into read-only mode since they need to be available for read-write access by processes. In addition, the new tablespace SYSAUX in Oracle10g cannot be put into read-only mode. Because of this we will work with the USERS tablespace:

a.
SQL> ALTER TABLESPACE USERS READ ONLY;
Tablespace altered.

-OR-

b.
SQL> ALTER TABLESPACE USERS OFFLINE;
Tablespace altered.

8. Run the COPY_FILE procedure to copy a file to the backup directory.

BEGIN
DBMS_FILE_TRANSFER.COPY_FILE(
source_directory_object => 'ORCL_ORA10G',
source_file_name => 'USERS01.DBF',
destination_directory_object => 'TEST_ORA10G',
destination_file_name => 'USERS01.DBF');
END;
/
PL/SQL procedure successfully completed.

9. Copy of data file is done, now end backup mode for tablespace.

a.
SQL> ALTER TABLESPACE CUSTOMER READ WRITE;
Tablespace altered.

-OR-

b.
SQL> ALTER TABLESPACE CUSTOMER ONLINE;
Tablespace altered.

Steps to copy a file on the local file system:
===================================================

The GET_FILE procedure allows you to copy binary files from a remote server to the local server.

-- Login to the remote server.

CONN Kunal/Kunal@remote

-- Create the source directory object and switch mode of a tablespace.

CREATE OR REPLACE DIRECTORY ORCL_REMOTE_10G AS '/h02/app/oracle/ORCL_REMOTE_10G';
GRANT READ ON DIRECTORY ORCL_REMOTE_10G TO Kunal;
ALTER TABLESPACE users READ ONLY;

-- Login to the local server.
CONN Kunal/Kunal@local

-- Create the destination directory object and a database link.
CREATE OR REPLACE DIRECTORY ORCL_LOCAL_10G AS '/h02/app/oracle/ORCL_LOCAL_10G';
CREATE DATABASE LINK remote CONNECT TO KUNAL IDENTIFIED BY Kunal USING 'REMOTE';

-- Get the file.
BEGIN
DBMS_FILE_TRANSFER.GET_FILE(
source_directory_object => 'ORCL_REMOTE_10G',
source_file_name => 'USERS01.DBF',
source_database => 'REMOTE',
destination_directory_object => 'ORCL_LOCAL_10G',
destination_file_name => 'USERS01.DBF');
END;
/

PL/SQL procedure successfully completed.

-- Login to the remote server.
CONN system/password@remote

-- Switch the tablespace back to read write mode.
ALTER TABLESPACE users READ WRITE;

Checking the destination directory on the local server will reveal that the file has been copied successfully.

Steps to copy a file on the remote file system:
===================================================

The PUT_FILE procedure allows you to copy binary files from the local server to a remote server.

-- Login to the remote server.
CONN Kunal/Kunal@remote

-- Create the destination directory object.
CREATE OR REPLACE DIRECTORY ORCL_REMOTE_10G AS '/h02/app/oracle/ORCL_REMOTE_10G';

-- Login to the local server.
CONN KUNAL/KUNAL@local

-- Create the source directory object, database link and switch mode of a tablespace.
CREATE OR REPLACE DIRECTORY ORCL_LOCAL_10G AS '/h02/app/oracle/ORCL_LOCAL_10G';
CREATE DATABASE LINK remote CONNECT TO KUNAL IDENTIFIED BY password KUNAL 'REMOTE';
ALTER TABLESPACE users READ ONLY;

-- Put the file.
BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(
source_directory_object => 'ORCL_LOCAL_10G',
source_file_name => 'USERS01.DBF',
destination_directory_object => 'ORCL_REMOTE_10G',
destination_file_name => 'USERS01.DBF',
destination_database => 'REMOTE');
END;
/

-- Switch the tablespace back to read write mode.
ALTER TABLESPACE users READ WRITE;

Checking the destination directory on the remote server will reveal that the file has been copied successfully. 
NOTE:  This does the physical copy of the file.  However, the steps in Note 115424.1 How to Rename or Move Datafiles and Logfiles will need to be implemented so that database registers the datafile in it's controlfile.  Without doing so, the database will not recognize the newly copied file.



Considerations:
=================

When using the DBMS_FILE_TRANSFER package there are a couple of considerations and usage notes of which you should be aware.

1. The procedures contained within the DBMS_FILE_TRANSFER package will convert the parameters to uppercase unless you use double quotes around the variable.

2. Not to worry, the file names are not converted to uppercase.

3. When a file is copied, it is treated as a binary file.

4. No character conversions are performed when copying files.

5. File size must be a multiple of 512 bytes.

6. File size cannot exceed 2 terabytes.

7. Also please note the package DBMS_FILE_TRANSFER.GET_FILE is not backwards compatible :

For instance, when trying to connect to a 9.2 database to pull a file from there, will report the below
error message :

SQL> BEGIN
2 DBMS_FILE_TRANSFER.GET_FILE(
3 source_directory_object => 'DB_FILES_DIR1',
4 source_file_name => 'DB92ATEST.ORA',
5 source_database => 'DB92A',
6 destination_directory_object => 'DB_FILES_DIR2',
7 destination_file_name => 'DB92ATEST.ORA');
8 END;
9 /
BEGIN
*
ERROR at line 1:
ORA-03115: unsupported network datatype or representation
ORA-02063: preceding line from DB92A
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 37
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 132
ORA-06512: at line 2
    Reply
  2. maclean Avatar
    maclean
    How to Transport a Tablespace from a 10g ASM on Windows to 11g ASM on AIX Using the DBMS_FILE_TRANSFER Package

Applies to:
Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 11.1.0.7 - Release: 10.1 to 11.1
Information in this document applies to any platform.
Goal
The below example will demonstrate how to transport a tablespace from a 10g ASM on Windows environment to 11g ASM on AIX environment using the DBMS_FILE_TRANSFER package.
Solution

Example:
----------
The source database on Windows is PROD1 and the target database is a 11g RAC database RACDB1.

1) Create the tablespace and the test object in the source database.
SQL> create tablespace test_asm datafile size 100m;
Tablespace created.

SQL> select file_name from dba_data_files where tablespace_name='TEST_ASM';

FILE_NAME
------------------------------------------------------------------------------
+DATA/PROD1/datafile/test_asm.376.696333127

SQL> conn system/xxx
Connected.

SQL> create table myobjects tablespace test_asm as select * from dba_objects;
Table created.

SQL> select count(*) from system.myobjects;

COUNT(*)
----------
56504


2) Create a database link from the source database to the target database. 
SQL> CREATE DATABASE LINK DBS2 CONNECT TO system identified by oracle using 'racdb1';
Database link created.

SQL> select * from dual@dbs2;

D
-
X


3) Create a directory to hold the converted datafile on the source.
SQL> create directory source_dir as '+DATA/PROD1/xtransport/';
Directory created.


4) Create a directory to hold the tablespace meta data dump on the source.
SQL> create directory source_dir_2 as '+DATA';
Directory created.


5) Create an O/S level directory for the data pump export logfile.
SQL> create directory test_asm_log as 'd:\oracle\';
Directory created.


6) Create a directory on the target to hold the transported datafile.
SQL> CREATE OR REPLACE DIRECTORY target_dir AS '+DATA';
Directory created.


7) Create an O/S level directory for the data pump import logfile.
SQL> create directory test_asm_log as '/u01/oracle/';
Directory created.


8) Make the tablespace which is going to be transported read only.
SQL> alter tablespace test_asm read only;
Tablespace altered.


9) Export the tablespace meta data using Data Pump.
C:\Documents and Settings>expdp directory=source_dir_2 dumpfile=test_asm.dmp transport_tablespaces=TEST_ASM transport_full_check=Y logfile=test_asm_log:exp.log

Export: Release 10.2.0.4.0 - Production on Monday, 31 August, 2009 10:41:34

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Username: system
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 -
Production With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/********
directory=sour
ce_dir_2 dumpfile=test_asm.dmp transport_tablespaces=TEST_ASM
transport_full_che
ck=Y logfile=test_asm_log:exp.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully
loaded/unloaded

******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
+DATA/test_asm.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at
10:42:16

10) Use DBMS_FILE_TRANSFER to transfer the export dump file to the target.
SQL> begin
dbms_file_transfer.put_file
(source_directory_object => 'source_dir_2',
source_file_name => 'test_asm.dmp',
destination_directory_object => 'target_dir',
destination_file_name => 'test_asm.dmp',
destination_database => 'dbs2');
end;
/
PL/SQL procedure successfully completed.

11) Use RMAN to convert the tablespace to the target AIX 64 bit format.

Note: The converted datafile will reside in the directory we created in
an earlier step - +DATA/PROD1/xtransport.

RMAN> convert tablespace test_asm to
2> platform 'AIX-Based Systems (64-bit)'
3> format '+DATA';

Starting backup at 31/AUG/09
configuration for DISK channel 2 is ignored
configuration for DISK channel 3 is ignored
configuration for DISK channel 4 is ignored
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=470 instance=PROD11 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00224 name=+DATA/PROD1/datafile/test_asm.376.696333127
converted datafile=+DATA/PROD1/xtransport/test_asm.266.696338369
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
Finished backup at 31/AUG/09


12) Use DBMS_FILE_TRANSFER to copy the converted datafile to the target.
SQL> begin
dbms_file_transfer.put_file
(source_directory_object => 'source_dir',
source_file_name => 'test_asm.266.696338369',
destination_directory_object => ' target_dir',
destination_file_name => 'tts1_db1.dbf',
destination_database => 'dbs2');
end;
/

PL/SQL procedure successfully completed.


13) On the target import the datafile meta data using Data Pump.

The imp.par import parameter file has the following contents:
directory=target_dir
dumpfile=test_asm.dmp
logfile=test_asm_log:imp.log
TRANSPORT_DATAFILES='+DATA1/tts1_db1.dbf'
keep_master=y

middba1:/u01/oracle> impdp parfile=imp.par

Import: Release 11.1.0.6.0 - 64bit Production on Monday, 31 August, 2009
11:39:32
Copyright (c) 2003, 2007, Oracle. All rights reserved.

Username: system
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 -
64bit Production
With the Partitioning, Real Application Clusters and Real Application
Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_02" successfully
loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_02": system/********
parfile=imp.par
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_02" successfully completed at
11:39:48

14) On the target database RACDB1, check that the TEST_ASM tablespace has been
plugged in and the MYOBJECTS table is present.
middba1:/u01/oracle> sql

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Aug 31 11:41:29 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit
Production
With the Partitioning, Real Application Clusters and Real Application
Testing options

SQL> select file_name from dba_data_files where tablespace_name='TEST_ASM';

FILE_NAME
--------------------------------------------------------------------------------
+DATA/tts1_db1.dbf

SQL> select count(*) from system.myobjects;

COUNT(*)
----------
56504
    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *