Oracle Networking Connecting two Nodes

Node1 :
——-

#vi /etc/hosts

172.16.128.151 node1.oracle.com node1
172.16.128.152 node2.oracle.com node2

:wq

Node2 :
——-

#vi /etc/hosts

172.16.128.151 node1.oracle.com node1
172.16.128.152 node2.oracle.com node2

:wq

Note:If two systems are in networking , we can configure one system in to another.

i.e: ssh node2 (ssh= secured shell)

root@node1> # ping node2.oracle.com (or) ping node2

root@node1> ssh node2
password: ********

root@node2> # su – oracle
password: ********

root@node2># su – oracle

oracle@node2> $ export ORACLE_SID=hrms

oracle@node2> $ sqlplus / as sysdba

SQL> startup

sql> exit

oracle@node2>$ cd $ORACLE_HOME/network/admin

admin>$ ls
listener.ora samples

admin>$ which netca

admin>$ netca

admin>$ exit

node2> xhost +

node2> exit ( enter into client machine)

node1> xhost +

root@node1># ssh -Y oracle@node2

oracle@node2>$ netca

.listener configuration > next
.add > next
.Listener name= listener > next
.TCP > next
.use standard port number=1521 >next
.would you like to configure another listener = no > next
.Listener configuration complete > next
.Finish

oracle@node2>$ cd /u01/app/oracle/product/11.2.0/db_home/network/admin

admin> ls
listener.ora samples

oracle@node2 admin> vi listener.ora

Note: Dynamic listener no contains SID, so if the listener can provide
accesibility to all databases.
and when we use static listener we mentioned SID manually.

oracle@node2 admin>$ cd samples

oracle@node2 samples>$ ls
listener.ora tnsnames.ora sqlnet.ora

After opening the file set the line numbers:
:set nu

:33,46 w >> /u01/app/oracle/product/11.2.0/db_home/network/admin/listener.ora
:q

oracle@node2 samples>$ cd ..

oracle@node2 admin>$ vi listener.ora

LISTENER=
(DESCRIPTION_LIST=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=node2.oracle.com)(PORT=1521)
)
)
ADR_BASE_LISTENER=/u01/app/oracle
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=hrms)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_home)
)
)
:wq

oracle@node2 admin>$ lsnrctl start listener
admin>$ lsnrctl status listener

To connect to client Machine:
——————————–

root@node1># su – oracle

oracle@node1>$ cd /u01/app/oracle/product/11.2.0/db_home/network/admin/

oracle@node1 admin>$ netca

.click on local net service name configuration > next
.Add > next
.service=hrms > next
.TCP > next
.Hostname:node2.oracle.com
.use standard port=1521 > next
.no doesnt > next
.Net service name=to _hrms > next
( we can write any name)

.next
.finish

oracle@node1 admin> $ vi tnsnames.ora

TO_HRMS=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=node2.oracle.com)(PORT=1521))
)

(CONNECT_DATA=
(SERVICE_NAME=hrms)
)
)

oracle@node1 admin>$ tnsping to_hrms

oracle@node2 admin>$ stop listener

oracle@node1 admin>$ tnsping to_hrms

oracle@node2>$ ps -ef|grep smon

oracle@node1>$ sqlplus scott/tiger@to_hrms

(Now we can enter as an scott user)

SQL> exit

Now we will enter as sys user but we forget my password of sys user.

oracle@node2>$ cd $ORACLE_HOME/dbs

oracle@node2 dbs> orapwd file=orapwdhrms password=oracle entries=3 force=y

oracle@node2 dbs>$ cd

oracle@node1>$ sqlplus sys/oracle@to_hrms as sysdba

SQL>

Advertisements

RMAN Backup and Recovery Scenarios

Recovery Management (RMAN)
—————————————-

API:DBMS_rcvman, DBMS_rcvcat,DBMS_backup_restore

VIEWS:RC_database,v$rman_configuration,v$rman_status,
v$session, v$rman_backup_job_details

BACKUP RETENTION: No. of backup sets, No. of days

FILES: Backup pieces

CONFIGURATION MODES: NO Catalog Mode, Catalog Mode

BACKUP DESTINATION: Disk ,Tape, NFS

3rd PARTY MML SOFTWARES: Tivoli Data Protector for Oracle
HP data protector for Oracle
Veritas, Legato.

BACKUP TYPES: Full Backup, Incremental/Diffrential Backup, Compressed Backup.

10g/11g New Features: Block Change Tracking (10g),
Compressed Backup (10g),
Backup Encryption (10g),
Channel Failover (10g),
Virtual Private Catalog (11g),
Multi Session Backup (11g).

—————————————————————————
>In catalog mode RMAN keeps the backup information in the reusable section
of the target database controlfile.

>The retention of information in the reusable section of the target database
control file depends on the parameter:

control_record_keep_time [default value is 7 days].

>Even if you specify a bigger value,retention may not be guaranteed since,
controlfile grows upto operating system limitation.

———————————————————————
RMAN INCREMENTAL BACKUP
———————————————————————

>In case of incremental backup, RMAN takes the backup of only modified blocks.

>The different levels of incremental are:
1.Level 0 [Complete]
2.Level 1 [Incremental]
3.Level 2 [Cumulative]

>In order to take incremental backup, first we need to take level 0 backup.

>Without taking level 0, if we initiate level 1, by dfeault RMAN takes
level 0 backup.

>In order to minimize incremental backup time, In 10g oracle has introduced
block change tracking.

SQL> alter database enable block change tracking using file ‘/opt/changetrack.file’;

>From 10.2.0.4 ,Levels greater than 0 and 1 have become obsolete.

RMAN> backup incremental level 0 database;
RMAN> backup incremental level 1 database;
RMAN> backup incremental level 2 database;
(or)
RMAN> backup incremental level 1 cumulative database;

————————————————-
Weekly Backup Script
————————————————-

RMAN> create script weekly-backup
SQL ‘alter system switch logfile’;
backup incremental level 0 database plus archivelog delete all input;
SQL ‘alter system switch logfile’;

RMAN>Run {
execute script weekly-backup;};

RMAN>crosscheck archivelog all;
RMAN>backup database plus archivelog delete all input;

—————————————————————————————–
RMAN NO CATALOG MODE (BACKUP & RECOVERY SCENARIOS)
—————————————————————————————–

root@node1> su – oracle
oracle@node1> export ORACLE_SID=hrms
oracle@node1> sqlplus / as sysdba

SQL> startup

oracle@node1> export ORACLE_SID=hrms
oracle@node1> which rman
oracle@node1> rman target /
(or)
oracle@node1> rman nocatalog target /
(or)
oracle@node1> rman
RMAN >connect target /
RMAN> shutdown immediate

oracle@node1> rman target /

RMAN> startup
RMAN> show all;

SQL> select * from v$rman_configuration;

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
RMAN> show all;

RMAN> spool log to rman.log
RMAN> show all;
RMAN> spool off;
RMAN> exit

oracle@node1> vi rman.log
:set nu
1.CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
2.BACKUP OPTIMIZATION ON; (it takes the backup of read only files at first time)
3.DEVICE TYPE TO DISK;
4.CONTROL FILE AUTOBACKUP ON;
5……….AUTO BACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/U03/rmanbackup/%F’
(default it takes ORACLE_HOME/dbs)
at the end of file,

6.CONFIGURE ENCRYPTION FOR DATABASE ON/OFF;
7.CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1/2;

(takes the copies of backup in archivelog mode)

8.CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUP SET;

CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT ‘/u03/rmanbackup/%U’;
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT ‘/u03/rmanbackup/%U’;
:wq

Create a structure of RMAN Backup:
———————————————-

root@node1>mkdir -p /u03/rmanbackup
root@node1>chown -R oracle:oinstall /u03/rmanbackup
root@node1>chmod -R 775 /u03/rmanbackup

root@node1> su – oracle

oracle@node1>export ORACLE_SID=hrms
oracle@node1> rman target /

RMAN> @rman.log

RMAN>show all;

RMAN>report schema;

RMAN>report need baackup;

—————————————————————-
Taking Backups and Recovery Scenario’s
—————————————————————-
1.Take the backup of the datafile.
——————————————

RMAN> backup datafile 4;

oracle@node1 rmanbackup> ls

RMAN > report need backup;

To see obsolete Backup:
——————————-

RMAN> report obsolete;

RMAN> backup datafile 4;

RMAN> report need backup;

RMAN> backup datafile 4;

oracle@node1 rmanbackup> ls

RMAN> report obsolete;

RMAN> show all;

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 3;

RMAN> report obsolete;

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

RMAN>report obsolete;
2.Delete all obsolete backup’s :
—————————————

RMAN> delete obsolete;

RMAN> delete noprompt obsolete;

RMAN> report obsolete;

3.To See Existing Backups:
———————————–

RMAN> List backup;

RMAN> List backup summary;

4.Deleting the backup:
—————————-

RMAN> delete noprompt backup;

(automatically it deletes os level backup also)

oracle@node1 rmanbackup> ls

RMAN> list backup;

RMAN> backup datafile 4;

RMAN> list backup;

RMAN> delete backupset 7;

RMAN> list backup;

5.Remove File at OS Level:
———————————–

oracle@node1 rmanbackup> rm * (or) rm c-73…………….

RMAN> List backup;

RMAN> crosscheck backup;

(It checks whether the backup is available at os level or not)

RMAN> list backup;

RMAN> delete noprompt expired backup;

RMAN> list backup;

RMAN> report schema;

6.Taking backup of tablespaces:
—————————————–

RMAN> backup tablespace users;

RMAN> backup current controlfile;

RMAN> backup spfile;

RMAN> backup archivelog all;

RMAN> List archivelog all;

7.Taking Full database backup:
——————————

RMAN> backup database plus archivelog;

—————————————————————
RECOVERY SCENARIOS:
—————————————————————
1. Loss of Full Database:
——————————–

RMAN> report schema;

oracle@node1> cd /u02/app/oracle/hrms/
hrms>rm *
hrms>cd /u01/app/oracle/fast_recovery_area/hrms/

RMAN> validate database; (If database is not in proper way)

RMAN> shutdown abort;

RMAN> startup nomount;

RMAN> restore controlfile from ‘/u03/rmanbackup/c-765432———‘;

RMAN> alter database mount;

RMAN> restore database;

RMAN> recover database;

RMAN> sql ‘alter database open resetlogs’;

2.Loss of non-system datafiles:
—————————————-

oracle@node1> export ORACLE_SID=hrms
oracle@node1> rman target /

RMAN> report schema;

oracle@node1> cd /u02/app/oracle/hrms/
hrms>ls
hrms>
hrms> rm users01.dbf

RMAN> validate database;

(Note:its a non-system datafile so no need to shutdown)

RMAN> sql ‘alter database datafile 4 offline’;

RMAN> restore datafile 4;

RMAN> recover datafile 4;

RMAN> sql ‘alter datbase datafile 4 online’;

RMAN> validate database;

RMAN> report schema;

(or) Method:2

RMAN> report schema;

oracle@node1> cd /u02/app/oracle/hrms/
hrms> ls
hrms> rm users01.dbf

RMAN> validate database;

RMAN> List failure;

RMAN> advise failure;

RMAN> repair failure;

3:Loss of system datafile:
———————————

RMAN> report schema;

oracle@node1> cd /u02/app/oracle/hrms/
hrms> ls
hrms> rm system01.dbf

RMAN> validate database;

RMAN> shutdown abort;

oracle@node1> export ORACLE_SID=hrms

oracle@node1> rman target /

RMAN> startup mount;

RMAN> restore datafile 1;

RMAN> recover datafile 1;

RMAN> alter database open;

4.Loss of controlfiles:
—————————-

oracle@node1> rman target /

oracle@node1> export ORACLE_SID=hrms

oracle@node1> sqlplus / as sysdba

oracle@node1> cd /u02/app/oracle/hrms/

oracle@node1> ls

oracle@node1> rm comtrol01.ctl

oracle@node1> cd /u01/app/oracle/fast_recovery_area/hrms/

oracle@node1> rm control02.ctl

RMAN> validate database;

RMAN> shut abort;

error:

oracle@node1> export ORACLE_SID=hrms

oracle@node1> sqlplus / as sysdba

SQL> shut abort

oracle@node1> rman target /

RMAN> startup nomount

RMAN> restore controlfile from ‘/u03/rmanbackup/c-76539872———‘;

RMAN> alter database mount;

RMAN> recover database;

RMAN> sql ‘alter database open resetlogs’;

5.Loss of Redo Log Files:
———————————

oracle@node1> cd /u02/app/oracle/hrms/
hrms> ls
hrms> rm *.log

RMAN> shutdown abort;
RMAN> startup mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open;

Error: ORA-01110 datafile 1: ‘/u02/app/oracle/hrms/system01.dbf’

RMAN> List Failure;

RMAN> advise failure;

Repair script: /u01/…………….

RMAN> @ /u01/app/oracle…………………………../reco………hm

oracle@node1> cat /u01/appp/oracle………/reco/hm

RMAN> alter database open resetlogs;

Datapump Export / Import Scenarios

>Data pump was introduced from 10 version of oracle.

>Utilities of datapump are:
1.expdp
2.impdp

>Dump file generated by expdp cant be imported by imp.

>The pre-requisite for data pump is we need to create a directory at the
server side at oracle level as well as os level.

>In order to take backup of objects user should have read,write privilege on the directory.

Practical / Demo:
—————–

root@node1>su – oracle
oracle@node1>export ORACLE_SID=sample
oracle@node1>sqlplus / as sysdba

SQL>startup
SQL>select name,open_mode,log_mode from v$database;
SQL>select instance_name,status from v$instance;

oracle@node1>export ORACLE_SID=sample
oracle@node1>which expdp
oracle@node1>which impdp
oracle@node1>expdp dumpfile=sys.dmp logfile=sys.log
username:/ as sysdba

SQL> select name from v$tablespace;
SQL>create tablespace temp03
tempfile ‘/u03/app/oracle/sample/temp03.dbf’ size 10m;

oracle@node1>expdp dumpfile=sys.dmp logfile=sys.log
username: / as sysdba

SQL>select count(*) from tab;

error: if dumpfile exist so we change name and enter again.

oracle@node1>expdp dumpfile=sys.dmp logfile=sys1.log
username: / as sysdba

SQL> select count(*) from tab;
SQL> create directory dp as ‘/u03/datapump’;
SQL> select * from dba_directories;

root@node1> mkdir -p /u03/datapump
root@node1> chown -R oracle:oinstall /u03/datapump
root@node1> chmod -R 775 /u03/datapump
root@node1> su – oracle

oracle@node1> export ORACLE_SID=sample

————————————————————
Taking Backups – EXPORTING
————————————————————
1.Full database backup
———————–

SQL>select count(*) from tab;

oracle@node1>expdp dumpfile=fulldb.dmp logfile=fulldb.log
full=y directory=dp job_name=fullbackup direct=y

username:/ as sysdba

SQL>select count(*) from tab;
SQL>select * from dba_datapump_jobs;

Stopping the job:

press ctrl+c

Export>stop_job=immediate
Are your sure stop the job:Yes

SQL>select * from dba_datapump_jobs;

To Restart particular job after some time:
——————————————

oracle@node1>expdp attach=fullbackup

username:/ as sysdba

Export> continue_client

2.Taking User Level Backup:
—————————

oracle@node1>expdp dumpfile=user.dmp logfile=user.log schemas=scott
directory=dp

username:/ as sysdba

3.Taking Table Level Backup:
—————————-

oracle@node1>expdp dumpfile=table.dmp logfile=table.log tables=emp,dept
directory=dp

username:scott/tiger

Error: No such permisssion on directory dp, so we give permission to user scott
and try again.

SQL> grant read,write on directory dp to scott;

oracle@node1>expdp dumpfile=table.dmp logfile=table.log tables=emp,dept
directory=dp

username: scott/tiger

4.Taking row level backup:
————————–

oracle@node1>expdp dumpfile=row.dmp logfile=row.log tables=emp
directory=dp query=’where deptno=10′

username:scott/tiger

4.Taking Metadata backup:
————————-

oracle@node1>expdp dumpfile=meta.dmp logfile=meta.log tables=emp rows=n
directory=dp

username: scott/tiger

5.Taking Tablespace level backup:
———————————

SQL>select name from v$tablespace;

oracle@node1>expdp dumpfile=ts.dmp logfile=ts.log tablespaces=users
directory=dp

username:/ as sysdba

————————————————————————–
APPLYING BACKUPS – IMPORTING
————————————————————————–
1.User Level Importing
———————–

SQL>drop user scott cascade;
SQL>select username from all_users where username=’SCOTT’;

oracle@node1>impdp dumpfile=user.dmp remap_schema=scott:scott

directory=dp

username:/ as sysdba

SQL>select username from all_users where username=’SCOTT’;

oracle@node1>impdp dumpfile=user.dmp remap_schema=scott:u1

username:/ as sysdba

2.Table Level Importing: (First Drop emp and dept tables)
———————————————————-

oracle@node1>impdp dumpfile=table.dmp remap_schema=scott:u6
directory=dp

username:/ as sysdba

U6> select * from tab;

3.Row Level Importing:
———————-

oracle@node1> impdp dumpfile=row.dmp remap_schema=scott:u6

directory=dp

username: / as sysdba

U6> select * from tab;

4.Importing Metadata:
———————

oracle@node1> impdp dumpfile=meta.dmp remap_schema=scott:u6
directory=dp

username:/ as sysdba

u6> select * from tab;
u6> select * from emp;
u6> desc emp;

Frequently used SQL Queries for ASM

Few frequently used SQL queries related to ASM :

1) Create Disk Group:

Create Disk groups using the CREATE DISKGROUP statement and specify the level of redundancy.

Disk group redundancy types:-

NORMAL REDUNDANCY – Two-way mirroring, requiring two failure groups.
HIGH REDUNDANCY –  Three-way mirroring, requiring three failure groups.
EXTERNAL REDUNDANCY – No mirroring for disks that are already protected using hardware RAID or mirroring.

Example 1 : External Redundancy

SQL> create diskgroup DATA external redundancy disk ‘/dev/oracleasm/disks/DISK1′ name DATA_1;

Example 2 : Normal Redundancy

SQL> CREATE DISKGROUP data NORMAL REDUNDANCY
 FAILGROUP failure_group_1 DISK ‘/dev/oracleasm/disks/DISK2′ NAME DATA_2,’/dev/oracleasm/disks/DISK3′ NAME DATA_3,
 FAILGROUP failure_group_2 DISK ‘/dev/oracleasm/disks/DISK4′ NAME DATA_4,’/dev/oracleasm/disks/DISK5′ NAME DATA_5;

2) Drop Disk Group:

Using DROP DISKGROUP statement.

SQL> DROP DISKGROUP data INCLUDING CONTENTS;

3) Alter Disk Group:

Add or remove disks from disk groups Using ALTER DISKGROUP statement. You can also use wildcard “*” to reference disks.

   3.1) Add a disk.

 SQL> ALTER DISKGROUP data ADD DISK ”/dev/oracleasm/disks/DISK6′ ;

 3.2) Drop/remove a disk.

 SQL> ALTER DISKGROUP data DROP DISK DATA_5;

  3.3) Undrop disk

The UNDROP command used to undo only pending drop of disks. After you drop the disks you cannot revert.

SQL> ALTER DISKGROUP data UNDROP DISKS;

  3.4) Diskgroup Rebalance:
Disk groups can be rebalanced manually Using REBALANCE clause and you can modify the POWER clause default value.

SQL> ALTER DISKGROUP DATA REBALANCE POWER 8;

  3.5) MOUNT and DISMOUNT DiskGroups:
Normally Disk groups are mounted at ASM instance startup and dismounted at shutdown.

Using MOUNT and DISMOUNT options you can make one or more Disk Groups available or unavailable.

 SQL> ALTER DISKGROUP data MOUNT;
 SQL> ALTER DISKGROUP data DISMOUNT;
 SQL> ALTER DISKGROUP ALL MOUNT;
 SQL> ALTER DISKGROUP ALL DISMOUNT;

 3.6) DiskGroup Check:

Use CHECK ALL to verify the internal consistency of disk group metadata and repair in case of any error.

SQL> ALTER DISKGROUP data CHECK ALL;

 3.7) DiskGroup resize:
Resize the one or all disks in the Diskgroup.

Resize all disks in a failure group.

SQL> ALTER DISKGROUP data RESIZE DISKS IN FAILGROUP failure_group_1 SIZE 1024G;

Resize a specific disk.

SQL> ALTER DISKGROUP data RESIZE DISK DATA_0006 SIZE 100G;

Resize all disks in a disk group.

SQL> ALTER DISKGROUP data RESIZE ALL SIZE 100G;

4) To find ASM Diskgroup and Disks status

set lines 132
col name format a14
col PATH format a33
select GROUP_NUMBER,NAME,STATE,TYPE,TOTAL_MB,FREE_MB,VOTING_FILES from v$asm_diskgroup;
GROUP_NUMBER NAME      STATE   TYPE  TOTAL_MB FREE_MB V
------------ ------------------------------ ----------- ------ ---------- ---------- -
1           OCR_VOTE MOUNTED EXTERN 152999    152603   Y
2           DB_DATA  MOUNTED EXTERN 812000   810198  N
select GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,VOTING_FILE,name,path from v$asm_disk;
 GROUP_NUMBER  DISK_NUMBER MOUNT_S HEADER_STATU MODE_ST STATE V     NAME              PATH
------------ ----------- ------- ------------ ------- -------- - -------------- ---------------------------------
1             0         CACHED MEMBER         ONLINE NORMAL Y OCR_VOTE_0 /dev/oracleasm/disks/OCR_VOTE01
2             3         CACHED MEMBER         ONLINE NORMAL N DB_DATA_3 /dev/oracleasm/disks/DB_DATA04
2             2         CACHED MEMBER         ONLINE NORMAL N DB_DATA_2 /dev/oracleasm/disks/DB_DATA03
2             1         CACHED MEMBER         ONLINE NORMAL N DB_DATA_1 /dev/oracleasm/disks/DB_DATA02
2             0         CACHED MEMBER         ONLINE NORMAL N DB_DATA_0 /dev/oracleasm/disks/DB_DATA01
6 rows selected.

Cold Backup and Scenarios

Cold Backup Procedure:
———————————————-
Steps:
——
1.shut immediate
2.create required backup directory structure
3.copy CRD files to backup destination
4.start the database

root@node1># su – oracle
oracle@node1>$ cat /etc/oratab
oracle@node1>$ export ORACLE_SID=hrms
oracle@node1>$ sqlplus / as sysdba

SQL>startup

SQL>select name,open_mode,log_mode from v$database;

SQL>select instance_name,status from v$instance;

SQL>select name from v$datafile;

SQL>select name from v$controlfile;

SQL>select member from v$logfile;

step:1
——

SQL>shut immediate;

oracle@node1>$ cd /u02/app/oracle/hrms/
oracle@node1 hrms>$ cd

step:2
——

root@node1>mkdir -p /u03/coldbkp
root@node1>chown -R oracle:oinstall /u03/coldbkp
root@node1>chmod -R 777 /u03/coldbkp

step:3
——
oracle@node1 hrms>$ cp *.* /u03/coldbkp
hrms>$ cp control02.ctl /u03/coldbkp/

step:4
——
SQL>startup

SQL>archive log list;

SQL>alter user scott account unlock identified by tiger;

SQL>conn scott/tiger

SQL>insert into salgrade select * from salgrade;

SQL>/
/
/

SQL>commit;

SQL>archive log list;

SQL>select count(*) from scott.salgrade;

SQL>alter system switch logfile;
—————————————————-
Scenario:1 (Loss of full Database)
—————————————————-
>Check the locations
SQL>select name from v$datafile;
SQL>select name from v$controlfile;
SQL>select member from v$logfile;

Oracle@Node1>cd /u02/app/oracle/hrms
hrms>ls
hrms>rm *
hrms>cd /u01/app/oracle/fast_recovery_area/hrms
hrms>ls
hrms>rm control02.ctl
SQL>select name from v$controlfile;
SQL>conn scott/tiger
error:ORA-27041 unable to open the file
SQL>conn /as sysdba
SQL>shut abort

step:1
——
oracle@node1>$ cd /u03/colbkp
coldbkp>$ls
coldbkp>cp *.* /u02/app/oracle/hrms
coldbkp>$ cp control02.ctl /u01/app/oracle/fast_recovery_area
step:2
——
SQL>startup mount

SQL>alter database recover automatic using backup controlfile until cancel;
error:ORA-27037 unable to obtain file status
Note:If it ask next archivelog file,when we give recover cancel upto that
it takes archivelogs.
SQL>recover cancel;

SQL>alter database open;

SQL>select open_resetlogs from v$database;

SQL>alter database open resetlogs;

—Again we take the backup for the new incarnation number.

SQL>shut immediate;

oracle@node1 hrms>$cd /u02/app/oracle/hrms/
hrms>ls
oracle@node1 hrms>$cp *.* /u03/coldbkp/
hrms>$ ls
oracle@node1 hrms> cp control02.ctl /u03/coldbkp/

SQL>startup

—————————————————————————————-
Scenario:2 (Loss of Non-System Datafile)
—————————————————————————————-
SQL>Select name from v$datafile;
oracle@node1>cd /u02/app/oracle/hrms
hrms>ls
oracle@node1 hrms>rm users01.dbf
—-for knowing if database is there or not
SQL>conn scott/tiger
SQL>insert into salgrade select * from salgrade;
ORA-01116 error in opening database file 4
ORA-01110 datafile 4 /u02/app/oracle/hrms/users01.dbf

SQL>conn /as sysdba
SQL>desc v$datafile;
SQL>select file#,error,status from v$datafile_header;

4 cannot open file ONLINE
steps:1
——-
SQL>alter database datafile 4 offline;
SQL>select * from v$recover_file
4 OFFLINE

step:2
——-
oracle@node1>$ cd /u03/coldbkp/
coldbkp>$ cp users01.dbf /u02/app/oracle/hrms/

Note:Before recovering the datafile,we check these commands.

SQL>select file#,checkpoint_change#, from v$datafile_header;
file# checkpoint_change#
—– ——————
1 782497
2 782497
3 782497
4 782497

SQL>save dfh.sql
SQL>select file#,checkpoint_change# from v$datafile;
(it gets information from controlfile)
SQL>save df.sql

step:3
——
SQL>recover datafile 4;
(media recovery complete)
SQL>alter database datafile 4 online;

SQL>@dfh.sql

SQL>@df.sql

(both script value should match)

——————————————————————————————–
Scenario:3 (Loss of system datafile)
——————————————————————————————–

SQL>select name,open_mode from v$database;
SQL>select name from v$datafile;

Another terminal:
—————–
root@node1> su – oracle
oracle@node1>cd /u02/app/oracle/hrms/
hrms>ls

oracle@node1 hrms> rm system01.dbf

SQL>shut abort

oracle@node1>cd /u03/coldbkp
coldbkp>cp system01.dbf /u02/app/oracle/hrms/

SQL>startup mount;

SQL>recover database;

SQL>alter database open;

——————————————————————————————–
Scenario:4 (Loss of Control Files)
——————————————————————————————–

SQL>select name from v$controlfile;

SQL>alter system switch logfile;

SQL>cd /u02/app/oracle/hrms/

hrms>$ ls

hrms>$ rm *.ctl

hrms>cd /u01/app/oracle/fast_recovery_area/hrms/

hrms>ls

hrms>rm control02.ctl

SQL>shut abort;

oracle@node1>$ cd /u03/coldbkp/

oracle@node1 coldbkp>$ ls

oracle@node1 coldbkp>$ cp control01.ctl /u02/app/oracle/hrms/

oracle@node1 coldbkp>$ cp control02.ctl /u01/app/oracle/fast_recovery_area/hrms/

SQL>startup mount;

SQL>recover database using backup controlfile until cancel;

specify log:auto

ORA-10879:error signaled
ORA-01547 WARNING….
ORA-01194:FILE 1 needs more recovery…

SQL>recover cancel;

SQL>select member from v$logfile;

SQL>recover database using backup controlfile until cancel;

specify log:
/u02/app/oracle/hrms/redo03.log
(again it will throw error so again cancel the recovery)

SQL>recover cancel;

SQL>recover automatic using backup controlfile until cancel;

specify log:
/u02/app/oracle/hrms/redo02.log

log applied
Media recovery completed

SQL>ALTER DATABASE OPEN RESETLOGS;

SQL>Shut immediate

Now again we will take the coldbkp of database and controlfile

oracle@node1 hrms>$ cp * /u03/coldbkp

oracle@node1 hrms>$cd /u01/app/oracle/fast_recovery_area/hrms

oracle@node1 hrms>$ cp control02.ctl /u03/coldbkp

——————————————————————————————
Scenario:5 (Loss of Redolog File)
——————————————————————————————

SQL>startup;

SQL>select member from v$logfile;

oracle@node1>$ cd /u02/app/oracle/hrms/

oracle@node1 hrms>$ rm *.log

SQL>shut abort

oracle@node1>$ cd /u03/coldbkp/

oracle@node1 coldbkp>$ cp *.dbf /u02/app/oracle/hrms/

SQL>startup mount;

SQL>recover database until cancel;

SQL>alter database open resetlogs;

SQL>shut immediate;

oracle@node1>$ cd /u02/app/oracle/hrms/

oracle@node1 hrms>$ cp *.* /u03/coldbkp/

SQL>startup;

—————————————————–
Scenario:6 (Loss of Datafile which was not in backup)
—————————————————–

SQL>select name from v$datafile;

SQL>create tablespace ssss
datafile ‘/u02/app/oracle/hrms/ssss01.dbf’ size 5m;

SQL>create user u1 identified by u1 default tablespace ssss;

SQL>grant connect,resource to u1;

SQL>conn u1/u1;

SQL>create table a (a number);

SQL>insert into a values(1);

SQL>insert into a select * from a;

SQL>/
/
/
/
/
/

SQL>commit;

SQL>select count(*) from a;

SQL>conn /as sysdba;

SQL>select name from v$datafile;

oracle@node1>$ cd /u02/app/oracle/hrms/

hrms>ls

hrms>rm ssss01.dbf

SQL>select name,file# from v$datafile;

SQL>alter database datafile 5 resize 6m;

ORA-01565 error in identifying file….

SQL>alter database datafile 5 offline;

SQL>alter database create datafile
‘/u02/app/oracle/hrms/ssss01.dbf’;

SQL>recover datafile 5;

SQL>alter database datafile 5 online;

SQL>conn u1/u1

SQL>select count(*) from a;

——————————————————————–
Scenario:7 (Performing point in time recovery)
——————————————————————–

SQL>select sysdate from dual;

SQL>set time on;

SQL>select username from all_users where username=’SCOTT’;

SQL>drop user scott cascade;

SQL>shut immediate;

oracle@node1>$ cd /u03/coldbkp/

oracle@node1 hrms>$ cp *.* /u02/app/oracle/hrms/

oracle@node1 hrms>$cp control02.ctl /u02/app/oracle/fast_recovery_area/hrms/

SQL>startup mount;

SQL>alter database recover automatic using backup controlfile until time
’25-APR-2009 22:57:23′;

SQL>alter database open;

SQL>alter database open read only;

SQL>recover cancel;

SQL>alter database open read only;

SQL>conn scott/tiger

SQL>conn /as sysdba

SQL>shut immediate;

SQL>startup mount;

SQL>alter database open resetlogs;

PATCHING AND UPGRADING 10.2.0.1 RAC Environment to 10.2.0.4

PATCHING AND UPGRADING 10.2.0.1 RAC Environment to 10.2.0.4

>Patch is a bug fix

>collection of bug fixes is called a patch set

>Different types of patches released by oracle are:
1.Interim patch / one-off patch
2.Patch sets
3.Critical patch updates.
4.Patchset updates
5.CRS Hash bundle patches

>All above patches can be installed using Opatch utility except Patchsets.

>Patchsets are installed by invoking runinstaller.

>CRS_HASH bundle patches are patches to fix the bugs in the cluster.

>Clusterware can be patched in two ways
1.Rolling upgrade
2.Non-Rolling upgrade

>Incase of rolling upgrade we will bringdown all the services on then ode that we
wish to install the patch set.This is node by node activity.

>In case of non-rolling upgrade we bring down the entire cluster and install the patch set.

>To know the list of patches installed in CRS_HOME
$ Opatch lsinventory -detail $ORA_CRS_HOME

>To know the list of patches installed in ORACLE_HOME
$ opatch lsinventory -detail $ORACLE_HOME