-- 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

20 Responses so far.

  1. Pascal says:

    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?

  2. 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

  3. Pascal says:

    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.

  4. 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

  5. Pascal says:

    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)

  6. 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.

  7. By the way, it's getting harder :))

  8. Pascal says:

    I guess I need to set standby_file_management to manuel prior to adding logfile.

    Thanks mate

  9. Pascal says:

    Dear Emre;

    When I perform failover, Do I need to copy the tnsnames from the old primary database?

  10. Anonymous says:

    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
    ?

  11. Anonymous says:

    I have no down time so this method will not cut it.

  12. Vivek says:

    Hey Really Thanks for sharing the best information regarding orale,hope you will write more great blogs.

    Oracle Fusion HCM Online Training

  13. 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

  14. 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

  15. Anonymous says:

    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.

Powered by Blogger.

Page Views

- Copyright © Emre Baransel - Oracle Blog -Metrominimalist- Powered by Blogger - Designed by Johanes Djogan -