Oracle Database Administration Fundamentals II by akm33296

VIEWS: 374 PAGES: 15

									                                                                 Cleaning Up Failed Online Backups _________________ 6
                                                                 Backing Up the Initialization Parameter File __________ 6
Oracle Database Administration
                                                                 DBVERIFY Utility________________________________ 7
Fundamentals II (Note Sheets) v. 1.0
On Oracle 9i
                                                               User-Managed Complete Recovery __________ 7
By: Ahmed Baraka                                                 User-Managed Complete Recovery in NOARCHIVEMODE_ 7
                                                                 User-Managed Complete Recovery in ARCHIVEMODE ___ 7
Oracle Net Architecture and Configuration ____ 3                 Recovery of a Datafile Without a Backup _____________ 7
   Oracle Net Configuration Files______________________ 3        Read-Only Tablespace Recovery ___________________ 7
   Connection Methods _____________________________ 3            Determining Which Files Need Recovery _____________ 7
   Implementing Spawn and Bequeath Connections in                Using Archived Redo Log Files During Recovery _______ 7
   Windows ______________________________________ 3
                                                                 Restoring Data Files to Different Locations ___________ 7
   The listener.ora File Parameters ____________________ 3
   Dynamic Service Registration ______________________ 3       User-Managed Incomplete Recovery ________ 7
   Configure the Listener for Oracle9i JVM: HTTP _________ 3     Cancel-Based Recovery __________________________ 7
   Listener Control Utility (LSNRCTL) __________________ 3       Time-Based Recovery ___________________________ 8
   Naming Methods ________________________________ 4             Change-Based Recovery _________________________ 8
   Host Naming Method_____________________________ 4             Using a Backup Control File During Recovery _________ 8
   Local Naming __________________________________ 4

                                                               Oracle Recovery Manager Configuration ______ 8
Configuration of the Oracle Shared Server ____ 4                 Connecting to RMAN without a Recovery Catalog ______ 8
   The SGA and PGA in the Shared Server ______________ 4         RMAN Command Line Arguments __________________ 8
   Configuring Oracle Shared Server___________________ 4         Configuring the RMAN Environment _________________ 8
   Setting DISPATCHERS Parameter ___________________ 4           RMAN Channel Commands _______________________ 8
   Other Shared Server Configuration Parameters ________ 5       Duration in days of RMAN information in control file ____ 8
   To Verify Shared-Server Setup _____________________ 5
   Dynamic Views of Shared Server ___________________ 5        RMAN Backups _________________________ 8
   Using a Dedicated Server with Oracle Shared Server ____ 5     Backup Piece Size ______________________________ 8
                                                                 Backup Command ______________________________ 8
Configuring the Database Archiving Mode ____ 5                   Control File Backups_____________________________ 9
   Setting ARCHIVELOG mode _______________________ 5             Backing Up the Server Parameter File _______________ 9
   View ARCHIVELOG mode of a Database ______________ 5           Backing Up Archived Redo Logs ____________________ 9
   Setting Automatic Archiving _______________________ 5         Multiplexed Backup Sets _________________________ 9
   Manually Archiving Logs __________________________ 5          Parallelization of Backup Sets _____________________ 9
   Specifying Archive Destinations ____________________ 6        Duplexed Backup Sets __________________________ 10
   LOG_ARCHIVE_MIN_SUCCEED_DEST Parameter _______ 6              Image Copies _________________________________ 10
   Formatting ArchiveLog Filenames ___________________ 6         Image Copy Parallelization_______________________ 10
   Multiple ARCn Processes __________________________ 6          Copying the Whole Database _____________________ 10
   Dynamic Performance Views _______________________ 6           Incremental Backups ___________________________ 10
                                                                 Backup in NOARCHIVELOG Mode __________________ 10
User-Managed Backup ___________________ 6
                                                                 Tags for Backups and Image Copies _______________ 10
   Cold Backup ___________________________________ 6
                                                                 RMAN Dynamic Views __________________________ 10
   Hot or Online Backup ____________________________ 6
                                                                 Monitoring RMAN Backups _______________________ 11
   Backing up Control Files __________________________ 6

Page 1                                                             Oracle Database Administration Fundamentals II (Note Sheets)
  Detecting Corruption ____________________________ 11          SQL*Loader Parallel Load Methods ________________ 15

RMAN Complete Recovery________________ 11                     Copyright and Usage Terms
  Recover a Database in ARCHIVELOG Mode___________ 11         • Anyone is authorized to copy this document to any
                                                                means of storage and present it in any format to any
  Restore Datafiles to a New Location ________________ 11       individual or organization for non-commercial purpose
  Recover a Tablespace ___________________________ 11
                                                              • No individual or organization is authorized to use this
  Relocate a Tablespace___________________________ 11           document for commercial purpose without a written
                                                                permission from the author.

RMAN Incomplete Recovery ______________ 11                    • There is no warranty of any type for the code or
                                                                information presented in this document. The editor is
  Incomplete Recovery of a Database ________________ 11         not responsible for any loses or damage resulted from
                                                                using the information or executing the code in this
RMAN Maintenance _____________________ 11
                                                              • If any one wishes to correct a statement or a typing
  Cross Checking Backups and Copies ________________ 11         error or add a new piece of information, please send
  Deleting Backups and Copies _____________________ 12          the request to . If the
                                                                modification is acceptable, it will be added to the
  Changing the Availability of RMAN Backups and Copies _ 12     document, the version of the document will be
                                                                incremented and the modifier name will be listed in
  Exempting a Backup or Copy from the Retention Policy _ 12
                                                                the version history list.
  The CATALOG Command_________________________ 12
  The CHANGE … UNCATALOG Command _____________ 12
                                                              Version History

                                                               Version   Individual            Date      Updates
Recovery Catalog Creation and Maintenance _ 12
  Creating Recovery Catalog _______________________ 12           1.0     Ahmed Baraka          Sept,     Initial document.
  To Update The Recovery Catalog Manually ___________ 13                                       2003

  Resynchronization of the Recovery Catalog __________ 13
  Resetting a Database Incarnation __________________ 13
  RMAN Catalog Reporting _________________________ 13
  Stored Scripts _________________________________ 13

Export and Import Utilities _______________ 13
  Requirements _________________________________ 13
  Invoking Export________________________________ 13
  Export Modes _________________________________ 13
  Direct Path mode ______________________________ 14
  Invoking Import _______________________________ 14
  Import Modes _________________________________ 14
  Invoking Import as SYSDBA ______________________ 14
  Import Process Sequence ________________________ 14
  Manually Creating Tables Before Importing Data ______ 14
  Using Parameter File ____________________________ 14

Using SQL*Loader______________________ 14
  Direct-Load Insert Operations_____________________ 14
  Issuing SQL*Loader ____________________________ 14
  Control File ___________________________________ 14
  Conventional, Direct-Path and External-Path Loads ____ 15

Page 2                                                             Oracle Database Administration Fundamentals II (Note Sheets)
                                                                       TRACE_DIRECTORY_lstname : directory of the trace file

Oracle Net Architecture and Configuration                              TRACE_FILE_lstname : filename of the trace file
                                                                       SAVE_CONFIG_ON_STOP_lstname : whether changes
                                                                       made by LSNRCTL SET command are made permanent
Oracle Net Configuration Files                                         (FLASE)
Following are the configuration files:
 o listener.ora
    o tnsnames.ora                                                Dynamic Service Registration
    o names.ora                                                   Configure Registration
    o sqlnet.ora                                                  The following initialization parameters must be configured:
    o ldap.ora                                                        o SERVICE_NAMES: specifies one or more names for the
                                                                        database service to which this instance connects.
Generally Oracle Net searches for those files in the following
                                                                      o INSTANCE_NAME: the instance name
1. The directory specified by the TNS_ADMIN environment           Examples
2. The ORACLE_HOME\network\admin directory                          INSTANCE_NAME=salesdb

                                                                  Registering Information with the Listener
Connection Methods                                                •    By default, PMON registers with a local listener on the
                                                                       server on the default local address of TCP/IP, port 1521
•    Spawn and Bequeath : The listener passes or bequeaths
     the connection to a spawned process. This method is used     •    PMON can register with a non default listener if:
     in a dedicated server configuration only.                          o LOCAL_LISTENER initialization parameter is defined
•    Direct Hand-Off Connections: The listener will hand off            o LISTENERS attribute of the DISPATCHERS initialization
     a connection to a dispatcher when an Oracle Shared Server            parameter is defined for Oracle Shared Server
     is used. This method is not possible with dedicated server
•    Redirected Session: A connection may be redirected by
                                                                  Configure the Listener for Oracle9i JVM: HTTP
     the listener to a dispatcher if a Shared Server is used.     •    If both the listener and database are Oracle9i, configuration
                                                                       occurs dynamically during service registration.
                                                                  •    If the database is Oracle8i or earlier, configure listening
Implementing Spawn and Bequeath
                                                                       addresses statically using the following procedure, even
Connections in Windows                                                 if an Oracle9i listener is used.
Set USE_SHARED_SOCKET environment variable (in the                    1. Configure listener with TCP/IP or TCP/IP with SSL
registry) to TRUE to allow multiple connections to use a single          protocol
socket. When the value is FALSE (default), bequeath
connections are not possible so a redirect session is initiated       2. Enter the host name of the database in the Host field.
                                                                      3. Enter port 2481 in the Port field if the chosen protocol
                                                                         is TCP/IP, or enter port 2482 in the Port field if the
                                                                         chosen protocol is TCP/IP with SSL.
The listener.ora File Parameters
                                                                      4. Dedicate the address for JServer connections.
                   (Host= stc-sun02)                                        (DESCRIPTION=
                   (Port= 1521)))                                      (ADDRESS=(PROTOCOL=tcp)(HOST=server1)(PORT=2481)
    SID_LIST_LISTENER =                                                )
     (SID_LIST =                                                         (PROTOCOL_STACK= (PRESENTATION=GIOP)
       (SID_DESC =                                                         (SESSION=raw))))
         (ORACLE_HOME= /home/oracle)
         (GLOBAL_DBNAME =
         (SID_NAME = ORCL)))                                      Listener Control Utility (LSNRCTL)
    Other Parameters:
                                                                  Invoke the utility
    LOGGING_lstname : turn logging on and off (ON)
    LOG_DIRECTORY_lstname : directory of the log file
    LOG_FILE_lstname : filename of the log file                   Starting a listener
                                                                    START listener_name
    TRACE_LEVEL_lstname: Turns tracing off or on to a specific
    level. Possible values: Off, User, Admin, Support

Page 3                                                                   Oracle Database Administration Fundamentals II (Note Sheets)
Stopping a listener                                                2. Configure HOSTNAME as the First Naming Method
  STOP listener_name                                                 In the sqlnet.ora file:
                                                                     NAMES.DIRECTORY_PATH=(hostname, tnsnames)
Additional Commands:                                               3. Set Up Host Name Resolution Environment
    o RELOAD: shuts down everything except listener addresses        The service name must be resolved through an IP
      and rereads the listener.ora file.                             address translation mechanism, such as DNS, NIS, or a
                                                                     centrally-maintained TCP/IP host file.
    o CHANGE_PASSWORD: dynamically changes the encrypted
      password of a listener.                                      4. Connect to the Database
                                                                   CONNECT username/
    o EXIT quits the LSNRCTL utility.

    o QUIT same as EXIT
                                                                   Local Naming
    o HELP display list of the utility commands
                                                                   1. Configure tnsnames.ora File
    o SAVE_CONFIG creates a backup of your listener                SAMPLE =
      configuration file (called listener.bak) and updates the      (DESCRIPTION =
      listener.ora file itself to reflect any changes                (ADDRESS_LIST =
                                                                      (ADDRESS =
    o SERVICES provides detailed information about services            (PROTOCOL=TCP)(HOST=sun1)(PORT = 8461)))
      and instances registered and the service handlers              (CONNECT_DATA =
      allocated to each instance.                                        (SERVICE_NAME = U461)))
    o SET <par> sets a listener parameter. SET modifiers are:      2. Configure The sqlnet.ora File
         CURRENT_LISTENER                                          NAMES.DIRECTORY_PATH= (TNSNAMES)
                                                                   Configuration of the Oracle Shared Server
         TRC_FILE                                                  The SGA and PGA in the Shared Server
                                                                   Cursor state and user session data will be stored in the
    o SHOW <par> lists the value of a listener parameter.          large pool area, if configured. Otherwise they will be stored
                                                                   in SGA, specifically in shared pool.
    o STATUS provides basic status information about a
      listener, including a summary of listener configuration
      settings, the listening protocol addresses, and a summary
      of services registered with the listener.                    Configuring Oracle Shared Server
                                                                   •    Required initialization parameters:
                                                                       o DISPATCHERS
Naming Methods
                                                                       o SHARED_SERVERS
•    Host naming
•    Local naming                                                  •    Optional initialization parameters:
•    Directory naming                                                  o MAX_DISPATCHERS
•    Oracle Names                                                      o MAX_SHARED_SERVERS
•    External naming                                                   o CIRCUITS
                                                                       o SHARED_SERVER_SESSIONS

                                                                   Note: The parameters have reasonable defaults. On many
Host Naming Method                                                 systems, the only parameter that should be configured is
•    Oracle Net Services software installed on client and server
•    Client and server are connecting using TCP/IP protocol        Setting DISPATCHERS Parameter
•    An IP address translation mechanism, such as Domain
     Name System (DNS) or a centrally maintained TCP/IP            •    Parameter type: String (Specify as a quoted string)
     hosts file, to resolve names.                                 •    Parameter class: Dynamic (use ALTER SYSTEM to modify)
•    No advanced features such as Oracle Connection Manager        •    Default value: NULL
     or security options are used
                                                                   Parameter Attributes:
To configure the host naming method:
                                                                       o PROTOCOL: the network protocol
1. Configure the Listener
    Register the database dynamically or statically.                   o ADDRESS : network protocol address of the endpoint
                                                                         on which the dispatchers listen

Page 4                                                                    Oracle Database Administration Fundamentals II (Note Sheets)
    o DESCRIPTION: the network description of the endpoint on
                                                                     To Verify Shared-Server Setup
      which the dispatchers listen, including the network
      protocol address For example:                                  Verify that the dispatcher has registered with the listener
      (DESCRIPTION=(ADDRESS=...))                                    when the instance was started by issuing:
    o DISPATCHERS:The initial number of dispatchers to start         lsnrctl services
      (default is 1). Use the following formula: Initial number of
      dispatchers = CEIL (Average number of concurrent
      sessions/Connections per dispatcher)                           Dynamic Views of Shared Server
    o SESSIONS: The maximum number of network sessions               •    V$CIRCUIT
      for each dispatcher. Mostly the default is16K
                                                                     •    V$SHARED_SERVER
    o CONNECTIONS: the maximum number of network
      connections to allow for each dispatcher. The default is       •    V$DISPATCHER
      1024 for Sun Solaris and Windows NT.                           •    V$SHARED_SERVER_MONITOR
                                                                     •    V$QUEUE
                                                                     •    V$SESSION

Note: All attributes can be abbreviated using the first three
or four letters of the attribute name.                               Using a Dedicated Server with Oracle Shared

Other Shared Server Configuration Parameters                         You must use a dedicated server process when:
                                                                         o Submitting batch jobs (no idle time)
•    MAX_DISPATCHERS                                                     o Connecting as sysdba for maintenance
      Description: Specifies the maximum number of
      dispatcher processes that can run simultaneously.              In tnsnames.ora file:
      Parameter type: Integer                                        (CONNECT_DATA=(SERVICE_NAME=TEST)(SERVER=DEDICATED
      Parameter class: Static                                        )
      Default value 5

      Description: Specifies the number of server processes          Configuring the Database Archiving Mode
      created when an instance is started up.
      Parameter type: Integer
                                                                     Setting ARCHIVELOG mode
      Parameter class: Dynamic (can use ALTER SYSTEM to
      modify)                                                        While database in mount mode:
      Default value 0                                                   ALTER DATABASE ARCHIVELOG | NOARCHIVELOG
Note: Additional shared servers start automatically when             Note: Backup the database before and after
needed and are deallocated automatically if they remain idle
for too long. However, the initial servers always remain
allocated, even if they are idle.                                    View ARCHIVELOG mode of a Database

•    MAX_SHARED_SERVERS                                              •    V$Database (cols: log_mode)
      Description: Specifies the maximum number of shared            •    ARCHIVE LOG LIST (in SQL*Plus)
      servers that can be started.
      Parameter type: Integer
      Parameter class: Static                                        Setting Automatic Archiving
      Default value: Derived from SHARED_SERVERS (either 20
      or 2 * SHARED_SERVERS)                                         While Database open
                                                                        ALTER SYSTEM ARCHIVE LOG START | STOP
•    SHARED_SERVER_SESSIONS                                          Note: When DB starts up again, it reads its automatic
      Description: Specifies the total number of Oracle Shared       archiving setting from init.ora file.
      Server user sessions to allow.                                 At Startup
      Parameter type         Integer
                                                                     LOG_ARCHIVE_START = TRUE | FALSE
      Parameter class        Static
      Default value the lesser of CIRCUITS and SESSIONS – 5
      Range of values        0 to SESSIONS – 5
                                                                     Manually Archiving Logs
• LARGE_POOL_SIZE                                                    ARCHIVE LOG ALL | NEXT
Configure the large pool to allocate shared server-related UGA
(User Global Area), not the shared pool.

Page 5                                                                      Oracle Database Administration Fundamentals II (Note Sheets)
                                                                  •   V$ARCHIVED_LOG
Specifying Archive Destinations
                                                                       displays archived log information from the control file,
Method1 (local or remote):                                             including archive log names.
    LOG_ARCHIVE_DEST_n      (dynamic)
    where: n is an integer from 1 to 10                           •   V$LOG
    Parameter Keywords:                                                contains log file information from the control files.
    LOCATION : a local file system
    SERVICE : remote archival through Oracle Net service name     •   V$LOG_HISTORY
    MANDATORY | OPTIONAL                                               contains log history information from the control file.
    REOPEN minimum number of seconds before the archiver
    process should try again to access a previously failed
    destination (default 300 seconds) (when setting to 0, it is   User-Managed Backup
    turned off)
    LOG_ARCHIVE_DEST_1 = 'LOCATION=/disk1/arc'
    LOG_ARCHIVE_DEST_2 = 'SERVICE=standby1'                       Cold Backup
                                                                  Mandatory Steps:
Method2 (local only):
                                                                      1. Shutdown the database cleanly
 LOG_ARCHIVE_DUPLEX_DEST                                              2. OS Copy of data files and control files.
Note: Any destination declared by LOG_ARCHIVE_DEST is                 3. restart the database
LOG_ARCHIVE_DEST = '/disk1/arc'                                   Note: It is highly advisable to copy redo logs, parameter
                                                                  file and archived logs as well.

Parameter                                                         Hot or Online Backup
                                                                  1. Put tablespace in backup mode using the command:
This parameter uses all mandatory destinations plus some
number of optional non-standby destinations to determine          ALTER TABLESPACE <tname> BEGIN BACKUP
whether LGWR can overwrite the online log.
                                                                  2. OS copy tablespace data files
Range of values: 1-10 in method 1 and 1-2 in method 2.
                                                                  3. End backup mode for the tablespace
                                                                  Note: It is advisable to copy parameter file, archived logs
Formatting ArchiveLog Filenames                                   and as well.
%s   log sequence number                                          Note: database must be in ARCHIVELOG mode
%S    log sequence number, zero filled
%t   thread number
%T   thread number, zero filled                                   Backing up Control Files
LOG_ARCHIVE_FORMAT = "LOG%s_%t.ARC"                               • Binary copy of the control file
                                                                  Alter database backup controlfile to '…'

                                                                  • ASCII copy of the control file:
Multiple ARCn Processes
                                                                  Alter database backup controlfile to trace
number of archiver background processes (ARC0 through
ARC9) Oracle initially invokes. It can be set after startup.      Cleaning Up Failed Online Backups
                                                                  1. Check data files that have STATUS column value equal to
Dynamic Performance Views                                            ACTIVE in the view V$BACKUP.

•    V$ARCHIVE_DEST                                               2. Issue the following commands for them:
                                                                  ALTER DATABASE DATAFILE '<filename>' END BACKUP
      all the archived redo log destinations.
                                                                  ALTER DATABASE END BACKUP
•    V$ARCHIVE_DEST_STATUS                                        or
      displays runtime and configuration information for the
                                                                  ALTER TABLESPACE <tname> BEGIN BACKUP
      archived redo log destinations.

                                                                  Backing Up the Initialization Parameter File
      provides information about the state of the various ARCH
      processes for the instance.                                 CREATE PFILE = '/fname.ora' FROM SPFILE;

Page 6                                                                   Oracle Database Administration Fundamentals II (Note Sheets)
                                                                  3. Recover tablespace
                                                                  4. Take the tablespace online
External utility that can be used to ensure that a backup
database or data file is valid before a restore. DBV has the
following parameters                                              Read-Only Tablespace Recovery
 o FILE data file to be verified
                                                                  RO backup and RO Recovery
 o START starting block
                                                                      Only copy tablespace data files.
 o END ending block
 o BLOCKSIZE blocksize of the data file                           RO backup and RW Recovery or RW backup and RO
 o LOGFILE log file to store the results                          Recovery
 o FEEDBACK display operation progress with dots                      Copy data files
                                                                      Apply archived logs
dbv file=/ORADATA/u03/users01.dbf logfile=dbv.log

                                                                  Determining Which Files Need Recovery

User-Managed Complete Recovery                                    •   V$RECOVER_FILE
                                                                       to determine which data files need recovery.

User-Managed Complete Recovery in                                 •   V$ARCHIVED_LOG
NOARCHIVEMODE                                                          for a list of all archived redo log files for the database.
Backup of Redo Log is available
                                                                  •   V$RECOVERY_LOG
1. Shutdown the database
                                                                       for a list of all archived redo log files required for
2. Copy all data files, control files and redo logs from a cold        recovery.
3. Startup the database
                                                                  Using Archived Redo Log Files During Recovery
Backup of Redo Log is unavailable                                 Oracle server can be notified before or during recovery, by
After copying the backup files, issue the following:              one of the following methods:
   RECOVER DATABASE USING BACKUP CONTROLFILE                          •   at the recover prompt:
   UNTIL CANCEL ;                                                         Specify log: {<RET>=suggested | filename |
   CANCEL                                                                 AUTO | CANCEL}
   ALTER DATABASE OPEN RESETLOGS;                                     •   ALTER SYSTEM ARCHIVE LOG START TO <new
                                                                      •   RECOVER FROM '<new location>' DATABASE
User-Managed Complete Recovery in
                                                                  To apply redo log files automatically, you can issue the SET
1. Take the recovered data file offline.                          AUTORECOVERY ON command before starting media
                                                                  recovery. Remember that you can control location of
                                                                  archived logs by setting the parameter: LOG_ARCHIVE_DEST
2. Copy the data file(s) from backup to the original location
Note: do not restore redo logs or control files
                                                                  Restoring Data Files to Different Locations
3. Issue the command:
                                                                  1. Startup Mount
Recover Datafile '..'
                                                                  2. Alter Database Rename File '..' To '..'
Recover Tablespace <number> | <name>                              Recover Database
Recover Database (in mount state)
Alter Database Recover
                                                                  User-Managed Incomplete Recovery
4. Take the data file online
                                                                  Cancel-Based Recovery
Recovery of a Datafile Without a Backup                           1. Shutdown the database cleanly
                                                                  2. Backup database files (optional but highly
1. Take the tablespace offline
   or                                                             3. Restore only ALL the backup data files. (Do not restore
   ALTER DATABASE CREATE DATAFILE '..' AS '..'                       control files or redo log files)

Page 7                                                                    Oracle Database Administration Fundamentals II (Note Sheets)
4. You may also need to restore archived logs. If there is
                                                                RMAN Command Line Arguments
   enough space available, restore to the LOG_ARCHIVE_DEST
   location or use the ALTER SYSTEM ARCHIVE LOG START TO        • Writing RMAN output to a log file:
   <location> command or the SET LOGSOURCE <location>           log $HOME/ORADATA/u03/rman.log append
   command to change the location.
                                                                • Executing a command file when RMAN is invoked:
5. Startup in mount state
6. Verify all data files you need to recover are online
7. Recover Database Until Cancel
8. Open database with RESETLOGS option                          Configuring the RMAN Environment
9. Complete Backup the database. (existing backup no longer
                                                                • To list current configuration
                                                                SHOW ALL
Note: It is advisable to SHUTDOWN and then STARTUP the
database after completing the recovery. Also take full          • To set value for a configuration setting
database backup.                                                CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/?/%U';
                                                                CONFIGURE CONTROL FILE AUTOBAKCUP ON

                                                                • To reset the setting value
Time-Based Recovery                                             CONFIGURE CONTROL FILE AUTOBAKCUP CLEAR
Same as above except step 6 which will be
RECOVER DATABASE UNTIL TIME 'yyyy-mm-dd:hh24:mi:ss'
                                                                RMAN Channel Commands
                                                                • Manual Channel Allocation
Change-Based Recovery
                                                                RUN {
Same as above except step 6 which will be                        ALLOCATE CHANNEL c1 TYPE disk
Recover Database Until Change <n>                                FORMAT = '/db01/BACKUP/usr0520.bak';
                                                                 BACKUP DATAFILE '/db01/ORADATA/users01.dbf';}
Note: You can query V$LOG_HISTORY and V$ARCHIVED_LOG to
display high and low SCN number in every archived log file.     • Automatic Channel Allocation
                                                                CONFIGURE DEFAULT DEVICE TO DISK | SBT
                                                                CONFIGUE DEVICE TYPE DISK PARALLELISM n
Using a Backup Control File During Recovery                     • Automatic Channel Options
This is needed when a physical structure of the database has    CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT =
been changed (like accidentally dropping a tablespace).
                                                                CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 2G
Same as above except step 6 which will be:
  Restore from the appropriate backed up Control File then
  issue the command:
                                                                Duration in days of RMAN information in
RECOVER DATABASE UNTIL TIME '2002-03-09:11:44:00'               control file
                                                                This is controlled by the initialization parameter
Notes:                                                          CONTROL_FILE_RECORD_KEEP_TIME
  • Opening a database with RESETLOGS option recreates any
    missing log files.
  • Alert files are important source of failures occurred and
    to confirm success of recovery.                             RMAN Backups

                                                                Backup Piece Size
Oracle Recovery Manager Configuration                           ALLOCATE CHANNEL … MAXPIECESIZE = integer
                                                                CONFIGURE CHANNEL … MAXPIECESIZE = integer
Connecting to RMAN without a Recovery
                                                                Backup Command
1. In OS command line, set ORACLE_SID environment
   variable                                                     Mandatory Steps
                                                                 • Database must be OPEN or in MOUNT state
2. rman target user/pswd nocatalog
                                                                 • Allocate Channel (manually or automatic)

                                                                BACKUP command options
                                                                 o FULL : copies all data blocks ( except not used ones).
                                                                 o INCREMENTAL LEVEL n
                                                                 o INCLUDE CURRENT CONTROLFILE

Page 8                                                               Oracle Database Administration Fundamentals II (Note Sheets)
    o FILESPERSET n :maximum number of input files in each
                                                                  Backing Up Archived Redo Logs
      backup set
    o SKIP OFFLINE | READONLY | INACCESSIBLE                      Using BACKUP ARCHIVELOG command
    o MAXSETSIZE n K|M|G                                           BACKUP ARCHIVELOG ALL

    o DELETE INPUT : useful when backing up archived redo         To delete backed up copy of the archived log file
      logs, datafile copies or backup sets.                        BACKUP ARCHIVELOG ALL DELETE INPUT
    o FORMAT :%c copy number                                      To delete logs from all enabled archiving destinations.
              %p backup piece number                               BACKUP ARCHIVELOG ALL DELETE ALL INPUT
              %s backup set number                                To specify a range of archived redo logs by time
              %d database name                                     BACKUP ARCHIVELOG UNTIL TIME 'SYSDATE-7';
              %n database name padded with 8 characters            BACKUP ARCHIVELOG FROM TIME 'SYSDATE-30' UNTIL
                                                                   TIME 'SYSDATE-7';
              %t backup set time stamp
              %u compressed version of %s and %t                  To specify a range of archived redo logs by SCN
                                                                   BACKUP ARCHIVELOG UNTIL SCN = 320
              %U (default) equivalent to %u_%p_%c                  BACKUP ARCHIVELOG SCN BETWEEN 205 AND 320

To make a whole database backup                                   To specify a range of archived redo logs by log sequence
BACKUP DATABASE FORMAT '/tmp/%U' TAG='weekly_bak'                 number
                                                                   BACKUP ARCHIVELOG FROM SEQUENCE integer
Backing Up Tablespaces
BACKUP TABLESPACE system, users, tools;                           Using BACKUP ... PLUS ARCHIVELOG:

Backing up Datafiles and Datafile Copies                          Note: In Oracle9i, Release 2, you can use the NOT BACKED
BACKUP DATAFILE 1,2,3,4, DATAFILECOPY                             UP integer TIMES clause of the BACKUP ARCHIVELOG
'/tmp/system01.dbf'                                               command to back up only those logs that have not been
LIST BACKUP OF DATAFILE 1,2,3,4;                                  backed up at least integer times.

Backing Up Backup Sets (from disk to tape or from disk
to disk)
BACKUP DEVICE TYPE sbt BACKUPSET ALL;                             Multiplexed Backup Sets
                                                                  Multiplexing is controlled by the following:
                                                                      • The FILESPERSET parameter on the BACKUP
Control File Backups                                                     command
                                                                      • The MAXOPENFILES parameter of the ALLOCATE
Control File Autobackup
                                                                         CHANNEL and CONFIGURE CHANNEL commands (default
                                                                         is 8)
    to enable
  • When enabled, RMAN automatically performs a back up
    of the control file and current server parameter file after
    BACKUP or COPY commands                                       Parallelization of Backup Sets
                                                                  Parallelization of backup sets is achieved by:
Control File Backup Format                                            • Configuring PARALLELISM to greater than 1 or
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE                        allocating multiple channels
disk TO 'controlfile_%F';
                                                                      • Specifying many files in the BACKUP command
                                                                  By default, RMAN determines which channels should back
Backing Up the Control File Manually                              up which database files. You can use the BACKUP ...
BACKUP CURRENT CONTROLFILE TAG = mondaypmbackup                   CHANNEL command to manually assign a channel to back up
LIST BACKUP OF CONTROLFILE                                        specified files.
Note: two control file backups and server parameter file are      BACKUP
created, if control file AUTOBACKUP is enabled.                     (DATAFILE 1,2,3
                                                                     FILESPERSET = 1
Including the Control File in a Backup Set                           CHANNEL ORA_DISK_1)
                                                                    (DATAFILECOPY '/tmp/system01.dbf',
BACKUP TABLESPACE users INCLUDE CURRENT CONTROLFILE;                 '/tmp/tools01.dbf'
                                                                     FILESPERSET = 2
                                                                     CHANNEL ORA_DISK_2);
Backing Up the Server Parameter File                              RUN
•    Automatically backed up when CONFIGURE CONTROLFILE               ALLOCATE CHANNEL c1 DEVICE TYPE sbt PARMS="ENV=
     AUTOBACKUP = ON                                                (BACKUP_SERVER=tape_server1)";
                                                                      ALLOCATE CHANNEL c2 DEVICE TYPE sbt PARMS="ENV=
•    Explicitly backed up with BACKUP SPFILE                        (BACKUP_SERVER=tape_server2)";
BACKUP COPIES 2 DEVICE TYPE sbt SPFILE                                BACKUP
                                                                      (DATAFILE 1,2,3 CHANNEL c1)

Page 9                                                                 Oracle Database Administration Fundamentals II (Note Sheets)
   (DATAFILECOPY '/tmp/system01.dbf',
    '/tmp/tools01.dbf' FILESPERSET = 2 CHANNEL c2)               Copying the Whole Database
                                                                     • Use the REPORT SCHEMA command to list the files.
                                                                     • Use the COPY command or make an image copy of each
Duplexed Backup Sets                                                   datafile.

You can use the following commands to produce a duplexed
backup set:                                                      Incremental Backups
    BACKUP COPIES 3 INCREMENTAL LEVEL = 0                        Differential incremental: backups contain only modified
    DATABASE;                                                    blocks from level n or lower.
    BACKUP COPIES 2 DATAFILE 1, DATAFILE 2                         BACKUP INCREMENTAL LEVEL N ... (n range: 1 to 4)
  • SET BACKUP COPIES within a run block                         Cumulative incremental: backups contain only modified
  • CONFIGURE … BACKUP COPIES                                    blocks from level n-1 or lower. (faster recovery, slower and
    CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT                    larger backups)
    '/save1/%U', '/save2/%U';                                       BACKUP INCREMENTAL LEVEL 2 CUMULATIVE ...
    CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE                  Note: In BACKUP command, you must set one of the
    TYPE sbt TO 2;                                               following parameters: DATAFILE, DATAFILECOPY,
    DEVICE TYPE sbt TO 2;

Note: there is little value in creating multiple copies on the   Backup in NOARCHIVELOG Mode
same physical media. For sbt channels, if you use a media
manager that supports Version 2 of the SBT API, then the         1. Shut down cleanly
media manager will automatically put each copy onto a            2. Mount the database.
separate medium.
                                                                 3. Allocate multiple channels, if not using automatic.
Note: You must set the BACKUP_TAPE_IO_SLAVES initialization      4. Run the BACKUP command.
parameter to TRUE in order to perform duplexed backups to
an sbt device.
                                                                 Tags for Backups and Image Copies
Image Copies                                                     BACKUP .. TAG='tag_name'

COPY DATAFILE { 'filename'| integer }                            COPY DATAFILECOPY TAG='tag_name' TO
     | DATAFILECOPY {'filename' |TAG='tag_name'}                 '\tmp\test.dbf'
     | ARCHIVELOG 'filename'
                                                                 RMAN Dynamic Views
     | CONTROLFILECOPY {'filename'|TAG='tag_name'}
  TO AUXNAME | 'filename'
                                                                 •   V$ARCHIVED_LOG
COPY DATAFILE '/ORADATA/users_01_db01.dbf' to                         shows which archives have been created, backed up,
'/BACKUP/users01.dbf' tag=DF3 ;
                                                                      and cleared in the database.
Note: Oracle server process copies the file and performs
additional actions such as checking for corrupt blocks and       •   V$BACKUP_CORRUPTION
registering the copy in the control file. To speed up the             shows which blocks have been found corrupt during a
process of copying, you can use the NOCHECKSUM parameter.             backup of a backup set.

                                                                 •   V$COPY_CORRUPTION
Image Copy Parallelization                                            shows which blocks have been found corrupt during an
                                                                      image copy.
You can parallelize the copy operation by:
                                                                 •   V$DATABASE_BLOCK_CORRUPTION
      allocating multiple channels ( required in Oracle8i)
                                                                      displays information about database blocks that were
    • Specifying one COPY command for multiple files
                                                                      corrupted after the last backup.
CONFIGURE   DEVICE TYPE disk parallelism 4;
COPY        # 3 files copied in parallel                         •   V$BACKUP_DATAFILE
 datafile   1 TO '/BACKUP/df1.dbf',                                   is useful for creating equal-sized backup sets by
 datafile   2 TO '/BACKUP/df2.dbf',                                   determining the number of blocks in each datafile. It
 datafile 3 TO '/BACKUP/df3.dbf';                                     can also help you find the number of corrupt blocks for
                                                                      the datafile.

Page 10                                                                Oracle Database Administration Fundamentals II (Note Sheets)
•   V$BACKUP_REDOLOG                                                 recover tablespace users;
     shows archived logs stored in backup sets.                      sql "alter tablespace users online";                    }

     shows backup sets that have been created.                      Relocate a Tablespace
•   V$BACKUP_PIECE                                                   SQL "alter tablespace users offline immediate";
     shows backup pieces created for backup sets.                    SET NEWNAME FOR DATAFILE
                                                                      TO '/ORADATA/u04/users01.dbf';
                                                                     RESTORE TABLESPACE users;
Monitoring RMAN Backups
                                                                     SWITCH datafile 3; # Update the control file and
To correlate a process with a channel during a backup:                recovery catalog
                                                                     RECOVER TABLESPACE users; #Recover the tablespace
1. In each session, set the COMMAND ID to a different value
         RUN                                                         SQL "alter tablespace tbs1 online";}
            ALLOCATE CHANNEL c1 TYPE sbt;
            SET COMMAND ID TO 'sess1';
            BACKUP DATABASE;
          }                                                         RMAN Incomplete Recovery
2. Query the joined V$SESSION and V$PROCESS views
  SELECT SID, SPID, CLIENT_INFO                                     Incomplete Recovery of a Database
  WHERE p.ADDR = s.PADDR                                            1. Mount the database.
  AND s.CLIENT_INFO LIKE '%id=sess%';
    The CLIENT_INFO column displays in the following format:        2. The following steps should be followed:
    id=command_id,rman channel=channel_id                           RUN {
                                                                         # multiple channels for parallelization
3. Query the V$SESSION_LONGOPS view to get the status of                 ALLOCATE CHANNEL c1 TYPE DISK;
   the backup or copy.                                                   ALLOCATE CHANNEL c2 TYPE DISK;
                                                                         # recover until time, SCN or sequence
                                                                         SET UNTIL TIME = '2001-12-09:11:44:00';
Detecting Corruption                                                     SET UNTIL TIME "to_date('09-05-2004
                                                                         00:00:20', 'dd-mm-yyyy hh24:mi:ss')" ;
• To view corrupt blocks encountered during backups from                 SET UNTIL SEQUENCE 120 THREAD 1;#120 not
  the control file, view either V$BACKUP_CORRUPTION for                  included
  backup sets or V$COPY_CORRUPTION for image copies.                     # ALL datafiles must be restored
                                                                         RESTORE DATABASE;
• RMAN tests data and index blocks for logical corruption and            RECOVER DATABASE;
  logs any errors in the alert.log and server session trace file.        ALTER DATABASE OPEN RESETLOGS; }
  By default, error checking for logical corruption is disabled.
                                                                    3. If using a recovery catalog, register the new incarnation
                                                                       of the database using the command: RESET DATABASE

                                                                    4. Perform a whole database backup.
RMAN Complete Recovery
                                                                    Note: Insure that NLS_LANG and NLS_DATE_FORMAT
                                                                    environment variables are set appropriately.
Recover a Database in ARCHIVELOG Mode
                                                                    Note: check the alert.log for any errors during recovery.
                                                                    Note: If you need to restore archived redo log files to a
                                                                    new location use the SET ARCHIVELOG DESTINATION TO
RECOVER DATABASE;                                                   <location> command.

Restore Datafiles to a New Location
run{set newname for datafile 1 to                                   RMAN Maintenance
    restore database;
    switch datafile all; # record in control file                   Cross Checking Backups and Copies
    recover database;}
                                                                    Use CROSSCHECK command to ensure that data about
                                                                    backup sets and image copies in the recovery catalog or
                                                                    control file is synchronized with corresponding files on disk
Recover a Tablespace                                                or in the media management catalog.
run{                                                                  CROSSCHECK BACKUPSET OF DATABASE;
 sql "alter tablespace users offline immediate";                      CROSSCHECK BACKUP OF TABLESPACE users DEVICE
                                                                      TYPE sbt COMPLETED BEFORE 'SYSDATE-31';
 restore tablespace users;

Page 11                                                                  Oracle Database Administration Fundamentals II (Note Sheets)
  CROSSCHECK   BACKUP OF ARCHIVELOG ALL SPFILE;                Note: The KEEP FOREVER clause requires the use of a
  CROSSCHECK   BACKUPSET 1338, 1339, 1340;                     recovery catalog.
  CROSSCHECK   BACKUPPIECE TAG = 'nightly_backup';
  CROSSCHECK   CONTROLFILECOPY '/tmp/control01.ctl';           Note: Use CHANGE ... NOKEEP to make the file conform to
  CROSSCHECK   DATAFILECOPY 113, 114, 115;                     the retention policy.

Note: If the backup or copy is no longer available, then
RMAN marks it as EXPIRED. You can determine which files are
                                                               The CATALOG Command
marked EXPIRED by issuing a LIST EXPIRED command.
                                                               Use CATALOG command to make RMAN aware of the
                                                               existence of archived logs that are not recorded in the
                                                               repository as well as file copies that are created through
                                                               means other than RMAN.
Deleting Backups and Copies
                                                                 CATALOG DATAFILECOPY '/DB01/BACKUP/users01.dbf';
Deleting Specified Backups and Copies                            CATALOG CONTROLFILECOPY ‘/DB01/BACKUP/db.ctl’;
  DELETE BACKUPPIECE 101;                                        CATALOG ARCHIVELOG
  DELETE CONTROLFILECOPY '/tmp/control01.ctl';                   '/ORADATA/ARCHIVE1/arch_12.arc',
  DELETE COPY OF CONTROLFILE LIKE '/tmp/%';                    Note: You need to make RMAN aware of the existence of
  DELETE NOPROMPT BACKUP OF SPFILE COMPLETED BEFORE            archived redo log files that are not recorded in the
  'SYSDATE-7';                                                 repository, if you manually have restored your control file
  TO sbt;

Deleting Expired Backups and Copies
  DELETE EXPIRED BACKUP;                                       The CHANGE … UNCATALOG Command
                                                               Run the CHANGE ... UNCATALOG command to perform the
  DEVICE TYPE sbt COMPLETED BEFORE 'SYSDATE-31';               following actions on RMAN repository records:
                                                                 • Delete a specific backup or copy record from the
Deleting Backups and Copies Rendered Obsolete by the               recovery catalog
Retention Policy                                                 • Update a backup or copy record in the target control
  DELETE OBSOLETE;                                                 file repository to status DELETED

Deleting Backups and Copies Defined as Obsolete by             RMAN does not touch the specified physical files: it only
the DELETE Command                                             alters the repository records for these files. You can use
  DELETE OBSOLETE REDUNDANCY = 3;                              this command when you have deleted a backup or copy
  DELETE OBSOLETE RECOVERY WINDOW OF 7 DAYS;                   through a means other than RMAN.
                                                                  CHANGE ARCHIVELOG … UNCATALOG;
Forcing the Deletion of Backups and Copies                       CHANGE DATAFILECOPY '/DB01/BACKUP/users01.dbf'

Changing the Availability of RMAN Backups and
                                                               Recovery Catalog Creation and
Use the command CHANGE ... UNAVAILABLE                         Maintenance
 CHANGE BACKUP OF CONTROLFILE UNAVAILABLE;                     Creating Recovery Catalog
                                                               1. Create tablespace
 240 UNAVAILABLE;                                              2. Create catalog owner
Note: If a file is marked UNAVAILABLE, RMAN will not use the   3. Grant privileges
file when a RESTORE or RECOVER command is issued.                 GRANT connect, resource, recovery_catalog_owner

                                                               4. Create catalog
                                                                  rman catalog rman_db1/rman_db1@catdb
Exempting a Backup or Copy from the                               RMAN> create catalog tablespace rman_ts;
Retention Policy
                                                               5. Connect to target database as SYSDBA
  CHANGE DATAFILECOPY '/DB01/BACKUP/users01.dbf'               6. Register target database
  KEEP UNTIL 'SYSDATE+60';                                        REGISTER DATABASE;
Note: Specify KEEP ... LOGS to save archived logs for a
possible incomplete recovery and KEEP ... NOLOGS not to
save archived logs for a possible incomplete recovery.

Page 12                                                             Oracle Database Administration Fundamentals II (Note Sheets)
                                                                  • To list the text of a specified stored script
To Update The Recovery Catalog Manually
                                                                           • Use PRINT SCRIPT command
Use the CATALOG, CHANGE, and DELETE commands. See                          • Query RC_STORED_SCRIPT_LINE
previous section
                                                                  • To rewrite a script use REPLACE SCRIPT command
                                                                    REPLACE SCRIPT Level0Backup { ... }
Resynchronization of the Recovery Catalog                         • To remove a script use DELETE SCRIPT
• RMAN     performs  partial  or  full resynchronizations
  automatically as needed when you execute certain
  commands, including BACKUP and COPY.                            Export and Import Utilities
• Resynchronization of the recovery catalog happens
  manually with RESYNC CATALOG command. You may use it in         Requirements
  the following situations:
                                                                  To export tables owned by another user, you must have
    o If you have run your backups in NOCATALOG mode.             privileges contained in the role EXP_FULL_DATABASE
    o Run it periodically every
    o The recovery catalog has been rebuilt for any reason like   Invoking Export
      recovery in catalog database.
                                                                   exp username/password PARAMETER=value
Note: During resynchronization, Recovery Manager may add            or
records for files that no longer exist, because files being re-     exp username/password
cataloged are not verified. Remove such records by issuing          PARAMETER=(value1,value2,...,valuen)
the CHANGE ... UNCATALOG command.                                   exp hr/hr TABLES=employees,departments               rows=y
                                                                    exp system/manager OWNER=hr direct=y
Resetting a Database Incarnation
                                                                  Note: The default export filename is expdat.dmp.
• Run the RESET DATABASE command in RMAN after
  executing the SQL statement ALTER DATABASE OPEN
  RESETLOGS.                                                      Export Modes
• Use RESET DATABASE TO INCARNATION n command to undo             • Full Database Mode
  the effects of a RESETLOGS operation by restoring backups           Full=Y
  of a prior incarnation of the database. The identifier n is
  obtained by the LIST INCARNATION OF DATABASE command            • Tablespace Mode
  or V$DATABASE_INCARNATION view                                      TABLESPACES=<tablespaces_list>

                                                                  • User Mode
RMAN Catalog Reporting
                                                                  • Table Mode
• Use REPORT and LIST commands.                                       Tables=<tables_list>

• Alternatively you can use SQL commands to query the                    You can export tables whose names match specific
  RMAN data dictionary views. Examples of those view are                 patterns:
  the following:                                                         TABLES=(scott.%P%,blake.%,scott.%S%)
      •   RC_DATABASE                                                    You can export a table with a specified partition:
      •   RC_DATAFILE                                                    TABLES=(emp:m,emp:sp4)

Stored Scripts
• Stored scripts are created using the CREATE SCRIPT
  Create Script Level0Backup {
           backup incremental level 0
           format '/u01/db01/backup/%d_%s_%p'
           fileperset 5
           (database include current controlfile);
           sql 'alter system archive log current';}

• To run a script use EXECUTE SCRIPT
  RMAN> run {execute script Level0Backup;}

Page 13                                                                  Oracle Database Administration Fundamentals II (Note Sheets)
Direct Path mode                                                 Using Parameter File
• Can be set by specifying the DIRECT=Y parameter                Parameter values can be stored in a parameter file that can
• Restrictions:                                                  be then used by export or import utilities using the PARFILE
    o The direct-path option cannot be invoked interactively.    imp PARFILE=filename
    o Client-side and server-side character sets must be the
    o The BUFFER parameter has no affect. Use
      RECORDLENGTH instead.
                                                                 Using SQL*Loader

Invoking Import
                                                                 Direct-Load Insert Operations
imp hr/hr TABLES=employees,departments rows=y
file=exp1.dmp                                                    Serial: uses one server process to insert data beyond the
                                                                 high-water mark.
imp system/manager FROMUSER=hr TOUSER=scott                        INSERT /*+ APPEND */
file=exp2.dmp                                                      INTO T1_NEW
Note: If the file parameter is not specified, Import looks for
                                                                   SELECT * FROM T1
the default file expdat.dmp

                                                                 Parallel: the statement or the table is put into parallel
                                                                 mode. The database must have parallel query salves
Import Modes                                                     configured in its initialization parameter file.
• Full Database Mode                                             Also, you must enable parallel DML for your session
    Full=Y                                                          ALTER SESSION ENABLE PARALLEL DML
                                                                   Use hint or place the table to be inserted into parallel mode.
• Tablespace Mode                                                  INSERT /*+ PARALLEL (SCOTT.T1_NEW,4)*/
    TABLESPACES=<tablespaces_list>                                 INTO T1_NEW
• User Mode                                                        NOLOGGING
   FROMUSER=<u1> TOUSER=<u2> TABLES=<tables_list>                  SELECT * FROM T1
• Table Mode (see Export Modes)                                    ALTER TABLE T1_NEW PARALLEL (DEGREE 4)

                                                                 Issuing SQL*Loader
Invoking Import as SYSDBA
                                                                 sqlldr test/test control=invoice_header.ctl
• You need to invoke Import as SYSDBA when importing a
  transportable tablespace set
  imp \'username/password@instance AS SYSDBA\'
                                                                 Control File
• If either the username or password is omitted, Import will       -- Invoice Header Sample Control File
  prompt you for it.                                               LOAD DATA
                                                                   INFILE 'mydata.dat'
                                                                   BADFILE 'baddata.dat'
Import Process Sequence                                            DISCARDFILE 'skippeddata.dat'
1. New tables are created                                          INTO TABLE invoice_header
2. Data is imported                                                WHEN SLAESPERSON(100)='EDI'
3. Indexes are built                                               FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY
4. Triggers are imported                                           TRAILING NULLCOLS
5. Integrity constraints are enabled on the new tables             ( COMPNO     decimal external
6. Any bitmap, functional, and/or domain indexes are built         , INV_NO     decimal external
                                                                   , DISCOUNT_RATE      decimal external
                                                                   ":discount_rate * .90"
                                                                   , DUEDATE    date "SYYYYMMDDHH24MISS"
Manually Creating Tables Before Importing
                                                                   , INVDATE    date "SYYYYMMDDHH24MISS"
Data                                                               , CUST_NO    char
When tables are manually created before data is imported,          , CUST_CAT   char NULLIF cust_cat=BLANKS
the CREATE TABLE statement in the export dump file will fail       , CO_OBJ     decimal
because the table already exists. To avoid this failure and        , SALESMAN   char    "UPPER(:salesman)"
continue loading data into the table, set the import parameter     , CUSTREF    char
IGNORE=y. Otherwise, no data will be loaded into the table         )
because of the table creation error.

Page 14                                                               Oracle Database Administration Fundamentals II (Note Sheets)
  • REPLACE keyword will delete existing data from the
    table. APPEND is used for non-empty tables and INSERT
    for empty tables.
  • TRAILING NULLCOLS tell SQL*Loader to handle any other
    columns that are not present in the record as null

Conventional, Direct-Path and External-Path
• Conventional load is the default method that SQL*Loader
  uses. The direct-path load is initiated by using the
  DIRECT=TRUE keyword on the command line.
• The external-path load is specialized to external tables.

SQL*Loader Parallel Load Methods
• Parallel conventional load is performed by issuing
  multiple SQL*Loader commands, each with their own
  control file and input data file, all to the same table.
• Intersegment concurrency with direct-path load is
  performed in the same way that parallel conventional load
  is, but it adds the DIRECT=TRUE keyword and uses different
• Intrasegment concurrency with direct-path load is
  performed by using direct-path load to load data into a
  single table or partition. This is performed by placing the
  DIRECT=TRUE and PARALLEL=TRUE option on the command
  line. In this parallel server, processes load the data into
  temporary segments and then merge them into the
  individual segments.

Page 15                                                         Oracle Database Administration Fundamentals II (Note Sheets)

To top