/*
|| Oracle Secured Backup Listing 3
||
|| Demonstrates Oracle Secured Backup (OSB) features, including:
|| - How to restore an Oracle 10gR2 database using RMAN and OSB
|| - How to back up operating system files with OSB
|| - How to restore operating system files with OSB
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various features of Oracle Secure
|| Backup, and it should be carefully proofread before executing it against 
|| any existing Oracle database to avoid any potential damage!
*/

/* 
|| Listing 3.1: Restoring an Oracle database using RMAN in concert with OSB
*/

#####
# First, remove all backup sets and image copies of the databases's
# datafiles from the database's Flash Recovery Area ...
####

rm -f /u01/app/oracle/flash_recovery_area/ORCL/datafiles/*

#####
# ... and then resynchronize the database's control file entries to
# reflect these deleted RMAN backups:
#####

RMAN> crosscheck copy;

released channel: ORA_SBT_TAPE_1
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
validation failed for datafile copy
datafile copy filename=/u01/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_system_31rf3f93_.dbf recid=2 stamp=619560426
validation failed for datafile copy
datafile copy filename=/u01/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_undotbs1_31rf717z_.dbf recid=5 stamp=619560466
validation failed for datafile copy
datafile copy filename=/u01/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_sysaux_31rf6272_.dbf recid=3 stamp=619560447
validation failed for datafile copy
datafile copy filename=/u01/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_31rf7401_.dbf recid=6 stamp=619560468
validation failed for datafile copy
datafile copy filename=/u01/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_example_31rf6sjk_.dbf recid=4 stamp=619560462
validation succeeded for control file copy
control file copy filename=/u01/app/oracle/flash_recovery_area/ORCL/controlfile/o1_mf_TAG20070410T200548_31rf756p_.ctl recid=7 stamp=619560469
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2007_04_10/o1_mf_1_5_31rf2rvm_.arc recid=12 stamp=619560329
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2007_04_10/o1_mf_1_6_31rf2v67_.arc recid=13 stamp=619560331
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2007_04_10/o1_mf_1_7_31rf34g0_.arc recid=17 stamp=619560340
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2007_04_10/o1_mf_1_8_31rf2jjb_.arc recid=11 stamp=619560323
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2007_04_10/o1_mf_1_9_31rf38yf_.arc recid=19 stamp=619560344
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2007_04_10/o1_mf_1_10_31rf3c7m_.arc recid=20 stamp=619560347
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2007_04_10/o1_mf_1_11_31rf36pw_.arc recid=18 stamp=619560342
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2007_04_10/o1_mf_1_12_31rf2zsy_.arc recid=15 stamp=619560336
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2007_04_10/o1_mf_1_13_31rf322d_.arc recid=16 stamp=619560338
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2007_04_10/o1_mf_1_14_31rf2xfx_.arc recid=14 stamp=619560333
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2007_04_10/o1_mf_1_15_31rf7ckv_.arc recid=22 stamp=619560475
Crosschecked 17 objects

RMAN> delete expired copy;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
specification does not match any archive log in the recovery catalog

List of Datafile Copies
Key     File S Completion Time     Ckp SCN    Ckp Time            Name
------- ---- - ------------------- ---------- ------------------- ----
2       1    X 04-10-2007 20:07:06 612120     04-10-2007 20:05:49 /u01/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_system_31rf3f93_.dbf
5       2    X 04-10-2007 20:07:46 612160     04-10-2007 20:07:44 /u01/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_undotbs1_31rf717z_.dbf
3       3    X 04-10-2007 20:07:27 612143     04-10-2007 20:07:12 /u01/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_sysaux_31rf6272_.dbf
6       4    X 04-10-2007 20:07:48 612162     04-10-2007 20:07:47 /u01/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_31rf7401_.dbf
4       5    X 04-10-2007 20:07:42 612157     04-10-2007 20:07:37 /u01/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_example_31rf6sjk_.dbf

Do you really want to delete the above objects (enter YES or NO)? yes

deleted datafile copy
datafile copy filename=/u01/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_system_31rf3f93_.dbf recid=2 stamp=619560426
deleted datafile copy
datafile copy filename=/u01/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_undotbs1_31rf717z_.dbf recid=5 stamp=619560466
deleted datafile copy
datafile copy filename=/u01/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_sysaux_31rf6272_.dbf recid=3 stamp=619560447
deleted datafile copy
datafile copy filename=/u01/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_31rf7401_.dbf recid=6 stamp=619560468
deleted datafile copy
datafile copy filename=/u01/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_example_31rf6sjk_.dbf recid=4 stamp=619560462

Deleted 5 EXPIRED objects

/* 
|| Listing 3.2: Simulating destruction of two datafiles
*/

#####
# Destroy two datafiles:
#####

rm -f /u01/app/oracle/oradata/ORCL/system01.dbf
rm -f /u01/app/oracle/oradata/ORCL/example01.dbf

#####
# Then, shut down the database:
#####

[oracle@10gBUR ~]$ ps -ef | grep ora_smon
oracle    4189     1  0 19:17 ?        00:00:03 ora_smon_orcl
oracle    7172  3546  0 20:00 pts/5    00:00:00 grep ora_smon
[oracle@10gBUR ~]$ kill -9 4189
[oracle@10gBUR ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue May 8 20:01:31 2007

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

Connected to an idle instance.

SQL> startup;
ORACLE instance started.

Total System Global Area  515899392 bytes
Fixed Size                  1220312 bytes
Variable Size             121635112 bytes
Database Buffers          390070272 bytes
Redo Buffers                2973696 bytes
Database mounted.

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'


SQL> select * from v$recover_file;

FILE#  ONLINE  ONLINE_ ERROR                               CHANGE#
------ ------- ------- ----------------------------------- --------
     1 ONLINE  ONLINE FILE NOT FOUND                        0
     5 OFFLINE OFFLINE FILE NOT FOUND                       0

SQL> exit

/* 
|| Listing 3.3: Restoring and recovering datafiles from an OSB backup
*/

#####
# Restart the database and bring it into MOUNT mode so that recovery
# can begin
#####

 [oracle@10gBUR ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue May 8 20:09:06 2007

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  515899392 bytes
Fixed Size                  1220312 bytes
Variable Size             121635112 bytes
Database Buffers          390070272 bytes
Redo Buffers                2973696 bytes
Database mounted.

#####
# Determine which RMAN backups would be required for restore operations
#####

RMAN> restore preview datafile 1;

Starting restore at 05-08-2007 20:18:36
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=154 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Oracle Secure Backup
using channel ORA_DISK_1


List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
2       Full    608.25M    SBT_TAPE    00:02:33     04-07-2007 22:53:35
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: FC_OSB_100_040707104945
        Handle: 05iejruo_1_1   Media: RMAN-DEFAULT-000002
  List of Datafiles in backup set 2
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1       Full 586317     04-07-2007 22:51:04 
/u01/app/oracle/oradata/orcl/system01.dbf
using channel ORA_SBT_TAPE_1


List of Archived Log Copies
Key     Thrd Seq     S Low Time            Name
------- ---- ------- - ------------------- ----
15      1    12      A 04-07-2007 21:34:48 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2007_04_10/o1_mf_1_12_31rf2zsy_.arc
16      1    13      A 04-07-2007 22:53:57 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2007_04_10/o1_mf_1_13_31rf322d_.arc
14      1    14      A 04-10-2007 19:52:32 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2007_04_10/o1_mf_1_14_31rf2xfx_.arc
22      1    15      A 04-10-2007 20:05:15 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2007_04_10/o1_mf_1_15_31rf7ckv_.arc

Media recovery start SCN is 586317
Recovery must be done beyond SCN 586317 to clear data files fuzziness
Finished restore at 05-08-2007 20:18:38

#####
# Next, restore datafiles 1 and 5 ...
#####

RMAN> restore datafile 1;

Starting restore at 05-08-2007 20:19:09
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1

channel ORA_SBT_TAPE_1: starting datafile backupset restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system01.dbf
channel ORA_SBT_TAPE_1: reading from backup piece 05iejruo_1_1
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=05iejruo_1_1 tag=FC_OSB_100_040707104945
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:01:26
Finished restore at 05-08-2007 20:20:36

RMAN> restore datafile 5;

Starting restore at 05-08-2007 20:21:22
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1

channel ORA_SBT_TAPE_1: starting datafile backupset restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to /u01/app/oracle/oradata/orcl/example01.dbf
channel ORA_SBT_TAPE_1: reading from backup piece 05iejruo_1_1
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=05iejruo_1_1 tag=FC_OSB_100_040707104945
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
Finished restore at 05-08-2007 20:22:08

#####
# ... recover both datafiles ...
#####

RMAN> recover datafile 1;

Starting recover at 05-08-2007 20:22:50
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 12 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2007_04_10/o1_mf_1_12_31rf2zsy_.arc
archive log thread 1 sequence 13 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2007_04_10/o1_mf_1_13_31rf322d_.arc
archive log thread 1 sequence 14 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2007_04_10/o1_mf_1_14_31rf2xfx_.arc
archive log thread 1 sequence 15 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2007_04_10/o1_mf_1_15_31rf7ckv_.arc
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2007_04_10/o1_mf_1_12_31rf2zsy_.arc thread=1 sequence=12
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2007_04_10/o1_mf_1_13_31rf322d_.arc thread=1 sequence=13
media recovery complete, elapsed time: 00:00:04
Finished recover at 05-08-2007 20:22:56

RMAN> recover datafile 5;

Starting recover at 05-08-2007 20:23:09
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 12 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2007_04_10/o1_mf_1_12_31rf2zsy_.arc
archive log thread 1 sequence 13 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2007_04_10/o1_mf_1_13_31rf322d_.arc
archive log thread 1 sequence 14 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2007_04_10/o1_mf_1_14_31rf2xfx_.arc
archive log thread 1 sequence 15 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2007_04_10/o1_mf_1_15_31rf7ckv_.arc
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2007_04_10/o1_mf_1_12_31rf2zsy_.arc thread=1 sequence=12
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2007_04_10/o1_mf_1_13_31rf322d_.arc thread=1 sequence=13
media recovery complete, elapsed time: 00:00:02
Finished recover at 05-08-2007 20:23:12

#####
# ... and open the database.
#####

SQL> alter database open;

Database altered.

/* 
|| Listing 3.4: Prepare for OSB operating system backup and restore operations
*/

#####
# SQL statements that create an external table using CREATE TABLE AS SELECT 
# and the ORACLE_DATAPUMP method
#####

/*
|| Script:  ExternalTablePopulationExamples.sql
|| Version: 10.2.0.1.0
|| Object:  Shows examples of populating external tables
|| Author:  Jim Czuprynski (Fujitsu Consulting)
*/

SET PAGESIZE 50
SET LINESIZE 100

-----
-- Create a DIRECTORY object for storing physical files resulting
-- from DataPump external table creation operations
-----
HOST rm -fR /home/oracle/extfiles
HOST mkdir /home/oracle/extfiles
HOST chmod 775 /home/oracle/extfiles
DROP DIRECTORY external_dir;
CREATE DIRECTORY external_dir 
  AS '/home/oracle/extfiles';

GRANT READ, WRITE ON DIRECTORY external_dir TO PUBLIC;

-----
-- Create an external table via CTAS (CREATE TABLE AS SELECT), 
-- populating it in parallel. Note that at least two external
-- files will be created.
-----
HOST rm -f /home/oracle/extfiles/*
DROP TABLE sh.xt_east_region_sales PURGE;
CREATE TABLE sh.xt_east_region_sales (
     prod_id
    ,cust_id
    ,promo_id
    ,amount_sold
    )
    ORGANIZATION EXTERNAL (
        TYPE ORACLE_DATAPUMP
        DEFAULT DIRECTORY external_dir
        LOCATION ('ext1.exp', 'ext2.exp', 'ext3.exp', 'ext4.exp')
    )
    PARALLEL
    AS
    SELECT
        S.cust_id
       ,S.prod_id
       ,S.promo_id
       ,SUM(S.amount_sold)
      FROM 
        sh.sales S
       ,sh.customers C     
     WHERE S.time_id BETWEEN TO_DATE('01/01/1996', 'mm/dd/yyyy') 
                         AND TO_DATE('12/31/1999', 'mm/dd/yyyy') 
       AND S.cust_id = C.cust_id
       AND C.cust_state_province IN (
        'IL', 'IN', 'MI', 'OH', 'WI', 
        'DE', 'MA', 'NH', 'NJ', 'NY',
        'PA', 'VA' ,'WV', 'MA', 'VT'
        )
     GROUP BY 
        S.cust_id
       ,S.prod_id
       ,S.promo_id
;    

#####
# Oracle DataPump Export operation shell script and parameter file
#####

#/bin/bash
######################################################################
# Shell Script: DataPumpExportExamples.sh
# Version: 10.2.0.1.0
# Purpose: Sets up DataPump environment and directories, then executes
#          two examples of DataPump export operations
# Author: Jim Czuprynski, Fujitsu Consulting
######################################################################

#####
# Create DataPump export directory and database directory object
#####
rm -fR /home/oracle/expfiles
mkdir /home/oracle/expfiles

sqlplus / as sysdba << EOF

SET ECHO ON

DROP DIRECTORY export_dir;
CREATE DIRECTORY export_dir AS '/home/oracle/expfiles';
GRANT READ, WRITE ON DIRECTORY export_dir TO PUBLIC;

exit;
EOF

#####
# Execute DataPump export operations
#####

expdp system/oracle parfile=SchemaExport.dpectl

################################################################
# DataPump Export Parameter File: SchemaExport.dpectl
# Version: 10.2.0.1.0
# Purpose: Creates a DataPump export of selected schemas from 
#          the "seed" database
# Author: Jim Czuprynski, Fujitsu Consulting
################################################################
JOB_NAME=SchemaExport
DIRECTORY=export_dir
DUMPFILE=export_dir:SchemaExport_%U.dmp
LOGFILE=export_dir:SchemaExport.explog
SCHEMAS=('HR','PM','SCOTT','SH')
FILESIZE=10M
CONTENT=DATA_ONLY

#####
# Results of DataPump Export operation. This creates approximately 45MB
# of data in the /home/oracle/expfiles directory.
#####

Export: Release 10.2.0.1.0 - Production on Saturday, 19 May, 2007 12:36:16

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SCHEMAEXPORT":  system/******** parfile=SchemaExport.dpectl
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 37.43 MB
. . exported "SH"."CUSTOMERS"                            9.850 MB   55500 rows
. . exported "SH"."SUPPLEMENTARY_DEMOGRAPHICS"           695.9 KB    4500 rows
. . exported "SH"."SALES":"SALES_Q4_2001"                2.257 MB   69749 rows
. . exported "SH"."SALES":"SALES_Q1_1999"                2.070 MB   64186 rows
. . exported "SH"."SALES":"SALES_Q3_2001"                2.129 MB   65769 rows
. . exported "SH"."SALES":"SALES_Q1_2000"                2.011 MB   62197 rows
. . exported "SH"."SALES":"SALES_Q1_2001"                1.964 MB   60608 rows
. . exported "SH"."SALES":"SALES_Q2_2001"                2.050 MB   63292 rows
. . exported "SH"."SALES":"SALES_Q3_1999"                2.166 MB   67138 rows
. . exported "SH"."SALES":"SALES_Q4_1999"                2.014 MB   62388 rows
. . exported "SH"."SALES":"SALES_Q2_2000"                1.801 MB   55515 rows
. . exported "SH"."SALES":"SALES_Q3_2000"                1.909 MB   58950 rows
. . exported "SH"."SALES":"SALES_Q4_1998"                1.581 MB   48874 rows
. . exported "SH"."SALES":"SALES_Q4_2000"                1.813 MB   55984 rows
. . exported "SH"."SALES":"SALES_Q2_1999"                1.753 MB   54233 rows
. . exported "SH"."SALES":"SALES_Q1_1998"                1.412 MB   43687 rows
. . exported "SH"."SALES":"SALES_Q3_1998"                1.633 MB   50515 rows
. . exported "PM"."ONLINE_MEDIA"                         39.24 KB       9 rows
. . exported "PM"."PRINT_MEDIA"                          188.8 KB       4 rows
. . exported "SH"."SALES":"SALES_Q2_1998"                1.159 MB   35758 rows
. . exported "SH"."FWEEK_PSCAT_SALES_MV"                 419.3 KB   11266 rows
. . exported "SH"."PROMOTIONS"                           57.85 KB     503 rows
. . exported "SH"."TIMES"                                377.2 KB    1826 rows
. . exported "SH"."COSTS":"COSTS_Q4_2001"                277.8 KB    9011 rows
. . exported "PM"."TEXTDOCS_NESTEDTAB"                   87.35 KB      12 rows
. . exported "SH"."COSTS":"COSTS_Q1_1999"                183.0 KB    5884 rows
. . exported "SH"."COSTS":"COSTS_Q1_2001"                227.3 KB    7328 rows
. . exported "SH"."COSTS":"COSTS_Q2_2001"                184.0 KB    5882 rows
. . exported "SH"."COSTS":"COSTS_Q3_2001"                233.9 KB    7545 rows
. . exported "SH"."COSTS":"COSTS_Q1_1998"                138.9 KB    4411 rows
. . exported "SH"."COSTS":"COSTS_Q1_2000"                120.0 KB    3772 rows
. . exported "SH"."COSTS":"COSTS_Q2_1998"                78.96 KB    2397 rows
. . exported "SH"."COSTS":"COSTS_Q2_1999"                132.0 KB    4179 rows
. . exported "SH"."COSTS":"COSTS_Q2_2000"                118.4 KB    3715 rows
. . exported "SH"."COSTS":"COSTS_Q3_1998"                130.5 KB    4129 rows
. . exported "SH"."COSTS":"COSTS_Q3_1999"                136.8 KB    4336 rows
. . exported "SH"."COSTS":"COSTS_Q3_2000"                150.9 KB    4798 rows
. . exported "SH"."COSTS":"COSTS_Q4_1998"                144.1 KB    4577 rows
. . exported "SH"."COSTS":"COSTS_Q4_1999"                158.5 KB    5060 rows
. . exported "SH"."COSTS":"COSTS_Q4_2000"                159.6 KB    5088 rows
. . exported "HR"."COUNTRIES"                            6.080 KB     433 rows
. . exported "HR"."DEPARTMENTS"                          6.632 KB      27 rows
. . exported "HR"."EMPLOYEES"                            15.76 KB     107 rows
. . exported "HR"."JOBS"                                 6.609 KB      19 rows
. . exported "HR"."JOB_HISTORY"                          6.585 KB      10 rows
. . exported "HR"."LOCATIONS"                            7.710 KB      23 rows
. . exported "HR"."REGIONS"                              5.289 KB       4 rows
. . exported "SCOTT"."DEPT"                              5.656 KB       4 rows
. . exported "SCOTT"."EMP"                               7.820 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          5.585 KB       5 rows
. . exported "SH"."CAL_MONTH_SALES_MV"                   6.125 KB      48 rows
. . exported "SH"."CHANNELS"                             6.687 KB       5 rows
. . exported "SH"."COUNTRIES"                            9.265 KB      23 rows
. . exported "SH"."PRODUCTS"                             24.09 KB      72 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
. . exported "SH"."COSTS":"COSTS_1995"                       0 KB       0 rows
. . exported "SH"."COSTS":"COSTS_1996"                       0 KB       0 rows
. . exported "SH"."COSTS":"COSTS_H1_1997"                    0 KB       0 rows
. . exported "SH"."COSTS":"COSTS_H2_1997"                    0 KB       0 rows
. . exported "SH"."COSTS":"COSTS_Q1_2002"                    0 KB       0 rows
. . exported "SH"."COSTS":"COSTS_Q1_2003"                    0 KB       0 rows
. . exported "SH"."COSTS":"COSTS_Q2_2002"                    0 KB       0 rows
. . exported "SH"."COSTS":"COSTS_Q2_2003"                    0 KB       0 rows
. . exported "SH"."COSTS":"COSTS_Q3_2002"                    0 KB       0 rows
. . exported "SH"."COSTS":"COSTS_Q3_2003"                    0 KB       0 rows
. . exported "SH"."COSTS":"COSTS_Q4_2002"                    0 KB       0 rows
. . exported "SH"."COSTS":"COSTS_Q4_2003"                    0 KB       0 rows
. . exported "SH"."MVIEW$_EXCEPTIONS"                        0 KB       0 rows
. . exported "SH"."SALES":"SALES_1995"                       0 KB       0 rows
. . exported "SH"."SALES":"SALES_1996"                       0 KB       0 rows
. . exported "SH"."SALES":"SALES_H1_1997"                    0 KB       0 rows
. . exported "SH"."SALES":"SALES_H2_1997"                    0 KB       0 rows
. . exported "SH"."SALES":"SALES_Q1_2002"                    0 KB       0 rows
. . exported "SH"."SALES":"SALES_Q1_2003"                    0 KB       0 rows
. . exported "SH"."SALES":"SALES_Q2_2002"                    0 KB       0 rows
. . exported "SH"."SALES":"SALES_Q2_2003"                    0 KB       0 rows
. . exported "SH"."SALES":"SALES_Q3_2002"                    0 KB       0 rows
. . exported "SH"."SALES":"SALES_Q3_2003"                    0 KB       0 rows
. . exported "SH"."SALES":"SALES_Q4_2002"                    0 KB       0 rows
. . exported "SH"."SALES":"SALES_Q4_2003"                    0 KB       0 rows
Master table "SYSTEM"."SCHEMAEXPORT" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SCHEMAEXPORT is:
  /home/oracle/expfiles/SchemaExport_01.dmp
  /home/oracle/expfiles/SchemaExport_02.dmp
  /home/oracle/expfiles/SchemaExport_03.dmp
  /home/oracle/expfiles/SchemaExport_04.dmp
  /home/oracle/expfiles/SchemaExport_05.dmp
Job "SYSTEM"."SCHEMAEXPORT" successfully completed at 12:37:11

#####
# Listing of files in directories that are potential OSB backup targets:
#####

[oracle@10gBUR ~]$ ls -laR /home/oracle/ex*
/home/oracle/expfiles:
total 45464
drwxr-xr-x   2 oracle dba     4096 May 19 12:37 .
drwx------  24 oracle dba     4096 May 19 12:36 ..
-rw-r-----   1 oracle dba 10485760 May 19 12:36 SchemaExport_01.dmp
-rw-r-----   1 oracle dba 10485760 May 19 12:36 SchemaExport_02.dmp
-rw-r-----   1 oracle dba 10485760 May 19 12:36 SchemaExport_03.dmp
-rw-r-----   1 oracle dba 10485760 May 19 12:37 SchemaExport_04.dmp
-rw-r-----   1 oracle dba  4485120 May 19 12:37 SchemaExport_05.dmp
-rw-r--r--   1 oracle dba     7294 May 19 12:37 SchemaExport.explog

/home/oracle/extfiles:
total 396
drwxrwxr-x   2 oracle dba   4096 May 19 12:23 .
drwx------  24 oracle dba   4096 May 19 12:36 ..
-rw-r-----   1 oracle dba 172032 May 19 12:23 ext1.exp
-rw-r-----   1 oracle dba 176128 May 19 12:23 ext2.exp
-rw-r--r--   1 oracle dba     41 May 19 12:23 XT_EAST_REGION_SALES_22521.log
-rw-r--r--   1 oracle dba     41 May 19 12:23 XT_EAST_REGION_SALES_22536.log
-rw-r--r--   1 oracle dba     41 May 19 12:23 XT_EAST_REGION_SALES_22538.log

/* 
|| Listing 3.5: Viewing the results of OSB backup and restore operations
*/

#####
# Show which OSB jobs have completed, either successfully or unsuccessfully:
#####

ob> lsjob -c
Job ID           Sched time  Contents                       State
---------------- ----------- ------------------------------ ---------------------------------------
admin/1          05/18.17:59 dataset exportfiles.ds         completed successfully at 2007/05/18.17:59
admin/1.1        05/18.17:59 backup 10gBUR                  completed successfully at 2007/05/18.17:59
admin/2          05/18.18:00 dataset exportfiles.ds         completed successfully at 2007/05/18.18:00
admin/2.1        05/18.18:00 backup 10gBUR                  completed successfully at 2007/05/18.18:00
admin/3          none        dataset exportfiles.ds         completed successfully at 2007/05/18.18:13
admin/3.1        none        backup 10gBUR                  completed successfully at 2007/05/18.18:13
admin/4          05/18.18:17 dataset exportfiles.ds         completed successfully at 2007/05/18.18:17
admin/4.1        05/18.18:17 backup 10gBUR                  completed successfully at 2007/05/18.18:17
admin/5          05/19.08:59 dataset exportfiles.ds         completed successfully at 2007/05/19.08:59
admin/5.1        05/19.08:59 backup 10gBUR                  completed successfully at 2007/05/19.08:59
admin/6          none        dataset exportfiles.ds         completed successfully at 2007/05/19.13:00
admin/6.1        none        backup 10gBUR                  completed successfully at 2007/05/19.13:00
oracle/1         none        database orcl (dbid=1143891885) completed successfully at 2007/05/18.17:53
oracle/1.1       none        datafile backup                completed successfully at 2007/05/18.17:52
oracle/1.2       none        datafile backup                completed successfully at 2007/05/18.17:49
oracle/1.3       none        datafile backup                completed successfully at 2007/05/18.17:52
oracle/1.4       none        datafile backup                completed successfully at 2007/05/18.17:53
oracle/2         none        database orcl (dbid=1143891885) completed successfully at 2007/05/18.17:55
oracle/2.1       none        archivelog backup              completed successfully at 2007/05/18.17:54
oracle/2.2       none        datafile backup                completed successfully at 2007/05/18.17:55

#####
# List the corresponding transcript for a specific OSB job:
#####

ob> catxcr admin/6.1
2007/05/19.13:00:39 ______________________________________________________________________
2007/05/19.13:00:39
2007/05/19.13:00:39         Transcript for job admin/6.1 running on 10gBUR
2007/05/19.13:00:39
Backup started on Sat May 19 2007 at 13:00:42
Volume label:
    Volume tag:         30569afce7e9102a2ae000c291eae22
    Volume ID:          VOL000001
    Volume sequence:    1
    Volume set owner:   root
    Volume set created: Fri May 18 17:59:30 2007

Archive label:
    File number:        6
    File section:       1
    Owner:              root
    Client host:        10gBUR
    Backup level:       0
    S/w compression:    no
    Archive created:    Sat May 19 13:00:42 2007


Dumping all files in /home/oracle/extfiles
Dumping all files in /home/oracle/expfiles

Backup complete on Sat May 19 2007 at 13:00:47

Backup statistics:
status 0
devices vt
devices 1
volumes VOL000001
voltags 30569afce7e9102a2ae000c291eae22
file 6
host 10gBUR
start_time  Sat May 19 2007 at 13:00:42 (1179597642)
end_time    Sat May 19 2007 at 13:00:47 (1179597647)
backup_time Sat May 19 2007 at 13:00:42 (1179597642)
entries_scanned 13
kbytes_scanned 45820
entries_excluded 3
entries_skipped 0
mount_points_skipped 0
files 8
directories 2
hardlinks 0
symlinks 0
sparse_files 0
filesys_errors 0
unknown_type 0
file_kbytes 45804
dev_kbytes 45704
dev_iosecs 5
dev_iorate 9.4 MB/S
wrt_iosecs 3
wrt_iorate 15.6 MB/S
physical_blks_written 0
write_errors 0
physical_blks_read 0
read_errors 0
error_rate 0%
path /home/oracle/extfiles completed, status 0
path /home/oracle/expfiles completed, status 0

/* 
|| Listing 3.6: Restoring OS files from OSB backups
*/

#####
# Result of successful restoration. Note the (expected!) omission of the .log
# files in the /home/oracle/extfiles directory.
#####

[oracle@10gBUR ~]$ ls -laR /home/oracle/ex*
/home/oracle/expfiles:
total 45464
drwxr-xr-x   2 oracle dba     4096 May 19 12:37 .
drwx------  24 oracle dba     4096 May 19 12:36 ..
-rw-r-----   1 oracle dba 10485760 May 19 12:36 SchemaExport_01.dmp
-rw-r-----   1 oracle dba 10485760 May 19 12:36 SchemaExport_02.dmp
-rw-r-----   1 oracle dba 10485760 May 19 12:36 SchemaExport_03.dmp
-rw-r-----   1 oracle dba 10485760 May 19 12:37 SchemaExport_04.dmp
-rw-r-----   1 oracle dba  4485120 May 19 12:37 SchemaExport_05.dmp
-rw-r--r--   1 oracle dba     7294 May 19 12:37 SchemaExport.explog

/home/oracle/extfiles:
total 372
drwxrwxr-x   2 oracle dba   4096 May 19 12:23 .
drwx------  24 oracle dba   4096 May 19 12:36 ..
-rw-r-----   1 oracle dba 172032 May 19 12:23 ext1.exp
-rw-r-----   1 oracle dba 176128 May 19 12:23 ext2.exp