During an 11GR2 DB installation Solaris OS Patch 124861-15 missing

If you are not installing the Pro*C precompiler, this message can be safely ignored by checking the box "Ignore all" and pressing "Next" and continue with installation.

If you wish to install the Pro*C precompiler, please install Sun Studio 12 plus patch 124861-15 (NOT Sun Studio 12 Update 1) before installing the Oracle software

Ref - BUG:9214210

Thanks to Metalink note id - 969497.1

Diferrent Datafile Renaming Methodologies

As far as renaming a datafile is concerned we have 3 diferrent ways through which it can be achieved

method 1 - Offline a particular datafile and renaming it
method 2 - offline the tablespace and renaming the datafile alone
method 3 - shutdown DB,mount DB and rename the datafile



Method 1
=========

Before you can use this method the database must be in archive log mode.
In the below scenario I am renaming a datafile names test11.dbf to test12.dbf



SQL> alter database datafile '/u06/oracle/11GR2/oradata/D11GR2/test11.dbf' offline;
alter database datafile '/u06/oracle/11GR2/oradata/D11GR2/test11.dbf' offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled


SQL> alter database archivelog;

Database altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8
SQL>
SQL> alter database open;

Database altered.

SQL> alter database datafile '/u06/oracle/11GR2/oradata/D11GR2/test11.dbf' offline;

Database altered.


$ cp test11.dbf test12.dbf
$

SQL> alter database rename file '/u06/oracle/11GR2/oradata/D11GR2/test11.dbf' to '/u06/oracle/11GR2/oradata/D11GR2/test12.dbf';

Database altered.

SQL> alter database datafile '/u06/oracle/11GR2/oradata/D11GR2/test12.dbf' online;
alter database datafile '/u06/oracle/11GR2/oradata/D11GR2/test12.dbf' online
*
ERROR at line 1:
ORA-01113: file 8 needs media recovery
ORA-01110: data file 8: '/u06/oracle/11GR2/oradata/D11GR2/test12.dbf'


SQL> recover datafile 8;
Media recovery complete.
SQL>
SQL> alter database datafile '/u06/oracle/11GR2/oradata/D11GR2/test12.dbf' online;

Database altered.

SQL>





Method 2
=========



Tablespace Offline Datafile Rename method

I take the particular tablespace offline and then rename the datafile.

a)

alter tablespace APPS_TS_TX_IDX offline;

b)

mv /u01/oracle/PROD/db/apps_st/data5/a_media19.dbf /u01/oracle/PROD/db/apps_st/index3/a_txn_ind43.dbf


c)

alter tablespace APPS_TS_TX_IDX rename datafile
/u01/oracle/PROD/db/apps_st/data5/a_media19.dbf
to
/u01/oracle/PROD/db/apps_st/index3/a_txn_ind43.dbf;

d)

alter tablespace APPS_TS_TX_IDX online;




Method 3
=========

In this scenario we take the database down ,start the database in mount option and then rename the datafile.Finally open the database.

In the below example I am renaming datafile test2.dbf to test3.dbf.



SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u06/oracle/11GR2/oradata/D11GR2/system01.dbf
/u06/oracle/11GR2/oradata/D11GR2/sysaux01.dbf
/u06/oracle/11GR2/oradata/D11GR2/undotbs01.dbf
/u06/oracle/11GR2/oradata/D11GR2/users01.dbf
/u06/oracle/11GR2/oradata/D11GR2/example01.dbf
/u06/oracle/11GR2/oradata/D11GR2/test1.dbf
/u06/oracle/11GR2/oradata/D11GR2/test2.dbf
/u06/oracle/11GR2/oradata/D11GR2/test12.dbf

8 rows selected.

SQL>
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL> exit


$ mv /u06/oracle/11GR2/oradata/D11GR2/test2.dbf /u06/oracle/11GR2/oradata/D11GR2/test3.dbf



$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed May 4 17:18:22 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 2723282944 bytes
Fixed Size 2151200 bytes
Variable Size 1509952736 bytes
Database Buffers 1191182336 bytes
Redo Buffers 19996672 bytes
Database mounted.
SQL>
SQL>
SQL> alter database rename file '/u06/oracle/11GR2/oradata/D11GR2/test2.dbf' to '/u06/oracle/11GR2/oradata/D11GR2/test3.dbf';

Database altered.

SQL> alter database open;

Database altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u06/oracle/11GR2/oradata/D11GR2/system01.dbf
/u06/oracle/11GR2/oradata/D11GR2/sysaux01.dbf
/u06/oracle/11GR2/oradata/D11GR2/undotbs01.dbf
/u06/oracle/11GR2/oradata/D11GR2/users01.dbf
/u06/oracle/11GR2/oradata/D11GR2/example01.dbf
/u06/oracle/11GR2/oradata/D11GR2/test1.dbf
/u06/oracle/11GR2/oradata/D11GR2/test3.dbf
/u06/oracle/11GR2/oradata/D11GR2/test12.dbf

8 rows selected.

SQL>

New System Privilege 'SYSASM' to manage ASM instance in 11g

Hello All,

Today when I was trying to shutdown a 11g ASM instance connected as sysdba it was not allowing me to shutdown and gave 'ORA-01031: insufficient privileges'.

After a bit of research found that in 11g there is a new privilege called 'SYSASM' which is separate for ASM storage administration privilege and for maintaining ASM instance.The basic idea was to separate the storage administrator and the database administrator responsbilities.

Below is what I was trying to do.


[oracle@oelbox1 ~]$ echo $ORACLE_SID
+ASM

[oracle@oelbox1 ~]$ ps -ef | grep -i pmon
oracle 11810 1 0 18:58 ? 00:00:00 asm_pmon_+ASM
oracle 22821 22724 0 22:19 pts/5 00:00:00 grep -i pmon

[oracle@oelbox1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Oct 14 22:19:07 2010
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Automatic Storage Management option

SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
+ASM

SQL> shutdown immediate
ORA-01031: insufficient privileges
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Pr oduction
With the Automatic Storage Management option

[oracle@oelbox1 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Thu Oct 14 22:19:46 2010
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL> connect sys as sysasm
Enter password:
Connected.
SQL>
SQL> shut immediate
ASM diskgroups volume disabled
ASM diskgroups dismounted
ASM instance shutdown
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Pr oduction With the Automatic Storage Management option