-- On Primary and standby
SQL> shutdown immediate
SQL> startup mount
-- On Standby
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
-- On primary and standby
ALTER DATABASE ADD STANDBY LOGFILE GROUP 14 ('/oracle/oradata/ORCL/stbyredo14a.log', '/oracle/oradata/ORCL/stbyredo14b.log') SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 15 ('/oracle/oradata/ORCL/stbyredo15a.log', '/oracle/oradata/ORCL/stbyredo15b.log') SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 16 ('/oracle/oradata/ORCL/stbyredo16a.log', '/oracle/oradata/ORCL/stbyredo16b.log') SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 17 ('/oracle/oradata/ORCL/stbyredo17a.log', '/oracle/oradata/ORCL/stbyredo17b.log') SIZE 512M;
-- On Primary and standby
ALTER DATABASE DROP STANDBY LOGFILE GROUP 4;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 5;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 6;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 7;
-- On Standby
SQL> shutdown immediate
-- On Primary
ALTER DATABASE ADD LOGFILE GROUP 11 ('/oracle/oradata/ORCL/stbyredo11a.log', '/oracle/oradata/ORCL/stbyredo11b.log') SIZE 512M;
ALTER DATABASE ADD LOGFILE GROUP 12 ('/oracle/oradata/ORCL/stbyredo12a.log', '/oracle/oradata/ORCL/stbyredo12b.log') SIZE 512M;
ALTER DATABASE ADD LOGFILE GROUP 13 ('/oracle/oradata/ORCL/stbyredo13a.log', '/oracle/oradata/ORCL/stbyredo13b.log') SIZE 512M;
SQL> alter database open;
ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 3;
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/orclstby_cntrl01.ctl';
- Copy STANDBY CONTROLFILE to standby site
#scp /tmp/orclstby_cntrl01.ctl server2:/tmp/orclstby_cntrl01.ctl
-- On Standby
SQL> startup mount
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
- Restart DBs on Primary and Standby sites
Hi Emre
Suppose I have standby redologs in standy database, after some transaction in production, production crashed,Now standby redolog is not full (I have half data in it)
Do I need to force logswitch to apply the data in standby or does oracle apply it automatically prior to opening the database?
Hi Pascal:
Refer to "Oracle Data Guard Concepts and Administration 10g Release 2"
It says "Redo data can be applied either from archived redo log files, or, if real-time apply is enabled, directly from the standby redo log files as they are being filled, without requiring the redo data to be archived first at the standby database."
This means if you use real time apply you don't need to force a logswitch or anyother operation to apply the changes in the standby redolog files.
To determine if real time apply is enabled, the recovery_mode column in the v$archive_dest_status view can be queried. It will display MANAGED REAL TIME APPLY when real time apply is enabled.
Using Real-Time Apply to Apply Redo Data Immediately:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/log_apply.htm#i1023371
Thanks Emre.
You have been really helpfull.
However I still have some doubts.
Does it mean that, if I use real time apply, every transaction is applied to standby immediately?
Is the below assumption correct?
Suppose,an insert occured in production and then this statement will be transferred to standby redologs and applied to standby db immediately without waiting for logswith.
Your welcome!
Actually i haven't tested, but as i see it from real-time apply definitions, your assumption is correct.
Second but:) this doesn't mean you won't lose data in a case of failover. There may be a gap because of intense updates, deletes etc. or network issues. So your standby database may be behind your primary.
If you want to be sure that you won't lose data in a case of failover, you have to chose Maximum Protection Mode. Take a look at data protection modes at: http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/log_transport.htm#i1179318
Hope this helps!!
Regards
Thanks Emre;
I have one last question :)
Assume I have dataguard configuration.
When I add a standby redolog to production, Does oracle apply the same statement(alter database create standby redolog..) to standby as well???
(Since this ddl is recorded in redolog file and eventually applied to standby)
http://stanford.edu/dept/itss/docs/oracle/10g/server.101/b10823/manage_ps.htm#1010567
This link "Adding or Dropping Online Redo Log Files" would help you clarify the concept. The value of STANDBY_FILE_MANAGEMENT initialization parameter is important here.
By the way, it's getting harder :))
I guess I need to set standby_file_management to manuel prior to adding logfile.
Thanks mate
Dear Emre;
When I perform failover, Do I need to copy the tnsnames from the old primary database?
Hello
Assume,
db_create_file_dest=+DATA
db_create_online_log_dest_1=+LOGS
db_create_online_log_dest_2=+FRA
If I issue, alter database add logfile clause,
Does it create a new logfile in the +DATA diskgroup, logfile in the +LOGS disk group, and a logfile in the +FRA disk group.
or
a new logfile in the +LOGS disk group, and a logfile in the +FRA disk group
?
I have no down time so this method will not cut it.
Hey Really Thanks for sharing the best information regarding orale,hope you will write more great blogs.
Oracle Fusion HCM Online Training
Really very helpful article , Thank you for sharing we have learned so much information from your blog
Big Data and Hadoop Online Training
Big Data Hadoop Training
Hyderabad
nice and interesting blog with us. I have seen that all will say the same thing repeatedly. But in your blog, I had a chance to get some useful and unique information.
Spark and Scala Online Training
Spark Scala Training
Hyderabad
Very good article, very nice information, thanks for sharing.
Regards
Tirupati darshan online booking
Tirupati darshan online booking
Tirupati package from Bangalore
Best Tirupati package from Bangalore
Tirupati darshan package from Bangalore
Tirupati tour package from Bangalore
Promote ABHI offer affordable Online Reputation Management(ORM) services are about this only. A top online reputation management company will not wait for any detrimental thing for your reputation to happen they will proactively check it to control that such things never occur.
thanks for this post !! Online reputation management
I really liked your blog article. Great.
office 365 online training
office 365 training
good blog. your blog is really informative and helpful for me
Oracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Oracle Integration Cloud Online Training
Oracle Fusion Technical Online Training