Problem: To change DB name from db11g to db11gnew without using nid utility ,
SQl> show parameter db_name ;
NAME TYPE VALUE
------------------------------------ ----------- -----
db_name string db11g
SQL> ARCHIVE LOG LIST ;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4
SQL> alter system switch logfile;
System altered.
SQL> alter database backup controlfile to trace as '/home/oracle/control_trace.sql' ; SQL> SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type" FROM sys.v_$parameter WHERE name = 'spfile';
Init File Type
----------------
SPFILE
SQL>create pfile=' /home/oracle/initdb11gnew.ora' from spfile ;
Database altered.
[oracle@oracle11g dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jan 29 18:04:37 2015
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, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Edit to control file trace /home/oracle/control_trace.sql . Remove everything above Set#2 : Resetlog case and edit the create statement as : CREATE CREATE CONTROLFILE SET DATABASE "db11gnew"
[oracle@oracle11g ~]$ cat control_trace.sql -- Set #2. RESETLOGS case -- -- The following commands will create a new control file and use it -- to open the database. -- Data used by Recovery Manager will be lost. -- The contents of online logs will be lost and all backups will -- be invalidated. Use this only if online logs are damaged. -- After mounting the created controlfile, the following SQL -- statement will place the database in the appropriate -- protection mode: -- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT CREATE CONTROLFILE SET DATABASE "db11gnew" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 ( '+DATA2/db11g/onlinelog/group_1.261.868740309', '+RMAN_DISK/db11g/onlinelog/group_1.257.868740313' ) SIZE 50M BLOCKSIZE 512, GROUP 2 ( '+DATA2/db11g/onlinelog/group_2.262.868740319', '+RMAN_DISK/db11g/onlinelog/group_2.258.868740323' ) SIZE 50M BLOCKSIZE 512, GROUP 3 ( '+DATA2/db11g/onlinelog/group_3.263.868740329', '+RMAN_DISK/db11g/onlinelog/group_3.259.868740333' ) SIZE 50M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE '+DATA2/db11g/datafile/system.256.868740081', '+DATA2/db11g/datafile/sysaux.257.868740081', '+DATA2/db11g/datafile/undotbs1.258.868740081', '+DATA2/db11g/datafile/users.259.868740081' CHARACTER SET WE8MSWIN1252 ; ALTER DATABASE OPEN RESETLOGS; ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA2' SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Rename the current init file and spfile as Bkp files .
Copy the init file create above in $ORACLE_HOME/dbs and change the db_name to db11gnew .
[oracle@oracle11g dbs]$ cp /home/oracle/initdb11gnew.ora $ORACLE_HOME/dbs
[oracle@oracle11g dbs]$ ls
Shu immediate and Edit the init file with the new control_file values . Again start the DB using pfile and create spfile .
[oracle@oracle11g ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 29-JAN-2015 18:18:07
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER2
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 29-JAN-2015 17:27:48
Uptime 0 days 0 hr. 50 min. 19 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/11.2.0/grid_11203/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/oracle11g/listener2/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle11g.localdomain)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "db11gXDB" has 1 instance(s).
Instance "db11gnew", status READY, has 1 handler(s) for this service...
Service "db11gnew" has 1 instance(s).
Instance "db11gnew", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@oracle11g ~]$
Check for the new path of control files created in ASM using asmcmd you can check that .
Shutdown the DB and change the new path of control_file in initdb11gnew.ora .
Start the db using initdb11gnew.ora and recreate the spfile , shutdown again and startup DB using spfile .
Total System Global Area 805875712 bytes
Fixed Size 2232680 bytes
Variable Size 478154392 bytes
Database Buffers 322961408 bytes
Redo Buffers 2527232 bytes
Database mounted.
Database opened.
SQL> show parameter pfile ;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA2/db11gnew/parameterfile/ spfile.268.870289035
SQL> show parameter control_file;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DATA2/db11gnew/controlfile/current.266.870286263, +RMAN_DISK/db11gnew/controlfile/current .262.870286265
To check Datafile fuzziness:
=======================
set numwidth 30;
set pagesize 50000;
alter session set nls_date_format = 'DD-MON-RRRR HH24:MI:SS';
set line 1000
SQL> select status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, count(*), fuzzy from v$datafile_header group by status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, fuzzy;
The different upgrade methods you can use to upgrade your database to the new Oracle Database release or higher are:
1) Database Upgrade Assistant (DBUA)
2) Manual Upgrade
3) Export/Import
4) Data Copying
5) Golden Gate
1) DBUA (Database Upgrade Assistant)
The Database Upgrade Assistant (DBUA) interactively steps you through the upgrade process and configures the database for the new Oracle Database release. The DBUA automates the upgrade process by performing all of the tasks normally performed manually. The DBUA makes appropriate recommendations for configuration options such as tablespaces and redo logs. You can then act on these recommendations. This method is very easy and user friendly. But if any error occurs it will take time to diagnose the error as the upgrade process is done automatically by the upgrade assistant.
For more information, refer to the following link:
Refer to the following oracle support notes for DBUA upgrade:
================================================ NOTE 556477.1 Complete Checklist for Upgrades to 11gR1 using DBUA NOTE 870814.1 Complete checklist to upgrade the database to 11g R2 using DBUA NOTE 1516557.1 Complete Checklist for Upgrading to Oracle Database 12c Release 1 (12.1) using DBUA
Upgrade Advisor: Database from 10.2 to 11.2 (Doc ID 251.1) How to Download and Run Oracle's Database Pre-Upgrade Utility (Doc ID 884522.1) 2) Manual upgrade
A manual upgrade consists of running SQL scripts and utilities from a command line to upgrade a database to the new Oracle Database release. A manual upgrade gives you finer control over the upgrade process as it is done step by step manually. So if any error occurs, it is easier to diagnose the error. While a manual upgrade gives you finer control over the upgrade process, it is more susceptible to error if any of the upgrade or pre-upgrade steps are either not followed or are performed out of order.
When manually upgrading a database, perform the following pre-upgrade steps:
Analyze the database using the Pre-Upgrade Information Tool. The Upgrade Information Tool is a SQL script that ships with the new Oracle Database release, and must be run in the environment of the database being upgraded.
The Upgrade Information Tool displays warnings about possible upgrade issues with the database. It also displays information about required initialization parameters for the new Oracle Database release.
Prepare the new Oracle Home.
Perform a backup of the database.
Depending on the release of the database being upgraded, you may need to perform additional pre-upgrade steps (adjust the parameter file for the upgrade, remove obsolete initialization parameters and adjust initialization parameters that might cause upgrade problems).
Refer to the following oracle support notes for manual upgrade:
================================================
NOTE 1503653.1 Complete Checklist for Manual Upgrades to Oracle Database 12c Release 1 (12.1)
NOTE 837570.1 Complete Checklist for Manual Upgrades to 11gR2
NOTE 429825.1 Complete Checklist for Manual Upgrades to 11gR1
NOTE 263809.1 Complete checklist for manual upgrades to 10gR1 (10.1.0.x).
NOTE 316889.1 Complete checklist for manual upgrades to 10gR2.
NOTE 466181.1 10g Upgrade Companion
NOTE 601807.1 Oracle 11gR1 Upgrade Companion
Note: DBUA can be used only if the source & target Oracle homes are on the same Server.
But manual upgrade will work even if the source and target Home are on different servers provided that the Hardware architecture and operating system on source and target Home are the same.
Example :
- You cannot manually upgrade a database from an AIX Operating System to Solaris Operating System.
- You cannot manually upgrade a database from a Solaris X86-64 Machine to Solaris SPARC 64-bit.
3) Export/Import
The Export/Import upgrade method does not change the current database, which enables the database to remain available throughout the upgrade process. However, if a consistent snapshot of the database is required (for data integrity or other purposes), then the database must run in restricted mode or must otherwise be protected from changes during the export procedure. Because the current database can remain available, you can, for example, keep an existing production database running while the new Oracle Database is being built at the same time by Export/Import. During the upgrade, to maintain complete database consistency, changes to the data in the database cannot be permitted without the same changes to the data in the new Oracle Database.
Most importantly, the Export/Import operation results in a completely new database. Although the current database ultimately contains a copy of the specified data, the upgraded database may perform differently from the original database. For example, although Export/Import creates an identical copy of the database, other factors, such as disk placement of data and unset tuning parameters, may cause unexpected performance problems.
Upgrading an entire database by using Export/Import can take a long time, especially compared to using the DBUA or performing a manual upgrade. Therefore, you may need to schedule the upgrade during non-peak hours or make provisions for propagating to the new database any changes that are made to the current database during the upgrade.
NOTE: Export/Import works for all versions. But for 10g and above, DataPump or Transportable Table Spaces methods are better.
NOTE 351598.1 Export/Import DataPump: The Minimum Requirements to Use Export DataPump and Import DataPump (System Privileges)
4) Data Copying
You can copy data from one Oracle Database to another using database links. For example, you can create new tables and fill the tables with data by using the INSERT INTO statement and the CREATE TABLE ... AS statement. Copying data and Export/Import offer the same advantages for upgrading. Using either method, you can defragment data files and restructure the database by creating new tablespaces or modifying existing tables or tablespaces. In addition, you can copy only specified database objects or users.
Copying data, however, unlike Export/Import, enables the selection of specific rows of tables to be placed into the new database. Copying data is a good method for copying only part of a database table. In contrast, using Export/Import, you can copy only entire tables
In this article we will do , out-of-Place Manual Upgrade from Previous 11.2.0.N Version to the Latest 11.2.0.N Patchset:
The 11.2.0.2 and later patchsets are a full release. The 11.2 Patchset Installer does not update existing 11.2 installations.The install process performs a new installation whether you are doing out-of-place upgrade or in-place upgrade.
Starting with 11.2.0.2, you have two ways to apply a patchset:
Out-of-place upgrade (Recommended)
In-place upgrade
In the below example we are going to upgrade 11.2.0.3 DB to 11.2.0.4 using out-of-place manual upgrade method :
Current GRID Version : 11.2.0.3 ---> Upgrade to 11.2.0.4
Current GRID_HOME : /data01/app/11.2.0/grid_11203
Upgrade to GRID_HOME : /data01/app/11.2.0/grid_11204
Current RDBMS Version : 11.2.0.3 ---> Upgrade to 11.2.0.4
Current ORACLE_HOME : /data01/app/oracle/product/11.2.0.3/db_1
Upgrade to ORACLE_HOME : /data01/app/oracle/product/11.2.0.4/db_1
1. Install 11.2.0.4 binaries in new ORACLE_HOME /data01/app/oracle/product/11.2.0.4/db_1
Download the binaries from oracle support website and unzip in a directory as below:
unzip p13390677_112040_Linux-x86-64_1of7 -d /home/oracle/software
unzip p13390677_112040_Linux-x86-64_2of7 -d /home/oracle/software
STEP 2 ======
Install the latest 11.2 RDBMS Software into a new ORACLE_HOME.
Please note that with the 11.2 installation forward, all base RDBMS components are installed. The only options are if a component is linked on or off (active and able to be used). Custom installations are not possible.
You can run "opatch lsinventory -detail" against previous and new ORACLE_HOME to compare installed products.
Also please note:
rootupgrade.sh will fail if you are using /opt/oracle as the ORACLE_BASE. For further information please see:
Note: 1281913.1 Root Script Fails if ORACLE_BASE is set to /opt/oracle
Start the runInstaller :
[oracle@node1 database]$ ls -lrth
total 60K
drwxrwxr-x 2 oracle oinstall 4.0K Aug 27 2013 sshsetup
-rwxrwxr-x 1 oracle oinstall 3.2K Aug 27 2013 runInstaller
drwxrwxr-x 2 oracle oinstall 4.0K Aug 27 2013 rpm
drwxrwxr-x 2 oracle oinstall 4.0K Aug 27 2013 response
-rwxrwxr-x 1 oracle oinstall 30K Aug 27 2013 readme.html
drwxrwxr-x 14 oracle oinstall 4.0K Aug 27 2013 stage
-rwxrwxr-x 1 oracle oinstall 500 Aug 27 2013 welcome.html
drwxrwxr-x 4 oracle oinstall 4.0K Aug 27 2013 install
Checking Temp space: must be greater than 120 MB. Actual 4702 MB Passed
Checking swap space: must be greater than 150 MB. Actual 1285 MB Passed
Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2015-01-16_10-11-15AM. Please wait ...
[oracle@node1 database]$ You can find the log of this install session at:
/data01/app/oraInventory/logs/installActions2015-01-16_10-11-15AM.log
When you click next it will promt you to run root.sh of node1 & node2 . So execute the root.sh using root on both nodes :
[root@node1 ~]# sh /data01/app/oracle/product/11.2.0.4/db_1/root.sh
Performing root user operation for Oracle 11g
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /data01/app/oracle/product/11.2.0.4/db_1
Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Finished product-specific root actions.
Check the current version of the 11.2.0.1 Oracle RDBMS time zone definitions -
If this reports version 14 then there is no action to take for the Oracle time zone definitions, you can upgrade from 10.1.0.5 / 10.2.0.x or 11.1.0.x to 11.2.0.2 without any action on TSLTZ or TSTZ data.
If it is not 14 follow the doc : Actions For DST Updates When Upgrading To Or Applying The 11.2.0.2 Patchset (Doc ID 1201253.1)
SQL>SELECT version FROM v$timezone_file;
VERSION
----------
14
Now run the Pre-upgrade Utility tool and follow the recommendations:
[oracle@node1 ~]$ cp /data01/app/oracle/product/11.2.0.4/db_1/rdbms/admin/utlu112i.sql /home/oracle/upgrade/
[oracle@node1 ~]$ echo $ORACLE_HOME
/data01/app/oracle/product/11.2.0.3/db_1
[oracle@node1 ~]$ [oracle@node1 ~]$ /data01/app/oracle/product/11.2.0.3/db_1/bin/sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 16 12:19:13 2015 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, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> spool pre_upgrade_utlu112i_log.log SQL> @/home/oracle/upgrade/utlu112i.sql Oracle Database 11.2 Pre-Upgrade Information Tool 01-16-2015 12:21:01 Script Version: 11.2.0.4.0 Build: 001 . ********************************************************************** Database: ********************************************************************** --> name: RACDB --> version: 11.2.0.3.0 --> compatible: 11.2.0.0.0 --> blocksize: 8192 --> platform: Linux x86 64-bit --> timezone file: V14 . ********************************************************************** Tablespaces: [make adjustments in the current environment] ********************************************************************** --> SYSTEM tablespace is adequate for the upgrade. .... minimum required size: 932 MB --> SYSAUX tablespace is adequate for the upgrade. .... minimum required size: 724 MB --> UNDOTBS1 tablespace is adequate for the upgrade. .... minimum required size: 400 MB --> TEMP tablespace is adequate for the upgrade. .... minimum required size: 60 MB . ********************************************************************** Flashback: OFF ********************************************************************** ********************************************************************** Update Parameters: [Update Oracle Database 11.2 init.ora or spfile] Note: Pre-upgrade tool was run on a lower version 64-bit database. ********************************************************************** --> If Target Oracle is 32-Bit, refer here for Update Parameters: -- No update parameter changes are required. . --> If Target Oracle is 64-Bit, refer here for Update Parameters: -- No update parameter changes are required. . ********************************************************************** Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile] ********************************************************************** -- No renamed parameters found. No changes are required. . ********************************************************************** Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile] ********************************************************************** -- No obsolete parameters found. No changes are required . ********************************************************************** Components: [The following database components will be upgraded or installed] ********************************************************************** --> Oracle Catalog Views [upgrade] VALID --> Oracle Packages and Types [upgrade] VALID --> JServer JAVA Virtual Machine [upgrade] VALID --> Oracle XDK for Java [upgrade] VALID --> Real Application Clusters [upgrade] VALID --> Oracle Workspace Manager [upgrade] VALID --> OLAP Analytic Workspace [upgrade] VALID --> OLAP Catalog [upgrade] VALID --> EM Repository [upgrade] VALID --> Oracle Text [upgrade] VALID --> Oracle XML Database [upgrade] VALID --> Oracle Java Packages [upgrade] VALID --> Oracle interMedia [upgrade] VALID --> Spatial [upgrade] VALID --> Expression Filter [upgrade] VALID --> Rule Manager [upgrade] VALID --> Oracle Application Express [upgrade] VALID ... APEX will only be upgraded if the version of APEX in ... the target Oracle home is higher than the current one. --> Oracle OLAP API [upgrade] VALID . ********************************************************************** Miscellaneous Warnings ********************************************************************** WARNING: --> The "cluster_database" parameter is currently "TRUE" .... and must be set to "FALSE" prior to running a manual upgrade. WARNING: --> Your recycle bin contains 2 object(s). .... It is REQUIRED that the recycle bin is empty prior to upgrading .... your database. The command: PURGE DBA_RECYCLEBIN .... must be executed immediately prior to executing your upgrade. WARNING: --> Database contains schemas with objects dependent on DBMS_LDAP package. .... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs. .... USER APEX_030200 has dependent objects. . ********************************************************************** Recommendations ********************************************************************** Oracle recommends gathering dictionary statistics prior to upgrading the database. To gather dictionary statistics execute the following command while connected as SYSDBA: EXECUTE dbms_stats.gather_dictionary_stats; ********************************************************************** Oracle recommends reviewing any defined events prior to upgrading. To view existing non-default events execute the following commands while connected AS SYSDBA: Events: SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2 WHERE UPPER(name) ='EVENT' AND isdefault='FALSE' Trace Events: SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2 WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE' Changes will need to be made in the init.ora or spfile. **********************************************************************
Backup database for a Manual Upgrade:
run {
Backup database format 'someformatdirectory%U' TAG BEFORE_UPGRADE ;
Backup current controlfile format '/location/control_backup_name' ;
}
Prepare the New Oracle Home for the Upgrade:
1. Copy configuration files from 11.2.0.3 home to 11.2.0.4 home $ORACLE_HOME/dbs ( pfile , spfile)
2. Take backup of spfile also by creating a pfile at some non-default location
3. copy the password file from old home to new home
4. If you are doing a cluster database upgrade perform above steps on all nodes
5. In the pfile make necessary changes like :
--- Remove obsolete parameters
--- Make sure compatibility parameter is 11.2
-- Adjust other parameters as suggested by pre-upgrade utility
-- Make sure all paths are fully specified in the pfile
-- If you are upgrading a cluster database , make sure you make cluster_database=FALSE and after upgrade
you must set this back to TRUE
Manually Upgrade the database :
1. Shutdown the database
2. change the PATH and bash profiles to point to new $ORACLE_HOME
3. If there us a different user and group for GRID and Oracle RDBMS then run the setasmgidwrap script which is located in $GRID_HOME/bin
against the new $ORACLE_HOME/bin/oracle directory with the -o option as shown below:
Run on all nodes :
[root@node1 ~]# ls -lrth /data01/app/oracle/product/11.2.0.3/db_1/bin/oracle*
11.For cluster database change the CLUSTER_DATABASE=TRUE and stop DB and start the database using the srvctl utility
SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE11.2.0.4.0Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
Follow the below steps to change only the dbid :
====================================
1. Backup the database
2. Shutdown all instances in the cluster make sure that this is a consistent shutdown done using shutdown immediate or shutdown normal command.
3. Make the parameter cluster_database=false
4. Startup mount only on one of the instances
5. Invoke the DBNEWID utility (nid) from the command line using a user with SYSDBA privilege.Do not specify a new DBNAME: $ORACLE_HOME/bin/nid TARGET=SYS/password@DB11G
6. Shutdown IMMEDIATE of the database (Note: This step is not required in 10g)
7. Open the database with resetlogs
8 . Make the parameter cluster_database=true and shutdown the current instance and startup all the instances
9. Take full backup of the database .
Follow the below steps to change only the DBNAME :
=========================================
1. Backup the database
2. Shutdown all instances in the cluster make sure that this is a consistent shutdown done using shutdown immediate or shutdown normal command.
3. Set the parameter CLUSTER_DATABASE=false
4. Startup mount only on one of the instances
5. Invoke the utility on the command line, specifying a valid user with the SYSDBA privilege.You must specify both the DBNAME and SETNAME parameters.
The SETNAME parameter tells the DBNEWID utility to only alter the database name.
6. Shutdown IMMEDIATE of the database
7. Set the DB_NAME initialization parameter in the initialization parameter file (PFILE) to the new database name
8. Create a new password file.
9. Open the database. When opening the database the RESETLOGS option is not needed so the database can be started using the STARTUP command.
11. Make the parameter CLUSTER_DATABASE=true and shutdown the current instance and startup all the instances.
Follow the below steps to Change Both DBID and DBNAME :
===============================================
1. Backup the database
2. Shutdown all instances in the cluster make sure that this is a consistent shutdown done using shutdown immediate or shutdown normal command.
3. Make the parameter CLUSTER_DATABASE=false
4. Startup mount only on one of the instances
5. Invoke the DBNEWID utility on the command line, specifying a valid user with the SYSDBA privilege
6. Shutdown IMMEDIATE of the database
7. Set the DB_NAME initialization parameter in the initialization parameter file (PFILE) to the new database name
8. Create a new password file.
10. Mount and Open the database with resetlogs
11. Make the parameter CLUSTER_DATABASE=true and shutdown the current instance and startup all the instances
Some points to consider :
===================
1. The DBNEWID utility does not change the server parameter file (SPFILE). Therefore, if you use SPFILE to start your Oracle database, you must recreate the initialization parameter file from the server parameter file, remove the server parameter file, change the DB_NAME in the initialization parameter file, and then recreate the server parameter file.
2. DBNEWID utility makes the changes in the controlfile and datafile headers. This utility is not RAC aware. So it will not update the OCR when the database is renamed. Hence, the user needs to invoke SRVCTL to remove and again add the database information to OCR
Example Below is for changing just the DBID:
$ORACLE_HOME/bin/nid TARGET=SYS/password@DB11G
DBNEWID: Release 11.2.0.4.0 - Production on Mon Jan 19 03:53:54 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to database DB11G (DBID=338404813)
Connected to server version 11.2.0
Control Files in database:
+DATA/db11g/controlfile/current.261.868735245
+DATA/db11g/controlfile/current.260.868735247
Change database ID of database DB11G? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 338404813 to 339029651
Control File +DATA/db11g/controlfile/current.261.868735245 - modified
Control File +DATA/db11g/controlfile/current.260.868735247 - modified
Datafile +DATA/db11g/datafile/system.256.86873510 - dbid changed
Datafile +DATA/db11g/datafile/sysaux.257.86873510 - dbid changed
Datafile +DATA/db11g/datafile/undotbs1.258.86873510 - dbid changed
Datafile +DATA/db11g/datafile/users.259.86873510 - dbid changed
Datafile +DATA/db11g/tempfile/temp.268.86873527 - dbid changed
Control File +DATA/db11g/controlfile/current.261.868735245 - dbid changed
Control File +DATA/db11g/controlfile/current.260.868735247 - dbid changed
Instance shut down
Database ID for database DB11G changed to 339029651.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.
Troubleshooting a DBID Change Operation
=================================
If the DBNEWID utility succeeds in its validation stage but detects an error while changing the DBID, then the utility stops and leaves the database in the middle of the change. In this case, you cannot open the database until the DBNEWID operation is either completed or reverted. DBNEWID displays messages indicating the status of the operation.
Before continuing or reverting, fix the underlying cause of the error. Sometimes the only solution is to restore the whole database from a recent backup and perform recovery to the point in time before DBNEWID was started. This underscores the importance of having a recent backup available before running DBNEWID.
If you choose to continue the DBID change operation rather than revert it, reexecute your original command. The DBNEWID utility resumes and attempts to continue the change until all datafiles and control files have the new DBID. At this point, the database is left mounted. You should shut it down and then mount it again prior to opening it with the RESETLOGS option.
If you choose to revert a DBNEWID operation, and if the reversion succeeds, then DBNEWID reverts all performed changes and leaves the database in a mounted state.
To revert a stalled DBID change operation, run the DBNEWID utility again, specifying the REVERT keyword. For example:
% nid TARGET=SYS/oracle REVERT=YES LOGFILE=$HOME/nid.log
Troubleshooting a Database Name Change Operation
=========================================
If you specify that only the database name should be changed (and not the DBID), then the validation process is the same as for a DBID change except that DBNEWID checks only the control files. It does not read the datafiles. If the validation encounters a problem, then the database is left mounted.
It is possible for validation to succeed, but for the actual database name change to fail. The possible failure scenarios depend on how many control files are in the database, as follows:
If you have one or more control files and DBNEWID fails on the first control file, then the database name is not changed in the control file. You can either try the operation again or open the database and resume normal database use.
If you have more than one control file and DBNEWID fails on the second control file or on any one thereafter, then some control files will have the old DBNAME and some will have the new DBNAME. In this case, you must either manually copy the first changed control file to all CONTROL_FILES locations, or revert by copying the unchanged control files to all CONTROL_FILES locations.
If the response file for an 11.2 silent install uses a password that contains the character '%' (percent sign) , then the installation will fail with the following errors: SEVERE:Unknown environment variable: %1 [FATAL] [INS-00001] Unknown irrecoverable error CAUSE: No additional information available. Workaround: Change any passwords in the response file that use the '%' character.
