Re: Renaming a Database

From: Your Name <your.address_at_cobe.com>
Date: 1997/10/07
Message-ID: <61cmk3$uct$1@news-2.csn.net>#1/1

Neil Boemio wrote:

> Hello,
>
> I know this question has already been asked and answered, but I don't
> remember what it was. So could someone be so kind as to tell me the
> general procedure for renaming a database. BACKUP
> CONTROLFILE TO TRACE is involved somewhere, no?
>
> And a totally unrelated question .... if you have an index on column X
>
> and you do a SELECT with a
> GROUP BY clause on column X, and there is no WHERE clause, will Oracle
>
> use the index?
>
> Thanks a bunch!

Neil,

Once an Oracle database is larger than 1-2 gig in size then coping and renaming is the
only way. I just copied a 8 gig database in 90 minutes. Break out the beer!

Here are the steps:

1. Retrieve a complete list of all datafiles on the source database except the control files.
2. Create the controlfile SQL script by alter database backup control file to trace; This is created in the user_dump_dest directory. Edit later.
3. Shutdown the Source database
4. Copy all datafiles (or FTP) into your destination or new database location.
5. Rename all your files for the new destination database (if desired).
6. Modify the controlfile SQL filename and syntax (see below).
7. Create your init<SID>.ora and dump destination directories. Just like a new database.
8. Login to svrmgrl and run the controlfile SQL. With luck, it should work.

spool hr_trace1.log
connect internal
STARTUP NOMOUNT;
CREATE CONTROLFILE SET DATABASE "HRPTO51" RESETLOGS NOARCHIVELOG     MAXLOGFILES 16
    MAXLOGMEMBERS 2
    MAXDATAFILES 200
    MAXINSTANCES 1
    MAXLOGHISTORY 100
LOGFILE
  GROUP 1 (
    '/data3/HRPTO51/HRPTO51log1.dbf'
  ) SIZE 50M,
  GROUP 2 (
    '/data3/HRPTO51/HRPTO51log2.dbf'
  ) SIZE 50M,
  GROUP 3 (
    '/data3/HRPTO51/HRPTO51log3.dbf'
  ) SIZE 50M,
DATAFILE

  '/data9/HRPTO51/HRPTO51sys1.dbf',
  '/data9/HRPTO51/HRPTO51hrrbs1.dbf',
  '/data9/HRPTO51/HRPTO51hrtemp1.dbf',
  '/data9/HRPTO51/HRPTO51hrindex1.dbf',
  '/data9/HRPTO51/HRPTO51hrtable1.dbf',

;

# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
# RECOVER DATABASE
# All data9 need archiving and a log switch is needed.
# ALTER SYSTEM ARCHIVE LOG ALL;
# Database can now be opened normally.

ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE rename global_name to HRPTO51;

Sylvan Creach
Oracle DBA
Cobe Labs
Lakewood CO 80215
303-239-2206 Received on Tue Oct 07 1997 - 00:00:00 CDT