CHANGING LOG FILE SIZE ON DATAGUARD CONFIGURATION

.

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

11 comments:

Pascal said...

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?

Emre Baransel said...

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

Pascal said...

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.

Emre Baransel said...

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

Pascal said...

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)

Emre Baransel said...

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.

Emre Baransel said...

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

Pascal said...

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

Thanks mate

Pascal said...

Dear Emre;

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

Anonymous said...

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
?

Anonymous said...

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