it would have been nice if oracle created a backup & convert software for all their databases.
as long as you are the system admin, you have access to the database server, you know the system password and can use exp/imp.
i like to know what is going on, so i tried to do a manual backup and recovery!
Every thing will go well with the manual copying of the database (all datafiles,controlfile,initSID.ora,orapwSID,lkSID,cr_control.sql) but when it comes to the manual restore/importing you have a lot of problem.
11g database file structure has change from 8i. 11g
/u02/oradata/SID/controlfile
/u02/oradata/SID/database
/u02/oradata/SID/onlinelog
now if i copy all the SID directory and put them one place, say /u02/oradata/
and create directories like 11g (eg: database, controlfile, onlinelog), maybe i can get it to work. now all that is stopping me is the sql file to re-create the SIDs,tables and users.
i can get the 8i sql script to re-make the database, but it will not work in 11g.
This is how i did my manual backup in oracle 8i:
$ whoami
oracle
$ . oraenv
ORACLE_SID = [oracle] ? DCTEST
# sqlplus "/ as sysdba"
username: system
password: ***
SQL> select name from v$database;
DCTEST
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/usr/oracle/oradata/DCTEST/system01.dbf
/usr/oracle/oradata/DCTEST/tools01.dbf
/usr/oracle/oradata/DCTEST/rbs01.dbf
/usr/oracle/oradata/DCTEST/temp01.dbf
/var/oracle/oradata/DCTEST/eqndxs01.dbf
/var/oracle/oradata/DCTEST/drsys01.dbf
/usr/oracle/oradata/DCTEST/eqdata01.dbf
/usr/oracle/oradata/DCTEST/CATTBS_1.dbf
$ cp -a /usr/oracle/archive/DCTEST/. /tmp/backup/DCTEST/archive/
# now create the sql file to re-make the controlfile for the DCTEST database
# svrmgrl is not in 11g so you have to use "sqlplus /nolog" then
# CONNECT / AS SYSDBA
$ svrrmgrl
Oracle Server Manager Release 3.1.7.0.0 - Production
Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved.
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
JServer Release 8.1.7.4.0 - Production
SVRMGR> connect internal
connect internal
Password:
Connected.
SVRMGR> alter database backup controlfile to trace;
Statement processed.
SVRMGR> exit
#write-down the file name at the top of the list, you can check the date.
$ ls -lt /home/oracle/admin/DCTEST/udump/ | less
# now remove everything from the start of the file up to the
# "START NOMOUNT" statement and everything after the semi-colon (.
# now edit the line starting with "CREATE CONTROLFILE"
# replace the word "REUSE" with "SET"
# replace the word "NORESETLOGS" with "RESETLOGS"
# this is my file:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
JServer Release 8.1.7.4.0 - Production
ORACLE_HOME = /home/oracle/8i
System name: Linux
Node name: linuxserv
Release: 2.4.9-e.3smp
Version: #1 SMP Fri May 3 16:48:54 EDT 2002
Machine: i686
Instance name: DCTEST
Redo thread mounted by this instance: 1
Oracle process number: 9
Unix process pid: 14891, image: oracle@linuxserv
*** SESSION ID:(8.15713) 2011-05-04 12:50:53.388
*** 2011-05-04 12:50:53.388
# The following commands will create a new control file and use it
# to open the database.
# Data used by the recovery manager will be lost. Additional logs may
# be required for media recovery of offline data files. Use this
# only if the current version of all online logs are available.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "DCTEST" NORESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 254
MAXINSTANCES 8
MAXLOGHISTORY 907
LOGFILE
GROUP 1 '/home/oracle/oradata/DCTEST/redo01.log' SIZE 4M,
GROUP 2 '/home/oracle/oradata/DCTEST/redo02.log' SIZE 4M,
GROUP 3 '/home/oracle/oradata/DCTEST/redo03.log' SIZE 4M
DATAFILE
'/usr/oracle/oradata/DCTEST/system01.dbf',
'/usr/oracle/oradata/DCTEST/tools01.dbf',
'/usr/oracle/oradata/DCTEST/rbs01.dbf',
'/usr/oracle/oradata/DCTEST/temp01.dbf',
'/var/oracle/oradata/DCTEST/eqndxs01.dbf',
'/var/oracle/oradata/DCTEST/drsys01.dbf',
'/usr/oracle/oradata/DCTEST/eqdata01.dbf',
'/usr/oracle/oradata/DCTEST/CATTBS_1.dbf'
CHARACTER SET US7ASCII
;
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
# Database can now be opened normally.
ALTER DATABASE OPEN;
# No tempfile entries found to add.
# after editing the file i now have this:
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "DCTEST" RESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 254
MAXINSTANCES 8
MAXLOGHISTORY 907
LOGFILE
GROUP 1 '/u02/oradata/DCTEST/onlinelog/redo01.log' SIZE 4M,
GROUP 2 '/u02/oradata/DCTEST/onlinelog/redo02.log' SIZE 4M,
GROUP 3 '/u02/oradata/DCTEST/onlinelog/redo03.log' SIZE 4M
DATAFILE
'/u02/oradata/DCTEST/database/system01.dbf',
'/u02/oradata/DCTEST/database/tools01.dbf',
'/u02/oradata/DCTEST/database/rbs01.dbf',
'/u02/oradata/DCTEST/database/temp01.dbf',
'/u02/oradata/DCTEST/database/eqndxs01.dbf',
'/u02/oradata/DCTEST/database/drsys01.dbf',
'/u02/oradata/DCTEST/database/eqdata01.dbf',
'/u02/oradata/DCTEST/database/CATTBS_1.dbf'
CHARACTER SET US7ASCII
;
# now i need to modify the initDCTEST.ora file to point to the new location
# of my database files. this file is also in the pfile directory.
# /tmp/backup/DCTEST/pfile/
.
Reply With Quote