Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2004
    Location
    London
    Posts
    76

    Unanswered: Recover/Restore database

    Hi,

    I have given the task to create a test Oracle 9i database from a hot backup. I am new to Oracle backup/recovery and need a speedy solution.

    I have created a typical database install and have copied the database files (control files/DBF files) onto the new server. How can I create this test database using these database files? Any help or guidance would be much appreciated.

    Thanks

    Dal

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    RMAN backup or manual cold backup?

    Sounds like you are starting with a manual cold-backup.
    ASsuming you are NOT using the same paths and filenames and you want to
    startup a different DB/sid.

    Assumptions (change these to whatever your sids are):
    original db sid: goldsecc
    copy db sid: goldsecf

    ** note: obviously you have already copied all the file except perhaps the backup controlfile. **

    Code:
    1.	Log into the source database (startup db if not already open) and 
    	backup controlfile to trace.
    
    		alter database backup controlfile to trace;
    
    2. 	Shut down the database (normal or immediate)
    
    		shutdown immediate;
    
    3. 	Goto the udump directory of the source db and rename the trace 
    	file just created to a create script for the new sid.
    
    		mv goldsecc_ora_29923.trc cre_goldsecf.sql
    
    4. 	Open the cre_goldsecf.sql file for editing.
    	
    		a. delete the first few dozen lines until the line "STARTUP NOMOUNT"
    		b. at line 2 change:
    				CREATE CONTROLFILE REUSE DATABASE "GOLDSECC" NORESETLOGS NOARCHIVELOG
    				to:
    				create controlfile SET database "goldsecf" RESETLOGS noarchivelog
    		c. delete the last 7 lines beginning with:
    				# Recovery is required ...
    		d. replace all references to the SOURCE sid with the new destination sid
    				(vi editor command line to replace strings in a file)
    				%s/goldsecc/goldsecf/g
    		e. note the directory PATHS for the redo and datafiles. These directories will
    			need to be created on the server for the destination database (step #4)
    	
    	
    4. 	Create directory structure for new database.
    
    Use the tar utility to group all relevant directories together. At the home directory of the source database (this should be located at /apps/oracle/admin/sid) change directory to that path (cd  /apps/oracle/admin/goldsecc) then issue:
    	tar -cvf goldsecc_base.tar *
    		
    Now goto the base directory where the datafiles/redo/ctrl reside (cd /oradata04/goldsecc) then tar the directory:
    			tar cvf goldsecc_files.tar *
    
    		Create two directories for the base files and the data files:
    			mkdir /apps/oracle/admin/goldsecf
    			mkdir /oradata01/goldsecf
    
    
    
    5. 	Move the appropriate tar files you just created to base paths you just created and untar the files.
    
    		Example:
    	
    mv /apps/oracle/admin/goldsecc/goldsecc_base.tar /apps/oracle/admin/goldsecf/goldsecc_base.tar
    		
    mv /oradata01/goldsecc/goldsecc_files.tar /oradata01/goldsecf/goldsecc_files.tar
    		
    		cd /apps/oracle/admin/goldsecf
    		tar xvf goldsecc_base.tar
    		
    		cd /apps/oracle/admin/goldsecf
    		tar xvf goldsecc_files.tar
    
    
    
    6. 	Rename all datafile/log names to reflect the new sid. Use the below awk script to change the datafiles to the appropriate names. Just replace the oldsid/newsid with the appropriate sid names and also change the grep to the correct oldsid.
    
    
    	 	ls -l | grep goldsecc | nawk '{\
     		old=$9; oldsid="goldsecc"; newsid="goldsecf"; gsub(oldsid, newsid, $9);\
     		print "mv",old,$9}' > rename_files.ksh
    
      		cd /export/home/oracle/data/goldsecf/redo  
    (now run the above script then run the rename_files.ksh file)
    	   	
    	   	cd /oradata01/goldsecf/redo 
    (now run the above script then run the rename_files.ksh file)
    
    		cd /oradata01/goldsecf/data 
    (now run the above script then run the rename_files.ksh file)
    
    
    7.	Modify files in /var/opt/oracle/:  oratab, listener.ora and tnsnames.ora to reflect 
    	the new db name (goldsecf)
    
    8. 	Modify init.ora to reflect new db name (filename and inside file)
    
    9. 	Add a soft link to the init.ora file just edited 
    	
    		Example:
    		ln -s /export/home/oracle/admin/goldsecf/pfile/initgoldsecf.ora 
    			/export/home/oracle/81720/dbs/initgoldsecf.ora
    
    10. 	Change SID to new db name (. oraenv)
    
    11. 	Run the new controlfile.sql script in sqlplus 
    	NOTE: you might need to rename or remove the existing controlfiles if you get errors
    
    		sqlplus "/as sysdba"
    		@/export/home/oracle/admin/goldsecf/create/cre_goldsecf.sql
    		
    		alter database open resetlogs;
    		shutdown;  
    		startup;
    	
    		alter database rename global_name to goldsecf;
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Feb 2004
    Location
    London
    Posts
    76
    Thanks for the speedy reply, however, Im assuming the 'source' database is the 'Original' database you are referring to? This is our production database and I dont have a time window to shutdown the database down. Is there any other way I can restore/create this copy database using the production database file that I have already copied??

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    source = production
    you don't have to shutdown since you already have a cold-backup correct?

    skip that step. you need to create a backup controlfile to trace from your
    production db (does not require a shutdown).

    so:
    skip step 2

    instead of steps 4 & 5, you will need to manually create the directory
    structures you want. Sometyhing like this:
    Code:
     sid home directory 
    mkdir /apps/oracle/admin/testsid
    mkdir /apps/oracle/admin/testsid/bdump
    mkdir /apps/oracle/admin/testsid/cdump
    mkdir /apps/oracle/admin/testsid/create
    mkdir /apps/oracle/admin/testsid/ctrl
    mkdir /apps/oracle/admin/testsid/pfile
    mkdir /apps/oracle/admin/testsid/redo
    mkdir /apps/oracle/admin/testsid/udump
    
     now for the datafiles and duplexing the redo/ctrl
    mkdir /oradata03/testsid
    mkdir /oradata03/testsid/data
    mkdir /oradata03/testsid/redo
    mkdir /oradata03/testsid/ctrl
    now move all your cold-backup files to the appropriate file-paths
    example below moves all your datafiles to one directory:
    Code:
    mv *.dbf /oradata03/testsid/data/
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Feb 2004
    Location
    London
    Posts
    76
    Thanks for the help, just need to clarify what I am doing is correct. I restoring this database on Windows platform. As I have installed Oracle 9i database, a service which I named "statsrv" already exists with its own control and db files. Do I need to create a new service or can I use the existing one? Shall I delete these db and ctl files?
    I have created a backup controlfile from the production database.

    Now I did not copy the redo logs (silly me I know!) but can I still create this database?

    I have created the same directory structure on the test server (called statsrv) and have copied the production db & ctl files in the relevant folder. As I will be creating new ctl files do I need the production ctl files in this folder? If new ctl files are created where are they created? What statements do I use for the log files as I need to create new ones? Also what mode does the database need to be in before I run this script (nomount?)

    I have amended the script as you advised, see below, if you find any issues please let me know:

    STARTUP NOMOUNT
    CREATE CONTROLFILE REUSE DATABASE "statsrv" NORESETLOGS ARCHIVELOG
    -- SET STANDBY TO MAXIMIZE PERFORMANCE
    MAXLOGFILES 50
    MAXLOGMEMBERS 5
    MAXDATAFILES 120
    MAXINSTANCES 1
    MAXLOGHISTORY 453
    LOGFILE
    HELP!
    DATAFILE
    'D:\CLUSTER_DB0\SYSTEM01.DBF',
    'D:\CLUSTER_DB0_LOG\UNDOTBS01.DBF',
    'D:\CLUSTER_DB0\CWMLITE01.DBF',
    'D:\CLUSTER_DB0\DRSYS01.DBF',
    'D:\CLUSTER_DB0\XCAL_DATA8.DBF',
    'D:\CLUSTER_DB0\INDX01.DBF',
    'D:\CLUSTER_DB0\TOOLS01.DBF',
    'D:\CLUSTER_DB0\USERS01.DBF',
    'D:\CLUSTER_DB0\XDB01.DBF',
    'D:\CLUSTER_DB0\XCAL_DATA0.DBF',
    'D:\CLUSTER_DB0\XCAL_DATA1.DBF',
    'D:\CLUSTER_DB0\XCAL_DATA2.DBF',
    'D:\CLUSTER_DB0\XCAL_DATA3.DBF',
    'D:\CLUSTER_DB0\XCAL_DATA4.DBF',
    'D:\CLUSTER_DB0\XCAL_DATA5.DBF',
    'D:\CLUSTER_DB0\XCAL_DATA6.DBF',
    'D:\CLUSTER_DB0\XCAL_HIST0.DBF',
    'D:\CLUSTER_DB0\XCAL_HIST1.DBF',
    'D:\CLUSTER_DB0\XCAL_HIST2.DBF',
    'D:\CLUSTER_DB0\XCAL_INDEX0.DBF',
    'D:\CLUSTER_DB0\XCAL_INDEX1.DBF',
    'D:\CLUSTER_DB0\XCAL_INDEX2.DBF',
    'D:\CLUSTER_DB0\PRODSYS_DATA.DBF',
    'D:\CLUSTER_DB0\PRODSYS_INDEX.DBF',
    'D:\CLUSTER_DB0\PROFMAN_DATA.DBF',
    'D:\CLUSTER_DB0\SYSTEM02.DBF',
    'D:\CLUSTER_DB0\XCAL_MI1.DBF',
    'D:\CLUSTER_DB0\INDX2',
    'D:\CLUSTER_DB0_LOG\UNDOTBS02.DBF',
    'D:\CLUSTER_DB0\XCAL_DATA7.DBF'
    CHARACTER SET WE8MSWIN1252;
    RECOVER DATABASE
    ALTER SYSTEM ARCHIVE LOG ALL;
    ALTER DATABASE OPEN;
    ALTER TABLESPACE TEMP01 ADD TEMPFILE 'D:\CLUSTER_DB0\TEMP015.ORA'
    SIZE 4000M REUSE AUTOEXTEND OFF;
    ALTER TABLESPACE TEMP01 ADD TEMPFILE 'D:\CLUSTER_DB0\TEMP014.ORA' REUSE;
    ALTER TABLESPACE TEMP01 ADD TEMPFILE 'D:\CLUSTER_DB0\TEMP013.ORA' REUSE;
    ALTER TABLESPACE TEMP01 ADD TEMPFILE 'D:\CLUSTER_DB0\TEMP011.ORA'
    SIZE 5000M REUSE AUTOEXTEND OFF;
    STARTUP NOMOUNT
    CREATE CONTROLFILE REUSE DATABASE "ORC1" RESETLOGS ARCHIVELOG
    -- SET STANDBY TO MAXIMIZE PERFORMANCE
    MAXLOGFILES 50
    MAXLOGMEMBERS 5
    MAXDATAFILES 120
    MAXINSTANCES 1
    MAXLOGHISTORY 453
    LOGFILE
    HELP!
    DATAFILE
    'D:\CLUSTER_DB0\SYSTEM01.DBF',
    'D:\CLUSTER_DB0_LOG\UNDOTBS01.DBF',
    'D:\CLUSTER_DB0\CWMLITE01.DBF',
    'D:\CLUSTER_DB0\DRSYS01.DBF',
    'D:\CLUSTER_DB0\XCAL_DATA8.DBF',
    'D:\CLUSTER_DB0\INDX01.DBF',
    'D:\CLUSTER_DB0\TOOLS01.DBF',
    'D:\CLUSTER_DB0\USERS01.DBF',
    'D:\CLUSTER_DB0\XDB01.DBF',
    'D:\CLUSTER_DB0\XCAL_DATA0.DBF',
    'D:\CLUSTER_DB0\XCAL_DATA1.DBF',
    'D:\CLUSTER_DB0\XCAL_DATA2.DBF',
    'D:\CLUSTER_DB0\XCAL_DATA3.DBF',
    'D:\CLUSTER_DB0\XCAL_DATA4.DBF',
    'D:\CLUSTER_DB0\XCAL_DATA5.DBF',
    'D:\CLUSTER_DB0\XCAL_DATA6.DBF',
    'D:\CLUSTER_DB0\XCAL_HIST0.DBF',
    'D:\CLUSTER_DB0\XCAL_HIST1.DBF',
    'D:\CLUSTER_DB0\XCAL_HIST2.DBF',
    'D:\CLUSTER_DB0\XCAL_INDEX0.DBF',
    'D:\CLUSTER_DB0\XCAL_INDEX1.DBF',
    'D:\CLUSTER_DB0\XCAL_INDEX2.DBF',
    'D:\CLUSTER_DB0\PRODSYS_DATA.DBF',
    'D:\CLUSTER_DB0\PRODSYS_INDEX.DBF',
    'D:\CLUSTER_DB0\PROFMAN_DATA.DBF',
    'D:\CLUSTER_DB0\SYSTEM02.DBF',
    'D:\CLUSTER_DB0\XCAL_MI1.DBF',
    'D:\CLUSTER_DB0\INDX2',
    'D:\CLUSTER_DB0_LOG\UNDOTBS02.DBF',
    'D:\CLUSTER_DB0\XCAL_DATA7.DBF'
    CHARACTER SET WE8MSWIN1252;
    RECOVER DATABASE USING BACKUP CONTROLFILE
    ALTER DATABASE OPEN RESETLOGS;
    ALTER TABLESPACE TEMP01 ADD TEMPFILE 'D:\CLUSTER_DB0\TEMP015.ORA'
    SIZE 4000M REUSE AUTOEXTEND OFF;
    ALTER TABLESPACE TEMP01 ADD TEMPFILE 'D:\CLUSTER_DB0\TEMP014.ORA' REUSE;
    ALTER TABLESPACE TEMP01 ADD TEMPFILE 'D:\CLUSTER_DB0\TEMP013.ORA' REUSE;
    ALTER TABLESPACE TEMP01 ADD TEMPFILE 'D:\CLUSTER_DB0\TEMP011.ORA'
    SIZE 5000M REUSE AUTOEXTEND OFF;

    If this is all ok and successful, I next need to change the init.ora file, currently it is:

    db_domain=" "
    db_name=statsrv

    As the name sid name is what I require it to be no changes need to be made here. However, I do need to change the file configuration for control files to point to the correct path (which would be 'D:\CLUSTER_DB0\CONTROL01.CTL etc). The instance name is statsrv which I do not need to change?

    If there are any other things I have missed or need to know, please let me know. Thanks for all your help...its much appreciated.

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    first, notice how the script repeats? delete one repeat section (either one)
    then change:
    CREATE CONTROLFILE REUSE DATABASE "statsrv" NORESETLOGS ARCHIVELOG
    to:
    CREATE CONTROLFILE set DATABASE "statsrv" resetlogs NOARCHIVELOG

    second, take everything off the end of your file (save the lines, but remove them from the file) after the line stating:
    CHARACTER SET WE8MSWIN1252;

    third, you do not need controlfiles, those will be created for you in the paths you set in the init.ora "controlfiles = blahblahblah".
    fourth, I don't THINK you need the redo, but you could copy those from
    production without shutting down the db (if you run into errors).
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Feb 2004
    Location
    London
    Posts
    76
    Thanks for all your help, have managed to create the control file.

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    so the db is up and running?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Feb 2004
    Location
    London
    Posts
    76
    No not up!! Kept getting error when recovering!! Oracle chucks out an inconsistency error...pointing at a System.dbf file!! Did a dbverify on the file no corruption!! Have re-installed Oracle naming the SID and instance the same as production database...gonna have another crack at it Monday!!!

  10. #10
    Join Date
    Oct 2004
    Posts
    145
    If you are restoring from hot backup system.dbf maybe out of sync with others.

    This is only a suggestion and I have not done this before.

    When you generate the control file enable the archivelog mode. Copy all of the relevant archive log files. When you start the database and it complains about inconsistency error, recover the database to a point in time using backup control files (ones you generated).

    recover database until cancel (until time...) using backup controlfiles;

    Hope it works. If database recovers you can pull the database out of archivelog mode.

  11. #11
    Join Date
    Jul 2003
    Posts
    2,296
    What?!

    You don't recover anything. Just run the create-controlfile script.
    Where in my steps does it say to recover the database?

    You are CLONING a database, not RESTORING a database.

    What step did you deviate?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  12. #12
    Join Date
    Oct 2004
    Posts
    145
    As I mentioned it was only a suggestion.

    I fully agree with your method of cloning from "cold" backup but have never used this method from a "hot" backup.

    Since the clone detected an out of sync file (from a "hot" backup) when database is started with error, I thought recover would help.

Posting Permissions

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