Oracle Database / Oracle Forms / PL/SQL / Solaris 10 / Unix

How to FTP Archive Log Files to Standby / DR Server at Regular Intervals, Using Unix Script and Cron Job

2011-03-16T14:08:00.000-07:00

How to FTP Archive Log Files to Standby / Disaster Recovery (DR) Server at Regular Intervals, Using Unix Script and Cron Job

The sample script will help to synchronize / transport archive log files from primary server to the DR / Standby Server

This sample copies all files since yesterday (estimated) to a temporary FTP directory and then starts FTPing to DR database. The process is very sensitive and could result in very serious consequences. Please be very careful while applying it on production database.

a. Create Unix script as listed following:

$ cd /data1/users/scripts/backup/

$ vi ftp_logs_to_dr.sh

#!/bin/sh

HOST='testHost.com'

USER='testuser'

PASSWD='testpassword'

## define actual archive log directory

TARGET_DIR1=/data/app/oracle/orcl/FTP_ARCLOGS/

## define temporary archive log directory to be used by ftp

TARGET_DIR2=/data/app/oracle/orcl/archlogs/

SUCCESS1='NOT OK'

SUCCESS2='NOT OK'

SUCCESS3='NOT OK'

#CREATE A LOG FILE

NEW_DATE1=`date +"%m_%d_%y"`

LOGFILE_NAME='FTP_ARCLOGS'$NEW_DATE1'.log'

touch /data1/users/ftplog/$LOGFILE_NAME

##Clear the temporary archive log directory first

cd /data/app/oracle/orcl/FTP_ARCLOGS/

CUR_DIR=`pwd`'/'

if [ "$CUR_DIR" = "$TARGET_DIR1" ]

then

rm -f *

SUCCESS1='OK'

echo $SUCCESS1

## Go to actual archive log directory and copy files(until yesterday) to

## the ftp directory

rcp -p `find /data/app/oracle/orcl/archlogs/ -name '*arc' -mtime -1` /data/app/oracle/orcl/FTP_ARCLOGS/

##Update log file with the list of files to be FTPed

cd /data/app/oracle/orcl/FTP_ARCLOGS/

echo 'List of files to be copied...... date/time: ' `date` >> /data1/users/ftplog/$LOGFILE_NAME

ls -l >> /data1/users/ftplog/$LOGFILE_NAME

CUR_DIR=`pwd`'/'

if [ "$CUR_DIR" = "$TARGET_DIR1" ]

then

echo 'Connecting to FTP server and start sending files. Please be patient during this operation.... date/time: ' `date` >> /data1/users/ftplog/$LOGFILE_NAME

## Kick start FTP to send files to target server

ftp -n -i $HOST <<END_SCRIPT

quote USER $USER

quote PASS $PASSWD

bin

cd cd /dataDR/app/oracle/orcl/archlogsDR/

mput *.arc

quit

END_SCRIPT

echo 'FTP operation completed successfully. -- date/time: ' `date` >> /data1/users/ftplog/$LOGFILE_NAME

SUCCESS3='OK'

else

`date` >> /data1/users/ftplog/$LOGFILE_NAME

SUCCESS3='NOT OK'

exit 0

b. Configure the script in crontab to run regularly at 10:00 pm (for example)

0 22 * * * /data1/users/scripts/backup/ftp_logs_to_dr.sh

How to Run Unix Script from within PL/SQL oracle 10g - Solaris 10

2011-03-15T14:07:00.000-07:00

How to Run Unix Script from within PL/SQL oracle 10g - Solaris 10

This example will demonstrate how to run an a unix script from within PL/SQL. The example uses DBMS_SCHEDULER builtin oracle package.The example was tested on Solaris 10 with Oracle 10g instance.

$ sqlplus / as sysdba
SQL>
CREATE OR REPLACE
PROCEDURE RUN_OS_SCRIPT( in_os_command IN VARCHAR2)
IS
   v_prg         NUMBER;
   v_jobs        NUMBER;
   error_message VARCHAR2(500);
   v_count99     NUMBER;
   v_job_action VARCHAR2(100);
   v_arg         VARCHAR2(50);
BEGIN
   v_job_action := in_os_command;
   v_prg        := 0;
   v_jobs       := 0;
   v_count99    := 0;
   BEGIN
      dBMS_SCHEDULER.stop_job (job_name => 'RUN_UNIX_SCRIPT_JOB' );
   EXCEPTION
   WHEN OTHERS THEN
      NULL;
   END;
   --=========================================================================
   -- This loop will make sure that the job is not already running. This seems to be a bug in 10g that
   -- stop and drop commands fail to stop or drop schedules / jobs immediately.
   -- To oversome this problem I have added this loop to make sure the next run should be successful.
   --=========================================================================
   LOOP
      IF v_count99 > 2000 THEN
         EXIT;
      END IF;
      v_count99 := v_count99+1;
      SELECT COUNT(*)
      INTO v_prg
      FROM sys.dba_scheduler_programs
      WHERE upper(PROGRAM_NAME) = upper('RUN_UNIX_SCRIPT_PRG') ;
      SELECT COUNT(*) INTO v_jobs
      FROM sys.dba_scheduler_jobs
      WHERE upper(JOB_NAME) = upper( 'RUN_UNIX_SCRIPT_JOB' ) ;
      IF v_prg > 0 OR v_jobs > 0
       THEN
         BEGIN
            DBMS_SCHEDULER.stop_job (job_name => 'RUN_UNIX_SCRIPT_JOB' );
            DBMS_SCHEDULER.drop_program ( program_name => 'RUN_UNIX_SCRIPT_PRG' , force => TRUE);
            DBMS_SCHEDULER.drop_schedule ( schedule_name => 'RUN_UNIX_SCRIPT_SCHEDULE' , force => TRUE);
            DBMS_SCHEDULER.drop_job      ( job_name => 'RUN_UNIX_SCRIPT_JOB' , force => TRUE);
         EXCEPTION
         WHEN OTHERS THEN
            NULL;
         END;
      ELSE
         EXIT ;
      END IF;
   END LOOP;
   --========================================================================

   --========================================================================
   -- Create a new schedule and define a job to run
   --========================================================================
   DBMS_SCHEDULER.CREATE_PROGRAM( program_name => 'RUN_UNIX_SCRIPT_PRG' , program_type => 'EXECUTABLE',
   program_action => v_job_action, enabled         => FALSE, number_of_arguments => 0, comments => 'RUN RUN_UNIX_SCRIPT' );
   DBMS_SCHEDULER.enable (name                     => 'RUN_UNIX_SCRIPT_PRG' );
   DBMS_SCHEDULER.CREATE_SCHEDULE( schedule_name   => 'RUN_UNIX_SCRIPT_SCHEDULE', start_date => sysdate + .001, repeat_interval => 'freq=hourly; byminute=0', end_date => NULL, comments => 'test schedule');
   DBMS_SCHEDULER.CREATE_JOB ( job_name            => 'RUN_UNIX_SCRIPT_JOB', program_name => 'RUN_UNIX_SCRIPT_PRG' ,   schedule_name => 'RUN_UNIX_SCRIPT_SCHEDULE', enabled=> true, auto_drop=> false);
   DBMS_SCHEDULER.run_job (job_name                => 'RUN_UNIX_SCRIPT_JOB' );
   error_message := 'job done';
   dbms_output.put_line(error_message);

   --===================================================================
   -- Drop schedule after the job is completed
   --===================================================================
   BEGIN
      DBMS_SCHEDULER.drop_program ( program_name     => 'RUN_UNIX_SCRIPT_PRG' , force => TRUE);
      DBMS_SCHEDULER.drop_schedule ( schedule_name   => 'RUN_UNIX_SCRIPT_SCHEDULE' , force => TRUE);
      DBMS_SCHEDULER.drop_job ( job_name             => 'RUN_UNIX_SCRIPT_JOB' , force => TRUE);
    EXCEPTION
    WHEN OTHERS THEN
      NULL;
   END;
EXCEPTION
WHEN OTHERS THEN
   error_message := v_count || '    ' ||SUBSTR(SQLERRM,1,100);
   dbms_output.put_line(error_message);
END;
/

Execute following code to run the operating system command / script.

BEGIN
     RUN_OS_SCRIPT('/data1/users/scripts/copy_arch_logs.sh' );
END;
/

How to extract / download / unpack a BLOB with large data into a CSV File using raw column

2011-03-13T12:01:00.000-07:00

How to extract / download / unpack a BLOB with large data into a CSV File using raw column

This example will help to upload larger taxt into blobs and later download / extract into a csv file. Normally this can be achived using CLOBS but what if your CSV file goes upto 4 GB? I think this is the best way in a situation like that.

Following procedure requires two parameters
1. A reference key to read the blob from database table (The blob must already be stored in a
     database table).
2. Directory where you want to create a CSV file.

The output will return 'OK' if the procedure ran successfully.

CREATE OR REPLACE PROCEDURE
                         CREATE_CSV_USING_BLOB(P_BLOB_ID in number,
                                                                             P_DIRECTORY IN VARCHAR2,
                                                                             P_RESULT OUT VARCHAR2) is
    V_BLOB BLOB;
    V_START NUMBER := 1;
    V_BLOB_CHUNK_LENGTH NUMBER;
    V_READ_BLOB_IN_RAW RAW(32000);
    V_BLOB_LENGTH NUMBER;
    V_FILENAME VARCHAR2(200);
    V_OUTPUT_FILENAME utl_file.file_type;
BEGIN
    P_RESULT := 'OK';
    V_START := 1;
    V_BLOB_CHUNK_LENGTH := 32000;
    -- get length of blob
    SELECT dbms_lob.getlength(FILE_BLOB)
    INTO V_BLOB_LENGTH
    FROM UPLOAD_STREET_FILES
    WHERE FILE_ID = P_BLOB_ID;
   -- select blob into variable
    SELECT FILE_BLOB,
           FILE_NAME
    INTO   VBLOB,
           V_FILENAME
    FROM   UPLOAD_STREET_FILES
    WHERE FILE_ID = P_BLOB_ID;
   -- define output file which will store the csv data

    V_OUTPUT_FILENAME := utl_file.fopen(P_DIRECTORY , V_FILENAME,'wb', 32760);
    -- if small enough for a single write just do it
    IF V_BLOB_LENGTH < 32760 THEN
      utl_file.put_raw(V_OUTPUT_FILENAME,V_BLOB);
      utl_file.fflush(V_OUTPUT_FILENAME);
    ELSE -- write in small pieces and carry on until finished
      V_START := 1;
      WHILE V_START < V_BLOB_LENGTH and V_BLOB_CHUNK_LENGTH > 0
      LOOP
        dbms_lob.read(V_BLOB,V_BLOB_CHUNK_LENGTH,V_START,V_READ_BLOB_IN_RAW);
        utl_file.put_raw(V_OUTPUT_FILENAME,V_READ_BLOB_IN_RAW);
        utl_file.fflush(V_OUTPUT_FILENAME);
        -- set the start position for the next chunk to read
        V_START := V_START + V_BLOB_CHUNK_LENGTH;
        -- set the end position if less than 32000 bytes (could be reading the last line)
        V_BLOB_LENGTH := V_BLOB_LENGTH - V_BLOB_CHUNK_LENGTH;
        IF V_BLOB_LENGTH < 32000 THEN
           V_BLOB_CHUNK_LENGTH := V_BLOB_LENGTH;
        END IF;
     END LOOP;
    END IF;
    utl_file.fclose(V_OUTPUT_FILENAME);
    dbms_output.put_line( P_RESULT );
    EXCEPTION
     wheN OTHERS THEN
       dbms_output.put_line (SQLERRM );
       P_RESULT := SQLERRM ;
END;
/

SQL> EXECUTE CREATE_CSV_USING_BLOB(1001, 'UNIX_CSV_OUTPUT_DIRECTORY' );

Global / Package based cursors - pl/sql

2011-02-25T14:30:00.000-08:00

This type of cursors are written to facilitate other languages to use oracle table in their queries. Widely used by Java develpers. Another advantage of writing cusrors at package level is that they can be used by any other procedures / functions and save from rewriting most commonly used cursors repeatedly.

To write a global / package level cursor we may need at least folloiwng 2 steps.

1. Defind a global cursor in package specs

create or replace package read_cursor_pkg is
    cursor c_read_employees is
    select * from employees;
    read_employees c_read_employees%ROWTYPE;
    procedure run_query;
end;
/

2. Use package body to open and fetch the cursor

create or replace package body read_cursor_pkg is
    procedure run_query is
    begin
        open read_cursor_pkg.c_read_employees ;
        loop
            fetch read_cursor_pkg.c_read_employees into read_cursor_pkg.read_employees;
            exit when read_cursor_pkg.c_read_employees%NOTFOUND;
            DBMS_OUTPUT.put_line(
                                 read_cursor_pkg.read_employees.EMPLOYEE_ID ||' - ' ||
                                 read_cursor_pkg.read_employees.FIRST_NAME ||' - ' ||
                                 read_cursor_pkg.read_employees.first_Name
                                );
        end loop;
        close read_cursor_pkg.c_read_employees;
     end;
end;
/

SQL>set serveroutput on
SQL>execute read_cursor_pkg.run_query ;
Package body created.
SQL>
SQL> set serveroutput on
SQL> execute read_cursor_pkg.run_query ;
198 - Donald - Donald
199 - Douglas - Douglas
200 - Jennifer - Jennifer
201 - Michael - Michael
202 - Pat - Pat
203 - Susan - Susan
204 - Hermann - Hermann
205 - Shelley - Shelley
206 - William - William
100 - Steven - Steven
101 - Neena - Neena
102 - Lex - Lex
103 - Alexander - Alexander

PL/SQL procedure successfully completed.
SQL>

How to read CSV file with commas inside / within quotation marks

2010-12-15T12:53:00.000-08:00

Reading a CSV file becomes more complicated when it includes quoation marks with combination of commas inside a column. To overcome that issues we can use string shunting method which realy works in certain cases.

Following excercise will help you to extract a CSV file by shunting every data item after a read from the string. You must know which columns are provided with quotation makrs.

Step 1: Create a CSV file

create a csv file using any text editor and it should be saved with extension like filename.csv
copy and paste following text in the text file and save it.

1233222, JACK, "123 UNKNOWN STREET, UNKNOWN TOWN", 12-DEC-2020, "THE CUSTOMER IS TAGGED AS VIP CUSTOMER", 200000000
1233222, TOM, "321 UNKNOWN STREET, UNKNOWN TOWN", 12-MAR-2010, "", 7000
1233222, JERRY, "551 UNKNOWN STREET, UNKNOWN TOWN", 12-OCT-2009, "THE ACCOUNT HOLDER MAY APPLY FOR LOAN, AS THE CUSTOMER IS AN INVESTOR", 150000

Step 2: Create a database directory

C:\ mkdir csvfiles
C:\SQLPLUS / AS SYSDBA
SQL> DROP DIRECTORY CSV_FILES;
SQL> CREATE DIRECTORY CSV_FILES AS 'C:\csvfiles';
SQL> GRANT read, write ON DIRECTORY CSV_FILES TO HR ;

Step 3: Create and compile following procedure in HR user

SQL> CONNECT HR/HR@ORCL
CREATE OR REPLACE PROCEDURE READ_WRITE_CSV (
                                              P_FILE_LOCATION IN VARCHAR2,
                                              P_FILENAME IN VARCHAR2,
                                              P_READ_WRITE_MODE IN VARCHAR2
                                             ) as
   V_EXTRACT_FILE      UTL_FILE.FILE_TYPE;
   V_EXTRACT_LOCATION VARCHAR2(300);
   V_READ_WRITE_MODE   VARCHAR2(1);
   V_LENGTH            number;
   V_FILE_LINE         varchar2(4000);
   account_id          varchar2(500);
   customer_name       varchar2(500);
   customer_address    varchar2(500);
   account_open_date   varchar2(500);
   comments            varchar2(500);
   opening_balance     varchar2(500);

BEGIN

   V_EXTRACT_FILE := UTL_FILE.fopen(P_FILE_LOCATION,P_FILENAME,P_READ_WRITE_MODE);
   --=================
   -- string format
   --=================
   -- account_id, customer name, customer address, account open date, comments, opening balance
   LOOP
          utl_file.get_line (V_EXTRACT_FILE,V_FILE_LINE);
          -- account id
          V_LENGTH := (INSTR(V_FILE_LINE , ',', 1, 1)) ;
          account_id := substr(V_FILE_LINE , 1, V_LENGTH -1) ;
          V_FILE_LINE := SUBSTR( V_FILE_LINE, V_LENGTH+1 );
          --customer name
          V_LENGTH := (INSTR(V_FILE_LINE , ',', 1, 1)) ;
          customer_name := substr(V_FILE_LINE , 1, V_LENGTH -1) ;
          V_FILE_LINE := SUBSTR( V_FILE_LINE, V_LENGTH+1 );
          --customer address
          V_LENGTH := (INSTR(V_FILE_LINE , '"', 1, 2))+1 ;
          customer_address := substr(V_FILE_LINE , 1, V_LENGTH -1) ;
          V_FILE_LINE := SUBSTR( V_FILE_LINE, V_LENGTH+1 );
          --account open date
          V_LENGTH := (INSTR(V_FILE_LINE , ',', 1, 1)) ;
          account_open_date := substr(V_FILE_LINE , 1, V_LENGTH -1) ;
          V_FILE_LINE := SUBSTR( V_FILE_LINE, V_LENGTH+1 );
          --comments
          V_LENGTH := (INSTR(V_FILE_LINE , '"', 1, 2))+1 ;
          comments := substr(V_FILE_LINE , 1, V_LENGTH -1) ;
          V_FILE_LINE := SUBSTR( V_FILE_LINE, V_LENGTH+1 );
          --openning balance
           opening_balance := substr(V_FILE_LINE , 1 ) ;

          dbms_output.put_line(customer_name     ||'-'||
                              customer_address ||'-'||
                              account_open_date ||'-'||
                              comments          ||'-'||
                              opening_balance
                             );
     end loop;
   EXCEPTION
    WHEN NO_DATA_FOUND THEN
    COMMIT;
    UTL_FILE.fclose(V_EXTRACT_FILE);
    dbms_output.put_line('OK') ;
    WHEN UTL_FILE.INVALID_PATH THEN
    dbms_output.put_line('NOT OK - INVALID PATH') ;
    WHEN OTHERS THEN
    UTL_FILE.fclose(V_EXTRACT_FILE);
    DBMS_OUTPUT.PUT_LINE(SQLERRM) ;
   END;
/
SQ>

Step 4: Execute procedure to read CSV file

SQL> SET SERVEROUTPUT ON
SQL> EXECUTE READ_WRITE_CSV ('CSV_FILES', 'customer.csv', 'R' ) ;

CREATING STANDBY (DISASTER RECOVERY) DATABASE WITHOUT USING DATAGUARD – by GMA

2010-11-04T14:47:00.000-07:00

CREATING STANDBY (DISASTER RECOVERY) DATABASE WITHOUT USING DATAGUARD – by Ghulam Abbasi

This article is focussed to provide basic understanding on how to create an standby (Disaster Recovery) database. In this article we are not using the dataguard therefore we have to go through few additional steps. The advantage of using dataguard is that you can create standby databases for primary databases already configured to use FRA (Flash Recovery Area). Also dataguard provide a great feature to automate your synchronization process and manage primary and standby databases with more control and flexibility.

My next article will cover the issue of how to synchronize archive log files automatically using some database tables and unix scripts.

The Standby database is always created in read only mode. Though you can switch it back to normal database at any time, bear in mind that standby database is always managed with statement ‘read only’. Suppose open the standby database we use following statement:

SQL> alter database open read only;

If you issued following statement instead, the database will switch to normal (read / write) mode and you cannot switch it back to standby mode again.

SQL> alter database open;

1. Configuring init.ora file (orcl primary database)

1.1 Remove FRA feature to allow recognised archive log file name (while not using dataguard).

Note: You don’t need this step if using dataguard for synchronizing archive log files

[oracle@orcl_primary]$ sqlplus / as sysdba

SQL> alter system reset db_recovery_file_dest_size scope=SPFILE sid='*';

System altered.

SQL> alter system reset db_recovery_file_dest scope=SPFILE sid='*';

System altered.

SQL> exit

[oracle@orcl_primary]$

1.2 Now set a new path where archive logs should be created by database.

- Create a new directory for archive logs

[oracle@orcl_primary]$ cd $ORACLE_HOME/db_1/

[oracle@orcl_primary]$ mkdir archivelogs_orcl

[oracle@orcl_primary]$ chmod 775 archivelogs_orcl

- Add/ Change Set the parameters log_archive_dest and log_archive_format in init.ora file.

[oracle@orcl_primary]$ sqlplus / as sysdba

SQL> ALTER SYSTEM SET log_archive_dest = '/app/oracle/product/10.2.0/db_1/archivelogs_orcl' scope=SPFILE sid='*' ;

SQL> ALTER SYSTEM SET log_archive_format='%t_%s_%r.arc' ;

SQL> ALTER SYSTEM SET

db_file_name_convert='/oradata/ORCL/','/oradata/ORCL_STDBY/';

- Now restart primary database to let new settings in init.ora take effect.

SQL> shutdown immediate;

SQL> startup;

SQL> create pfile from spfile;

File created.

NOTE: The new SPFILE / PFILE are normally created at following path in unix environment (10g only).

Please note that :

- spfile is created by default as ‘spfile’+’instance name’+.’ora’.

- pfile is created by default as ‘init’+’instance name’+.’ora’.

Remember, we need to move initORCL.ora form following location to standby server later in section 3.

$ORACLE_HOME/db_1/dbs/

2. Create backup for Standby Database (orcl primary database)

2.1 Create a separate directory to store standby backup. This backup set will be

moved to standby database.

- Create a new directory to store backup for standby database.

[oracle@orcl_primary]$ cd $ORACLE_HOME/db_1/

[oracle@orcl_primary]$ mkdir backup_standby

[oracle@orcl_primary]$ chmod 775 backup_standby

2.2 Take an standby backup of the primary database now.

- Logon to primary database target using RMAN and run following script to backup in standby mode.

[oracle@orcl_primary]$ rman

RMAN> connect target /

RMAN> sql 'alter system archive log current';

run {

allocate channel d01 device type disk format

'/app/oracle/product/10.2.0/db_1/backup_standby/%d_%t_%s_%U.dbf';

backup as compressed backupset database include current controlfile for

standby plus archivelog;

}

3. Move Required files to Standby Database

Please note that this article is tested on two databases (primary and standby) where:

- Same software version was installed on both servers (i.e. 10g).

- Similiar oracle home and directory structure was used on both servers.

- The Primary database was running in archive log mode.

- Primary host name is set to orcl_primary

- Standby host name is set to orcl_standby

- Instance name is used as ‘orcl’ for both the primary and standby databases.

3.1 Move init.ora (initORCL.ora) file from primary database to standby database.

Copy initORCL.ora from ORACLE_HOME/db_1/dbs/ directory on primary server to the same directory on sandby database (i-e. ORACLE_HOME/db_1/dbs/).

3.2 Move standby backup set from primary database to standby database.

Note: You need to create directory backup_standby under $ORACLE_HOME/dbs/ on standby server before copying backup files.

Copy all files from $ORACLE_HOME/db_1/backup_standby/ directory on primary server to the same directory on sandby server (i-e. $ORACLE_HOME/db_1/backup_standby/).

4. Create TNSNAMES entry on standby database pointing to primary server.

TNSNAMES.ORA file normally exists at $ORACLE_HOME/db_1/network/admin/

Edit this file and add following lines at the end of this file.

Note: Host name should be same as your primary host name. In this case the primary hostname is set as orcl_primary. Also you must be able to ping the primary database from standby database. You may use following command to make sure you primary database is accessible.

$ ping orcl_primary

[oracle@orcl_standby]$ vi ORACLE_HOME/db_1/network/admin/tnsname.ora

ORCL_PRIMARY =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = orcl_primary)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ORCL)

)

:wq

[oracle@orcl_standby]$

5. Restore standby database

Before restoring the standby database we need to delete any existing control files and datafiles from standby database locations.

5.1 Remove all datafiles and control files from all locations of standby database.

[oracle@orcl_standby]$ cd /oradata/datafile/

[oracle@orcl_standby]$ rm *

[oracle@orcl_standby]$ cd /oradata/controlfile/

[oracle@orcl_standby]$ rm *

5.2 Shutdown and start database with new spfile in nomount mode.

[oracle@orcl_standby]$ sqlplus / as sysdba

SQL> shutdown immediate;

SQL> startup nomount pfile='/datadr/app/oracle/product/10.2.0/db_1/dbs/initORCL.ora';

SQL> create spfile from pfile='/datadr/app/oracle/product/10.2.0/Db_1/dbs/initORCL.ora';

File created.

SQL> exit

5.3 Shutdown and start database with new spfile in nomount mode.

[oracle@orcl_standby]$ rman

RMAN>

connect target sys/password@ORCL;

connect auxiliary /;

run {

allocate channel d01 device type disk;

allocate auxiliary channel aux01 device type disk;

duplicate target database for standby

dorecover;

}

Note: There are three types of connection RMAN normally deals with. The database connection, the Catalogue DB and the Standby database connection. The RMAN statement ‘connect auxiliary /;’ is used to connect with standby database. This connection uses TNS connection entry (ORCL_PRIMARY) we just configured in step 4 of this article. This connectivity validates to control file sequence number, the log file sequence number and some other necessary checks.

RMAN> exit

[oracle@orcl_standby]$ sqlplus / as sysdba

SQL> shutdown;

SQL> Startup nomount;

SQL> alter database mount standby database;

SQL> alter database open read only;

SQL> alter system register;

SQL> exit

The database is now restored on DR (standby) server.

6. Applying archive logs to the standby database

6.1 Create new archive log files on primary database.

Here we will force the primary database to create some new archive logs.

Connect to primary database first:

[oracle@orcl_primary]$ sqlplus / as sysdba

SQL>alter system archive log current;

Note: Repeat this statement for few times to create more log files. As a result some new archive log files with be created at following location on primary database.

/app/oracle/product/10.2.0/db_1/archivelogs_orcl/

6.2 Move new archive log files to DR (Standby) database.

Copy the new arc files created as result of step 6.1 to the same location of DR (standby database).

/app/oracle/product/10.2.0/db_1/archivelogs_orcl/

6.3 Apply new archive log files to the DR (Standby) Database.

SQL> set autorecovery on;

SQL> recover standby database;

SQL> alter database open read only;

SQL> exit

Above recovery will fail if any active sessions are made to DR database. I would write following script to automate recovery. The script can be set in crontab to run every night.

#!/bin/sh

ORACLE_SID='orcl'

export ORACLE_SID

sqlplus / as sysdba << EOF

shutdown immediate;

startup nomount;

alter database mount standby database;

alter database open read only;

set autorecovery on;

recover standby database;

alter database open read only;

exit;

EOF

exit 0

Now both the databases are synchronised upto this point of time. Use following statement to check current log file sequence number on both databases. Both databases must show you the same last sequence number in following query.

[oracle@orcl_primary]$ sqlplus / as sysdba

SQL> select * from V_$LOGHIST order by SEQUENCE#;

[oracle@orcl_standby]$ sqlplus / as sysdba

SQL> select * from V_$LOGHIST order by SEQUENCE#;

GMA

Simple Backup and Recovery Example / Cloning a database (Oracle10g)

2010-10-09T12:04:00.000-07:00

I will discuss very simple but most crucial activity performed by every DBA as his / her routine. This scenario will be applied when your database is crashed and your physical machine is not available. Definitely you need to arrange same database on another machine with up-to-date database.

Following per-requisites must be met before going further with this exercise.

1. Live database will be called as primary database here.

2. Target database / server will be one where you want to recover your backed up database.

3. Primary database is installed with autobackup. The full backup is performed every night.

4. Same software version (Oracle 10g) is installed both the primary and target database / machine.

5. Same directory structure is available on both the primary and target database.

6. Database is running in archive log mode

Step 1: Backup the Live database

If your database is already configured to perform full backup every night then please proceed to next section.

Create a backup directory

$ ORACLE_HOME=/data/app/oracle/product/10.2.0/db_1;export ORACLE_HOME

$ ORACLE_BASE=/data/app/oracle/;export ORACLE_BASE

$ PATH=$ORACLE_HOME/bin:$PATH;export PATH

$ ORACLE_SID='orcl';export ORACLE_SID

$ cd /data/app/oracle/product/10.2.0/db_1/dbs/
$ mkdir backup
$ chmod 775 backup

$ rman
RMAN> connect target /
connected to target database: ORCL (DBID=1258382971)
RMAN> run {

         allocate channel d01 device type disk
         format '/data/app/oracle/product/10.2.0/db_1/dbs/backup/%d_%t_%s_%U.dbf';
         set limit channel d01 kbytes 1024000;
         backup as compressed backupset database include current controlfile plus archivelog;
     }
using target database control file instead of recovery catalog
allocated channel: d01
channel d01: sid=136 devtype=DISK

Starting backup at 08-OCT-10
current log archived
channel d01: starting compressed archive log backupset
channel d01: specifying archive log(s) in backup set
..
..
..
tag=TAG20101008T221834 comment=NONE
channel d01: backup set complete, elapsed time: 00:00:02
Finished backup at 08-OCT-10
released channel: d01

RMAN>exit

Note: Assume this backup was taken at 01:00 A.M,

Before restoring backup to target server we assume that our database was crashed in following patters

· Nightly full database backup taken at 01:00 A.M

· Users performed some transactions on database at 11:00 A.M

Note : To prove the recovery upto last committed transation, please do the following

on live database after step 1.

SQL> Create table test_backup_tr (transaction_dttm date);

SQL> Insert into test_backup_tr values(sysdate);

SQL> Commit;

· Database crashed at 11:35 A.M

Step 2: Recover database on another machine.

Once you got full backup of the database, you can restore it on any other machine which has suitable/ required hardware installed in hand. It is important that the target / recovery server has same version of oracle software installed alongwith the same directory structure (for simplicity).

The recovery process requires following files to be moved to Recovery Server

1. A good copy of control file.

Run following query to find location(s) of control file(s).

SQL> select name from v$controlfile;

2. Latest copy redo log files

Run following query to find location(s) of redo log files.

SQL> select member from v$logfile;

3. Archive logs.

Run following statement at sql prompt to find location(s) of Archive Logs.

SQL> show parameter log_archive_dest

log_archive_dest string /data/oradata/ORCLLIV_ARC

4. Full database backup files

Remember we did mention backup location as following.

/data/app/oracle/product/10.2.0/db_1/dbs/backup/

5. Latest copy of spfile

In 10g following is the default location of spfile

/data/app/oracle/product/10.2.0/db_1/dbs/

Please make sure you have good copies of all the above mentioned files to recover the target / recovery server. If any of those files are missing and you think you have physically lost them, learn to maintain them on multiple places (multiplexing) and find alternative ways to recover database with old backups. In case of any missing files, this process will not guarantee the last committed transactions to be recovered.

Step 3: Restore database on target / recovery server

Setup environment for the database instance

$ ORACLE_HOME=/data/app/oracle/product/10.2.0/db_1;export ORACLE_HOME

$ ORACLE_BASE=/data/app/oracle/;export ORACLE_BASE

$ PATH=$ORACLE_HOME/bin:$PATH;export PATH

$ ORACLE_SID='orcl';export ORACLE_SID

First shutdown recovery instance (if running) with abort option.

$ sqlplus / as sysdba

SQL> Shutdown abort;

SQL> exit

Now delete any existing datafile / controlfiles from database location

$ cd /data/oradata/controlfile/

$ rm *

$ cd /data/oradata/datafile/

$ rm *

Copy the recovery files taken from live database and move them to there same locations on recovery / target database.

Please note that directory structure may be different on your database, in this case please make necessary changes in the directories mentioned below

Move the new controlfile copy(s) to $ /data/oradata/controlfile/

Move the new redo logfiles to to $ /data/oradata/redo/

Move archive log files to /data/oradata/ORCLLIV_ARC

Move spfile to /data/app/oracle/product/10.2.0/db_1/dbs/

Move database backup piece files to /data/app/oracle/product/10.2.0/db_1/dbs/backup/

Connect to sqlplus to startup instance in mount position.

SQL> Startup mount;

SQL> exit

Finally, connect to rman target and recover the database.

$ rman
RMAN> connect target /
connected to target database: ORCL (DBID=1258382971)
RMAN> run {

restore database;

Recover database;

}

RMAN>exit
$
$ sqlplus / as sysdba
SQL>Alter database open;

Check the last committed transactions to verify restore operation.

SQL> select * from test_backup_tr;

DATE_IN
---------
09-OCT-10
09-OCT-10
09-OCT-10

SQL>