AFAIK, you're right that AWS RDS Aurora (a MySQL 5.6 fork) does not support automatic or transparent read/write splitting:
In order to do that in a way that's completely transparent to the application, you would need an intermediate proxy. Your application would then always connect to ...
I just wanted to point out that AWS has updated and now have a cluster Read endpoint that does load balancing in case anyone runs into this from Google.
Turns out it can be done if you use CONTINUE_AFTER_ERROR
RESTORE DATABASE foo WITH RECOVERY, CONTINUE_AFTER_ERROR
I did still get a warning when I tried it but then did a CHECKDB and received no errors.
RESTORE WITH CONTINUE_AFTER_ERROR was successful but some damage was encountered. Inconsistencies in the database are possible.
RESTORE DATABASE ...
I think what you want is real time apply, but for that, you'll have to create standby redo logs on your standby database first. As a side note, to make role transitions easier, it is considered best practice to create standby redo logs on your primary database as well:
The standby redo logs should be the same size as your redo logs and you need at least 1 ...
The blog post is using the terms incorrectly.
Those are two different things.
"Hot Standby" is the state the replica is in and means it is available for read-only queries. A replica can also "just" be a standby, but then it does not allow queries, it just sits there and waits to take over.
A replica needs to have exactly the same data as the master and ...
Short version: no.
Active Data Guard is an optional feature that allows you to open the standby database in read-only mode while applying logs from the primary. If you need the standby database only in case of disaster, then you don't need to buy this option.
Yes! we can do that.
Which can be observed from the following demo.
I have two hosts Server1(192.168.30.132) and Server2(192.168.30.142) and ORCl database is on Server1.
I skipped some of the steps, creating standby redo logs for example, and configuration to make the answer short.
SQL> alter database archivelog;
If I change the database to online status and created a mapped user to
the database, would that be okay to bring it back to a standby again
and continue the restoring process without hassle?
No, that will break log shipping.
Unfortunately, until SQL Server 2014 you don't have much for other options.
Grant access via stored procedures or views in ...
I have a standby database that is being restored in standby mode using
log-shipped transaction files downloaded from a vendor's sFTP site on
a daily basis.
I am required to give a read-only access on this database to someone
else, outside our domain.
With log shipping and the DB staying in standby mode, it stands there waiting for ...
Your users from db on server A will be moved to the database on server B with the backup. Then all you need is to recreate the logins with the same sid. You do this using WITH SID option:
CREATE LOGIN My_login WITH SID = 0x14585E90117152449347750164BA00A7
the sid obviously is your user sid that you can find in sys.database_principals:
select name, sid
The physical standby database must be physically the same as the primary database and this means the versions must be identical.
A logical standby database can be any version, depending on how it is implemented. With 3rd party tools you can go from v10 to v12 and back. The restrictions here are on the data types that are to be replicated.
If this is for an ...
You do realize the standby is a "physical" copy of the primary (read-only in this case)? If you change the password in the primary, the changes will be propagated to the standby also. You can not change such things in a physical standby independently.
Ok, so I had some time to waste, and this is just for the "fun" or "interesting" factor. It's nowhere near that I would use in a real scenario, I have played with it in my lab environment on x86-64 Linux platform, with a few 10g, 11g and 12c databases. At least you can do this even if the database is shut down.
When you do a controlfile dump with:
No. This is not possible. There are various options that might help you recover from this situation or even prevent it from happening in the first place though.
Think about it like this: how do you determine when the "data gets corrupted"?
Imagine that you could decode and scan through the archived redo logs, before they get shipped to the standby. How ...
Not possible, redo logs get transferred unprocessed. If you want to transfer changes related to just some specific objects or schemas, Streams (deprecated, but free) or GoldenGate (requires extra license) does that.
Another option for reducing network traffic is to configure redo transport compression (requires the Advanced Compression option), but this ...
According to the output of the command ps -ef|grep pmon which has shows that the instance name of the standby database is teststan.
[oravis@standbysrv ~]$ ps -ef|grep pmon oravis 2749 1 0 16:02 ? 00:00:00 ora_pmon_teststan oravis 2825 2697 0 16:03 pts/0 00:00:00 grep pmon
But in the description of listener on standby database, SID_NAME is defined as 'test'....
Just disable sending redo to the standby on the primary (replace with actual dest number):
SQL> alter system set log_archive_dest_state_2=defer scope=memory;
To re-enable (replace with actual database name):
DGMGRL> enable database orcl_sb
You should create a login on your primary, create a corresponding user in log shipped database and wait until this user "flows" on your secondary, at this point you can drop the login on primary.
If you use windows authentication, you've done. If you use SQL Server authentication, memorize the sid of the newly created login before you drop it, on your ...
You should be able to code up a series of restore logs (with standby) and specify the STOPAT with 10 minute increments
--Full database restore with standby
RESTORE DATABASE [StandbyTest]
FROM DISK = N'c:\StandbyTest\StandbyTest.bak'
WITH FILE = 1
,STANDBY = N'C:\StandbyTest\ROLLBACK_UNDO_StandbyTest.BAK'
--Pretend this was ...
Does archived redo logs required for logical standby or not?
Yes, it does.
The archived redo logs (archivelogs, commonly) contain SQL statements to apply the changes being made to the database, as well as SQL statements to undo the work if a rollback occurs later on the ...
If you want delay apply on Cascaded Standby you need to set the DELAY of LOG_ARCHIVE_DEST_n on Primary.
The DELAY value that a cascaded standby uses is the value that was set for the LOG_ARCHIVE_DEST_n parameter on the primary that shipped the redo to the cascading standby.
Thanks to Amacvar on reddit for pointing me to the answer:
max_standby_archive_delay and max_standby_streaming_delay have to be set for longer period than the longest query you want to run on the hot-standy.
It is important that the administrator select appropriate settings for ...
Please do restore verifyonly of your log backups just to make sure they are restorable. Your issue is similar to the one defined in Log Shipping - RESTORE WITH STANDBY - on SQL Server 2012 keeps breaking. Try those suggestions as well.
Few more things I would like to suggest is, if your log backups are valid try doing restores with NORECOVERY option to ...
From what you are describing about what Actifio needs you should be able to put the secondary database in readonly mode after you restore the latest log file. See Kendra Little's post on log shipping secondaries in read only mode, she goes over some things that you need to account for. One of the things she mentions you'll need to account for putting the ...
The same can be specified in the standby database.
RMAN Configurations at a Standby Where Backups Are Not Performed
The following RMAN configurations are recommended at a standby
database where backups are not done:
Connect RMAN to the standby database as target, and to the recovery catalog.
Enable automatic deletion of archived logs once ...
There's a few ways to determine if MRP is running. One is to query open_mode from v$database. If MRP is running, you will get:
READ ONLY WITH APPLY
If not, you will get:
You can run the following query which will show what MRP processes are running:
Look for the process MRPn
At first glance, in MOS there's a related known bug with that message in 184.108.40.206 on Solaris (Bug 10331312) and 220.127.116.11 on Linux x86-64 (Bug 14016842 , this one refilled for review) both referenced by Doc Id 1308804.1.
However it doesn't provides any source of cause. As workaround they recommend to disable the log_archive_trace parameter to = 0 (default), ...