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

Tags:

Comments

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

  1. maclean Avatar
    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
    
  2. maclean Avatar
    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
    

Leave a Reply to maclean Cancel reply

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