Change the DBID and the DBNAME




This document changes DBNAME AND DBID with NID utility method witch is available since Oracle 9.2
If you change the DBID you must open the database with the RESETLOGS option, which re-creates the online redo logs and resets their sequence to 1. So your previous backups and archivelogs will be invalidated..
However with NID you can change DBNAME only, without below side effects with NID parameter SETNAME=Y, witch allow you change DBNAME without changing DBID...

SQL> select dbid,name,open_mode,activation#,created from v$database;

      DBID NAME      OPEN_MODE            ACTIVATION# CREATED
---------- --------- -------------------- ----------- ---------
1423238009 ORCL      READ WRITE            1423232377 11-NOV-15

We create a new pfile from spfile in order to modify it later...

SQL> create pfile from spfile;

File created.


SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@wcp12cr2_clone ~]$ cd $ORACLE_HOME/dbs
[oracle@wcp12cr2_clone dbs]$ ls -ltr
total 9836
-rw-r--r--. 1 oracle oinstall     2992 Feb  3  2012 init.ora
-rw-r-----. 1 oracle oinstall       24 Nov 11  2015 lkORCL
-rw-r-----. 1 oracle oinstall     7680 Nov 11  2015 orapworcl
-rw-r-----. 1 oracle oinstall 10043392 Sep 27 07:18 snapcf_orcl.f
-rw-rw----. 1 oracle oinstall     1544 Oct 19 00:04 hc_orcl.dat
-rw-r-----. 1 oracle oinstall     2560 Oct 19 00:05 spfileorcl.ora
-rw-r--r--. 1 oracle oinstall      917 Oct 19 00:14 initorcl.ora

so after that we can shutdown database and start it with pfile that we created previously as follows:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount pfile=$ORACLE_HOME/dbs/initorcl.ora
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2296576 bytes
Variable Size             713032960 bytes
Database Buffers          348127232 bytes
Redo Buffers                5480448 bytes
Database mounted.
SQL> exit

Now, we can run NID utility with dbname parameter set to desired new dbname... this utility has no feedback, but let us a complete log in location=logfile. Remember if you don´t want to change DBID you should set SETNAME=Y in NID commad..

nid target=sys/oracle@orcl dbname=orclstb logfile=$ORACLE_HOME/log/nid.log

DBNEWID: Release 12.1.0.1.0 - Production on Thu Oct 19 00:57:25 2017

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

Connected to database ORCL (DBID=1423238009)

Connected to server version 12.1.0

Control Files in database:
    /oracle/db/oradata/orcl/control01.ctl
    /oracle/db/fast_recovery_area/orcl/control02.ctl

Changing database ID from 1423238009 to 1140464757
Changing database name from ORCL to ORCLSTB
    Control File /oracle/db/oradata/orcl/control01.ctl - modified
    Control File /oracle/db/fast_recovery_area/orcl/control02.ctl - modified
    Datafile /oracle/db/oradata/orcl/system01.db - dbid changed, wrote new name
    Datafile /oracle/db/oradata/orcl/WCPVM_ias_opss.db - dbid changed, wrote new name
    Datafile /oracle/db/oradata/orcl/sysaux01.db - dbid changed, wrote new name
    Datafile /oracle/db/oradata/orcl/undotbs01.db - dbid changed, wrote new name
    Datafile /oracle/db/oradata/orcl/WCPVM_webcenter_portlet.db - dbid changed, wrote new name
    Datafile /oracle/db/oradata/orcl/users01.db - dbid changed, wrote new name
    Datafile /oracle/db/oradata/orcl/WCPVM_iaswebcenter.db - dbid changed, wrote new name
    Datafile /oracle/db/oradata/orcl/WCPVM_iasjive.db - dbid changed, wrote new name
    Datafile /oracle/db/oradata/orcl/WCPVM_svctbl.db - dbid changed, wrote new name
    Datafile /oracle/db/oradata/orcl/WCPVM_iau.db - dbid changed, wrote new name
    Datafile /oracle/db/oradata/orcl/WCPVM_mds.db - dbid changed, wrote new name
    Datafile /oracle/db/oradata/orcl/WCPVM_ocs.db - dbid changed, wrote new name
    Datafile /oracle/db/oradata/orcl/WCPVM_iasactivities.db - dbid changed, wrote new name
    Datafile /oracle/db/oradata/orcl/temp01.db - dbid changed, wrote new name
    Datafile /oracle/db/oradata/orcl/WCPVM_iastemp.db - dbid changed, wrote new name
    Datafile /oracle/db/oradata/orcl/WCPVM_ocstemp.db - dbid changed, wrote new name
    Control File /oracle/db/oradata/orcl/control01.ctl - dbid changed, wrote new name
    Control File /oracle/db/fast_recovery_area/orcl/control02.ctl - dbid changed, wrote new name
    Instance shut down

Database name changed to ORCLSTB.
Modify parameter file and generate a new password file before restarting.
Database ID for database ORCLSTB changed to 1140464757.
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 name and ID.
DBNEWID - Completed succesfully.


now, we can change db_name parameter into init.ora file adn rename it according new dbname
i change this *.db_name='orcl' for this *.db_name='orclstb'
and 
mv $ORACLE_HOME/dbs/initorcl.ora $ORACLE_HOME/dbs/initorclstb.ora
after that we can startup mount database, but as we said at first, we must to open it by resetlogs because we change DBID
SQL> startup mount pfile=$ORACLE_HOME/dbs/initorclstb.ora
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2296576 bytes
Variable Size             713032960 bytes
Database Buffers          348127232 bytes
Redo Buffers                5480448 bytes
Database mounted.


SQL> alter database open resetlogs;

Database altered.

SQL>  select dbid,name,open_mode,activation#,created from v$database;

      DBID NAME      OPEN_MODE            ACTIVATION# CREATED
---------- --------- -------------------- ----------- ---------
1140464757 ORCLSTB   READ WRITE            1140467336 11-NOV-15

now , we can generate a new password file with these commands
export ORACLE_SID=orclstb
orapwd file=orapw${ORACLE_SID} password=oracle entries=5

and finally we create a new spfile and restart database to make effective spfile.

SQL> create spfile from pfile;

File created.

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

Total System Global Area 1068937216 bytes
Fixed Size                  2296576 bytes
Variable Size             713032960 bytes
Database Buffers          348127232 bytes
Redo Buffers                5480448 bytes
Database mounted.
Database opened.
SQL>

Comments

Post a Comment

Popular posts from this blog

Oracle Historical Session Information with ASH >10g

Image
In Oracle 10.2 and above with ASH is very useful to see the number of sessions active and connected to a database over time: You can use this 2 views to obtain this information: v$sysmetric_history --> This is a sort term view, that contains data of last hour by intervals of minutes dba_hist_sysmetric_summary -->This is a long term view, that contains data of last month by intervals of 1 hour. v$sysmetric_history and dba_hist_sysmetric_summary contains information a lot of different metrics information, so the first thing we need to do is to filter metric type. With this query you can select a list of all metric types: set linesize 190 col METRIC_NAME for a100 select METRIC_ID, METRIC_NAME from v$sysmetric_history group by METRIC_ID, METRIC_NAME ; for example, if you are looking for metrics related with connected sessions to database, you can do: SQL> set linesize 190 col METRIC_NAME for a100 select METRIC_ID, METRIC_NAME from v$sysmetric_history where lo
Read more

Purging and archiving Oracle alert.log and listener.log

As i said in " Purging Oracle traces with ADRCI " ADRCI does not work properly for alert.log and listener.log, so in this article i´m going to study who to do this tasks externally, with unix tools. 1-With logrotate Linux-command. I think this is the best option if you have an Oracle linux because it can be setup easily... Put this lines in a config file but modifing your alert.log and listener.log paths # alert log /oracle/db/diag/rdbms/orcl/orcl/trace/alert_orcl.log { monthly rotate 13 notifempty missingok copytruncate nocreate } # listener log /oracle/db/diag/tnslsnr/wcp12cr2/listener/trace/listener.log { weekly rotate 53 notifempty missingok copytruncate nocreate } i put them into alert_logrotate.conf as you can see: [oracle@wcp12cr2 Documents]$ ls -ltr alert_logrotate.conf -rw-r--r--. 1 oracle oinstall 593 Oct 5 03:08 alert_logrotate.conf [oracle@wcp12cr2 Documents]$ cat alert_logrotate.conf # see "man logrotate" for details # rotate log files
Read more

Check sessions and processes limits in Oracle

Oracle database has a gv$resource_limit view to check if any database resource reach his connection limit: set linesize 190 set pages 200 select * from gv$resource_limit; INST_ID RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCAT LIMIT_VALUE ---------- ------------------------------ ------------------- --------------- --------------- ------------ 1 processes 44 300 300 300 1 sessions 52 215 512 512 1 enqueue_locks 30 295 6480 6480 1 enqueue_resources 24 165 2472 UNLIMITED 1 ges_procs 0 0 0 0 1 ges_ress 0 0
Read more