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/
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
Database was active at the time you cp-ed the datafiles, wasn't it?
This means you got an inconsistent backup. Either you are prepared to apply archivelogs - provided database is in archivelog mode - or you have not a valid backup.
Either way, creation of an Ora11g database requires much more than the parameters and basic setup needed to create an Ora8i one.
Let me say it once again...
1- Manually create your empty Ora11g database.
2- Export from 8i
3- FTP
4- Import into 11g
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
it would have been nice if oracle created a backup & convert software for all their databases.
It would be nice if users read some things first before jumping into using a complex database management system. Oracle does have backup "software" and it also has a "convert" capability.
Had you done any reading ahead of time, you would have known that copying all of your 8i files onto the 11g system was a complete waste of time.
Another statement to that effect:
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.
Are you claiming that a user cannot export or import into his or her own schema, that one has to be the system admin or know the system password?
# create a cr_control.sql with is info:
$ vim /u02/oradata/DCTEST/cr_control.sql
CREATE DATABASE DCTEST
USER SYS IDENTIFIED BY DCTEST
USER SYSTEM IDENTIFIED BY DCTEST
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
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 254
MAXINSTANCES 8
MAXLOGHISTORY 907
CHARACTER SET us7ascii
NATIONAL CHARACTER SET al16utf16
DATAFILE '/u02/oradata/DCTEST/database/system01.dbf' REUSE EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TEMPFILE '/u02/oradata/DCTEST/database/temp01.dbf' REUSE
SYSAUX DATAFILE ‘/u02/oradata/DCTEST/database/sysaux.dbf’ REUSE
;
# Create Server parameter file (SPFILE) using this parameter file and STARTUP the
# instance in NOMOUNT mode.
$ cd /u02/oradata/DCTEST/
$ sqlplus /nolog
SQL> connect / as sysdba
SQL> create SPFILE from PFILE=’/u02/oradata/DCTEST/dbs/initDCTEST.ora’;
SQL> startup nomount
SQL> @cr_control
# Run the scripts necessary to build views, synonyms, and PL/SQL packages
SQL> connect / as sysdba
SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql
SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql
# Shutdown the instance and startup the database. Your database is ready for use!
SQL> shutdown immediate
SQL> startup
SQL> exit
# now just copy the DCTEST.dmp file fromthe old server and import it into the new server.
Name your script create_database, because that is what is does. Why use cr_control to confuse the issue? Spend some time reading the documentation and learn something instead of relying on cut and paste from someone else. What do you have against using dbca? There is nothing special about your create database statement which requires extra steps or anything like that.
Have you edited the initORA file and removed old/obsoleted parameters? Why are you using weird paths for your spfile? Do you know what OFA is? Why are you setting LD_LIBRARY_PATH in an 11g environment? Why are you using 11gR1 instead of R2?
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
i use dbca and create a custom dtatabase with default settings, then try to import, it did not work.
I can't believe i fail at a simply thing like this, only think i can do now is do a full OS backup (image the server) so i can recreate it if that machine dies, or two or more HD fail at the same time. all i have now is a daily export of all the SIDs, that it backed up every night to tape.