As per the requirement from the Application Vendor, we had to change the database character set on one of the existing test databases.
CSSCAN Utility and CSALTER Script:
Database character set migration has two stages: data scanning (csscan) and data conversion (csalter.plb).
The CSSCAN Utility is used to denitrify possible database character set conversion problems and truncation of data. Data scanning identifies the amount of effort required to migrate data into the new character encoding scheme before changing the database character set. This information helps to determine the best approach for converting the database character set.
The CSALTER script is part of the Database Character Set Scanner utility. The CSALTER script is the most straightforward way to migrate a character set, but it can be used only if all of the schema data is a strict subset of the new character set. The new character set is a strict superset of the current character set if:
Each and every character in the current character set is available in the new character set.
Each and every character in the current character set has the same code point value in the new character set.
Here are the simple steps to migrate the default Database Character Set:
Database Version: Oracle 10g Database R2 (10.2.0.3)
Source Character Set: AL32UTF8
Target Character Set: WE8ISO8859P1
Shut down the database, using either a SHUTDOWN IMMEDIATE or a SHUTDOWN NORMAL statement.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Take a full database backup:
Sometimes, incorrect data conversion can lead to data corruption, so perform a full backup of the database before attempting to migrate the data to a new character set. The database can be reconstructed later using the backup taken before this action, if something goes wrong.
Note: We have taken the cold backup of the database and also performed the full database export using Datapump utility (expdp/impdp). It’s up to you, how and what type of full backup you prefer for your database i.e. Cold/Hot Backup/RMAN. So, you should recreate the database using this backup when the conversion fails.
Start up the database:
SQL> startup;
ORACLE instance started.
Total System Global Area 2097152000 bytes
Fixed Size 2074120 bytes
Variable Size 771754488 bytes
Database Buffers 1308622848 bytes
Redo Buffers 14700544 bytes
Database mounted.
Database opened.
Run the Database Character Set Scanner (CSSCAN) utility.
This CSSCAN Utility executable is under the $ORACLE_HOME/bin directory.
$ csscan \"sys/password@mydb as sysdba\" full=y
Character Set Scanner v2.1 : Release 10.2.0.3.0 - Production on Sat Sep 13 15:58:28 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Current database character set is AL32UTF8.
Enter new database character set name: > WE8ISO8859P1
Enter array fetch buffer size: 1024000 >
Enter number of scan processes to utilize(1..32): 1 > 32
Enumerating tables to scan...
. process 1 scanning SYS.SOURCE$[AAAABIAABAAAAIRAAA]
. process 2 scanning SYS.SOURCE$[AAAABIAABAAAHCJAAA]
. process 3 scanning SYS.ATTRIBUTE$[AAAAC1AABAAAAURAAA]
…………………………………………………………………………….
…………………………………………………………………………….
…………………………………………………………………………….
Creating Database Scan Summary Report...
Creating Individual Exception Report...
Scanner terminated successfully.
$
For any help on CSSCAN utility i.e. keyword, prompt, and description, use the following command.
$ csscan help=y
Run the CSALTER script.
Once the Database Character Set Scanning has completed successfully, the database must be opened in restricted mode, because no normal user should allow to access the database during this task is being performed. So you can run the CSALTER script as the SYS user. The location of the CSALTER Script is "$ORACLE_HOME/rdbms/admin/csalter.plb".
Shut Down the Database
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Start Up the Database in Restricted Mode.
SQL> startup restrict;
ORACLE instance started.
Total System Global Area 2097152000 bytes
Fixed Size 2074120 bytes
Variable Size 771754488 bytes
Database Buffers 1308622848 bytes
Redo Buffers 14700544 bytes
Database mounted.
Database opened.
Run the csalter.plb script
SQL> @?/rdbms/admin/csalter.plb
0 rows created.
Function created.
Function created.
Procedure created.
This script will update the content of the Oracle Data Dictionary.
Please ensure you have a full backup before initiating this procedure.
Would you like to proceed (Y/N)?y
old 6: if (UPPER('&conf') <> 'Y') then
new 6: if (UPPER('y') <> 'Y') then
Checking data validility...
begin converting system objects
12 rows in table SYS.WRI$_ADV_RATIONALE are converted
1 row in table MDSYS.SDO_GEOR_XMLSCHEMA_TABLE is converted
80 rows in table SYS.METASTYLESHEET are converted
17 rows in table SYS.WRI$_DBU_HWM_METADATA are converted
79 rows in table SYS.WRI$_DBU_FEATURE_METADATA are converted
4 rows in table SYS.RULE$ are converted
78 rows in table MDSYS.SDO_STYLES_TABLE are converted
10576 rows in table SYS.WRH$_SQL_PLAN are converted
21 rows in table SYS.WRI$_DBU_FEATURE_USAGE are converted
19 rows in table SYS.WRI$_ADV_ACTIONS are converted
4 rows in table MDSYS.SDO_XML_SCHEMAS are converted
2308 rows in table MDSYS.SDO_COORD_OP_PARAM_VALS are converted
578 rows in table SYS.WRI$_ADV_OBJECTS are converted
789 rows in table SYS.WRH$_SQLTEXT are converted
PL/SQL procedure successfully completed.
Alter the database character set...
CSALTER operation completed, please restart database
PL/SQL procedure successfully completed.
0 rows deleted.
Function dropped.
Function dropped.
Procedure dropped.
Shut Down the Database
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Start Up the Database
SQL> startup;
ORACLE instance started.
Total System Global Area 2097152000 bytes
Fixed Size 2074120 bytes
Variable Size 771754488 bytes
Database Buffers 1308622848 bytes
Redo Buffers 14700544 bytes
Database mounted.
Database opened.
SQL>
Make sure the changes:
SQL> SELECT * FROM nls_database_parameters;
References:
Character Set Migration
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14225/ch11charsetmig.htm
Character Set Scanner Utilities
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14225/ch12scanner.htm
Oracle Metalink Notes on Database Character Set Migration
Note: 225912.1 - Subject: Changing the Database Character Set - a short Overview
Note: 119164.1 - Subject: Changing Database Character Set - Valid Superset Definitions
Note: 66320.1 -Subject: Changing the Database Character Set or the Database National Character Set
Note: 123670.1 - Subject: Use Scanner Utility before Altering the Database Character Set
Note: 225938.1 - Subject: Database Character Set Health check.
Conclusion: Here I have only explained the steps I followed to convert the database character set, which is specific to our single instance database. It means that this article doesn’t consist of any other information about Database Character Set, as these details are available and discussed thoroughly in the Oracle Documentations and Oracle Metalink. If the database is a multi instance database i.e. RAC, and has other options enabled in place. I would strongly recommend to follows the Oracle Documentations and Oracle Metalink Notes. Moreover, this scenario, explained here, is specific to our requirement, and need not be the same with others. When you are not sure or in doubt, then do *NOT* do this for other character set combinations without logging a tar with Oracle Support for verification.
Regards,
Sabdar Syed,
http://sabdarsyed.blogspot.com/
Showing posts with label Utilities. Show all posts
Showing posts with label Utilities. Show all posts
Monday, September 15, 2008
Wednesday, August 27, 2008
How to perform a full database export using original export/import utility?
Hello,
In my blog, there is a post already available on how to perform a full database export using the new and enhanced feature of Oracle Database 10g Export Utility, Data Pump – expdp/impdp. However, I’m still being requested, by my blog viewers and OTN forum users, to place a procedure on how to perform a full database export using traditional export utility – exp/imp. However, this is not the complete informative post for export/import utility; so, please refer the Oracle Documents links specified at the end of this post.
Before starting off with the steps, to perform the export at the database level, let’s have clear understanding on the following questions.
Can I perform or consider the export of my database as a backup approach?
Absolutely *NO*, because, the import can be done only up to the time the export was originally taken, i.e. the export dump cant be used to import until the point in time (up-to-date) and will not be imported the updates after the export done. This is not acceptable for Production Databases. So, never treat that, taking the export of the database is part of backup strategy of your Production database. In fact, a couple of other approaches are available to perform the backup of your database using the following ways – Cold Backup (Shutdown/Copy/Startup), Hot Backup (ALETER TABLESPACE ….BEGIN/END BACKUP), and RMAN backup methods.
Situations: When and why to perform a full database export.
* Export/import is only the possible way to construct a similar database on remote servers regardless of Operating System compatibility i.e. cross platform (Export on Linux and Import on Windows Systems).
* To migrate the database from one Operating System to another i.e. (Linux/Unix to Windows), and to upgrade the database from one version to another i.e. 8i-9i or 9i-10g, but not vice versa.
* If you are on Oracle 10g, above two points can be possible, using RMAN Convert Commands i.e. to construct or move a database from one OS to another OS i.e. Cross platform.
* When there is a requirement to clone a database on another test or development server, then take a full database mode export dump and create a database with the similar settings of target database, and import the dump in source database.
Simple Steps: how to perform a full database export using export utility.
* Use either system user or any other database user who has the EXP_FULL_DATABASE privilege.
* Set the NLS_LANG environment variable according the database character set and language details.
SQL> select * from nls_database_parameters
2 where parameter in ('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET');
PARAMETER VALUE
------------------------------ ----------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CHARACTERSET WE8ISO8859P1
Windows (Dos Prompt):
C:\> set NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
Unix/Linux:
$ export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
* Start the export with following command and options.
exp system/password@mydb file=c:\exportdmp\exp_fulldb_MYDB_27Aug08.dmp
full=y log= c:\exportdmp\exp_fulldb_MYDB_27Aug08.log
Note: This is just a simple export command to perform the full database export. I would request and suggest you to refer Oracle Documentations on export/import and their options. Check the references.
Help on Export and Import:
Windows:
C:\> exp help=y
C:\> imp help=y
Linux/Unix.
$ exp help=y
$ imp help=y
References:
Oracle 10g :
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/exp_imp.htm
Oracle 9i:
http://download.oracle.com/docs/cd/B10501_01/server.920/a96652/part1.htm#435787
Oracle 8i:
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76955/ch01.htm
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76955/ch02.htm
Regards,
Sabdar Syed,
http://sabdarsyed.blogspot.com/
In my blog, there is a post already available on how to perform a full database export using the new and enhanced feature of Oracle Database 10g Export Utility, Data Pump – expdp/impdp. However, I’m still being requested, by my blog viewers and OTN forum users, to place a procedure on how to perform a full database export using traditional export utility – exp/imp. However, this is not the complete informative post for export/import utility; so, please refer the Oracle Documents links specified at the end of this post.
Before starting off with the steps, to perform the export at the database level, let’s have clear understanding on the following questions.
Can I perform or consider the export of my database as a backup approach?
Absolutely *NO*, because, the import can be done only up to the time the export was originally taken, i.e. the export dump cant be used to import until the point in time (up-to-date) and will not be imported the updates after the export done. This is not acceptable for Production Databases. So, never treat that, taking the export of the database is part of backup strategy of your Production database. In fact, a couple of other approaches are available to perform the backup of your database using the following ways – Cold Backup (Shutdown/Copy/Startup), Hot Backup (ALETER TABLESPACE ….BEGIN/END BACKUP), and RMAN backup methods.
Situations: When and why to perform a full database export.
* Export/import is only the possible way to construct a similar database on remote servers regardless of Operating System compatibility i.e. cross platform (Export on Linux and Import on Windows Systems).
* To migrate the database from one Operating System to another i.e. (Linux/Unix to Windows), and to upgrade the database from one version to another i.e. 8i-9i or 9i-10g, but not vice versa.
* If you are on Oracle 10g, above two points can be possible, using RMAN Convert Commands i.e. to construct or move a database from one OS to another OS i.e. Cross platform.
* When there is a requirement to clone a database on another test or development server, then take a full database mode export dump and create a database with the similar settings of target database, and import the dump in source database.
Simple Steps: how to perform a full database export using export utility.
* Use either system user or any other database user who has the EXP_FULL_DATABASE privilege.
* Set the NLS_LANG environment variable according the database character set and language details.
SQL> select * from nls_database_parameters
2 where parameter in ('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET');
PARAMETER VALUE
------------------------------ ----------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CHARACTERSET WE8ISO8859P1
Windows (Dos Prompt):
C:\> set NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
Unix/Linux:
$ export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
* Start the export with following command and options.
exp system/password@mydb file=c:\exportdmp\exp_fulldb_MYDB_27Aug08.dmp
full=y log= c:\exportdmp\exp_fulldb_MYDB_27Aug08.log
Note: This is just a simple export command to perform the full database export. I would request and suggest you to refer Oracle Documentations on export/import and their options. Check the references.
Help on Export and Import:
Windows:
C:\> exp help=y
C:\> imp help=y
Linux/Unix.
$ exp help=y
$ imp help=y
References:
Oracle 10g :
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/exp_imp.htm
Oracle 9i:
http://download.oracle.com/docs/cd/B10501_01/server.920/a96652/part1.htm#435787
Oracle 8i:
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76955/ch01.htm
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76955/ch02.htm
Regards,
Sabdar Syed,
http://sabdarsyed.blogspot.com/
Friday, June 1, 2007
All about the STATSPACK (Statistics Packages) in Oracle 8i, 9i & 10g
Dear Friends,
By default Oracle provides the STATSPACK information in the form of spdoc.txt under
Unix: $ORACLE_HOME/rdbms/admin/spdoc.txt
Windows: %ORACLE_HOME%\rdbms\admin\spdoc.txt
This document talks about the following contents.
TABLE OF CONTENTS
-------------------------------
0. Introduction and Terminology
1. Enterprise Manager (EM), Automatic Workload Repository (AWR) and Statspack
2. Statspack Configuration
2.1. Database Space Requirements
2.2. Installing the Tool
2.3. Errors during Installation
3. Gathering data - taking a snapshot
3.1. Automating Statspack Statistics Gathering
3.2. Using dbms_job
4. Running the Performance reports
4.1. Running the instance report
4.2. Running the instance report when there are multiple instances
4.3. Configuring the Instance Report
4.4. Running the SQL report
4.5. Running the SQL report when there are multiple instances
4.6. Configuring the SQL report
4.7. Gathering optimizer statistics on the PERFSTAT schema
5. Configuring the amount of data captured
5.1. Snapshot Level
5.2. Snapshot SQL thresholds
5.3. Changing the default values for Snapshot Level and SQL Thresholds
5.4. Snapshot Levels - details
5.5. Specifying a Session Id
5.6. Input Parameters for the SNAP and
MODIFY_STATSPACK_PARAMETERS procedures
6. Time Units used for Performance Statistics
7. Event Timings
8. Managing and Sharing performance data
8.1. Baselining performance data
8.1.1. Input Parameters for the MAKE_BASELINE and CLEAR_BASELINE
procedure and function which accept Begin and End Snap Ids
8.1.2. Input Parameters for the MAKE_BASELINE and CLEAR_BASELINE
procedure and function which accept Begin and End Dates
8.2. Purging/removing unnecessary data
8.2.1. Input Parameters for the PURGE procedure and function
which accept Begin Snap Id and End Snap Id
8.2.2. Input Parameters for the PURGE procedure and function
which accept Begin Date and End Date
8.2.3. Input Parameters for the PURGE procedure and function
which accept a single Purge Before Date
8.2.4. Input Parameters for the PURGE procedure and function
which accept the Number of Days of data to keep
8.2.5. Using sppurge.sql
8.3. Removing all data
8.4. Sharing data via export
9. New and Changed Features
9.1. Changes between 10.1 and 10.2
9.2. Changes between 9.2 and 10.1
9.3. Changes between 9.0 and 9.2
9.4. Changes between 8.1.7 and 9.0
9.5. Changes between 8.1.6 and 8.1.7
10. Compatibility and Upgrading from previous releases
10.1. Compatibility Matrix
10.1.1. Using Statspack shipped with 10.1
10.1.2. Using Statspack shipped with 10.0
10.1.3. Using Statspack shipped with 9.2
10.1.4. Using Statspack shipped with 9.0
10.1.5. Using Statspack shipped with 8.1.7 on 9i releases
10.2. Upgrading an existing Statspack schema to a newer release
10.2.1. Upgrading the Statspack schema from 10.1 to 10.2
10.2.2. Upgrading the Statspack schema from 9.2 to 10.1
10.2.3. Upgrading the Statspack schema from 9.0 to 9.2
10.2.4. Upgrading the Statspack schema from 8.1.7 to 9.0
10.2.5. Upgrading the Statspack schema from 8.1.6 to 8.1.7
10.2.6. Upgrading the Statspack schema from 8.1.6 to 9.2
10.2.7. Upgrading the Statspack schema from 8.1.6 to 9.0
10.2.8. Upgrading the Statspack schema from 8.1.7 to 9.2
11. Oracle Real Application Clusters specific considerations
11.1. Changing Instance Numbers
11.2. Cluster Specific Reports
11.3. Cluster Specific Data
12. Conflicts and differences compared to UTLBSTAT/UTLESTAT
12.1. Running BSTAT/ESTAT in conjunction to Statspack
12.2. Differences between Statspack and BSTAT/ESTAT
13. Removing the package
14. Supplied Scripts Overview
15. Limitations and Modifications
15.1. Limitations
15.2. Modifications
Happy reading!!!!
With Best Regards,
Sabdar Syed.
By default Oracle provides the STATSPACK information in the form of spdoc.txt under
Unix: $ORACLE_HOME/rdbms/admin/spdoc.txt
Windows: %ORACLE_HOME%\rdbms\admin\spdoc.txt
This document talks about the following contents.
TABLE OF CONTENTS
-------------------------------
0. Introduction and Terminology
1. Enterprise Manager (EM), Automatic Workload Repository (AWR) and Statspack
2. Statspack Configuration
2.1. Database Space Requirements
2.2. Installing the Tool
2.3. Errors during Installation
3. Gathering data - taking a snapshot
3.1. Automating Statspack Statistics Gathering
3.2. Using dbms_job
4. Running the Performance reports
4.1. Running the instance report
4.2. Running the instance report when there are multiple instances
4.3. Configuring the Instance Report
4.4. Running the SQL report
4.5. Running the SQL report when there are multiple instances
4.6. Configuring the SQL report
4.7. Gathering optimizer statistics on the PERFSTAT schema
5. Configuring the amount of data captured
5.1. Snapshot Level
5.2. Snapshot SQL thresholds
5.3. Changing the default values for Snapshot Level and SQL Thresholds
5.4. Snapshot Levels - details
5.5. Specifying a Session Id
5.6. Input Parameters for the SNAP and
MODIFY_STATSPACK_PARAMETERS procedures
6. Time Units used for Performance Statistics
7. Event Timings
8. Managing and Sharing performance data
8.1. Baselining performance data
8.1.1. Input Parameters for the MAKE_BASELINE and CLEAR_BASELINE
procedure and function which accept Begin and End Snap Ids
8.1.2. Input Parameters for the MAKE_BASELINE and CLEAR_BASELINE
procedure and function which accept Begin and End Dates
8.2. Purging/removing unnecessary data
8.2.1. Input Parameters for the PURGE procedure and function
which accept Begin Snap Id and End Snap Id
8.2.2. Input Parameters for the PURGE procedure and function
which accept Begin Date and End Date
8.2.3. Input Parameters for the PURGE procedure and function
which accept a single Purge Before Date
8.2.4. Input Parameters for the PURGE procedure and function
which accept the Number of Days of data to keep
8.2.5. Using sppurge.sql
8.3. Removing all data
8.4. Sharing data via export
9. New and Changed Features
9.1. Changes between 10.1 and 10.2
9.2. Changes between 9.2 and 10.1
9.3. Changes between 9.0 and 9.2
9.4. Changes between 8.1.7 and 9.0
9.5. Changes between 8.1.6 and 8.1.7
10. Compatibility and Upgrading from previous releases
10.1. Compatibility Matrix
10.1.1. Using Statspack shipped with 10.1
10.1.2. Using Statspack shipped with 10.0
10.1.3. Using Statspack shipped with 9.2
10.1.4. Using Statspack shipped with 9.0
10.1.5. Using Statspack shipped with 8.1.7 on 9i releases
10.2. Upgrading an existing Statspack schema to a newer release
10.2.1. Upgrading the Statspack schema from 10.1 to 10.2
10.2.2. Upgrading the Statspack schema from 9.2 to 10.1
10.2.3. Upgrading the Statspack schema from 9.0 to 9.2
10.2.4. Upgrading the Statspack schema from 8.1.7 to 9.0
10.2.5. Upgrading the Statspack schema from 8.1.6 to 8.1.7
10.2.6. Upgrading the Statspack schema from 8.1.6 to 9.2
10.2.7. Upgrading the Statspack schema from 8.1.6 to 9.0
10.2.8. Upgrading the Statspack schema from 8.1.7 to 9.2
11. Oracle Real Application Clusters specific considerations
11.1. Changing Instance Numbers
11.2. Cluster Specific Reports
11.3. Cluster Specific Data
12. Conflicts and differences compared to UTLBSTAT/UTLESTAT
12.1. Running BSTAT/ESTAT in conjunction to Statspack
12.2. Differences between Statspack and BSTAT/ESTAT
13. Removing the package
14. Supplied Scripts Overview
15. Limitations and Modifications
15.1. Limitations
15.2. Modifications
Happy reading!!!!
With Best Regards,
Sabdar Syed.
Saturday, March 31, 2007
Changing an Oracle Database Name in Oracle 10g
As per the requirement for our development team, we needed to perform a fresh installation of Oracle 10g and Creation of a database on Solaris 10, and import the production data. During the database creation, we had to use the same scrip what we used for Production Database Creation. After the database creation of database for development environment we realized that the Database name is also same as Production Database. So, we had used the oracle DBNEWID utility to change the Database Name to avoid the confusion between Prod DB and Dev DB.
Below is the snapshot the task.
Make sure that you have a whole database backup.
Note: In our case, we had not taken the backup as the database was created recently and we had an export dump of a production DB.
Ensure to perform clean shut down of a database and startup in mount stage (but not open).
SQL> SHUDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
Invoke the DBNEWID utility (nid) specifying the new DBNAME from the command line using a user with SYSDBA privilege:
$ nid TARGET=SYS/XXXX@unc DBNAME=uncdev SETNAME=YES
DBNEWID performs validations in the headers of the control files (not the data files) before attempting I/O to the files. If validation is successful, then DBNEWID prompts for confirmation, changes the database name in the control files, shuts down the database and exits.
DBNEWID: Release 10.2.0.1.0 - Production on Mon Mar 26 20:04:26 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to database UNIC (DBID=3680129277)
Connected to server version 10.2.0
Control Files in atabase:
/s/oracle10g/product/10.2.0/uncdev/oradata/control01.ctl
/s/oracle10g/product/10.2.0/uncdev/oradata/control02.ctl
/s/oracle10g/product/10.2.0/uncdev/oradata/control03.ctl
Change database ID and database name UNIC to UNCDEV? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 3680129277 to 975738954
Changing database name from UNIC to UNCDEV Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control01.ctl - modified Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control02.ctl - modified Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control03.ctl - modified Datafile /s/oracle10g/product/10.2.0/oradata/system01.dbf - dbid changed, wrote new name Datafile /s/oracle10g/product/10.2.0/oradata/undotbs01.dbf - dbid changed, wrote new name Datafile /s/oracle10g/product/10.2.0/oradata/sysaux01.dbf - dbid changed, wrote new name Datafile /s/oracle10g/product/10.2.0/oradata/users01.dbf - dbid changed, wrote new name Datafile /s/oracle10g/product/10.2.0/oradata/temp01.dbf - dbid changed, wrote new name Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control01.ctl - dbid changed, wrote new name Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control02.ctl - dbid changed, wrote new name Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control03.ctl - dbid changed, wrote new name
Instance shut down
Database name changed to UNCDEV.
Modify parameter file and generate a new password file before restarting.
Database ID for database UNCDEV changed to 975738954.
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.
Successfully changed database name and ID.
DBNEWID - Completed successfully.
Connect to SQL* Plus and shutdown the database
SQL> SHUDOWN IMMEDIATE;
Change DB_NAME initialization parameter in the initialization parameter file (PFILE) to the new database name.
SQL> STARTUP MOUNT;
SQL > ALTER SYSTEM SET DB_NAME=uncdev SCOPE=spfile;
SQL> SHUDOWN IMMEDIATE;
Create a new password file
On Windows:
C:\> orapwd file= C:\oracle\product\10.2.0\db_1\database\pwdUNCDEV.ora password=xxxxxxx entries=10
On Unix:
$ orapwd file= /s/oracle10g/product/10.2.0/uncdev/dbs/ pwdUNCDEV.ora password=xxxxxxx entries=10
We have changed only the database name, The SETNAME parameter tells the DBNEWID utility to only alter the database name. and not the database ID, it is not necessary to use the RESETLOGS option when you open the database
SQL> STARTUP;
SQL> SHOW PARAMETER DB_NAME
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string UNCDEV
Backup the whole database again
Conclusion: The above series of steps are only for changing the database name, if you wanna change Database ID, or Database ID and Database Name both, please refer the below URLs.
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14215/dbnewid.htm
http://www.oracle-base.com/articles/9i/DBNEWID.php
Regards,
Sabdar Syed.
Below is the snapshot the task.
Make sure that you have a whole database backup.
Note: In our case, we had not taken the backup as the database was created recently and we had an export dump of a production DB.
Ensure to perform clean shut down of a database and startup in mount stage (but not open).
SQL> SHUDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
Invoke the DBNEWID utility (nid) specifying the new DBNAME from the command line using a user with SYSDBA privilege:
$ nid TARGET=SYS/XXXX@unc DBNAME=uncdev SETNAME=YES
DBNEWID performs validations in the headers of the control files (not the data files) before attempting I/O to the files. If validation is successful, then DBNEWID prompts for confirmation, changes the database name in the control files, shuts down the database and exits.
DBNEWID: Release 10.2.0.1.0 - Production on Mon Mar 26 20:04:26 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to database UNIC (DBID=3680129277)
Connected to server version 10.2.0
Control Files in atabase:
/s/oracle10g/product/10.2.0/uncdev/oradata/control01.ctl
/s/oracle10g/product/10.2.0/uncdev/oradata/control02.ctl
/s/oracle10g/product/10.2.0/uncdev/oradata/control03.ctl
Change database ID and database name UNIC to UNCDEV? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 3680129277 to 975738954
Changing database name from UNIC to UNCDEV Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control01.ctl - modified Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control02.ctl - modified Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control03.ctl - modified Datafile /s/oracle10g/product/10.2.0/oradata/system01.dbf - dbid changed, wrote new name Datafile /s/oracle10g/product/10.2.0/oradata/undotbs01.dbf - dbid changed, wrote new name Datafile /s/oracle10g/product/10.2.0/oradata/sysaux01.dbf - dbid changed, wrote new name Datafile /s/oracle10g/product/10.2.0/oradata/users01.dbf - dbid changed, wrote new name Datafile /s/oracle10g/product/10.2.0/oradata/temp01.dbf - dbid changed, wrote new name Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control01.ctl - dbid changed, wrote new name Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control02.ctl - dbid changed, wrote new name Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control03.ctl - dbid changed, wrote new name
Instance shut down
Database name changed to UNCDEV.
Modify parameter file and generate a new password file before restarting.
Database ID for database UNCDEV changed to 975738954.
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.
Successfully changed database name and ID.
DBNEWID - Completed successfully.
Connect to SQL* Plus and shutdown the database
SQL> SHUDOWN IMMEDIATE;
Change DB_NAME initialization parameter in the initialization parameter file (PFILE) to the new database name.
SQL> STARTUP MOUNT;
SQL > ALTER SYSTEM SET DB_NAME=uncdev SCOPE=spfile;
SQL> SHUDOWN IMMEDIATE;
Create a new password file
On Windows:
C:\> orapwd file= C:\oracle\product\10.2.0\db_1\database\pwdUNCDEV.ora password=xxxxxxx entries=10
On Unix:
$ orapwd file= /s/oracle10g/product/10.2.0/uncdev/dbs/ pwdUNCDEV.ora password=xxxxxxx entries=10
We have changed only the database name, The SETNAME parameter tells the DBNEWID utility to only alter the database name. and not the database ID, it is not necessary to use the RESETLOGS option when you open the database
SQL> STARTUP;
SQL> SHOW PARAMETER DB_NAME
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string UNCDEV
Backup the whole database again
Conclusion: The above series of steps are only for changing the database name, if you wanna change Database ID, or Database ID and Database Name both, please refer the below URLs.
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14215/dbnewid.htm
http://www.oracle-base.com/articles/9i/DBNEWID.php
Regards,
Sabdar Syed.
Subscribe to:
Posts (Atom)
