1 Attachment(s)
manual backup of 8i to 11g
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
8i
/var/oracle/oradata/SID/ - database, *.ctl
/usr/oracle/oradata/SID/ - database
/usr/oracle/archive/SID/ - logs
/home/oracle/admin/SID/ - alerts
/home/oracle/oradata/SID/ - redologs
ORACLE_HOME/dbs/ - init*.ora, lk*, orapw*
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
SQL> exit
$ mkdir -p /tmp/backup/DCTEST/database
$ mkdir /tmp/backup/DCTEST/controlfile
$ mkdir /tmp/backup/DCTEST/onlinelog
$ mkdir /tmp/backup/DCTEST/archive
$ mkdir /tmp/backup/DCTEST/dbs
$ cp /usr/oracle/oradata/DCTEST/system01.dbf /tmp/backup/DCTEST/database/
$ cp /usr/oracle/oradata/DCTEST/tools01.dbf /tmp/backup/DCTEST/database/
$ cp /usr/oracle/oradata/DCTEST/rbs01.dbf /tmp/backup/DCTEST/database/
$ cp /usr/oracle/oradata/DCTEST/temp01.dbf /tmp/backup/DCTEST/database/
$ cp /var/oracle/oradata/DCTEST/eqndxs01.dbf /tmp/backup/DCTEST/database/
$ cp /var/oracle/oradata/DCTEST/drsys01.dbf /tmp/backup/DCTEST/database/
$ cp /usr/oracle/oradata/DCTEST/eqdata01.dbf /tmp/backup/DCTEST/database/
$ cp /usr/oracle/oradata/DCTEST/CATTBS_1.dbf /tmp/backup/DCTEST/database/
$ cd $ORACLE_HOME
$ find . | grep initDCTEST
./dbs/initDCTEST.ora
$ cp ./dbs/initDCTEST.ora /tmp/backup/DCTEST/dbs/
$ cp ./dbs/lkDCTEST /tmp/backup/DCTEST/dbs/
$ cp ./dbs/orapwDCTEST /tmp/backup/DCTEST/dbs/
$ ls /home/oracle/admin/DCTEST
bdump cdump create pfile scripts udump
$ cp -a /home/oracle/admin/DCTEST/. /tmp/backup/DCTEST/
$ ls /home/oracle/oradata/DCTEST
control01.ctl redo01.log redo02.log redo03.log
$ cp /home/oracle/oradata/DCTEST/control01.ctl /tmp/backup/DCTEST/controlfile/
$ cp /home/oracle/oradata/DCTEST/redo*.log /tmp/backup/DCTEST/onlinelog/
$ ls /var/oracle/oradata/DCTEST
control02.ctl drsys01.dbf eqndxs01.dbf
$ cp /var/oracle/oradata/DCTEST/control02.ctl /tmp/backup/DCTEST/controlfile/
$ ls /usr/oracle/oradata/DCTEST
CATTBS_1.dbf eqdata01.dbf system01.dbf tools01.dbf
control03.ctl rbs01.dbf temp01.dbf
$ cp /usr/oracle/oradata/DCTEST/control03.ctl /tmp/backup/DCTEST/controlfile/
$ 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
$ cp /home/oracle/admin/DCTEST/udump/ora_21295.trc /tmp/backup/DCTEST/cr_control.sql
$ vim /tmp/backup/DCTEST/cr_control.sql
# 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/
$ vim /tmp/backup/DCTEST/dbs/initDCTEST.ora
# change:
control_files = ("/home/oracle/oradata/DCTEST/control01.ctl",
"/var/oracle/oradata/DCTEST/control02.ctl",
"/usr/oracle/oradata/DCTEST/control03.ctl")
log_archive_dest_1 = "location=/usr/oracle/archive/DCTEST"
background_dump_dest = /home/oracle/admin/DCTEST/bdump
core_dump_dest = /home/oracle/admin/DCTEST/cdump
user_dump_dest = /home/oracle/admin/DCTEST/udump
# to
control_files = ("/u02/oradata/DCTEST/controlfile/control01.ctl",
"/u02/oradata/DCTEST/controlfile/control02.ctl",
"/u02/oradata/DCTEST/controlfile/control03.ctl")
log_archive_dest_1 = "location=/u02/oradata/DCTEST/archive"
background_dump_dest = /u02/oradata/DCTEST/bdump
core_dump_dest = /u02/oradata/DCTEST/cdump
user_dump_dest = /u02/oradata/DCTEST/udump
# MANUAL BACKUP DONE :)
# now copy the DCTEST directory to the new server
#on new server
# su - oracle
$ whoami
oracle
$ cd /u02/oradata
$ scp -4Cpr root@linuxserv:/tmp/backup/. .
yes
*****
..... etc....
$ cp /u02/oradata/DCTEST/dbs/initDCTEST.ora $ORACLE_HOME/dbs/
$ cp /u02/oradata/DCTEST/dbs/lkDCTEST $ORACLE_HOME/dbs/
$ cp /u02/oradata/DCTEST/dbs/orapwDCTEST $ORACLE_HOME/dbs/
# this is where i get stuck, the creating new control file using the sql file.
$ cd DCTEST
$ echo $ORACLE_SID
TESTDB
$ export ORACLE_SID=DCTEST
$ svrmgrl
-bash: svrmgrl: command not found
$ sqlplus /nolog
SQL*Plus: Realease 11.1.0.6.0
SQL> CONNECT / AS SYSDBA
Connected to an idle instance.
SQL> @cr_control
http://www.dbasupport.com/forums/att...0&d=1304372229