Database & Middleware Magic: January 2015

Friday, January 30, 2015

Oracle Opatch failed with "CheckActiveFilesAndExecutables"

Environment Details:
OS: Linux Server 5.8 64bit
GRID Home Version: 11.2.0.4.0
GRID Home: /opt/oracle/product/11.2.0.4.GRD

Issue: While applying patch, the opatch failed with the below error

Following executables are active :
/opt/oracle/product/11.2.0.4.GRD/lib/libclntsh.so.11.1
UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.

Solution: The above mentioned file is still active or in use.

Check which process/tool is using the file and stop the process cleanly else kill the process.

you can use lsof(list open files) or fuser (identify processes using files or sockets) to identify the process using the file.

[oracle@linux01 ~]$ lsof /opt/oracle/product/11.2.0.4.GRD/lib/libclntsh.so.11.1
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
ons 2342 oracle mem REG 253,53 53817421 21213079 /apps/oracle/product/11.2.0.4.GRD/lib/libclntsh.so.11.1
ons 2344 oracle mem REG 253,53 53817421 21213079 /apps/oracle/product/11.2.0.4.GRD/lib/libclntsh.so.11.1

[oracle@linux01 ~]$ /sbin/fuser -cv /opt/oracle/product/11.2.0.4.GRD/lib/libclntsh.so.11.1
USER PID ACCESS COMMAND
/opt/oracle/product/11.2.0.4.GRD/lib/libclntsh.so.11.1:
oracle 2342 F.ce. ons
oracle 2344 F.ce. ons

Once the processes are identified, stop/kill them:
[oracle@linux01 ~]$ kill -9 2342
[oracle@linux01 ~]$ kill -9 2344

How to move datafile from one ASM diskgroup to another

Follow the below steps to move datafile from one ASM diskgroup to another.

Case 1: Make the tablespace offline and move the datafile
1. Check the file which you need to move.
2. Make the tablespace offline
3. Connect to rman
4. issue the copy command from the rman prompt
5. login to the sql prompt and issue rename datafile command
6. Make the tablespace online

Case 2: Make the datafile offline and move the datafile
1. Check the file which you need to move.
2. Make the datafile offline
3. Connect to rman
4. issue the copy command from the rman prompt
5. login to the sql prompt and issue rename datafile command
6. Recover the datafile (In case you make datafile offline you need to recover the datafile)
7. Make the datafile online

Note: Be very cautious while executing the rename file command as it will remove the original file from the ASM.(tested in 11.2.0.4)

Case 1:
SQL> select tablespace_name, file_name, online_status from dba_data_files;
TABLESPACE_NAME FILE_NAME ONLINE_STATUS
---------------- ---------------------------------------------------- -------------
TEST1 +DATA_DG01/orcl/datafile/test1.1117.870325481 ONLINE

SQL> alter tablespace test1 offline;
Tablespace altered.

SQL> select tablespace_name, file_name, online_status from dba_data_files;
TABLESPACE_NAME FILE_NAME ONLINE_STATUS
---------------- ---------------------------------------------------- -------------
TEST1 +DATA_DG01/orcl/datafile/test1.1117.870325481 OFFLINE

[oracle@linux01 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jan 30 05:24:02 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=408657265)

RMAN> copy datafile '+DATA_DG01/orcl/datafile/test1.1117.870325481' to '+FRA_DG01';

Starting backup at 30-JAN-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATA_DG01/orcl/datafile/test1.1117.870325481
output file name=+FRA_DG01/orcl/datafile/test1.263233.870326681 tag=TAG20150130T052441 RECID=7 STAMP=870326681
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 30-JAN-15

SQL> alter database rename file '+DATA_DG01/orcl/datafile/test1.1117.870325481' to '+FRA_DG01/orcl/datafile/test1.263233.870326681';
Database altered.

SQL> select tablespace_name, file_name, online_status from dba_data_files;
TABLESPACE_NAME FILE_NAME ONLINE_STATUS
---------------- ---------------------------------------------------- -------------
TEST1 +FRA_DG01/orcl/datafile/test1.263233.870326681 OFFLINE

SQL> alter tablespace test1 online;
Tablespace altered.

SQL> select tablespace_name, file_name, online_status from dba_data_files;
TABLESPACE_NAME FILE_NAME ONLINE_STATUS
---------------- ---------------------------------------------------- -------------
TEST1 +FRA_DG01/orcl/datafile/test1.263233.870326681 ONLINE

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

Case 2:
SQL> select tablespace_name, file_name, ONLINE_STATUS from dba_data_files;
TABLESPACE_NAME FILE_NAME ONLINE_STATUS
---------------- ------------------------------------------------ -------------
TEST1 +FRA_DG01/orcl/datafile/test1.256846.868340771 ONLINE

SQL> alter database datafile '+FRA_DG01/orcl/datafile/test1.256846.868340771' offline;
Database altered.

SQL> select tablespace_name, file_name, ONLINE_STATUS from dba_data_files;
TABLESPACE_NAME FILE_NAME ONLINE_STATUS
---------------- ------------------------------------------------ -------------
TEST1 +FRA_DG01/orcl/datafile/test1.256846.868340771 RECOVER

[oracle@linux01 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jan 30 05:03:51 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=408657265)

RMAN> copy datafile '+FRA_DG01/orcl/datafile/test1.256846.868340771' to '+DATA_DG01';

Starting backup at 30-JAN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=245 instance=RL3DB151 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+FRA_DG01/orcl/datafile/test1.256846.868340771
output file name=+DATA_DG01/orcl/datafile/test1.1117.870325481 tag=TAG20150130T050439 RECID=6 STAMP=870325480
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 30-JAN-15

SQL> alter database rename file '+FRA_DG01/orcl/datafile/test1.256846.868340771' to '+DATA_DG01/orcl/datafile/test1.1117.870325481';
Database altered.

SQL> recover datafile '+DATA_DG01/orcl/datafile/test1.1117.870325481';
Media recovery complete.

SQL> select tablespace_name, file_name, ONLINE_STATUS from dba_data_files;
TABLESPACE_NAME FILE_NAME ONLINE_STATUS
---------------- ------------------------------------------------ -------------
TEST1 +DATA_DG01/orcl/datafile/test1.1117.870325481 OFFLINE

SQL> alter database datafile '+DATA_DG01/orcl/datafile/test1.1117.870325481' online;
Database altered.

SQL> select tablespace_name, file_name, ONLINE_STATUS from dba_data_files;
TABLESPACE_NAME FILE_NAME ONLINE_STATUS
---------------- ------------------------------------------------ -------------
TEST1 +DATA_DG01/orcl/datafile/test1.1117.870325481 ONLINE

Wednesday, January 7, 2015

How to move system datafile from one ASM diskgroup to another

Follow the below steps to move system datafile from one ASM diskgroup to another.

1. Check the file which you need to move.

2. Stop the database and start it in mount mode.

3. Connect to rman

4. issue the copy command from the rman prompt

5. login to the sql prompt and issue rename file command

6. stop and start the database

Note: Be very cautious while executing the rename file command as it will remove the original file from the ASM.(tested in 11.2.0.4)

[oracle@linux01 ~]$ sqlplus sys as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 2 03:09:48 2015

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> select file_name from dba_data_files;

FILE_NAME

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

+FRA_DG01/orcl/datafile/system.144021.867898701

[oracle@linux01 ~]$ srvctl stop database -d ORCL

[oracle@linux01 ~]$ srvctl start database -d ORCL -o mount

[oracle@linux01 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jan 2 03:05:00 2015

connected to target database: ORCL (DBID=408657265, not open)

RMAN> copy datafile '+FRA_DG01/orcl/datafile/system.144021.867898701' to '+DATA_DG01';

Starting backup at 02-JAN-15

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=81 instance=ORCL1 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=+FRA_DG01/orcl/datafile/system.144021.867898701

output file name=+DATA_DG01/orcl/datafile/system.307.867899135 tag=TAG20150102T030534 RECID=2 STAMP=867899143

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

Finished backup at 02-JAN-15

RMAN> exit

Recovery Manager complete.

[oracle@linux01 ~]$sqlplus sys as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 2 03:06:19 2015

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> alter database rename file '+FRA_DG01/orcl/datafile/system.144021.867898701' to '+DATA_DG01/orcl/datafile/system.307.867899135';

Database altered.

SQL> exit

[oracle@linux01 ~]$ srvctl stop database -d ORCL

[oracle@linux01 ~]$ srvctl start database -d ORCL

[oracle@linux01 ~]$ sqlplus sys as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 2 03:09:48 2015

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> select file_name from dba_data_files;

FILE_NAME

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

+DATA_DG01/orcl/datafile/system.307.867899135