Database version: 11.2.0.x (11gR2) using ASM
OS: RedHat Enterprise Linux 6.x
Imagine that you have a single instance database, with a SID called olddb (let's say our db_name is equal too).
For some reason, you need to change your SID to newdb, but how to do that after everything is installed and working fine?
Before start this tutorial, make sure of somethings:
* Your DB_RECOVERY_FILE_DEST will be used to copy your datafiles to your new location
* Your DB_RECOVERY_FILE_DEST_SIZE must be configure with a space higher or equal than your full database (specially your datafiles)
1st: Mount your database. You need to use the command nid (with oracle user, and make sure that you have your ORACLE_SID set to olddb):
$ nid target=/ setname=true dbname=newdb
DBNEWID: Release 11.2.0.4.0 - Production on Sat Sep 12 09:35:20 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to database OLDDB (DBID=2045464205)
Connected to server version 11.2.0
Control Files in database:
+DATA/olddb/control01.ctl
+DATA/olddb/control02.ctl
Change database name of database OLDDB to NEWDB? (Y/[N]) => Y
Proceeding with operation
Changing database name from OLDDB to NEWDB
Control File +DATA/olddb/control01.ctl - modified
Control File +DATA/olddb/control02.ctl - modified
Datafile +DATA/olddb/datafile/system.387.89016408 - wrote new name
...
Instance shut down
Database name changed to NEWDB.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.
Warning!: if you get error "NID-00131: Control file is not current", means that you didnt open your database anytime by now. So, open it to make sure your controlfile is up to date and ready to be use (maybe you'll need to open it with resetlogs clause - alter database open resetlogs).
Now, if you try to mount your database, you'll get the following error:
alter database mount
*
ERROR at line 1:
ORA-01103: database name 'NEWDB' in control file is not 'OLDDB'
This error tell us that your database is not ready to be used. We only changed the control file definition about our database (you can check this showing your parameter db_name... as you can see, your SID is still OLDDB).
2nd: You need to create your new parameter file and control file to your new configuration. First, create a pfile.
$ export ORACLE_SID=olddb$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Sep 13 14:21:18 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1.0689E+10 bytes
Fixed Size 2262656 bytes
Variable Size 1677724032 bytes
Database Buffers 8992587776 bytes
Redo Buffers 16900096 bytesSQL> create pfile='/tmp/pfilenewdb.ora' from spfile='+DATA/olddb/spfileolddb.ora';
File created.
Now, edit your db_name parameter to set your new database name:
SQL> alter system set db_name='newdb' scope=spfile;
System altered.
SQL> shutdown immediate;
ORA-01109: database not openSee? now you've changed your database name and everything was fine! But we are not done yet.
Now we need to create our controlfile in the right place!
Save your controlfile old destination because we'll need it later, and then change your parameter control_files to your new destination.
SQL> show parameter control_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATA/olddb/control01.ctl,
+DATA/olddb/control02.ctl
SQL> alter system set control_files='+DATA/newdb/control01.ctl','+DATA/newdb/control02.ctl' scope=spfile;
System altered.
Shutdown your olddb instance.
Replace the parameters from your pfilenewdb.ora to your new instance (like db_name, audit_file_dest, and all others that have OLDDB on it). Should be something like this:
*.archive_lag_target=0
*.audit_file_dest='/u01/app/oracle/admin/prodbr/adump'
*.audit_trail='NONE'
*.cell_offload_processing=FALSE
*.cluster_database=false
*.compatible='11.2.0.4.0'
*.control_files='+DATA/newdb/control01.ctl','+DATA/newdb/control02.ctl'#Restore Controlfile
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_multiblock_read_count=32
*.db_files=1000
*.db_name='newdb'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=53687091200
*.db_unique_name='newdb'
*.dg_broker_start=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.instance_number=1
*.job_queue_processes=10
*.log_archive_dest='+DATA'
*.log_archive_format='arch_%t_%s_%r.dbf'
newdb.log_archive_format='arch_%t_%s_%r.dbf'
*.log_archive_max_processes=10
*.log_archive_min_succeed_dest=1
*.log_archive_trace=0
newdb.log_archive_trace=0
*.log_buffer=31703040
*.open_cursors=10000
*.parallel_execution_message_size=65536
*.pga_aggregate_target=4294967296
*.processes=4000
*.remote_login_passwordfile='exclusive'
*.resource_manager_plan=''
*.sec_case_sensitive_logon=TRUE
*.session_cached_cursors=5000
*.sessions=2100
*.sga_max_size=3221225472
*.sga_target=3221225472
*.standby_file_management='AUTO'
newdb.thread=1
*.undo_retention=900
*.undo_tablespace='UNDOTBS1'
IMPORTANT: Make sure your db_recovery_file_dest is pointing to your target directory where all datafiles will stay (and, of course, the db_recovery_file_dest_size is big enough to support it).Now you need to create your new spfile at your newdb location in ASM:
$ export ORACLE_SID=newdb
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 3 16:44:30 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/tmp/pfileolddb.ora';
ORACLE instance started.
Total System Global Area 3206836224 bytes
Fixed Size 2257520 bytes
Variable Size 1157631376 bytes
Database Buffers 2013265920 bytes
Redo Buffers 33681408 bytes
SQL>
SQL> create spfile='+DATA/newdb/spfilenewdb.ora' from pfile='/tmp/pfileolddb.ora';
File created.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
Startup at nomount state to restore your controlfiles, as below:
$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sun Sep 13 14:34:01 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: NEWDB (not mounted)
RMAN> restore controlfile from '+DATA/olddb/control01.ctl';
Starting restore at 13-SEP-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=68 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DATA/newdb/control01.ctl
output file name=+DATA/newdb/control02.ctl
Finished restore at 13-SEP-15
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
Put your instance in mount state.
3rd: Using ASM, you can see that your db files is still in your OLDDB directory. Now, let's move it to your new SID directory (NEWDB).
$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sun Sep 13 14:27:05 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: NEWDB (DBID=2045464205, not open)
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name NEWDB
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 1700 SYSTEM *** +DATA/olddb/datafile/system.387.890164085
...
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 +DATA/olddb/tempfile/temp.506.890212713
...
RMAN> backup as copy database;
Starting backup at 13-SEP-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=68 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/olddb/datafile/undotbs1.481.890157651
output file name=+DATA/newdb/datafile/undotbs1.509.890318271 tag=TAG20150913T143748 RECID=433 STAMP=890318867
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:10:06
channel ORA_DISK_1: starting datafile copy
...
copying current control file
output file name=+DATA/newdb/controlfile/backup.725.890327841 tag=TAG20150913T143748 RECID=649 STAMP=890327841
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 13-SEP-15
channel ORA_DISK_1: finished piece 1 at 13-SEP-15
piece handle=+DATA/newdb/backupset/2015_09_13/nnsnf0_tag20150913t143748_0.726.890327843 tag=TAG20150913T143748 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 13-SEP-15
RMAN> list copy of database;
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
522 1 A 13-SEP-15 53694861539 12-SEP-15
Name: +DATA/newdb/datafile/system.598.890325775
Tag: TAG20150913T143748
...
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DATA/newdb/datafile/system.598.890325775"
...
RMAN> alter database open;
database opened
RMAN> report schema;
Report of database schema for database with db_unique_name NEWDB
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 1700 SYSTEM *** +DATA/newdb/datafile/system.598.890325775
...
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 +DATA/olddb/tempfile/temp.506.890212713
...
Note that your tempfiles wasn't moved to your new directory. Unfortunately, neither your redo logs wasn't, too.
4th: Now, what we have to do is recreate your tempfiles and redo logs at the right directory. So, let's do that.
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Sep 13 17:28:05 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> alter tablespace TEMP add tempfile '+DATA' size 100m autoextend on next 100m maxsize 4096m;
Tablespace altered.
SQL> set lines 200;
SQL> set pages 500;
SQL> select 'alter database tempfile ''' || file_name || ''' drop;' command_ from dba_temp_files where file_name not like '%newdb%';
COMMAND_
-------------------------------------------------------------------------------------------------
alter database tempfile '+DATA/olddb/tempfile/temp.506.890212713' drop;
...
SQL> alter database tempfile '+DATA/olddb/tempfile/temp.506.890212713' drop;
Database altered.
...
SQL> alter database add logfile group 111 ('+DATA','+DATA') size 512m;
Database altered.
SQL> alter database add logfile group 112 ('+DATA','+DATA') size 512m;
Database altered.
SQL> select 'alter database drop logfile group ' || group# || ';' command_ from v$logfile where member not like '%newdb%';
COMMAND_
---------------------------------------------------------------------------
alter database drop logfile group 1;
SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01623: log 1 is current log for instance olddb (thread 1) - cannot drop
ORA-00312: online log 1 thread 1: '+DATA/olddb/onlinelog/group_1.276.890212603'
ORA-00312: online log 1 thread 1: '+DATA/olddb/onlinelog/group_1.260.890212611'
What happened? If you face this, and you probably will, its because you cant drop a redo logfile that is in ACTIVE state, meaning your logfile is generating an archive log file, so you can't drop it until it finishes. But, as Im in hurry (lol), I will just clean it and lose this archivelog (be aware that its not a good practice, since you wont be able to recover your database on some catastrophe).
SQL> alter database clean unarchived logfile group 1;
Database clear UNARCHIVED altered.And it's done!
Considerations:
* You can recreate your controlfile with control01.ctl and control02.ctl alias;
* You can now delete your old OLDDB directory from ASM to erase all old files;
* Remember to check if you are using archivelog or not, and turn it on/off.
* Change your db_recovery_file_dest to the old directory (FRA), where you save your archivelogs and backups.
