manual backup of 8i to 11g
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 12

Thread: manual backup of 8i to 11g

Threaded View

  1. #1
    Join Date
    May 2011
    Posts
    25

    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

    Attached Images Attached Images

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width