9i Export/Import Parameters

USERID:   username/password
BUFFER:   size of data buffer
FILE:   output file (EXPDAT.DMP)
COMPRESS:   import into one extent (Y)
GRANTS:   export grants (Y)
INDEXES:   export indexes(Y)
ROWS:   export data rows (Y)
CONSTRAINTS:   export table constraints (Y)
CONSISTENT:   cross-table consistency (N)
LOG:   log file of screen output (None)
STATISTICS:   analyze objects (ESTIMATE)
DIRECT:   Bypass the SQLcommand processing layer (N) (new in Oracle8)
FEEDBACK:   Show a process meter (a dot) every X rows exported (0 – Xvalue)
HELP:   Shows help listing MLS MLS_LABEL_FORMAT Used with secure Oracle; we won't cover
these.
FULL:   export entire file (N)
OWNER:   list of owner usernames
TABLES:   list of table names
RECORDLENGTH:  length of IO record
INCTYPE:  incremental export type
RECORD:   track incr. export (Y)
PARFILE:   parameter file name

How to get parent/child of a table in Oracle

This query gives list of children tables of a given table:


SELECT p.table_name PARENT_TABLE, c.table_name CHILD_TABLE
FROM user_constraints p, user_constraints c
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R'
AND p.constraint_name = c.r_constraint_name
AND p.table_name = UPPER('&tab');


And this one gives list of parent tables of a given table:

SELECT c.table_name CHILD_TABLE, p.table_name PARENT_TABLE
FROM user_constraints p, user_constraints c
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R'
AND p.constraint_name = c.r_constraint_name
AND c.table_name = UPPER('&tab');

Chmod commands

The UNIX chmod command is used to change the execution permissions of a UNIX file. The  chmod stands for change mode. The chmod command is based on the permissions and the chmod permissions can be assigned either by number or by a letter value.

Owner	Group	World	Meaning
7	7	7	Read + Write + execute
6	6	6	Write + execute
5	5	5	Read + execute
4	4	4	read only
2	2	2	write only
1	1	1	Execute only

Owner (u)	Group (g)	World(o)	Meaning
rwx	rwx	rwx	Read + Write + execute
rw	rw	rw	Read + Write
rx	rx	rx	Read + execute
wx	wx	wx	Write + execute
r	r	r	Read only
w	w	w	Write Only
x	x	x	Execute only

                 

Suspend & Resume a Database

                The ALTER SYSTEM SUSPEND statement halts all input and output (I/O) to datafiles (file header and file data) and control files. The suspended state lets us back up a database without I/O interference. When the database is suspended all preexisting I/O operations are allowed to complete and any new database accesses are placed in a queued state. The suspend command is not specific to an instance. In an Oracle Real Application Clusters environment, when we issue the suspend command on one system, internal locking mechanisms propagate the halt request across instances, thereby quiescing all active instances in a given cluster. However, if someone starts a new instance another instance is being suspended, the new instance will not be suspended.

Using the ALTER SYSTEM RESUME statement to resume normal database operations. The SUSPEND and RESUME commands can be issued from different instances. For example, if instances 1, 2, and 3 are running, and we issue an ALTER SYSTEM SUSPEND statement from instance 1, then we can issue a RESUME statement from instance 1, 2, or 3 with the same effect. The suspend/resume feature is useful in systems that allow us to mirror a disk or file and then split the mirror, providing an alternative backup and restore solution. If we use a system that is unable to split a mirrored disk from an existing database while writes are occurring, then we can use the suspend/resume feature to facilitate the split. 

The suspend/resume feature is not a suitable substitute for normal shutdown operations, because copies of a suspended database can contain uncommitted updates.

The following statements illustrate suspend and resume usage. The V$INSTANCE view is queried to confirm database status.

     SQL>    alter system suspend;
               System altered.

     SQL>   select database_status from V$instance;
  

             DATABASE_STATUS
               ------------------------
                 SUSPENDED

       SQL>  alter system resume ;
                System altered.

      SQL> select database_status from v$instance ;
        

             DATABASE_STATUS
             -------------------------
              ACTIVE

Oracle Release Dates

Version	Release Date
Oracle 6	1988
Oracle 7	1992
Oracle 8	1997
Oracle 8i	1998
Oracle 9i	2001
Oracle 10gR1	2003
Oracle 10gR2	July 2005
Oracle 11gR1	Aug 2007
Oracle 11gR2	Sep 2009

RMAN Backup Views

The following Oracle dynamic views can be used to obtain RMAN information stored in the control file:

•  V$ARCHIVED_LOG:    show which archives have been created, backed up, and      cleared in the database
    
•  V$BACKUP_CORRUPTION:    show which blocks have been found to be corrupt during a backup of a backup set             

• V$BACKUP_DATAFILE:   useful for creating equal-sized backup sets by determining the number of blocks in each datafile. It can also help you find the number of corrupt blocks in the datafile 

•    V$BACKUP_DEVICE:   display information about supported backup devices. DISK is  not returned because it is always available

•  V$BACKUP_FILES:   display information about all RMAN backups (image copies and backup sets) and archived logs. The view simulates the LIST BACKUP and LIST COPYcommands

•  V$BACKUP_PIECE:    show backup pieces created for backup sets 

• V$BACKUP_REDOLOG:   show archived logs stored in backup sets 

•  V$BACKUP_SET :    show backup sets that have been created 

•  V$BACKUP_SPFILE:   display information about server parameter files in backup sets 

• V$COPY_CORRUPTION:     show which blocks have been found to be corrupt during an image copy 

• V$DATABASE_BLOCK_CORRUPTION:    list database blocks marked as corrupt during the most recent RMAN backup 

• V$RMAN_CONFIGURATION:    list information about RMAN persistent configuration settings 

• V$PROXY_ARCHIVEDLOG, V$PROXY_DATAFILE:     proxy settings for RMAN

• V$BACKUP_ASYNC_IO, V$BACKUP_SYNC_IO:    backup performance statistics

Fractured block in Oracle

A block in which the header and footer are not consistent at a given SCN. In a user-managed backup, an operating system utility can back up a datafile at the same time that DBWR is updating the file. It is possible for the operating system utility to read a block in a half-updated state, so that the block that is copied to the backup media is updated in its first half, while the second half contains older data. In this case, the block is fractured.

For non-RMAN backups, the ALTER TABLESPACE ... BEGIN BACKUP or ALTER DATABASE BEGIN BACKUP command is the solution for the fractured block problem. When a tablespace is in backup mode, and a change is made to a data block, the database logs a copy of the entire block image before the change so that the database can reconstruct this block if media recovery finds that this block was fractured.

The block that the operating system reads can be split, that is, the top of the block is written at one point in time while the bottom of the block is written at another point in time. If you restore a file containing a fractured block and Oracle reads the block, then the block is considered a corrupt.

What Happens When A Tablespace/Database Is Kept In Begin Backup Mode (Doc ID 469950.1)

Vi Editor Commands

Here are a few useful commands for those who are new to vi.

esc :q!	Just quit - don't save
esc :e!	Revert to saved
esc :wq	Save and exit
esc shift zz	Save and exit
esc i	Enter insert mode (edit mode)
esc a	Enter append mode (edit mode)
esc	Exit edit mode
esc r	Replace a single character
esc x	Delete a single character
esc  u	Undo last Change
esc  U	Undo all changes to line
esc dd	Delete a single line
esc yy	Copy a single line
esc p	Paste a single line
.	Repeat the last command
esc /	String search
esc $	Jump to end of line
esc ^	Jump to begining of line
shift g (or) :$	Jump to the end of the file
:1  or gg	Jump to the begining of the file
:.=	Display the current line number
nG  (or) :n	Move to nth line of the file
:set nu	To turn ON numbering to each line
:set nonu	To turn OFF numbering to each line

FIND & REPLACE :

Syntax:          :%s/WORD-To-Find-HERE/Replace-Word-Here/g   

Examples

To find each occurrence of 'UNIX', and replace it with 'Linux', enter (press ESC, type : and following command):
                                 :%s/UNIX/Linux/g

Task: Find and Replace with Confirmation

Find a word called 'UNIX' and replace with 'Linux', but ask for confirmation first, enter:
:%s/UNIX/Linux/gc

Task: Find and Replace Whole Word Only

Find whole words exactly matching 'UNIX' to 'Linux'; and ask for confirmation too:
:%s/\<UNIX\>/Linux/gc

Task: Case Insensitive Find and Replace

Find 'UNIX' (match UNIX, unix, UnIx, Unix and so on) and replace with 'Linux':
:%s/unix/Linux/gi

Same command with confirmation:
:%s/unix/Linux/gic

Task: Case sensitive Find and Replace

Find each 'UNIX' and replace with 'bar':
:%s/UNIX/bar/gI

Same command with confirmation:

:%s/UNIX/bar/gIc

How Do I Replace In the Current Line Only?

Find 'UNIX' and replace with 'Linux' in the current line only (note % is removed from substitute command)
       :s/UNIX/Linux/g

NOTE: You need to prefix % the substitute command to make changes on all lines:

     :%s/UNIX/Linux/g

 How Do I Replace All Lines Between line 100 and line 250?

     :{START-n},{END-n}s/word1/word2/g

EX : Find 'UNIX' and replace with 'Linux' all lines between line 100 and line 250, enter
           :100,200s/UNIX/Linux/g
OR
           :100,200s/UNIX/Linux/gc

Task :  Count the word ABC  in entire vi file     :%s/ABC/ABC/g