rename an Oracle database and sid with command nid

• • Leave a comment

http://www.dba-oracle.com/t_rename_database_oracle_sid.htm
A new method for a database rename instance

For Oracle9i and beyond, Oracle author Dr. Tim Hall has this procedure to rename an Oracle database using the new dbnewid (also called nid, for new ID) utility:

•STEP 1: Backup the database.
 
•STEP 2: Mount the database after a clean shutdown:
SHUTDOWN IMMEDIATE
STARTUP MOUNT

•STEP 3: Invoke the DBNEWID utility (nid) specifying the new DBNAME from the command line using a user with SYSDBA privilege:
nid TARGET=sys/password@TSH1 DBNAME=TSH2
Assuming the validation is successful the utility prompts for confirmation before performing the actions. Typical output may look something like:
C:\oracle\920\bin>nid TARGET=sys/password@TSH1 DBNAME=TSH2
DBNEWID: Release 9.2.0.3.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

Connected to database TSH1 (DBID=1024166118)

Control Files in database:
    C:\ORACLE\ORADATA\TSH1\CONTROL01.CTL
    C:\ORACLE\ORADATA\TSH1\CONTROL02.CTL
    C:\ORACLE\ORADATA\TSH1\CONTROL03.CTL

Change database ID and database name TSH1 to TSH2? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 1024166118 to 1317278975
Changing database name from TSH1 to TSH2
    Control File C:\ORACLE\ORADATA\TSH1\CONTROL01.CTL - modified
    Control File C:\ORACLE\ORADATA\TSH1\CONTROL02.CTL - modified
    Control File C:\ORACLE\ORADATA\TSH1\CONTROL03.CTL - modified
    Datafile C:\ORACLE\ORADATA\TSH1\SYSTEM01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\TSH1\UNDOTBS01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\TSH1\CWMLITE01.DBF - dbid changed, wrote new name
    Control File C:\ORACLE\ORADATA\TSH1\CONTROL01.CTL - dbid changed, wrote new name
    Control File C:\ORACLE\ORADATA\TSH1\CONTROL02.CTL - dbid changed, wrote new name
    Control File C:\ORACLE\ORADATA\TSH1\CONTROL03.CTL - dbid changed, wrote new name

Database name changed to TSH2.
Modify parameter file and generate a new password file before restarting.
Database ID for database TSH2 changed to 1317278975.
All previous backups and archived redo logs for this database are unusable.
Shut down database and open with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

•STEP 4: Shutdown the database:
SHUTDOWN IMMEDIATE

•STEP 5: Modify the DB_NAME parameter in the initialization parameter file. The startup will result in an error but proceed anyway.
STARTUP MOUNT
ALTER SYSTEM SET DB_NAME=TSH2 SCOPE=SPFILE;
SHUTDOWN IMMEDIATE

•STEP 6: Create a new password file:
orapwd file=c:\oracle\920\database\pwdTSH2.ora password=password entries=10

•STEP 7: Rename the SPFILE to match the new DBNAME.
 
•STEP 8: If you are using Windows you must recreate the service so the correct name and parameter file are used:
 
oradim -delete -sid TSH1
oradim -new -sid TSH2 -intpwd password -startmode a -pfile c:\oracle\920\database\spfileTSH2.ora
If you are using UNIX/Linux simply reset the ORACLE_SID environment variable:
 
ORACLE_SID=TSH2; export ORACLE_SID

•STEP 9: Alter the listener.ora and tnsnames.ora setting to match the new database name and restart the listener:
 
lsnrctl reload

•STEP 10: Open the database with RESETLOGS:
 
STARTUP MOUNT
ALTER DATABASE OPEN RESETLOGS;

•STEP 11: Backup the database.

http://www.erpgreat.com/oracle-database/finding-oracle-sid-of-a-database.htm

Finding Oracle_SID Of A Database
Need to set ORACLE_SID of a database.

What if the person who installed database can't remember what it was, is it the same as the instance_name in the init.ora file?

ORACLE_SID is the instance name, check init.ora for instance_name parameter.

Check /etc/oratab file (or depending on Oracle version /var/opt/oracle/oratab) for a list of instances on the server.

ORACLE_SID MAY BE or MAY NOT BE the same as the database name, check init.ora for db_name parameter.

In need of a Unix/Solaris shell script that would rename the Oracle SID of our Oracle 9.2 database instance running on Solaris 8 platform.

Rename the sid:

1) Shutdown database,
2) In $ORACLE_HOME/dbs directory copy initOLDDB.ora to initNEWDB.ora
3) In DB startup script replace ORACLE_SID
export ORACLE_SID=NEWDB
4) Rename sid in /etc/oratab (or /var/opt/oracle/oratadb)
5) Startup database.

Change my SID name after Creating Database

You need to REcreate the Control file to achieve this .

1. SVRMGR>Alter Database backup controlfile to trace;
# this will generate an Ascii Trace file in $USER_DUMP_DEST directory which will have the Control File Creation Script.

2. Shutdown the Database and Do a Physical Backup of all the Datafiles,Controlfiles,RedoLog files,Archived Redo log files etc etc...for Safety.

3. Rename the Init<oldSID>.ora and config<OLDSID>.ora to Init<NEWSID>.ora and Config<NewSid>.ora files in $ORACLE_HOME/dbs This is to prevent any errors during Database Startups looking for default 'pfile' names.

4. Rename the Old Controlfiles to say control01.old etc This is to Create New Controlfile and not reuse the existing one.

5. Edit the Control File creation Script ..It should read like:
Startup nomount;
Create Controlfile set Database 'NEW_SID' Resetlogs
...... <all others remain the Same>
;

6. Open your database:
alter database open resetlogs;

What is the difference between Oracle SIDs and Oracle SERVICE NAMES. One config tool looks for SERVICE NAME and then the next looks for SIDs! What's going on?!

Oracle SID is the unique name that uniquely identifies your instance/database where as Service name is the TNS alias that you give when you remotely connect to your database and this Service name is recorded in Tnsnames.ora file on your clients and it can be the same as SID and you can also give it any other name you want.

SERVICE_NAME is the new feature from oracle 8i onwards in which database can register itself with listener. If database is registered with listener in this way then you can use SERVICE_NAME parameter in tnsnames.ora otherwise - use SID in tnsnames.ora.

Also if you have OPS (RAC) you will have different SERVICE_NAME for each instance.

SERVICE_NAMES specifies one or more names for the database service to which this instance connects. You can specify multiple services names in order to distinguish among different uses of the same database. For example:

SERVICE_NAMES = sales.acme.com, widgetsales.acme.com

You can also use service names to identify a single service that is available from two different databases through the use of replication.

In an Oracle Parallel Server environment, you must set this parameter for every instance.

https://community.oracle.com/thread/2478517?start=0&tstart=0

:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Dec 14 19:51:42 2012

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

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

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2077904 bytes
Variable Size             385878832 bytes
Database Buffers         1207959552 bytes
Redo Buffers               14696448 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\>nid target=sys/oracle dbname=test

DBNEWID: Release 10.2.0.5.0 - Production on Fri Dec 14 19:52:24 2012

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

Connected to database 10GR2 (DBID=1672979762)

Connected to server version 10.2.0

Control Files in database:
    C:\ORACLE\PRODUCT\10.2.0\ORADATA\10GR2\CONTROL01.CTL
    C:\ORACLE\PRODUCT\10.2.0\ORADATA\10GR2\CONTROL02.CTL
    C:\ORACLE\PRODUCT\10.2.0\ORADATA\10GR2\CONTROL03.CTL

Change database ID and database name 10GR2 to TEST? (Y/[N]) => y

Proceeding with operation
Changing database ID from 1672979762 to 2100191993
Changing database name from 10GR2 to TEST
    Control File C:\ORACLE\PRODUCT\10.2.0\ORADATA\10GR2\CONTROL01.CTL - modified
    Control File C:\ORACLE\PRODUCT\10.2.0\ORADATA\10GR2\CONTROL02.CTL - modified
    Control File C:\ORACLE\PRODUCT\10.2.0\ORADATA\10GR2\CONTROL03.CTL - modified
    Datafile C:\ORACLE\PRODUCT\10.2.0\ORADATA\10GR2\SYSTEM01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\PRODUCT\10.2.0\ORADATA\10GR2\UNDOTBS01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\PRODUCT\10.2.0\ORADATA\10GR2\SYSAUX01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\PRODUCT\10.2.0\ORADATA\10GR2\USERS01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\PRODUCT\10.2.0\ORADATA\10GR2\TEMP01.DBF - dbid changed, wrote new name
    Control File C:\ORACLE\PRODUCT\10.2.0\ORADATA\10GR2\CONTROL01.CTL - dbid changed, wrote new name
    Control File C:\ORACLE\PRODUCT\10.2.0\ORADATA\10GR2\CONTROL02.CTL - dbid changed, wrote new name
    Control File C:\ORACLE\PRODUCT\10.2.0\ORADATA\10GR2\CONTROL03.CTL - dbid changed, wrote new name
    Instance shut down

Database name changed to TEST.
Modify parameter file and generate a new password file before restarting.
Database ID for database TEST changed to 2100191993.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.
But you need additional steps (ORADIM is only for Windows):
 - changing ORACLE_SID
 - renaming init. file
 - changing DB_NAME instance parameter
 - opening database with RESETLOGS option.
C:>oradim -new -sid test
Instance created.

C:\>set ORACLE_SID=TEST

C:\>cd c:\oracle\product\10.2.0\db_1\database

C:\oracle\product\10.2.0\db_1\database>rename init10GR2.ora initTEST.ora

C:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Dec 14 20:00:24 2012

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

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
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2077904 bytes
Variable Size             385878832 bytes
Database Buffers         1207959552 bytes
Redo Buffers               14696448 bytes
SQL> alter system set db_name=TEST scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2077904 bytes
Variable Size             385878832 bytes
Database Buffers         1207959552 bytes
Redo Buffers               14696448 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

Database altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2077904 bytes
Variable Size             385878832 bytes
Database Buffers         1207959552 bytes
Redo Buffers               14696448 bytes
Database mounted.
Database opened.

SQL> select name from v$database;

NAME
---------
TEST

SQL>

No Comments

Geef een reactie

Het e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *