Recover Log Shipping and then Resume Log Shipping

  • Quick question that I'm sure others have been through.

    If we recover our log shipped DR instance as part of a DR exercise what effort will be required to get it participating in log shipping again?

    My first thought was to take a copy-only back up after the restore and use that as the seed dB. When we finish with testing we would restore the full backup with no recovery, then resume the LS jobs. Will the log restores match up wrt LSNs?

    Our DR site is across a slow pipe and I don't relish having to squeeze a 100 GB file across it. 😛

    It would also leave us without a DR instance for quite awhile.

    Regards;

    Greg

  • I believe I tried that for the same reason and I had no luck. I created a job that has 2 process

    then I can schedule it for off hours.Then you can run the LSCOPY and LSRESTORE when you come in or add 2 more steps to your job.

    ---hope that helps

    PROCESS #1

    EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'YOUR _DB'

    GO

    USE [master]

    GO

    ALTER DATABASE [YOUR_DB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    GO

    ALTER DATABASE [YOU_DB] SET SINGLE_USER

    GO

    USE [master]

    GO

    DROP DATABASE [YOUR_DB]

    PROCESS #2

    RESTORE DATABASE your_DB

    FROM DISK = '\\Bak\YOUR_backup.bak'

    WITH

    DBO_ONLY,

    REPLACE,

    STANDBY = 'D:\\YOUR_DB_database_name.ldf',

    MOVE 'xxx' TO 'D:\xxx\YOUR_Data.mdf',

    MOVE 'xxx_Log' TO 'D:\YOUR_DB_new_Log.ldf'

  • If you use the system stored procedures sp_change_primary_role and sp_change_secondary_role to switch the roles in the first place, and then use them to reverse the roles when you have finished the DR exercise, then no full database restore is required.

    There's a lot more to it, but in summary these procedures actually do the following:

    On the primary BACKUP LOG WITH NORECOVERY (or STANDBY)

    On the secondary RESTORE LOG WITH RECOVERY

    So to reverse the log shipping back to it's original state, do the opposite

    On the old secondary BACKUP LOG WITH NORECOVERY (or STANDBY)

    On the old primary RESTORE LOG WITH RECOVERY

  • Ian Scarlett (10/7/2009)


    If you use the system stored procedures sp_change_primary_role and sp_change_secondary_role to switch the roles in the first place, and then use them to reverse the roles when you have finished the DR exercise, then no full database restore is required.

    I should have been more complete in my description.

    By part of a DR exercise I meant the application team wants to test that the secondary is recoverable and that the latest data has been populated. It is not a full, re-point everything, DR exercise. The PROD instance would remain live.

    Is there a way to re-establish Log Shipping without creating a new base backup?

    EDIT: I am looking for the Oracle equivalent of opening a Data Guard secondary as Read/Write, doing the checks/tests, and then performing a flashback and continuing with the secondary acting as a DR-ready server.

    Regards;

    Greg

  • Aah, I didn't think it would be that simple.

    If you only want to verify that the data is there, then pause all the log shipping jobs and restore the last transaction log WITH STANDBY instead of NORECOVERY.

    Run your tests to verify that the data is in the secondary, then let log shipping carry on as normal.

  • what state is your secondary database in. loading or read only? If its in read only already you will be able to connect to it to read data and see if it is up to date, just updates will not be allowed.

    If its in loading state, go to the restore transaction log tab in the logshipping properties and click the standby mode radio button rather than the norecovery button. The next log restore will then put the db in read_only mode.

    If you want you can always put it back into norecovery afterwards.

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

  • Thank you Ian and George.

    As I understand it:

    - if we want a full DR test, with read/write capability, perform dual failovers

    - if we only want validation of Log Shipping, pause/standby/resume

    I'll run some tests and break the good news to the application team.

    Very much appreciated;

    Greg

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply