Amit Oracle DBA Blog: DATABASE ADMINISTRATION

To change Database name, we have two options.

1. By recreating controlfile

2 Using nid (DBNEWID utility).

Let’s first see how to change DB name using manual method (By recreating control file)

METHOD 1: BY RE-CREATING CONTROLFILE

STEP1:- CREATE CONTROLFILE CREATATION SCRIPT.

[oracle@NVMBD1BZY150D00 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 24 16:29:40 2014

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, OLAP, Data Mining and Real Application Testing options

SQL> alter database backup controlfile to trace;

Database altered.

STEP2:- EDIT/MODIFY CONTROLFILE CREATION SCRIPT.

[oracle@oralinux trace]$ ls -lrt

total 16
-rw-r-----. 1 oracle oinstall 1.3K Dec 24 16:26 CLONEDB_dbrm_41909.trc

-rw-r-----. 1 oracle oinstall 73 Dec 24 16:27 CLONEDB_ora_42007.trm

-rw-r-----. 1 oracle oinstall 6.8K Dec 24 16:27 CLONEDB_ora_42007.trc

-rw-r-----. 1 oracle oinstall 494M Dec 24 16:27 alert_CLONEDB.log

[oracle@oralinux trace]$ cp CLONEDB_ora_42007.trc control_clonedb.sql
[oracle@oralinux trace]$ vi control_clonedb.sql

This will generate script in above location

[oracle@dbaserver1 ~ ]$ vi control_clonedb.sql
Here change the database name and replace word REUSE with SET and make sure it is having RESETLOGS

CREATE CONTROLFILE SET DATABASE "CLONEDBN" RESETLOGS ARCHIVELOG
    MAXLOGFILES 5
    MAXLOGMEMBERS 5
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/data/oracle/app/oracle/oradata/CLONEDB/redo1.log' SIZE 10M BLOCKSIZE 512,
GROUP 2 '/data/oracle/app/oracle/oradata/CLONEDB/redo2.log' SIZE 10M BLOCKSIZE 512,
GROUP 3 '/data/oracle/app/oracle/oradata/CLONEDB/redo3.log' SIZE 10M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/data/oracle/app/oracle/oradata/CLONEDB/system.dbf',
'/data/oracle/app/oracle/oradata/CLONEDB/user04.dbf',
'/data/oracle/app/oracle/oradata/CLONEDB/sysaux.dbf',
'/data/oracle/app/oracle/oradata/CLONEDB/undo.dbf',
'/data/oracle/app/oracle/oradata/CLONEDB/test1_tmp.dbf',
'/data/oracle/app/oracle/oradata/CLONEDB/test_corrupt.dbf'
CHARACTER SET UTF8;

STEP3: CHANGE DB_NAME PARAMETER IN SPFILE/PFILE.

SQL> sho parameter control

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /data/oracle/app/oracle/oradat
                                                 a/CLONEDB/control01.ctl, /data
                                                 /oracle/app/oracle/oradata/CLO
                                                 NEDB/control02.ctl
control_management_pack_access       string      DIAGNOSTIC+TUNING

SQL> shut immediate;

ORA-01013: user requested cancel of current operation

SQL> shut abort;

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 601272320 bytes

Fixed Size                  2230712 bytes

Variable Size             276825672 bytes

Database Buffers          310378496 bytes

Redo Buffers               11837440 bytes

SQL> alter system set db_name=CLONEDBN scope=spfile;
System altered.

STEP4:- NOW REMOVE OR MOVE OLD CONTROL FILES.

Before removing old controlfile do take backup of all controlfiles.

[oracle@NVMBD1BZY150D00 dbs]$cd

/data/oracle/app/oracle/product/11.2.0/dbhome_1/dbs

[oracle@NVMBD1BZY150D00 CLONEDB]$ mv control01.ctl control01.ctl_bkp

[oracle@NVMBD1BZY150D00 CLONEDB]$ mv control02.ctl control02.ctl_bkp

STEP5: SHUT DOWN THE DATABASE AND STARTUP IN NOMOUNT MODE TO REFLECT DB_NAME PARAMETER CHANGES.

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

[oracle@NVMBD1BZY150D00 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 24 16:34:38 2014

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> startup nomount;

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 601272320 bytes
Fixed Size                  2230712 bytes
Variable Size             276825672 bytes
Database Buffers          310378496 bytes
Redo Buffers               11837440 bytes

STEP6: NOW EXECUTE NEWLY CREATED CONTROLFILE SCRIPT.

SQL> @control_clonedb.sql

Control file created.

STEP7: OPEN DATABASE WITH RESETLOGS OPTION.

SQL> alter database open resetlogs;

Database altered.

SQL> select database_name from v$database;

DATABASE_NAME

--------------------------------------------------------------------------------
CLONEDBN

METHOD 2: USING NID(DBNEWID UTILITY)

DBNEWID is a database utility that can change the internal database identifier (DBID) and the database name (DBNAME) for an operational database.

The DBNEWID utility solves this problem by allowing you to change any of the following:

• Only the DBID of a database
• Only the DBNAME of a database
• Both the DBNAME and DBID of a database

I prefer to change both DBNAME and DBID at the same time as a best practice during creation of test environments.

Step-1. We will change both db_name to CLONE and dbid belongs to cloned database.

Check db_id and db_name for new environment before operation.

SQL> select dbid,name from v$database;

DBID NAME

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

953825422 CLONEDBN

Step-2. Startup instance with mount state.

export ORACLE_SID=CLONEDB
sqlplus / as sysdba
shutdown immediate;
startup mount;

Step-3. Execute nid command and check the log file “/tmp/nid.log”:

oracle@NVMBD1BZY150D00 dbs]$ nid target=/ dbname=CLONE logfile=/tmp/nid.log
[oracle@NVMBD1BZY150D00 dbs]$ cat /tmp/nid.log

DBNEWID: Release 11.2.0.3.0 - Production on Thu Dec 25 12:07:16 2014

Connected to database CLONEDBN (DBID=953825422)

Connected to server version 11.2.0

Control Files in database:

/data/oracle/app/oracle/oradata/CLONEDB/control01.ctl

/data/oracle/app/oracle/oradata/CLONEDB/control02.ctl

Changing database ID from 953825422 to 1066065334

Changing database name from CLONEDBN to CLONE

Control File /data/oracle/app/oracle/oradata/CLONEDB/control01.ctl - modified

Control File /data/oracle/app/oracle/oradata/CLONEDB/control02.ctl - modified

Datafile /data/oracle/app/oracle/oradata/CLONEDB/system.db - dbid changed, wrote new name

Datafile /data/oracle/app/oracle/oradata/CLONEDB/user04.db - dbid changed, wrote new name

Datafile /data/oracle/app/oracle/oradata/CLONEDB/sysaux.db - dbid changed, wrote new name

Datafile /data/oracle/app/oracle/oradata/CLONEDB/undo.db - dbid changed, wrote new name

Datafile /data/oracle/app/oracle/oradata/CLONEDB/test1_tmp.db - dbid changed, wrote new name

Datafile /data/oracle/app/oracle/oradata/CLONEDB/test_corrupt.db - dbid changed, wrote new name

Control File /data/oracle/app/oracle/oradata/CLONEDB/control01.ctl - dbid changed, wrote new name

Control File /data/oracle/app/oracle/oradata/CLONEDB/control02.ctl - dbid changed, wrote new name

Instance shut down

Database name changed to CLONE.

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

Database ID for database CLONE changed to 1066065334.

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.

After DBNEWID completed successful, instance has been also closed automatically.

Step-4. Startup instance with nomount option and change the db_name to CLONE.

Then shutdown and startup mount instance again for activate new db_name. At last, open database with resetlogs option.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 601272320 bytes

Fixed Size 2230712 bytes

Variable Size 276825672 bytes

Database Buffers 310378496 bytes

Redo Buffers 11837440 bytes

SQL> show parameter db_name

NAME TYPE VALUE

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

db_name string CLONE

SQL> alter system set db_name=CLONE scope=spfile;

System altered.

SQL> shutdown immediate;

ORA-01507: database not mounted
ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 1663012104 bytes
Fixed Size 744712 bytes
Variable Size 805306368 bytes
Database Buffers 855638016 bytes
Redo Buffers 1323008 bytes

SQL> show parameter db_name

NAME TYPE VALUE

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

db_name string CLONE

SQL> alter database mount;

Database altered.

SQL> alter database open resetlogs;

Database altered.

Step-5. Control the value of dbid and name of the new database.

SQL> select dbid, name from v$database;

DBID NAME

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

1066065334 CLONE

Step-6. You should create new password file for the new environment if you need

cd $ORACLE_HOME/dbs
orapwd file=orapwCLONE password=clone entries=3

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

For resource planning it is vital to know how the memory usage is changing during night/day time.

Most often the database load varies over time. If you know the hourly usage of the SGA / PGA , you can better plan your resources or tune your instance.

You need to have diagnostic pack for your database to get this information.

Here is a query getting hourly sga/pga memory usages of an Oracle instance.

select sn.INSTANCE_NUMBER, sga.allo sga, pga.allo pga,(sga.allo+pga.allo) tot,

trunc(SN.END_INTERVAL_TIME,'mi') time
  from
(select snap_id,INSTANCE_NUMBER,round(sum(bytes)/1024/1024/1024,3) allo 
   from DBA_HIST_SGASTAT 
  group by snap_id,INSTANCE_NUMBER) sga
,(select snap_id,INSTANCE_NUMBER,round(sum(value)/1024/1024/1024,3) allo 
    from DBA_HIST_PGASTAT where name = 'total PGA allocated' 
   group by snap_id,INSTANCE_NUMBER) pga
, dba_hist_snapshot sn 
where sn.snap_id=sga.snap_id
  and sn.INSTANCE_NUMBER=sga.INSTANCE_NUMBER
  and sn.snap_id=pga.snap_id
  and sn.INSTANCE_NUMBER=pga.INSTANCE_NUMBER
order by sn.snap_id desc, sn.INSTANCE_NUMBER
;

Sample Output

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

INSTANCE_NUMBER        SGA        PGA        TOT TIME
--------------- ---------- ---------- ---------- ---------
              1      69.75       .278     70.028 17-DEC-14
              1     69.751       .277     70.028 17-DEC-14
              1     69.753       .276     70.029 17-DEC-14
              1     69.764       .257     70.021 17-DEC-14
              1     69.798       .304     70.102 17-DEC-14
              1     69.798       .301     70.099 17-DEC-14
              1     69.798       .303     70.101 17-DEC-14
              1     69.798       .304     70.102 17-DEC-14
              1     69.798       .307     70.105 17-DEC-14
              1     69.798       .307     70.105 17-DEC-14
              1     69.798       .307     70.105 17-DEC-14
              1     69.799       .307     70.106 17-DEC-14
              1       69.8       .309     70.109 17-DEC-14
              1       69.8       .308     70.108 17-DEC-14
              1     69.805       .304     70.109 16-DEC-14
              1     69.806       .304      70.11 16-DEC-14
              1     69.791       .301     70.092 16-DEC-14
              1     69.793       .292     70.085 16-DEC-14
              1     69.793        .29     70.083 16-DEC-14

Amit Oracle DBA Blog

MYSQL

Wednesday 24 December 2014

HOW TO CHANGE DATABASE NAME IN ORACLE

Wednesday 17 December 2014

How to find history of SGA/PAGA usage

Measuring average tablespace growth per day based on previous 7 day statistics.