Upgrade OPatch version from 11.2.0.1.1 to 11.2.0.3.3

I want to update my OPatch version from 11.2.0.1.1 to 11.2.0.3.3 on My LINUX server, to do so we need to apply patch 6880880

$ cd $ORACLE_HOME/OPatch

$ ls

crs  docs  emdpatch.pl  fmw  jlib  ocm  opatch  opatch.ini  opatch.pl  opatchprereqs

$ ./opatch lsinventory

Invoking OPatch 11.2.0.1.1

Oracle Interim Patch Installer version 11.2.0.1.1

Copyright (c) 2009, Oracle Corporation.  All rights reserved.

Oracle Home       : /opt/oracle/app/11.2.0.2

Central Inventory : /opt/oracle/oraInventory

   from           : /etc/oraInst.loc

OPatch version    : 11.2.0.1.1

OUI version       : 11.2.0.2.0

OUI location      : /opt/oracle/app/11.2.0.2/oui

Log file location : /opt/oracle/app/11.2.0.2/cfgtoollogs/opatch/opatch2013-03-19_15-45-56PM.log

Patch history file: /opt/oracle/app/11.2.0.2/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /opt/oracle/app/11.2.0.2/cfgtoollogs/opatch/lsinv/lsinventory2013-03-19_15-45-56PM.txt

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

Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.2.0

There are 1 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.

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

OPatch succeeded.

Download patch 6880880 from Oracle support and Copy the p6880880_112000_Linux-x86-64.zip file to $ORACLE_HOME location.

Before unzip the file backup the existing OPatch folder

$mv OPatch OPatch.bak

Now, unzip the file then you’ll see new OPatch folder.

$ unzip p6880880_112000_Linux-x86-64.zip

New OPatch version is created.

$ cd /opt/oracle/app/11.2.0.2/OPatch

$ ./opatch lsinventory

Oracle Interim Patch Installer version 11.2.0.3.3

Copyright (c) 2012, Oracle Corporation.  All rights reserved.

Oracle Home       : /opt/oracle/app/11.2.0.2

Central Inventory : /opt/oracle/oraInventory

   from           : /opt/oracle/app/11.2.0.2/oraInst.loc

OPatch version    : 11.2.0.3.3

OUI version       : 11.2.0.2.0

Log file location : /opt/oracle/app/11.2.0.2/cfgtoollogs/opatch/opatch2013-03-19_16-05-48PM_1.log

Lsinventory Output file location : /opt/oracle/app/11.2.0.2/cfgtoollogs/opatch/lsinv/lsinventory2013-03-19_16-05-48PM.txt

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

Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.2.0

There are 1 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.

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

OPatch succeeded.

ORA-39213: Metadata processing is not available

select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

PL/SQL Release 10.2.0.5.0 - Production

CORE    10.2.0.5.0      Production

TNS for Linux: Version 10.2.0.5.0 - Production

NLSRTL Version 10.2.0.5.0 - Production

when i'm trying to take full database export using DATAPUMP i got an error below:

$ expdp directory=EXPORT dumpfile=FULL_devdb.dmp logfile=exp_FULL_devdb.log full=y

Export: Release 10.2.0.5.0 - 64bit Production on Saturday, 06 April, 2013 16:09:04

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORA-39006: internal error

ORA-39213: Metadata processing is not available

Then i checked,

SQL > SELECT comp_id, version, status FROM dba_registry;

COMP_ID                        VERSION                        STATUS

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

OWM                             10.2.0.5.0                      VALID

CATALOG                        10.2.0.5.0                     VALID

CATPROC                        10.2.0.5.0                     VALID

JAVAVM                         10.2.0.5.0                     VALID

According to Metalink ID: 402242.1, 

DataPump export uses DBMS_METADATA_UTIL package but this package does not successfully ran.  Executing sys.dbms_metadata_util.load_stylesheets procedure failed with following error:

ORA-31609: error loading file "kualter.xsl" from file system directory "/rdbms/xml/xsl"

ORA-06512: at "SYS.DBMS_METADATA_UTIL", line 1807

ORA-06512: at line 1

Then I ran

connect / as sysdba

SQL > execute sys.dbms_metadata_util.load_stylesheets;

PL/SQL procedure successfully completed.

Then my export went successful.

100K HITS

Wow its time to celebrate, i hit 100,000 blog hits !! very happy to see such a huge number, i really thank each and everyone who is visiting my blog and make this happen, This is the best motivation for me to post more and new kind of issues/posts. Thanks all again :)

Add database to 11g OEM Grid control

Here I have a 11g Grid control running on Linux x86_64.

To add a database in the Grid control, Open OEM grid control page --> targets --> databases

 Click on ADD button, then the below screenshot will appear

Select the hostname from the list shown and click continue.

NOTE : The hostnames are displayed when that server is configured with the GRID server, if not configure.

 It will take some time to find and display the targets and the  listeners found on that host(server).

Select the target you want to add and the listener associated with it. Then click on configure button for the selected target and you’ll get below screen.

Give ‘dbsnmp’ password and fill all the fields correctly then click next and ok.

Done, now you’ll see your target(database) in the OEM grid database list. Njoy !!!!!!

Move datafile to NEW Location

SQL> select * from v$version;

BANNER

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

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

PL/SQL Release 11.2.0.2.0 - Production

CORE    11.2.0.2.0      Production

TNS for Linux: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 - Production

SQL>

SQL> select name from v$datafile;

NAME

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

/opt/oracle/data/DEV3/system01.dbf

/opt/oracle/data/DEV3/sysaux01.dbf

/opt/oracle/data/TEST01.dbf

Here my TEST tablespace datafile is not in DEV3 location, so I wanted to move into /DEV location.

Here I’m testing in my DEV database but make sure no one using this tablespace if this is doing in prod database J

1)      Make the corresponding Tablespace OFFLINE

        SQL> ALTER tablespace TEST offline;

                  Tablespace altered.

2)      Update the data dictionary pointing to new location using below command:

         SQL> ALTER DATABASE RENAME FILE  '/opt/oracle/data/TEST01.dbf'  to    '/opt/oracle/data/DEV3/TEST01.dbf';

                 Database altered.

3)      Now, recover the datafile from new location:

         SQL> RECOVER DATAFILE '/opt/oracle/data/DEV3/TEST01.dbf';

                   Media recovery complete.

4)      Bring back the TEST tablespace ONLINE

         SQL>  ALTER tablespace TEST online;

                   Tablespace altered.

SQL> select name from v$datafile;

NAME

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

/opt/oracle/data/DEV3/system01.dbf

/opt/oracle/data/DEV3/sysaux01.dbf

/opt/oracle/data/DEV3/TEST01.dbf

Now my TEST datafile moved to the new location.

Generate DDL scripts using "DBMS_METADATA" package

To Generate all Tablespace DDL's:

set long 2000;

select dbms_metadata.get_ddl('TABLESPACE',tb.name) from v$tablespace tb ;

for 1 tablespace:

select dbms_metadata.get_ddl('TABLESPACE','TABLESPACE_NAME') from dual;

To Generate DDL for a USER :

set long 5000 linesize 280;

select dbms_metadata.get_ddl('USER', 'USER_NAME')  from dual;

To Generate DDL for all PROFILE's :

set long 5000;

select dbms_metadata.get_ddl('PROFILE',a.profile) from dba_profiles a;

For 1 profile:

 select dbms_metadata.get_ddl('PROFILE','PROFILE_NAME') from dual;

To Generate DDL for any TABLE :

set long 5000;

select dbms_metadata.get_ddl('TABLE','TABLE_NAME','OWNER') from dual;

To Generate DDL for any INDEX :

select dbms_metadata.get_ddl('INDEX',’INDEX_NAME’,'OWNER') from dual;

To Generate all INDEX DDL’s on a TABLE:

select dbms_metadata.get_dependent_ddl('INDEX',’TABLE_NAME','OWNER') from dual;

The above command will generate all index ddl’s which are on table.

To Generate DDL for a Specific SEQUENCE :

set long 5000;
set linesize 200;
select dbms_metadata.get_ddl('SEQUENCE','SEQUENCE_NAME','OWNER_NAME') from dual;

To Generate DDL for a Specific TRIGGER:

set long 5000;
set linesize 200;

SQL> select dbms_metadata.get_ddl('TRIGGER','TRGGER_NAME','TRIGGER_OWNER') from dual;

Changing Default CHARACTER SET of the database

Here my goal is to change the default character set from WE8ISO8859P1 to AL32UTF8 on  LINUX
Remember, this was tested on my DEVELOPMENT standalone database (refer Oracle Doc before doing on PROD).

SQL> select * from v$version;

BANNER

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

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

PL/SQL Release 11.2.0.2.0 - Production

CORE    11.2.0.2.0      Production

TNS for Linux: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 - Production

SQL>

SQL> SELECT SUM (a.log_space + b.data_space + c.tempspace) "Total_DB_Size (G)"

   FROM (SELECT ROUND (SUM (BYTES/1024/1024/1024), 2) data_space  FROM dba_data_files) b,(SELECT ROUND (SUM (BYTES*members/1024/1024/1024), 2) log_space  FROM v$log) a,

  (SELECT NVL(ROUND(SUM(BYTES/1024/1024/1024),2), 0) tempspace FROM dba_temp_files) c;

Total_DB_Size (G)

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

            11.29

Do a full backup of the database because the ALTER DATABASE CHARACTER SET statement cannot be rolled back.

SQL> select name from v$database;

NAME

---------

TEST

SQL > select value from NLS_DATABASE_PARAMETERS where Parameter='NLS_CHARACTERSET';

VALUE

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

WE8ISO8859P1

SQL> SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_NCHAR_CHARACTERSET';

VALUE

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

AL16UTF16

SQL> select * from v$nls_parameters where parameter like '%CHARACTERSET';

PARAMETER

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

VALUE

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

NLS_CHARACTERSET

WE8ISO8859P1

NLS_NCHAR_CHARACTERSET

AL16UTF16

16UTF16

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')

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

AMERICAN_AMERICA.WE8ISO8859P1

SQL> exit

$

$ expdp directory=TEST dumpfile=FULL_TEST_10112012.dmp logfile=FULL_TEST_10112012.log full=y

Full Database backup is taken, 

SQL> select distinct(nls_charset_name(charsetid)) CHARACTERSET,

decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2','UNKOWN'),

9, decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKOWN'),

96, decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKOWN'),

112, decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKOWN')) TYPES_USED_IN

from sys.col$ where charsetform in (1,2) and type# in (1, 9, 96, 112) order by CHARACTERSET;

CHARACTERSET                             TYPES_USED_IN

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

AL16UTF16                                NCHAR

AL16UTF16                                NCLOB

AL16UTF16                                NVARCHAR2

WE8ISO8859P1                             CHAR

WE8ISO8859P1                             CLOB

WE8ISO8859P1                             VARCHAR2

6 rows selected.

Oracle note suggests that if the character set conversion has happened between  a 7/8 bit character set like WE8ISO8859P1, US7ASCII etc to a mutibyte character set like UTF8, AL32UTF8 etc,

then there will be data loss for clob columns which display the old character set.

So it is best to take a full back of the database, preferably using the tradional export utility.

SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;

ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8
*
ERROR at line 1:

ORA-12719: operation requires database is in RESTRICTED mode

sql> shut immediate

SQL> startup Restrict

SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;

 Database altered.

after changing the character set bounced the database

SQL> select distinct(nls_charset_name(charsetid)) CHARACTERSET,

decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2','UNKOWN'),

9, decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKOWN'),

96, decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKOWN'),

112, decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKOWN')) TYPES_USED_IN

from sys.col$ where charsetform in (1,2) and type# in (1, 9, 96, 112) order by CHARACTERSET;

CHARACTERSET                             TYPES_USED_IN

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

AL16UTF16                                NCHAR

AL16UTF16                                NCLOB

AL16UTF16                                NVARCHAR2

AL32UTF8                                 CHAR

AL32UTF8                                 CLOB

AL32UTF8                                 VARCHAR2

6 rows selected.

SQL> select value from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';

VALUE

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

AL32UTF8

SQL>  select userenv('language') from dual;

USERENV('LANGUAGE')

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

AMERICAN_AMERICA.AL32UTF8

SQL>  SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_NCHAR_CHARACTERSET';

VALUE

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

AL16UTF16

SQL>  select * from v$nls_parameters where parameter like '%CHARACTERSET';

PARAMETER

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

VALUE

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

NLS_CHARACTERSET

AL32UTF8

NLS_NCHAR_CHARACTERSET

AL16UTF16

NOTE:   we can use new GUI based DMU (Database Migration Assistant for Unicode) tool to convert the NLS_CHARACTERSET of an existing database to AL32UTF8 or UTF8

How to Migrate a WE8ISO8859P1 DB to AL32UTF8 using DMU 1.2 - an example (Doc ID 1546507.1)

50,000 BLOG HITS

Thank you all Oracle lovers/users.........today is the day i need to celebrate that my blog has reached a 50K hits i thank once again all the oracle users whether it is useful or not useful to you but i keep on posting a new topics and issues what ever i face

Oracle Drop Database 11gR2 on LINUX

Dropping a database will remove all corresponding datafiles, redo log files and control files. The database must be mounted in exclusive and restricted mode.

Drop database command will not do any effect on archived log files and backups of the database. Drop database command will not delete the files on RAW disks.

If you created your database with DBCA, you can use the same DBCA tool to drop the database.

Example.

$export ORACLE_SID=DEVDB

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Sep 28 18:18:41 2012

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

Connected to:

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

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>DROP DATABASE;

DROP DATABASE

*

ERROR at line 1:

ORA-01586: database must be mounted EXCLUSIVE and not open for this operation

NOTE :  Drop database does not work when the database is opened in normal open mode

SQL > shut immediate

SQL> connect / as sysdba

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 282352256 bytes

Fixed Size                  2132856 bytes

Variable Size            1644174848 bytes

Database Buffers         1140850688 bytes

Redo Buffers               36388864 bytes

Database mounted.

SQL> alter system enable restricted session;

System altered.

Or open the database using “startup mount Restrict” command

SQL> drop database;

Database dropped.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

Now, all the Control files, redo log files and Datafiles are automatically removed but you can still see the Parameter file and archivelog files.

Note:     If your database is running on windows you have to remove the registry entries manually.

Datapump PARTITION_OPTION parameter

PARTITION_OPTIONS=  NONE | DEPARTITION | MERGE

·        NONE:  The partitions are created exactly as they were on the system the export was taken from.

·        DEPARTITION: Each partition and sub-partition is created as a separate table, named using a combination of the table and (sub-) partition name.

·        MERGE: Combines all partitions into a single table.

Here I want to Import schema1 partitioned tables to schema2 non-partitioned tables, to do so

    1) Take data pump complete Export of schema1

    2) Import into schema2 as below

NOTE :    Here “schema2” is not there but creates automatically during the Datapump import process

SQL> select * from v$version;

BANNER

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

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

PL/SQL Release 11.2.0.2.0 - Production

CORE    11.2.0.2.0      Production

TNS for Linux: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 - Production

SQL>

$ impdp dumpfile=xxx.dmp directory=EXP remap_schema=schema1:schema2 PARTITION_OPTIONS=merge

Import: Release 11.2.0.2.0 - Production on Thu Sep 27 20:38:11 2012

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYS"."SYS_IMPORT_FULL_05" successfully loaded/unloaded

Starting "SYS"."SYS_IMPORT_FULL_05":  /******** AS SYSDBA dumpfile=xxx.dmp directory=EXP remap_schema= schema1:schema2 PARTITION_OPTIONS=merge

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "SCHEMA2"."TABLE1":"SYS_P23561"  14.92 KB      34 rows

--------      OUTPUT TRIMMED          ------------------

Job "SYS"."SYS_IMPORT_FULL_05" completed with 12 error(s) at 20:39:53

A new schema2 will be created and all the partitioned tables are created as Single tables (non-partitioned)

STEPS TO CREATE ORACLE DATABASE MANUALLY ON LINUX

Step 1:
Prepare the database creation script. Following is my script "testdb.sql"

CREATE DATABASE TESTDB

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '/opt/oracle/TESTDB/redo01.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 2 '/opt/oracle/TESTDB/redo02.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 3 '/opt/oracle/TESTDB/redo03.log'  SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

  '/opt/oracle/TESTDB/system01.dbf' SIZE 200M,

 '/opt/oracle/TESTDB/users01.dbf' SIZE 100M

sysaux datafile '/opt/oracle/TESTDB/sysaux01.dbf' SIZE 200M

undo tablespace undotbs1

datafile '/opt/oracle/TESTDB/undotbs01.dbf' SIZE 100M

CHARACTER SET WE8MSWIN1252

;

NOTE :  You can get this similar script , when you perform ‘ALTER DATABASE BACKUP CONTROLFILE TO TRACE’, we can edit this script.

Step 2:

Create all the necessary directories.

Oracle:/opt/oracle $ mkdir TESTDB

Similarly create all necessary directories 

Step 3:

Prepare the init file(pfile) [initTESTDB.ora]

TESTDB.__db_cache_size=436207616

TESTDB.__java_pool_size=4194304

TESTDB.__large_pool_size=4194304

TESTDB.__oracle_base='/opt/oracle'#ORACLE_BASE set from environment

TESTDB.__pga_aggregate_target=432013312

TESTDB.__sga_target=641728512

TESTDB.__shared_io_pool_size=0

TESTDB.__shared_pool_size=184549376

TESTDB.__streams_pool_size=0

*.audit_file_dest='/opt/oracle/admin/TESTDB/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/opt/oracle/TESTDB/control01.ctl','/opt/oracle/fra/TESTDB/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='TESTDB'

*.db_recovery_file_dest='/opt/oracle/fra'

*.db_recovery_file_dest_size=4227858432

*.diagnostic_dest='/opt/oracle/'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=TESTDBXDB)'

#*.local_listener='LISTENER_TESTDB'

*.memory_target=1073741824

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS'

Step 4:

Now perform the following steps:

$ export ORACLE_SID=TESTDB

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Thu May 22 17:35:28 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL >

SQL> startup nomount  pfile=/u01/app/oracle/product/10.2.0/db_1/dbs/initTESTDB.ora

ORACLE instance started.

Total System Global Area 603979776 bytes

Fixed Size 1263176 bytes

Variable Size 167774648 bytes

Database Buffers 427819008 bytes

Redo Buffers 7122944 bytes

SQL> @testdb.sql

Database created.

Step 5:

So your database is created. Now just run the catalog.sql and catproc.sql scripts.

You will find the in $ cd $ORACLE_HOME/rdbms/admin

SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql

SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql

This will create all dictionary views, now verify

SQL> select name from v$database;

NAME

---------

TESTDB

Okay, now your database is ready to use.

Steps to change database name only

$ cat /etc/redhat-release

Red Hat Enterprise Linux Server release 5.7 (Tikanga)

SQL> select * from v$version;

BANNER

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

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

PL/SQL Release 11.2.0.2.0 - Production

CORE    11.2.0.2.0      Production

TNS for Linux: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 – Production

Now, I want to change only database name from DUMMYDB to DEMODB for my standalone dev database

Follow the same steps from my last post Steps to Rename database name and DBID

NOTE: This is tested on my Standalone Development database, for RAC please see [Doc ID 464922.1]

STEP 1 ) Shut down the database and open database in mount

SQL> select name from v$database;

NAME

---------

DUMMYDB

SQL> select instance_name,status from v$instance;

INSTANCE_NAME       STATUS

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

DUMMYDB                       OPEN

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size                  2233336 bytes

Variable Size             683674632 bytes

Database Buffers          377487360 bytes

Redo Buffers                5541888 bytes

Database mounted.

SQL>

Step 2) Invoke the DBNEWID utility (nid) specifying the new database name in DBNAME and set=YES

$ nid  TARGET=/   dbname=DEMODB  SETNAME=YES

DBNEWID: Release 11.2.0.2.0 - Production on Tue Sep 18 14:00:18 2012

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

Connected to database DUMMYDB (DBID=471263051)

Connected to server version 11.2.0

Control Files in database:

    /opt/oracle/oradata/TESTDB/control01.ctl

    /opt/oracle/app/fast_recovery_area/TESTDB/control02.ctl

Change database name of database DUMMYDB to DEMODB? (Y/[N]) => Y

Proceeding with operation

Changing database name from DUMMYDB to DEMODB

    Control File /opt/oracle/oradata/TESTDB/control01.ctl - modified

    Control File /opt/oracle/app/fast_recovery_area/TESTDB/control02.ctl - modified

    Datafile /opt/oracle/oradata/TESTDB/system01.db - wrote new name

    Datafile /opt/oracle/oradata/TESTDB/users01.db - wrote new name

    Datafile /opt/oracle/oradata/TESTDB/sysaux01.db - wrote new name

    Datafile /opt/oracle/oradata/TESTDB/undotbs01.db - wrote new name

    Control File /opt/oracle/oradata/TESTDB/control01.ctl - wrote new name

    Control File /opt/oracle/app/fast_recovery_area/TESTDB/control02.ctl - wrote new name

    Instance shut down

Database name changed to DEMODB.

Modify parameter file and generate a new password file before restarting.

Succesfully changed database name.

DBNEWID - Completed succesfully.

Step 3) Make changes to pfile and password file and opened the database with resetlogs option

$ cd $ORACLE_HOME/dbs

Edit pfile   ---->  initDEMODB.ora

Vi   initDEMODB.ora

Change ‘dbname’

NOTE: database need not to be open with RESTLOGS option

$ export ORACLE_SID=DEMODB

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Sep 18 14:03:10 2012

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

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size                  2233336 bytes

Variable Size             616565768 bytes

Database Buffers          444596224 bytes

Redo Buffers                5541888 bytes

Database mounted.

Database opened.

SQL> select name from v$database;

NAME

---------

DEMODB

Now, your database name has been changed :-)

See:  How to Change the DBID, DBNAME Using NID Utility in version 10gR2 onwards (Doc ID 863800.1)