ORA-15014: path ‘/dev/disk1′ is not in the discovery set

ORA-15014: path ‘/dev/disk1′ is not in the discovery set

ORA-15014: path ‘/dev/disk1′ is not in the discovery set

First we need to check with system administrators to name the disks for the RAC 11gR2 installation using one common naming convention like say – disk1,disk2,disk3.....

SQL> create diskgroup DATA external redundancy disk ‘/dev/disk1’ force;
create diskgroup DATA external redundancy disk ‘/dev/disk1’ force
*
ERROR at line 1:
ORA-15018: diskgroup cannot be created
ORA-15031: disk specification ‘/dev/disk1’ matches no disks
ORA-15014: path ‘/dev/asm_disk2′ is not in the discovery set

SQL> alter system set asm_diskstring=’/dev/disk1′;
alter system set asm_diskstring=’/dev/disk1’
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-15014: path ‘/dev/ocr_disk1′ is not in the discovery set

SQL> alter system set asm_diskstring=’/dev/disk*’ sid=’*’;

System altered.

SQL> create diskgroup DATA external redundancy disk ‘/dev/asm_disk1’;

Diskgroup created.

ASMlib for RHEL6

ASMlib for RHEL6

ASMLIB is available for OEL Versions even after 6 but not in ant other linux platforms.

Oracle has not yet released ASMlib for RHEL6 yet.

Oracle's policy about this can be found in the metalink document id 1089399.1 

Regarding ASMLIB Oracle mentioned in note 1089399.1 :

For RHEL 6, Oracle will provide ASMLib software and updates onlywhen configured with a kernel distributed by Oracle.

Oracle will not provide ASMLib packages for kernels distributed by Red Hat as part of RHEL 6.

ASMLib updates will be delivered via Unbreakable Linux Network (ULN), which is available to customers with Oracle Linux support.

ULN works with both Oracle Linux or Red Hat Linux installations, but ASMLib usage will require replacing any Red Hat kernel with a kernel provided by Oracle.

=-=-=-=-=-=-=-=-=-=

Instead of ASMLIB , We can use Udev for oracle 11G clusters. you can refer to ID 371814.1

Issue with using ASMLIB with Multipath Disks

Issue with using ASMLIB with Multipath Disks

During Grid infrastructure installation root.sh ran successfully in first root and it errored in the second node with the following message as it could not recognize the voting disk from second node.

Error

====

[cssd(17275)]CRS-1709:Lease acquisition failed for node vsnlpmdb02 because no voting file has been configured; Details at (:CSSNM00031:) in /orabin/app/11.2.0/grid/log/vsnlpmdb02/cssd/ocssd.log

Explanation of fix

=============

ASM cannot handle seeing the same disk twice. If it does, it will cause an error. A single disk can appear three times in a multipath configuration:

    The first path to the disk

    The second path to the disk

    The multipath access point

Here's an example: say a system has one local disk, /dev/sda, and one disk attached via external storage. The machine has two connections, or paths, to that external storage. The Linux SCSI driver will see both paths. They will appear as /dev/sdb and /dev/sdc. The system may access sdb or sdc, the access ends up in the same place.

If multipathing is enabled, there will be a multipath disk, for example /dev/multipatha, that can access both paths. That is, any I/O to multipatha can use either path. If a system were using the sdb path, and that cable is unplugged, the system will get an error. But the multipath disk will know to switch to the sdc path.

Most system software will be unaware of the multipath configuration. It can use any of the paths, sdb, sdc, or multipatha, and it won't know the difference. ASMLib is the same way. The default configuration does not care which path it uses.

ASMLib will choose only one of the paths, because ASM cannot handle seeing the same disk twice. This solves the first issue. ASM only sees one path, and it is happy. Here is the second issue: which path does ASM see? In its default configuration, ASMLib will choose the first path it finds. This is the first path as reported by Linux. Depending on your storage driver, it could be the multipath, or it could be one of the single paths.

The system administrator wants ASMLib to always use the multipath disk. What's the point of having it if Oracle is not using it? There is no way, however, for ASMLib to know what a multipath looks like. It must be told via its configuration in the following way.

Solution

======

Scan Multipath Disks First

====================

The system administrator configures ASMLib to scan the multipath disks first. In the ASMLib configuration file the ORACLEASM_SCANORDER variable is edited to look like so:

      ORACLEASM_SCANORDER="<Multipath disk>"

During a scan, ASMLib first tries all disks that start with "multipath". The multipath device /dev/multipatha certainly matches. It is scanned first. Next, ASMLib tries all disks that start with "sd". This is all the SCSI disks.

The local disk /dev/sda will be scanned, but it is not an ASM disk. The single path disks /dev/sdb and /dev/sdc are also scanned. They are ASM disks, but ASMLib will see that it already has a path to that disk. It will ignore them. Finally, ASMLib will scan any other disks that did not match either prefix.

Excluding Single Path Disks

======================

The system administrator configures ASMLib to ignore the single path disks. In the ASMLib configuration, he edits the ORACLEASM_SCANEXCLUDE variable to look like so:

      ORACLEASM_SCANEXCLUDE="<Single Path disk>"

So we had changed the configuration file /etc/sysconfig/oracleasm-_dev_oracleasm in the below manner and created the candidate disks and we were able to run root.sh successfully on both the nodes. Here dm is the multipath disk prefix and sd is the single disk prefix.

                            

ORACLEASM_SCANORDER="dm"

ORACLEASM_SCANEXCLUDE="sd"

Non ASM to ASM Conversion

Non ASM to ASM Conversion

Before Proceeding to process, please know what is ASM and how to use it.

1. Set the ASM environment and create diskgroup
oracle@AIX01]. oraenv
ORACLE_SID] +ASM1

sqlplus /as sysasm

sql> Create diskgroup DATA EXTERNAL REDUNDANCY DISK '/dev/rhdiskpower47','/dev/rhdiskpower48';

2. set the db environment and shutdown the database

oracle@AIX01]. oraenv
ORACLE_SID] ORCL

oracle@AIX01] sqlplus '/as sysdba'
sql> shu immediate

3. Make the database to nomount state through RMAN
$ Rman target /
RMAN>STARTUP NOMOUNT

4. move controlfile to asm through RMAN
RMAN> restore controlfile to ‘+DATA’ from ‘/oracledb/control1/ctrl_1.ctl’;

5. set the asm environment and find the controlfile from asm diskgroup
$ . oraenv
+ASM1
$ asmcmd
ASMCMD> find --type CONTROLFILE +DATA *
+DATA/ORCL/CONTROLFILE/current.256.734177857
ASMCMD>exit
--Copy the name and full path of controlfile

6. change the control_files parameter from non-asm to asm and restart the database post the db parameter changes.

SQL> alter system set control_files='+DATA/ORCL/CONTROLFILE/current.256.734727317' scope=spfile;
SQL>shutdown immediate;
SQL>startup mount;

7. Now check the control file
SQL> select name from v$controlfile;
+DATA/ORCL/CONTROLFILE/current.256.734727317

8. Now moving the Datafiles to ASM

. oraenv
ORCL

$ rman target /
RMAN>configure device type disk parallelism 4;
RMAN> backup as copy database format '+DATA';
RMAN>switch database to copy ;
RMAN> ALTER DATABASE OPEN;

9. Connect to sqlplus as sysdba and check for all data files

SQL> select file_name from dba_data_files;

10. Now move the Redo Log files to ASM

Please check for which logfile group is active and proceed with caution
NOTE :In this case Logfile group 6 is in active/current state so switching logfile so that it becomes inactive.
SQL> alter database drop logfile group 1;
SQL> alter database add logfile group 1 ('+DATA) size 300M;
SQL> alter database drop logfile group 2;
SQL> alter database add logfile group 2 ('+DATA') size 300M;
SQL> alter database drop logfile group 3;
SQL> alter database add logfile group 3 ('+DATA') size 300M;

11. Move SPfile to ASM
Connect to sqlplus as sysdba
Sqlplus “ / as sysdba”
SQL> create pfile='/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initpfile.ora' from spfile;
SQL> shutdown immediate;
Edit the pfile to change the data file creation place
*.db_create_file_dest=+DATA
SQL>startup pfile='/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initpfile.ora';
SQL> CREATE SPFILE='+DATA' FROM PFILE='/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initpfile.ora';

12. find the spfile and Modify the pfile point to spfile
. oraenv
+ASM1

$asmcmd
ASMCMD> find --type parameterfile +DATA *
+DATA/ORCL/PARAMETERFILE/spfile.354.734189649

.oraenv
ORCL

##remove all entries and add the spfile parameter
$cd $ORACLE_HOME/dbs
vi initpfile.ora
spfile='+DATA/ORCL/PARAMETERFILE/spfile.354.734189649'

13. startup with pfile
sqlplus '/as sysdba'
shu immediate
startup pfile=?/dbs/initpfile.ora

14. moving/creating temporary tablespace in ASM

Creating TEMPFILEs In ASM

SQL> select name, bytes from v$tempfile;
SQL> create temporary tablespace temp1 tempfile SIZE 100M extent management local uniform size 1M;
SQL> alter database default temporary tablespace temp1;
SQL> drop tablespace temp including contents;
SQL> create temporary tablespace temp tempfile SIZE 10120M extent management local uniform size 1M;
SQL> alter database default temporary tablespace temp;
SQL> drop tablespace temp1 including contents;
SQL> select name from v$tempfile;

Steps to purge the FND_OPP_AQ Table if the count reaches the threshold

1) Bring down the Application services before performing this activity

2) Reboot the database once.

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue May 3 00:33:57 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2228344 bytes
Variable Size             612372360 bytes
Database Buffers          440401920 bytes
Redo Buffers               13934592 bytes
Database mounted.
Database opened.
SQL>
SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
DEV       READ WRITE
SQL>
SQL>
SQL> select count(*) from applsys.FND_CP_GSM_OPP_AQTBL ;


  COUNT(*)
----------
    821105

SQL>
SQL> create table applsys.FND_CP_GSM_OPP_AQTBL_2May16 as select * from applsys.FND_CP_GSM_OPP_AQTBL;

Table created.

SQL> conn
Enter user-name: applsys
Enter password:
Connected.
SQL>
SQL>
SQL> DECLARE
po dbms_aqadm.aq$_purge_options_t;
BEGIN
po.block := FALSE;
DBMS_AQADM.PURGE_QUEUE_TABLE(
queue_table => 'applsys.FND_CP_GSM_OPP_AQTBL',
purge_condition => NULL,
purge_options => po);
END;
/

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.




----------------------------------

[oraprod@mercury:PROD] su - applprod
applprod's Password:
$
$
$ envconc (Calling concurrent env)
$
$ cd $FND_TOP
$ ls
3rdparty    bin         fndenv.env  help        include     lib         mds         mesg        patch       reports     secure      usrxit
admin       bin_bkp     forms       html        java        log         media       out         perl        resource    sql         xml
$
$ cd patch/115/sql
$
$ sqlplus apps

SQL*Plus: Release 10.1.0.5.0 - Production on Tue May 3 00:41:46 2016

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL>
SQL> @afopp002.sql
Enter value for 1: applsys
Enter value for 2: dev8mgr
Connected.

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Commit complete.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$
$
$
$ sqlplus apps

SQL*Plus: Release 10.1.0.5.0 - Production on Tue May 3 00:51:19 2016

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL>
SQL>
SQL> exec fnd_cp_opp_ipc.remove_all_subscribers();

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL> select count(*) from applsys.FND_CP_GSM_OPP_AQTBL ;

  COUNT(*)
----------
         0



Check whether the queue is enabled or not with the following sql
----------------------------------------------------------------

SQL>  select * from dba_queues where name = 'FND_CP_GSM_OPP_AQ';

Enqueu and Dequeue should be 'YES', If not run the below begin statement

If the queue status returned 'NO'. Enable the queue using the below statement.

conn applsys

Statement
---------
begin
 dbms_aqadm.start_queue(‘AQ$_WF_NOTIFICATION_OUT_E’,FALSE,TRUE);
 end;
 /

In our case the value was 'YES'. Hence did not perform the above statement and started the application.

Interview Questions & Answer on RMAN

What is RMAN?

Recovery Manager (RMAN) is a utility that can manage your entire Oracle backup and recovery activities.

What is the difference between using recovery catalog and control file?

When new incarnation happens, the old backup information in control file will be lost. It will be preserved in recovery catalog.

In recovery catalog we can store scripts.

Recovery catalog is central and can have information of many databases.

Can we use same target database as catalog?

No, The recovery catalog should not reside in the target database (database should be backed up), because the database can’t be recovered in the mounted state.

How do you know that how much RMAN task has been completed?

By querying v$rman_status or v$session_longops

From where list & report commands will get input?

Both the commands command quering v$ and recovery catalog views. V$BACKUP_FILES or many of the recovery catalog views such asRC_DATAFILE_COPY or RC_ARCHIVED_LOG.

Command to delete archive logs older than 7days?

RMAN> delete archivelog all completed before sysdate-7;

How many times does oracle ask before dropping a catalog?

The default is two times one for the actual command, the other for confirmation.

How to view the current defaults for the database.

RMAN> show all;

What is the use of crosscheck command in RMAN?

Crosscheck will be useful to check whether the catalog information is intact with OS level information. This command only updates repository records with the status of the backups.

e.g. If user removes archived logs from disk with an operating system command, the repository still indicates that the logs are on disk, when in fact they are not.

What are the differences between crosscheck and validate commands?

Validate command is to examine a backup set and report whether it can be restored. RMAN scans all of the backup pieces in the specified backup sets and looks at the checksum to verify that the contents are intact so that backup can be successfully restored if necessary.

Crosscheck command is to verify the status of backups and copies recorded in the RMAN repository against media such as disk or tape. The crosscheck command only processes files created on the same device type as the channel running crosscheck.

Which one is good, differential (incremental) backup or cumulative (incremental) backup?

A differential backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

A cumulative backup, which backs up all blocks changed after the most recent incremental backup at level 0

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

Cumulative backups are preferable to differential backups when recovery time is more important than disk space, because during recovery each differential backup must be applied in succession. Use cumulative incremental backups instead of differential, if enough disk space is available to store cumulative incremental backups.

This is command for taking Level 0 backup.

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;

What is the difference between backup set and backup piece?

Backup set is logical and backup piece is physical.

RMAN command to backup for creating standby database

RMAN> duplicate target database

You loss one datafile and DB is running in ARCHIVELOG mode. You have full database backup of 1 week/day old and don’t have backup of this (newly created) datafile. How do you restore/recover file?

Create data file and recover datafile.

SQL> alter database create datafile ‘/u01/app/oracle/oradata/xyz.dbf’ size 2G;

RMAN> recover datafile file_id;

What is obsolete backup & expired backup?

A status of “expired” means that the backup piece or backup set is not found in the backup destination.

A status of “obsolete” means the backup piece is still available, but it is no longer needed. The backup piece is no longer needed since RMAN has been configured to no longer need this piece after so many days have elapsed, or so many backups have been performed.

What is the difference between hot backup & RMAN backup?

For hot backup, we have to put database in begin backup mode, then take backup.
RMAN won’t put database in backup mode.

How to put manual/user-managed backup in RMAN (recovery catalog)?

By using catalog command.

RMAN> CATALOG START WITH ‘/tmp/backup.ctl’;

What are the Architectural components of RMAN?

1. RMAN Executables
2. Sercer process
3. Channels
4. Target database
5. Recovery catalog database (optional)
6. Media management Layer (optional)
7. Backups, backup sets and backup pieces

What are channels?

A channel is an RMAN server process started when there is a need to communicate with an I/O device, such as a disk or a tape. A channel is what reads and writes RMAN backup files. It is through the allocation of channels that you govern I/O characteristics:

• Type of I/O device being read or written to, either a disk or an sbt_tape
• Number of processes simultaneously accessing an I/O device
• Maximize size of files created on I/O devices
• Maximize rate at which database files are read
• Maximize number of files open at a time

Why is the catalog optional?

Because RMAN manages backup and recovery operations, it requires a place to store necessary information about the database. RMAN always stores this information in the target database control file. You can also store RMAN metadata in a recovery catalog schema contained in a separate database. The recovery catalog schema must be stored in a database other than the target database.

What is a Backup set?

A logical grouping of backup files — the backup pieces — that are created when you issue an RMAN backup command. A backup set is RMAN’s name for a collection of files associated with a backup. A backup set is composed of one or more backup pieces.

What are the benefits of using RMAN?

1. Incremental backups that only copy data blocks that have changed since the last backup.
2. Tablespaces are not put in backup mode, thus there is noextra redo log generation during online backups.
3. Detection of corrupt blocks during backups.
4. Parallelization of I/O operations.
5. Automatic logging of all backup and recovery operations.
6. Built-in reporting and listing commands.

What are the various reports available with RMAN

RMAN>list backup;

RMAN> list archive;

In catalog database, if some of the blocks are corrupted due to system crash, How will you recover?

using RMAN BLOCK RECOVER command

How do you enable the autobackup for the controlfile using RMAN?

Issue command at RMAN prompt.

RMAN> configure controlfile autobackup on;

Also we can configure controlfile backup format.

RMAN> configure controlfile autobackup format for device type disk to

2> ‘$HOME/BACKUP/RMAN/ F.bkp’;

How do you identify what are the all the target databases that are being backed-up with RMAN database?

You don’t have any view to identify whether it is backed up or not. The only option is connect to the target database and give list backup this will give you the backup information with date and timing.

How do you identify the block corruption in RMAN database? How do you fix it?

Using v$block_corruption view you can find which blocks corrupted.

RMAN> block recover datafile <fileid> block <blockid>;

Using the above statement You recover the corrupted blocks. First check whether the block is corrupted or not by using this command

SQL>select file# block# from v$database_block_corruption;

file# block

2 507

the above block is corrupted…

conn to Rman

To recover the block use this command…

RMAN>blockrecover datafile 2 block 507;

the above command recover the block 507

Now just verify it…..

Rman>blockrecover corruption list;

How do you clone the database using RMAN software? Give brief steps? When do you use crosscheck command?

Check whether backup pieces proxy copies or disk copies still exist.

Two commands available in RMAN to clone database:

1) Duplicate

2) Restore.

List some of the RMAN catalog view names which contain the catalog information?

RC_DATABASE_INCARNATION RC_BACKUP_COPY_DETAILS

RC_BACKUP_CORRUPTION

RC_BACKUP-DATAFILE_SUMMARY

How do you install the RMAN recovery catalog?

Steps to be followed:

1) Create connection string at catalog database.

2) At catalog database create one new user or use existing user and give that user a recovery_catalog_owner privilege.

3) Login into RMAN with connection string

a) export ORACLE_SID

b) rman target catalog @connection string

4) rman> create catalog;

5) register database;

What is the difference between physical and logical backups?

In Oracle Logical Backup is “which is taken using either Traditional Export/Import or Latest Data Pump”. Where as Physical backup is known “when you take Physical O/s Database related Files as Backup”.

What is RAID? What is RAID0? What is RAID1? What is RAID 10?

RAID: It is a redundant array of independent disk

RAID0: Concatenation and stripping

RAID1: Mirroring

How to enable Fast Incremental Backup to backup only those data blocks that have changed?

SQL> ALTER DATABASE enable BLOCK CHANGE TRACKING;

How do you set the flash recovery area?

SQL> ALTER SYSTEM SET db_recovery_file_dest_size = 100G;

SQL> ALTER SYSTEM SET db_recovery_file_dest = ‘/u10/oradata/school’;

What is auxiliary channel in RMAN? When do you need this?

An auxiliary channel is a link to auxiliary instance. If you do not have automatic channels configured, then before issuing the DUPLICATE command, manually allocate at least one auxiliary channel within the same RUN command.

How do you use the V$RECOVERY_FILE_DEST view to display information regarding the flashrecovery area?

SQL> SELECT name, space_limit, space_used,space_reclaimable, number_of_filesFROM v$recovery_file_dest;

How can you display warning messages?

SQL> SELECT object_type, message_type,message_level, reason, suggested_actionFROM dba_outstanding_alerts;

How do you backup the entire database?

RMAN> BACKUP DATABASE;

How do you backup an individual tablespaces?

RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK;

RMAN> BACKUP TABLESPACE system;

How do you backup datafiles and control files?

RMAN> BACKUP DATAFILE 3;

RMAN> BACKUP CURRENT CONTROLFILE;

Use a fast recovery without restoring all backups from their backup location to the location specified inthe controlfile.

RMAN> SWITCH DATABASE TO COPY;