Tuesday, November 15, 2011

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');

Saturday, November 12, 2011

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

                 

Wednesday, November 9, 2011

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
Auto Scroll Stop Scroll