April 20, 2023 at 8:39 pm
I would like to ask someone working on both Oracle and SQL server.
For oracle, we have a script on a standby server that restores the archive log every 5 hrs from a production server ServerA to a report readonly server ServerB
I see in the script that runs from synchronizing job which is scheduled in task scheduler on the report server, our previous DBA set up having code like below .
It looks like the bolded lines below is what it really does the work. As I understand it recover from production server's archive logs to synchronizing the report server from prod server.
Can we do something in SQL server? I don't see it will work for SQL server.
For SQL server, I have to setup transaction log shipping that does similar work, that is setup the log shipping, copy backups to reports server, and then do restore, but cannot just point to prod transaction log files to directly to do the synchronization.
Is my understanding correct?
Thanks,
....
(
echo Shutdown immediate;
echo Shutdown abort;
) | sqlplus -s / as sysdba >> F:\oradata\PSPRODDB\data_pump_dir\RecoverStandbyDB.log
(
echo startup nomount;
echo alter database mount standby database;
) | sqlplus -s / as sysdba >> F:\oradata\PSPRODDB\data_pump_dir\RecoverStandbyDB.log
rem set ORACLE_SID=%1
set ORACLE_SID=PSPRODDB
set PATH=%ORACLE_HOME%\bin;%PATH%
(
echo set escape on
echo set heading off
echo alter system set log_archive_dest_1='location=/\/\MyProdServerB/\archivelog/\';
echo recover standby database until cancel;
echo auto
echo alter database open read only;
) | sqlplus -s / as sysdba >> F:\oradata\PSPRODDB\data_pump_dir\RecoverStandbyDB.log
@echo off
for /f "tokens=2 delims==" %%a in ('wmic OS Get localdatetime /value') do set "dt=%%a"
set "YY=%dt:~2,2%" & set "YYYY=%dt:~0,4%" & set "MM=%dt:~4,2%" & set "DD=%dt:~6,2%"
set "HH=%dt:~8,2%" & set "Min=%dt:~10,2%" & set "Sec=%dt:~12,2%"
set "datestamp=%YYYY%%MM%%DD%" & set "timestamp=%HH%%Min%%Sec%"
set "fullstamp=%YYYY%-%MM%-%DD%_%HH%:%Min%:%Sec%"
echo datestamp: "%datestamp%"
echo timestamp: "%timestamp%"
echo End of standby recovery...at: "%fullstamp%" >> F:\oradata\PSPRODDB\data_pump_dir\RecoverStandbyDB.log
GOTO EOF
April 21, 2023 at 12:44 pm
What are you trying to accomplish? If you want a read-only secondary server for reporting, then there are multiple technologies in SQL that you can leverage. Availability Groups and replication can be real-time, or near real time. There are also additional benefits that an availably group provides. There is a good set of articles on AG's and Replication in the Stairway Series on this site.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 21, 2023 at 3:41 pm
Thank you. I know there is AG options for replication or high availability.
My purpose for this post is to learn how oracle handles this and what is difference between this and SQL server log shipping?
I think someone who is both Oracle DBA and SQL server DBA may help on this topic.
Thanks,
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply