Oracle Recovery Manager (10g)

ORACLE RMAN (10g)

10g enhancements and some more functionalities.

Connecting to rman:

Host#> rman 

Rman> connect target /

Rman> connect target dbuser as sysdba/passwd@TNS

Rman> connect catalog rman/pass@TNS

Rman> connect auxiliary dbuser/dbpass@TNS

### interactive mode

Host#> rman target / catalog rman/pass@TNS

Host#> rman target / nocatalog

### batch mode

Host#> rman target / catalog rman/rman@d cmdfile <cmd_file_name> log

<log_file_name>

Host#> rman target / @‘daily.rcv’

Rman> report schema

### creating rman catalog

1-  Connect to rman catalog database with sysdba privileges

2-  Create catalog user with unlimited quota and grant

recovery_catalog_owner, connect, resource

3-  Start rman with recovery catalog options

4-  run “create catalog” command in rman

5-  register the target database with “register database” command

Channels:

-- %U = %u_%p_%c

-   %c:  copy number of the backup file

-   %t:  backup set timestamp

-   %n: database name

-   %d:  database name 

-   %p:  backup piece number

Rman>

configure device type disk|sbt parallelism 3; ### max 255

configure default device type to disk;

configure device type disk clear;

configure default device type clear;

configure channel device type disk  Rate 5M;

      Readrate 5M;

      Format=’/disk2/backup/%U’;

      Maxopenfiles=10;

      Maxpiecesize=2G;

### setting disk IO rates

configure channel 1 device type disk rate 5M;

configure channel 2 device type disk rate 1M;

### rac option for backing up

configure channel 1 device type disk connect=’sys/password@node1’;

configure channel 2 device type disk connect=’sys/password@node2’;

### clearing channel settings

configure channel device type disk clear;

configure channel 1 device type disk clear;

### backing up to different disk media

configure channel 1 device type disk format ’/disk1/%U’;

configure channel 2 device type disk format ’/disk2/%U’;

Show all;

Show device type;

Show default device type;

Show channel;

### command blocks

Rman>

Run

{

  Allocate channel c1 device type disk;

  Backup datafile 3,4,5;

}

Backup:

### backup database

Rman>

Backup database include current controlfile;

Backup database plus archivelog [delete all [input]];

Backup [device type disk|sbt] database; 

Backup database tag=‘<tag_name>’;

Backup not backed up since time ‘sysdate-3’ database plus archivelog;

Backup format ‘path/%U’ database fileperset 3;

Backup full database [skip offline|readonly|inaccessible];

### incremental and differential backups

Backup incremental level 0 database;

Backup incremental level 1 database;

Backup incremental level 1 differential database;

Backup incremental level 1 cumulative database;

### overriding backup retentions 

Backup database keep until time “to_date(’03.08.2007’,’DD.MM.YYYY’)”;

Backup database keep forever nologs;

### validation of backups

Backup validate database archivelog all;

### backup parameters

Backup tablespace data_ts filesperset=10 maxsetsize=20G;

Backup tablespace <ts_name>;

### backup backupsets and imagecopies

Backup backupset 12;

Backup backupset completed before ‘sysdate-7’ delete input;

Backup backupset all [delete input];

Backup copy of database delete input;

### configure backup copies

Configure datafile backup copies for device type disk to 3;

Configure archivelog backup copies for device type disk to 3;

Configure datafile backup copies for device type disk clear;

Backup device type disk copies 3 datafile 7 format=

“/temp/%U”,“/data2/backup/%U”,“?/dbs/%U”;

### backup tablespace

Backup tablespace users format=“users_%u%p%c”;

Configure exclude for tablespace <ts_name>;

Backup database; ### will exclude the tablespaces

Backup database noexclude; ### if you dont want to exclude the ts

Configure exclude for tablespace <ts_name> clear;

### backup database archivelog

Backup archivelog all [delete [all] input];

Backup archivelog from sequence 121 until sequence 125;

Backup archivelog from time ‘sysdate-30’ until time ‘sysdate-7’;

### backing up controlfile and spfile

Backup (spfile) (current controlfile);

4 | Page  

Backup current controlfile tag=‘<tag_name>’;

Backup current controlfile format ‘?/oradata/%F.ctl’;

Backup controlfilecopy '/tmp/control01.ctl';

### automatically backup controlfile

Configure controlfile autobackup on | off;

Backup current controlfile tag=’controlfile_27072007’;

Configure controlfile autobackup format [for device type disk] to

'?/oradata/%F.ctl';

### backup datafile 

Backup datafile ‘system.dbf’;

Backup datafile 48;

Backup device type disk datafilecopy ‘/data2/backup/online/system_02.dbf’;

Backup datafilecopy ‘/tmp/data01.dbf’;

### copying database files

Copy [current] controlfile to ‘/tmp/control01.ctl’;

Copy datafile 1 to ‘/tmp/1.dbf’;

Copy datafile  1 to ‘/tmp/1.dbf’,

    2 to ‘/tmp/2.dbf’,

    3 to ‘/tmp/3.dbf’;

Copy datafile 1 to ‘/data2/backup/datafile.dbf’;

### reporting backups

List backup;

List backup of database [summary];

List backup of datafile 48,49,50;

List backup of datafile ‘<datafile_full_path/name>’;

List backup of tablespace users;

List backup of controlfile;

List backup of archivelog all;

List backup of archivelog [summary];

List backup of archivelog from sequence 1000 until sequence 1050;

List copy;

List copy of tablespace <ts_name>;

List copy of archivelog from time=‘sysdate-7’;

### compressed backups

RMAN> Backup as compressed backupset format ‘/oradata/backup/%d_%u.dbf’ 

database;

RMAN> Sql ‘alter database archive log current’;

RMAN> Backup as compressed backupset format ‘..’ archivelog all delete all 

input;

### image copies and backupsets

RMAN> Backup as backupset format ‘../data_06.bus’ datafile 6;

RMAN> Backup as backupset  datafile 6;

RMAN> Backup as copy database;

### incremental restore to imagecopies

RMAN> backup as copy incremental level 0 database tag db_whole_copy;

RMAN> backup incremental level 1 for recover of copy with tag db_whole_copy 

database tag db_copy_upd;

RMAN> recover copy of database with tag db_whole_copy;

RMAN> delete backupset tag db_copy_upd;

Restore & Recover:

### Preparing Recovery

SQL> 

Select Status from v$instance;

SQL> 

Select 

file#, 

status, 

error, 

recover, 

tablespace_name, 

name 

from 

v$datafile_header;

SQL>

Select  

    r.file# as df#, 

    d.name as df_name, 

    t.name as tbsp_name, 

    d.status, 

    r.error, 

    r.change#, 

    r.time

from 

    v$recover_file r, 

    v$datafile d, 

    v$tablespace t

where

    t.ts# = d.ts# and 

    d.file# = r.file#;

### validating backed up database files

Rman>

Restore validate;

Restore database validate;

### recovering full db

Restore database;

Recover database;

Recover database

  # optionally, delete logs restored for recovery and limit disk space used

  Delete archivelog maxsize 100M

  # optionally, skip the recovery of some tablespaces

  Skip tablespace history;  

Recover database [delete archivelog [maxsize=1G]];

### incomplete recovery

run

{ 

  set until time 'Nov 15 2001 09:00:00';

  # you can use until clause with restore or recover commands

  # restore until ‘sysdate-7’;

  # recover until ‘sysdate-7’;

  # alternatively, you can specify SCN

  # set until scn 1000;

  # alternatively, you can specify log sequence number

  # set until sequence 9923;  

6 | Page  

  restore database;

  recover database;

  sql ‘alter database open resetlogs’;

}

### recovering partial db

sql 'alter tablespace <ts_name> offline immediate';

restore tablespace <ts_name>;

recover tablespace <ts_name>;

sql 'alter tablespace <ts_name> online;

### recovering spfile

Rman>

Startup force nomount; # force because spfile is lost

Restore spfile; # if you are using a catalog

Restore spfile from autobackup; # if in NOCATALOG mode

Restore spfile to ‘/tmp/spfile<SID>.ora’ [from autobackup];

Restore spfile to pfile ‘/tmp/spfile<SID>.ora’ [from autobackup];

### restoring controlfile from auto backup

Rman>

Restore controlfile from auto backup; --can be run in nomount mode

### restoring to a new location

### restoring datafiles to a new location

run

{

  sql 'alter tablespace users offline immediate';

  sql 'alter tablespace tools offline immediate';

  # restore the datafile to a new location

  set newname for datafile '?/trgt/users01.dbf' to '/tmp/users01.dbf';

  set newname for datafile '?/trgt/tools01.dbf' to '/tmp/tools01.dbf';

  restore tablespace users, tools;

# point control file to new filenames  

  switch datafile all;

  recover tablespace users, tools;

  sql 'alter tablespace users online;

  sql 'alter tablespace tools online;

}

Rman>

Set newname for datafile ‘?/oradata/dbn/tools01.dbf’ to ‘/tmp/tools01.dbf’;

Restore datafile ‘?/oradata/dbname/tools01.dbf’;

Rman>

Switch datafile ‘/tmp/tools01.dbf’ to datafilecopy ‘<new dbf name>’;

### restoring controlfile to a new location

run

{ 

  # to restore a control file created before a certain date, issue the 

  # following

  # set command using a valid date for 'date_string'. you can also specify 

  # an scn or log sequence number.

  # set until time = 'date_string'; 

  restore controlfile to '/tmp/control01.ctl'; # restore to new location

  # replicate the control file manually to control_files locations

  restore controlfile from '/tmp/control01.ctl';

  startup mount;

}

### restoring archivelogs to a new location

run

{ 

  set archivelog destination to '/oracle/temp_restore';

  restore archivelog all;

  # restore and recover datafiles as needed

}

run 

{ 

  # set a new location for logs 1 through 10.

  set archivelog destination to '/tmp';

  restore archivelog from sequence 1 until sequence 10;

  # set a new location for logs 11 through 20.

  set archivelog destination to '?/oradata';

  restore archivelog from sequence 11 until sequence 20;

  # set a new location for logs 21 through 30.

  set archivelog destination to '?/dbs';

  restore archivelog from sequence 21 until sequence 30;

  restore archivelog from time 'sysdate-20' until time 'sysdate-19';

  restore archivelog from time “to_date(’20.02.2008’,’DD.MM.YYYY HH24:MI:SS’)” 

                    until time “to_date(’22.02.2008’,’DD.MM.YYYY HH24:MI:SS’)”;

  # restore and recover datafiles as needed

}

Oracle Database Architecture in less than 10 minutes

Oracle Data Pump in Oracle Database 10g

For the examples to work we must first unlock the SCOTT account and create a directory object it can access. The directory object is only a pointer to a physical directory, creating it does not actually create the physical directory on the file system.

CONN / AS SYSDBA

ALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK;

CREATE OR REPLACE DIRECTORY test_dir AS '/u01/app/oracle/oradata/';

GRANT READ, WRITE ON DIRECTORY test_dir TO scott;

Table Exports/Imports

The TABLES parameter is used to specify the tables that are to be exported. The following is an example of the table export and import syntax.

expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log

For example output files see expdpEMP_DEPT.log and impdpEMP_DEPT.log.

The TABLE_EXISTS_ACTION=APPEND parameter allows data to be imported into existing tables.

Schema Exports/Imports

The OWNER parameter of exp has been replaced by the SCHEMAS parameter which is used to specify the schemas to be exported. The following is an example of the schema export and import syntax.

expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

impdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log

For example output files see expdpSCOTT.log and impdpSCOTT.log.

Database Exports/Imports

The FULL parameter indicates that a complete database export is required. The following is an example of the full database export and import syntax.

expdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log

impdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=impdpDB10G.log

For an example output file see expdpDB10G.log.

INCLUDE and EXCLUDE

The INCLUDE and EXCLUDE parameters can be used to limit the export/import to specific objects. When the INCLUDE parameter is used, only those objects specified by it will be included in the export/import. When the EXCLUDE parameter is used, all objects except those specified by it will be included in the export/import. The two parameters are mutually exclusive, so use the parameter that requires the least entries to give you the result you require. The basic syntax for both parameters is the same.

INCLUDE=object_type[:name_clause] [, ...]

EXCLUDE=object_type[:name_clause] [, ...]

The following code shows how they can be used as command line parameters.

expdp scott/tiger@db10g schemas=SCOTT include=TABLE:"IN ('EMP', 'DEPT')" directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

expdp scott/tiger@db10g schemas=SCOTT exclude=TABLE:"= 'BONUS'" directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

If the parameter is used from the command line, depending on your OS, the special characters in the clause may need to be escaped, as follows. Because of this, it is easier to use a parameter file.

include=TABLE:\"IN \(\'EMP\', \'DEPT\'\)\"

A single import/export can include multiple references to the parameters, so to export tables, views and some packages we could use either of the following approaches.

INCLUDE=TABLE,VIEW,PACKAGE:"LIKE '%API'"

or

INCLUDE=TABLE

INCLUDE=VIEW

INCLUDE=PACKAGE:"LIKE '%API'"

Network Exports/Imports (NETWORK_LINK)

The NETWORK_LINK parameter identifies a database link to be used as the source for a network export/import. The following database link will be used to demonstrate its use.

CONN / AS SYSDBA

GRANT CREATE DATABASE LINK TO test;

CONN test/test

CREATE DATABASE LINK remote_scott CONNECT TO scott IDENTIFIED BY tiger USING 'DEV';

In the case of exports, the NETWORK_LINK parameter identifies the database link pointing to the source server. The objects are exported from the source server in the normal manner, but written to a directory object on the local server, rather than one on the source server. Both the local and remote users require theEXP_FULL_DATABASE role granted to them.

expdp test/test@db10g tables=SCOTT.EMP network_link=REMOTE_SCOTT directory=TEST_DIR dumpfile=EMP.dmp logfile=expdpEMP.log

For imports, the NETWORK_LINK parameter also identifies the database link pointing to the source server. The difference here is the objects are imported directly from the source into the local server without being written to a dump file. Although there is no need for a DUMPFILE parameter, a directory object is still required for the logs associated with the operation. Both the local and remote users require the IMP_FULL_DATABASE role granted to them.

impdp test/test@db10g tables=SCOTT.EMP network_link=REMOTE_SCOTT directory=TEST_DIR logfile=impdpSCOTT.log remap_schema=SCOTT:TEST

Install Oracle 10g Release 2 on Redhat 5 Enterprise Edition

         Install Oracle 10g Release 2 on Redhat 5 Enterprise Edition

                                                      ORACLE 10G

                                         INSTALLATION ON LINUX REDHAT 5

LOGIN AS ROOT USER

STEP # 1:  SET KERNEL PARAMETERS on SYSCTL.CONF file

type in Terminal

cd /etc

ls

gedit sysctl.conf -->File open on editor mode add the following parameters

kernel.shmall = 2097152

kernel.shmmax = 2147483648

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

fs.file-max = 65536

net.ipv4.ip_local_port_range = 1024 65000

net.core.rmem_default = 262144

net.core.rmem_max = 262144

net.core.wmem_default = 262144

net.core.wmem_max = 262144

•               fs.file-max sets the maximum number of open files that can be handled by the Linux kernel.

•               kernel.shmall determines the total amount of shared memory to be allocated in pages.

•               kernel.shmmax controls the maximum amount of memory to be allocated for shared memory which.

•               kernel.shmmni defines the maximum number of segments system-wide.

•               net.core.rmem_default and net.core.rmem_max define the default and maximum read buffer queue for network operations (1 MB in this example)

•               net.core.wmem_default and net.core.wmem_max define the default and maximum write buffer queue for network operations (256 KB in this example)

•               net.ipv4.ip_local_port_range tells the kernel the port ranges that will be used for outbound connections.

•               kernel.sem has four parameters:

•               SEMMSL – semaphores per array

•               SEMMNS – max semaphores system-wide (SEMMNI*SEMMSL)

•               SEMOPM – max operations per semop call

•               SEMMNI – max number of semaphore arrays

Run the following command in Terminal  to change the kernel parameters

/sbin/sysctl -p

STEP # 2:     SET PARAMETERS on LIMITS.CONF file

cd /etc/security

ls

gedit limits.conf à File open on editor mode add the following parameters

*          soft     nproc            2047

*          hard   nproc             16384

*          soft     nofile             1024

*          hard   nofile             65536

STEP # 3:     ADD PARAMETER ON LOGIN file

cd /etc/pam.d

ls

gedit login à File open on editor mode add the parameters

session         required       pam_limits.so

STEP # 4:     INSTALL REQUIRED RPM FROM LINUX CD’S

From CD # 1

cd /media/cdrom/Server

rpm -Uvh setarch-*

rpm -Uvh make-3*

rpm -Uvh libaio-0*

rpm -Uvh --force tcl-*

rpm -Uvh glibc-*

From CD # 2

Cd /media/cdrom/Server

rpm -Uvh libXp-1*

rpm -Uvh compat-libstdc++-33*

rpm -Uvh compat-libf2c-34-*

rpm -Uvh compat-gcc-34-*

rpm -Uvh compat-gcc-34-c++-3*

rpm -Uvh compat-libstdc++-296*

rpm -Uvh gcc-4*

From CD # 3

Cd /media/cdrom/Server

rpm -Uvh compat-db-4*

rpm -Uvh openmotif-2*

 STEP # 5:  

Create 2 groups:  

groupadd oinstall

groupadd dba

Create a user and assign the above groups to user

useradd -g oinstall -G dba oracle

passwd oracle

Create directories using mkdir command

mkdir -p /u01/app/oracle

Change the ownership of above directories and allow permissions

chown  -R oracle:oinstall  /u01/app

chmod  -R 775  /u01/app

 STEP # 6:     CHANGES ON REDHAT-RELEASE File

cd /etc

mv redhat-release redhat-release.old

gedit redhat-release

Redhat-4

  STEP # 7:     Login As Oracle User

gedit .bash_profile

# Oracle Settings

TMP=/tmp; export TMP

TMPDIR=$TMP; export TMPDIR

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME

ORACLE_SID=ORCL; export ORACLE_SID

ORACLE_TERM=xterm; export ORACLE_TERM

PATH=/usr/sbin:$PATH; export PATH

PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH

CLASSPATH=$ORACLE_HOME/jre:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

if [ $USER = "oracle" ]; then

if [ $SHELL = "/bin/ksh" ]; then

ulimit -p 16384

ulimit -n 65536

else

ulimit -u 16384 -n 65536

fi

fi

source .bash_profile

Logout from Oracle user

STEP # 8:     Login As Root User

create folder @ /u01/app named “10g”

copy all files from cdrom to /u01/app/10g

chmod -R 775 /u01/app/10g

now login as oracle user and run following command

from /u01/app/10g

e.g

cd /u01/app/10g

./runInstaller

STEP # 9:     Run Scripts As Root User (as appear on screen)