October 6, 2009 at 1:55 pm
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
October 6, 2009 at 2:45 pm
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'
October 7, 2009 at 1:57 am
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
October 7, 2009 at 6:23 am
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
October 7, 2009 at 6:57 am
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.
October 7, 2009 at 7:00 am
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.
---------------------------------------------------------------------
October 7, 2009 at 7:16 am
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