/*
|| 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