Wednesday, August 31, 2011

Just Learned more - Rename an Oracle database name -)

It's nothing in this post. I just changed Oracle database name and did it something for Grid Infrastructure for standalone server also.
- Check
SQL> select name from v$database;

NAME
---------
ORCLOLD

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

Total System Global Area 835104768 bytes
Fixed Size 2217952 bytes
Variable Size 503318560 bytes
Database Buffers 322961408 bytes
Redo Buffers 6606848 bytes
Database mounted.
- Backup before!!!
$ rman target /
RMAN> backup database;
Starting backup at 31-AUG-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=125 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/orclold/datafile/system.293.760536553
input datafile file number=00002 name=+DATA/orclold/datafile/sysaux.294.760536553
input datafile file number=00003 name=+DATA/orclold/datafile/undotbs1.295.760536553
input datafile file number=00004 name=+DATA/orclold/datafile/users.296.760536553
channel ORA_DISK_1: starting piece 1 at 31-AUG-11
channel ORA_DISK_1: finished piece 1 at 31-AUG-11
piece handle=+DATA/orclold/backupset/2011_08_31/nnndf0_tag20110831t120028_0.284.760622429 tag=TAG20110831T120028 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 31-AUG-11
channel ORA_DISK_1: finished piece 1 at 31-AUG-11
piece handle=+DATA/orclold/backupset/2011_08_31/ncsnf0_tag20110831t120028_0.285.760622577 tag=TAG20110831T120028 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 31-AUG-11
- Use "nid" to change Oracle database name.
$ nid TARGET=sys/password DBNAME=orcl LOGFILE=/tmp/nid.log
$ cat /tmp/nid.log
DBNEWID: Release 11.2.0.1.0 - Production on Wed Aug 31 12:06:17 2011

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

Connected to database ORCLOLD (DBID=685834868)

Connected to server version 11.2.0

Control Files in database:
+DATA/orclold/controlfile/current.298.760536693
+DATA/orclold/controlfile/current.297.760536693

Changing database ID from 685834868 to 1288455737
Changing database name from ORCLOLD to ORCL
Control File +DATA/orclold/controlfile/current.298.760536693 - modified
Control File +DATA/orclold/controlfile/current.297.760536693 - modified
Datafile +DATA/orclold/datafile/system.293.76053655 - dbid changed, wrote new name
Datafile +DATA/orclold/datafile/sysaux.294.76053655 - dbid changed, wrote new name
Datafile +DATA/orclold/datafile/undotbs1.295.76053655 - dbid changed, wrote new name
Datafile +DATA/orclold/datafile/users.296.76053655 - dbid changed, wrote new name
Datafile +DATA/orclold/tempfile/temp.305.76053675 - dbid changed, wrote new name
Control File +DATA/orclold/controlfile/current.298.760536693 - dbid changed, wrote new name
Control File +DATA/orclold/controlfile/current.297.760536693 - dbid changed, wrote new name
Instance shut down

Database name changed to ORCL.
Modify parameter file and generate a new password file before restarting.
Database ID for database ORCL changed to 1288455737.
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.
- Do others things.
$ mv /u01/app/oracle/11.2.0/dbhome_old/dbs/initorclold.ora /u01/app/oracle/11.2.0/dbhome_old/dbs/initorcl.ora
$ export ORACLE_SID=orcl
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 31 12:09:30 2011

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 835104768 bytes
Fixed Size 2217952 bytes
Variable Size 461375520 bytes
Database Buffers 364904448 bytes
Redo Buffers 6606848 bytes
SQL> show parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string orclold

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

System altered.

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.

$ mv /u01/app/oracle/11.2.0/dbhome_old/dbs/orapworclold /u01/app/oracle/11.2.0/dbhome_old/dbs/orapworcl

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 31 12:20:25 2011

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 835104768 bytes
Fixed Size 2217952 bytes
Variable Size 461375520 bytes
Database Buffers 364904448 bytes
Redo Buffers 6606848 bytes
Database mounted.
SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.
- Check new Database name and etc...
SQL> select name from v$database;

NAME
---------
ORCL

SQL> !lsnrctl status | grep Service
Service "orcl" has 1 instance(s).

SQL> select name, value from v$parameter where value like '%orclold%';
-- check --
Remark:
Thank You Laurent Schneider.
To rename only :
nid setname=yes

If You need to rename Oracle database name only.
Keyword Description (Default)
----------------------------------------------------
SETNAME Set a new database name only NO
I use Grid Infrastructure also ... have to change something. but I chose to add new - -*
$ crsctl stat res | grep NAME\= | grep db
NAME=ora.orclold.db

$ crsctl stat res ora.orclold.db -p
NAME=ora.orclold.db
TYPE=ora.database.type
ACL=owner:grid:--x,pgrp:asmdba:--x,other::r--,group:oinstall:r-x,user:oracle:rwx
ACTION_FAILURE_TEMPLATE=
ACTION_SCRIPT=
ACTIVE_PLACEMENT=1
AGENT_FILENAME=%CRS_HOME%/bin/oraagent%CRS_EXE_SUFFIX%
AUTO_START=restore
CARDINALITY=1
CHECK_INTERVAL=1
CHECK_TIMEOUT=600
CLUSTER_DATABASE=false
DATABASE_TYPE=
DB_UNIQUE_NAME=orclold
DEFAULT_TEMPLATE=PROPERTY(RESOURCE_CLASS=database) PROPERTY(DB_UNIQUE_NAME= CONCAT(PARSE(%NAME%, ., 2), %USR_ORA_DOMAIN%, .)) ELEMENT(INSTANCE_NAME= %GEN_USR_ORA_INST_NAME%)
DEGREE=1
DESCRIPTION=Oracle Database resource
ENABLED=1
FAILOVER_DELAY=0
FAILURE_INTERVAL=60
FAILURE_THRESHOLD=1
GEN_AUDIT_FILE_DEST=
GEN_START_OPTIONS=
GEN_USR_ORA_INST_NAME=
HOSTING_MEMBERS=
INSTANCE_FAILOVER=0
LOAD=1
LOGGING_LEVEL=1
MANAGEMENT_POLICY=AUTOMATIC
NLS_LANG=
NOT_RESTARTING_TEMPLATE=
OFFLINE_CHECK_INTERVAL=0
ONLINE_RELOCATION_TIMEOUT=0
ORACLE_HOME=/u01/app/oracle/11.2.0/dbhome_old
PLACEMENT=balanced
PROFILE_CHANGE_TEMPLATE=
RESTART_ATTEMPTS=2
ROLE=PRIMARY
SCRIPT_TIMEOUT=60
SERVER_POOLS=
SPFILE=+DATA/orclold/spfileorclold.ora
START_DEPENDENCIES=hard(ora.DATA.dg) weak(type:ora.listener.type,uniform:ora.ons,uniform:ora.eons) pullup(ora.DATA.dg)
START_TIMEOUT=600
STATE_CHANGE_TEMPLATE=
STOP_DEPENDENCIES=hard(intermediate:ora.asm,shutdown:ora.DATA.dg)
STOP_TIMEOUT=600
TYPE_VERSION=2.2
UPTIME_THRESHOLD=1h
USR_ORA_DB_NAME=orclold
USR_ORA_DOMAIN=
USR_ORA_ENV=
USR_ORA_FLAGS=
USR_ORA_INST_NAME=orclold
USR_ORA_OPEN_MODE=open
USR_ORA_OPI=false
USR_ORA_STOP_MODE=immediate
VERSION=11.2.0.1.0

$ crsctl stat res ora.orclold.db -p > /tmp/ora.orclold.db.res
*** Modified something in ora.orclold.db.res file.***

- Add new resource (after modified)
$ crsctl add res ora.orcl.db -type ora.database.type -file /tmp/ora.orclold.db.res

$ crsctl stat res | grep NAME\= | grep db
NAME=ora.orcl.db
NAME=ora.orclold.db

$ crsctl start res ora.orcl.db
CRS-2672: Attempting to start 'ora.orcl.db' on 'mytest'
CRS-5010: Update of configuration file "/u01/app/oracle/11.2.0/dbhome_old/srvm/admin/oratab.bak.mytest" failed: details at "(:CLSN00011:)" in "/u01/app/oracle/11.2.0/grid/log/mytest/agent/ohasd/oraagent_grid/oraagent_grid.log"
CRS-2676: Start of 'ora.orcl.db' on 'mytest' succeeded

$ crsctl status res ora.orcl.db
NAME=ora.orcl.db
TYPE=ora.database.type
TARGET=ONLINE
STATE=ONLINE on mytest

$ crsctl stop res ora.orcl.db
CRS-2673: Attempting to stop 'ora.orcl.db' on 'mytest'
CRS-5017: The resource action "ora.orcl.db stop" encountered the following error:
CRS-5003: Invalid attribute value: '' for attribute DATABASE_TYPE
CRS-2675: Stop of 'ora.orcl.db' on 'mytest' failed
CRS-2679: Attempting to clean 'ora.orcl.db' on 'mytest'
CRS-2681: Clean of 'ora.orcl.db' on 'mytest' succeeded

$ crsctl status res ora.orcl.db
NAME=ora.orcl.db
TYPE=ora.database.type
TARGET=OFFLINE
STATE=OFFLINE
So, You can use "srvctl"
srvctl add database -d orcl -o /u01/app/oracle/11.2.0/dbhome_old -p +DATA/orclold/spfileorclold.ora
- Delete old!!!
$ su -
Password:
# /u01/app/oracle/11.2.0/grid/bin/crsctl delete res ora.orclold.db
About CRS-5010 !!! during "crsctl start res ora.orcl.db" - because I use "grid" user for Grid Infrastructure and "oracle" for database software.
$ su - oracle
Password:
$ chmod 775 /u01/app/oracle/11.2.0/dbhome_old/srvm/admin
About "CRS-5003: Invalid attribute value: '' for attribute DATABASE_TYPE"
$ crsctl stat res ora.orcl.db -p | grep DATABASE_TYPE
DATABASE_TYPE=
OK, It should be "DATABASE_TYPE=SINGLE"
$ su -
Password:
# /u01/app/oracle/11.2.0/grid/bin/crsctl modify res ora.orcl.db -attr 'DATABASE_TYPE=SINGLE'
- Test "crsctl"
$ crsctl start res ora.orcl.db
CRS-2672: Attempting to start 'ora.orcl.db' on 'mytest'
CRS-2676: Start of 'ora.orcl.db' on 'mytest' succeeded
$ crsctl stop res ora.orcl.db
CRS-2673: Attempting to stop 'ora.orcl.db' on 'mytest'
CRS-2677: Stop of 'ora.orcl.db' on 'mytest' succeeded
$ crsctl start res ora.orcl.db
CRS-2672: Attempting to start 'ora.orcl.db' on 'mytest'
CRS-2676: Start of 'ora.orcl.db' on 'mytest' succeeded
$ crsctl status res ora.orcl.db
NAME=ora.orcl.db
TYPE=ora.database.type
TARGET=ONLINE
STATE=ONLINE on mytest
No error -)

4 comments:

Laurent Schneider said...

if you just want to rename it, why changing the DBID then? It will mess up your rman backups then.

To rename only :
nid setname=yes

Surachart said...

@Laurent
Thank you -)

goryunov said...

I think rename of the directory
for ASM should be included too.

It usually happens if data files
under normal file system, so it
would be useful to have it here
to avoid any mix of datafiles

Surachart said...

Yes... it should be able to rename directory in ASM also.