DB refresh

[Former Member]

I need to do a DB refresh in quality server.

PRD ONLINE backup should be restore to quality server with HP data protector and recover oracle RDBMS.

We have a quality server up and running, we don't need any kind of data from quality.

so, there is nothing to be exported from quality server.

we have stopped SAP & Oracle services in quality & deleted files with in directories of quality server:-

sapdata<n>,

sapbackup,

oraarch,

contorl files at 3 locations (MirrA, MirrB & Sapdata1),

[sapcheck, sapreorg, saptrace\background, saptrace\usertrace are intact]

We started restoring online backup with help of HP Data protector GUI to Qulaity server.

it got restored to oracle\PRD along with oracle\QUA.

after restoration; we have moved sapdata<n>, oraarch and sapbackup in to oracle\QUA.

Oraarch files were renamed accordingly; PRDARCHARCxxxxx.001 to QUAARCHARCxxxxxx.001

on source system, we have generated control file script with help of "alter database backup controlfile to trace"

we have edited the script for controlfile by considering from " Set #2. RESETLOGS case ".

STARTUP NOMOUNT

CREATE CONTROLFILE SET DATABASE "QUA" RESETLOGS NOARCHIVELOG

    MAXLOGFILES 255

    MAXLOGMEMBERS 3

    MAXDATAFILES 254

    MAXINSTANCES 50

    MAXLOGHISTORY 9344

LOGFILE

  GROUP 1 (

    'D:\ORACLE\QUA\ORIGLOGA\LOG_G11M1.DBF',

    'D:\ORACLE\QUA\MIRRLOGA\LOG_G11M2.DBF'

  ) SIZE 50M,

  GROUP 2 (

    'D:\ORACLE\QUA\ORIGLOGB\LOG_G12M1.DBF',

    'D:\ORACLE\QUA\MIRRLOGB\LOG_G12M2.DBF'

  ) SIZE 50M,

  GROUP 3 (

    'D:\ORACLE\QUA\ORIGLOGA\LOG_G13M1.DBF',

    'D:\ORACLE\QUA\MIRRLOGA\LOG_G13M2.DBF'

  ) SIZE 50M,

  GROUP 4 (

    'D:\ORACLE\QUA\ORIGLOGB\LOG_G14M1.DBF',

    'D:\ORACLE\QUA\MIRRLOGB\LOG_G14M2.DBF'

  ) SIZE 50M

-- STANDBY LOGFILE

DATAFILE

  'D:\ORACLE\QUA\SAPDATA1\SYSTEM_1\SYSTEM.DATA1',

  'D:\ORACLE\QUA\SAPDATA3\UNDO_1\UNDO.DATA1',

  'D:\ORACLE\QUA\SAPDATA1\SYSAUX_1\SYSAUX.DATA1',

  'D:\ORACLE\QUA\SAPDATA1\SR3_1\SR3.DATA1',

  'D:\ORACLE\QUA\SAPDATA<n-1>\SR3_<n-1>\SR3.DATA<n-1>',

  'D:\ORACLE\QUA\SAPDATA<n>\SR3_<n>\SR3.DATA<n>'

CHARACTER SET UTF8

;

-- Commands to re-create incarnation table

-- Below log names MUST be changed to existing filenames on

-- disk. Any one log file from each branch can be used to

-- re-create incarnation records.

-- ALTER DATABASE REGISTER LOGFILE 'D:\ORACLE\QUA\ORAARCH\QUAARCHARC00001_0723399286.001';

-- Recovery is required if any of the datafiles are restored backups,

-- or if the last shutdown was not normal or immediate.

RECOVER DATABASE USING BACKUP CONTROLFILE

-- Database can now be opened zeroing the online logs.

ALTER DATABASE OPEN RESETLOGS;

-- Commands to add tempfiles to temporary tablespaces.

-- Online tempfiles have complete space information.

-- Other tempfiles may require adjustment.

ALTER TABLESPACE PSAPTEMP ADD TEMPFILE 'D:\ORACLE\QUA\SAPDATA2\TEMP_1\TEMP.DATA1'

     SIZE 8500M REUSE AUTOEXTEND ON NEXT 20971520  MAXSIZE 10000M;

-- End of tempfile additions.

--

Now the problem is that i'm not able to do recovery.

Oracle is getting mounted but not able to recover media.

we haven't tried open database. since, the recovery is not complete.

Request you to guide and correct me in case of any wrong in procedure.

i have attached my PRD backup log.

regards,

Harika

PRD_Backup_Log.txt.zip

[Former Member · Accepted solution]

Hi Harika,

Did you created the control files, successfully? At the first step old control files need to be deleted and re-created by using "control.sql". To do so, execute the statement between "

CREATE CONTROLFILE SET DATABASE "QUA" RESETLOGS NOARCHIVELOG" and "

CHARACTER SET UTF8;" and execute with sysdba role.

Secondly, import the offline redolog files, by the statement;

SQL>shutdown

SQL>startup mount

SQL>recover database using backup controlfile until cancel;

After the offline redologs imported successfully, execute the statement, below;

SQL>alter database open resetlogs;

Please note that you check the directory file permissions and owners over the operating system.

Best regards,

Orkun Gedik

[Former Member · Accepted solution]

Hi Orkun,

Thanks for your reply

I have deleted old control files and i'm able to re-create with help of "control.sql"
But, in the script i have taken up to "-- End of tempfile additions."

Database started in Mount mode.

During the stage of recovery; it got failed.

Herewith i'm pasting the log of previous recovery attempt:

SQL> @QUA_control.sql

ORACLE instance started.

Total System Global Area 2097152000 bytes

Fixed Size                  2067008 bytes

Variable Size            1107297728 bytes

Database Buffers          973078528 bytes

Redo Buffers               14708736 bytes

Control file created.

ORA-00279: change 1300962913 generated at 04/14/2013 08:11:22 needed for thread

1

ORA-00289: suggestion : D:\ORACLE\QUA\ORAARCH\QUAARCHARC70631_0723399286.001

ORA-00280: change 1300962913 for thread 1 is in sequence #70631

ORA-00308: cannot open archived log '--'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) The system cannot find the file specified.

ORA-00308: cannot open archived log 'ALTER'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) The system cannot find the file specified.

ORA-00308: cannot open archived log '--'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) The system cannot find the file specified.

ORA-00308: cannot open archived log '--'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) The system cannot find the file specified.

ORA-00308: cannot open archived log '--'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) The system cannot find the file specified.

ORA-00308: cannot open archived log 'ALTER'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) The system cannot find the file specified.

ORA-00308: cannot open archived log 'SIZE'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) The system cannot find the file specified.

ORA-00308: cannot open archived log '--'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) The system cannot find the file specified.

ORA-00308: cannot open archived log '--'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) The system cannot find the file specified.

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

AUTO

ORA-00283: recovery session canceled due to errors

ORA-00368: checksum error in redo log block

ORA-00353: log corruption near block 76687 change 1300974142 time 04/14/2013

08:12:04

ORA-00334: archived log: 'D:\ORACLE\QUA\ORAARCH\QUAARCHARC70631_0723399286.001'

ORA-01112: media recovery not started

SQL>

How to over come this corrupted log ?

Is there any dependency of generating control file to trace in source system ?

like PIT recovery; can i restore up to QUAARCHARC70630 instead of 31 ?

do we have to follow any specific process ?

Here in my case HP data protector directly restoring files to storage (media agents).

Hope i'm on the right track. please guide with the best approach.

Kind regards,

Harika

[Former Member · Accepted solution]

SQL> select status from v$instance;

STATUS

------------

MOUNTED

SQL> select file#,STATUS, FUZZY from v$datafile_header;

     FILE# STATUS  FUZ

---------- ------- ---

         1 ONLINE  NO

         2 ONLINE  NO

         3 ONLINE  NO

         4 ONLINE  NO

         5 ONLINE  NO

         6 ONLINE  NO

         7 ONLINE  NO

         8 ONLINE  NO

         9 ONLINE  NO

        10 ONLINE  NO

        11 ONLINE  NO

     FILE# STATUS  FUZ

---------- ------- ---

        12 ONLINE  NO

        13 ONLINE  NO

        14 ONLINE  NO

        15 ONLINE  NO

        16 ONLINE  NO

        17 ONLINE  NO

        18 ONLINE  NO

        19 ONLINE  NO

        20 ONLINE  NO

        21 ONLINE  NO

        22 ONLINE  NO

     FILE# STATUS  FUZ

---------- ------- ---

        23 ONLINE  NO

        24 ONLINE  NO

        25 ONLINE  NO

        26 ONLINE  NO

        27 ONLINE  NO

        28 ONLINE  NO

        29 ONLINE  NO

        30 ONLINE  NO

        31 ONLINE  NO

        32 ONLINE  NO

        33 ONLINE  NO

     FILE# STATUS  FUZ

---------- ------- ---

        34 ONLINE  NO

        35 ONLINE  NO

        36 ONLINE  NO

        37 ONLINE  NO

        38 ONLINE  NO

        39 ONLINE  NO

        40 ONLINE  NO

        41 ONLINE  NO

        42 ONLINE  NO

        43 ONLINE  NO

        44 ONLINE  NO

     FILE# STATUS  FUZ

---------- ------- ---

        45 ONLINE  NO

        46 ONLINE  NO

        47 ONLINE  NO

        48 ONLINE  NO

        49 ONLINE  NO

        50 ONLINE  NO

        51 ONLINE  NO

        52 ONLINE  NO

        53 ONLINE  NO

        54 ONLINE  NO

        55 ONLINE  NO

     FILE# STATUS  FUZ

---------- ------- ---

        56 ONLINE  NO

        57 ONLINE  NO

        58 ONLINE  NO

        59 ONLINE  NO

        60 ONLINE  NO

        61 ONLINE  NO

        62 ONLINE  NO

        63 ONLINE  NO

        64 ONLINE  NO

        65 ONLINE  NO

        66 ONLINE  NO

     FILE# STATUS  FUZ

---------- ------- ---

        67 ONLINE  NO

        68 ONLINE  NO

        69 ONLINE  NO

69 rows selected.

SQL>

[former_member182034 · Accepted solution]

hi Harika,

please execute the following commands

SQL>recover database using backup controlfile until cancel;

cancel

SQL>alter database open resetlogs;

Regards,

[Former Member · Accepted solution]

Hi,

>> But, in the script i have taken up to "-- End of tempfile additions."

Because of you execute whole script, the procedure has not been executed successfully. Now that you created the control files successfully, you should import the offline redolog files by using statement, I noted in my previous message.

Best regards,

Orkun Gedik

[Former Member · Accepted solution]

Hi Abdul,

It worked

thank you.