Primary Database:
sqlplus sys/admin as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 18 02:33:02 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select status,instance_name,DB_UNIQUE_NAME,database_role from v$database,v$Instance;
STATUS
------------------------------------
INSTANCE_NAME
------------------------------------------------
DB_UNIQUE_NAME
--------------------------------------------------------------------------------
DATABASE_ROLE
------------------------------------------------
OPEN
DGTEST
DGTEST_P
PRIMARY
SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$log group by group#;
GROUP# Size in MB
---------- ----------
1 50
2 50
3 50
SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;
GROUP# Size in MB
---------- ----------
6 50
4 50
5 50
7 50
Standby Database:
$sqlplus '/ as sysdba'
SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 18 03:08:15 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select status,instance_name,DB_UNIQUE_NAME,database_role from v$database,v$Instance;
STATUS
------------------------------------
INSTANCE_NAME
------------------------------------------------
DB_UNIQUE_NAME
--------------------------------------------------------------------------------
DATABASE_ROLE
------------------------------------------------
OPEN
DGTEST
DGTEST_S
PHYSICAL STANDBY
SQL> select group#, sum(bytes/1024/1024)"Size in MB" from v$log group by group#;
GROUP# Size in MB
---------- ----------
1 50
2 50
3 50
SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;
GROUP# Size in MB
---------- ----------
6 50
4 50
5 50
7 50
SQL> show parameter standby_file_management
NAME TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
standby_file_management string
auto
SQL> alter system set standby_file_management=manual;
System altered.
SQL> show parameter standby_file_management
NAME TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
standby_file_management string
MANUAL
On the primary database:
Check the status of the Online Redo Logs and resize them by dropping the INACTIVE redo logs and re-creating them with the new size.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ------------------------------------------------
1 INACTIVE
2 CURRENT
3 INACTIVE
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile group 1 ('/u01/oracle/oradata/DGTEST/redo01.log') size 25M;
alter database add logfile group 1 ('/u01/oracle/oradata/DGTEST/redo01.log') size 25M
*
ERROR at line 1:
ORA-00301: error in adding log file '/u01/oracle/oradata/DGTEST/redo01.log' -
file cannot be created
ORA-27038: created file already exists
Additional information: 1
SQL> ! rm /u01/oracle/oradata/DGTEST/redo01.log
SQL> alter database add logfile group 1 ('/u01/oracle/oradata/DGTEST/redo01.log') size 25M;
Database altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ------------------------------------------------
1 UNUSED
2 ACTIVE
3 CURRENT
SQL> alter system checkpoint;
System altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ------------------------------------------------
1 UNUSED
2 INACTIVE
3 CURRENT
SQL> alter database drop logfile group 2;
Database altered.
SQL> !rm /u01/oracle/oradata/DGTEST/redo02.log
SQL> alter database add logfile group 2 ('/u01/oracle/oradata/DGTEST/redo02.log') size 25M;
Database altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ------------------------------------------------
1 UNUSED
2 UNUSED
3 CURRENT
SQL> alter system switch logfile;
System altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ------------------------------------------------
1 CURRENT
2 UNUSED
3 ACTIVE
SQL> alter system checkpoint;
System altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ------------------------------------------------
1 CURRENT
2 UNUSED
3 INACTIVE
SQL> alter database drop logfile group 3;
Database altered.
SQL> !rm /u01/oracle/oradata/DGTEST/redo03.log
SQL> alter database add logfile group 3 ('/u01/oracle/oradata/DGTEST/redo03.log') size 25M;
Database altered.
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$log group by group#;
GROUP# size in MB
---------- ----------
1 25
2 25
3 25
Moving on to the Standby Redo Logs on the Primary Database:
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;
GROUP# size in MB
---------- ----------
6 50
4 50
5 50
7 50
SQL> select group#,status from v$standby_log;
GROUP# STATUS
---------- ------------------------------
4 UNASSIGNED
5 UNASSIGNED
6 UNASSIGNED
7 UNASSIGNED
SQL> alter database drop standby logfile group 4;
Database altered.
SQL> alter database drop standby logfile group 5;
Database altered.
SQL> alter database drop standby logfile group 6;
Database altered.
SQL> alter database drop standby logfile group 7;
Database altered.
SQL> alter database add standby logfile group 4 ('/u01/oracle/oradata/DGTEST/srl0.log') size 25M;
alter database add standby logfile group 5 ('/u01/oracle/oradata/DGTEST/srl1.log') size 25M;
Database altered.
SQL>
Database altered.
SQL> alter database add standby logfile group 6 ('/u01/oracle/oradata/DGTEST/srl2.log') size 25M;
Database altered.
SQL> alter database add standby logfile group 7 ('/u01/oracle/oradata/DGTEST/srl3.log') size 25M;
Database altered.
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;
GROUP# size in MB
---------- ----------
6 25
4 25
5 25
7 25
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ------------------------------------------------
1 CLEARING_CURRENT
2 CLEARING
3 CLEARING
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ------------------------------------------------
1 CLEARING_CURRENT
2 CLEARING
3 CLEARING
SQL> alter database clear logfile group 2;
Database altered.
SQL> ^[[A select group#,status from v$log;
GROUP# STATUS
---------- ------------------------------------------------
1 CLEARING_CURRENT
2 UNUSED
3 CLEARING
SQL> alter database drop logfile group 2;
Database altered.
SQL> !rm /u01/oracle/oradata/DGTEST/redo02.log
SQL> alter database add logfile group 2 ('/u01/oracle/oradata/DGTEST/redo02.log') size 25M;
Database altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ------------------------------------------------
1 CLEARING_CURRENT
2 UNUSED
3 CLEARING
SQL> alter database clear logfile group 3;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> !rm /u01/oracle/oradata/DGTEST/redo03.log
SQL> alter database add logfile group 3 ('/u01/oracle/oradata/DGTEST/redo03.log') size 25M;
Database altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ------------------------------------------------
1 CLEARING
2 CLEARING_CURRENT
3 UNUSED
SQL> alter database clear logfile group 1;
Database altered.
SQL> alter database drop logfile group 1;
Database altered.
SQL> !rm /u01/oracle/oradata/DGTEST/redo01.log
SQL> alter database add logfile group 1 ('/u01/oracle/oradata/DGTEST/redo01.log') size 25M;
Database altered.
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$log group by group#;
GROUP# size in MB
---------- ----------
1 25
2 25
3 25
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;
GROUP# size in MB
---------- ----------
6 50
4 50
5 50
7 50
SQL> select group#,status from v$standby_log;
GROUP# STATUS
---------- ------------------------------
4 ACTIVE
5 UNASSIGNED
6 UNASSIGNED
7 UNASSIGNED
SQL> alter database drop standby logfile group 5;
Database altered.
SQL> alter database add standby logfile group 5 ('/u01/oracle/oradata/DGTEST/srl1.log') size 25M;
Database altered.
SQL> alter database drop standby logfile group 6;
Database altered.
SQL> alter database drop standby logfile group 7;
Database altered.
SQL> alter database add standby logfile group 6 ('/u01/oracle/oradata/DGTEST/srl2.log') size 25M;
Database altered.
SQL> alter database add standby logfile group 7 ('/u01/oracle/oradata/DGTEST/srl3.log') size 25M;
Database altered.
SQL> alter system checkpoint;
alter system checkpoint
*
ERROR at line 1:
ORA-16000: database open for read-only access
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 4;
Database altered.
SQL> select group#,status from v$standby_log;
GROUP# STATUS
---------- ------------------------------
4 UNASSIGNED
5 UNASSIGNED
6 UNASSIGNED
7 UNASSIGNED
SQL> select group#,status from v$standby_log;
GROUP# STATUS
---------- ------------------------------
4 UNASSIGNED
5 UNASSIGNED
6 UNASSIGNED
7 UNASSIGNED
SQL> alter database drop standby logfile group 4;
Database altered.
SQL> alter database add standby logfile group 4 ('/u01/oracle/oradata/DGTEST/srl0.log') size 25M;
Database altered.
SQL> select group#,status from v$standby_log;
GROUP# STATUS
---------- ------------------------------
4 UNASSIGNED
5 UNASSIGNED
6 UNASSIGNED
7 UNASSIGNED
SQL> alter system set standby_file_management=auto;
System altered.
SQL> alter database recover managed standby database disconnect from session using current logfile;
or ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE;
Database altered.
SQL> select group#,status from v$standby_log;
GROUP# STATUS
---------- ------------------------------
4 ACTIVE
5 UNASSIGNED
6 UNASSIGNED
7 UNASSIGNED
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------------------------- ------------------------------------ ----------
ARCH CLOSING 17149
ARCH CLOSING 17150
ARCH OPENING 14989
ARCH CLOSING 17148
RFS IDLE 0
RFS IDLE 0
RFS IDLE 17151
RFS IDLE 0
MRP0 APPLYING_LOG 17151
9 rows selected.
Primary:
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
17150
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
17152
Standby:
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
17149
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
17152
On the 2nd standby database:
$sqlplus '/ as sysdba'
SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 18 03:08:18 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select status,instance_name,DB_UNIQUE_NAME,database_role from v$database,v$Instance;
STATUS
------------------------------------
INSTANCE_NAME
------------------------------------------------
DB_UNIQUE_NAME
--------------------------------------------------------------------------------
DATABASE_ROLE
------------------------------------------------
OPEN
DGTEST
DGTEST_S_US
PHYSICAL STANDBY
SQL>
SQL> select group#, sum(bytes/1024/1024)"Size in MB" from v$log group by group#;
GROUP# Size in MB
---------- ----------
1 50
2 50
3 50
SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;
GROUP# Size in MB
---------- ----------
6 50
4 50
5 50
7 50
SQL> show parameter standby_file_management
NAME TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
standby_file_management string
AUTO
SQL> alter system set standby_file_management=manual;
System altered.
SQL> show parameter standby_file_management
NAME TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
standby_file_management string
MANUAL
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ------------------------------------------------
1 CLEARING_CURRENT
2 CLEARING
3 CLEARING
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ------------------------------------------------
1 CLEARING_CURRENT
2 CLEARING
3 CLEARING
SQL> alter database clear logfile group 2;
Database altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ------------------------------------------------
1 CLEARING_CURRENT
2 UNUSED
3 CLEARING
SQL> alter database drop logfile group 2;
Database altered.
SQL> !rm /u01/oracle/oradata/DGTEST/redo02.log
SQL> alter database add logfile group 2 ('/u01/oracle/oradata/DGTEST/redo02.log') size 25M;
Database altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ------------------------------------------------
1 CLEARING_CURRENT
2 UNUSED
3 CLEARING
SQL> alter database clear logfile group 3;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> !rm /u01/oracle/oradata/DGTEST/redo03.log
SQL>
SQL> alter database add logfile group 3 ('/u01/oracle/oradata/DGTEST/redo03.log') size 25M;
Database altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ------------------------------------------------
1 CLEARING
2 CLEARING_CURRENT
3 UNUSED
SQL> alter database clear logfile group 1;
Database altered.
SQL> alter database drop logfile group 1;
Database altered.
SQL> !rm /u01/oracle/oradata/DGTEST/redo01.log
SQL> alter database add logfile group 1 ('/u01/oracle/oradata/DGTEST/redo01.log') size 25M;
Database altered.
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$log group by group#;
GROUP# size in MB
---------- ----------
1 25
2 25
3 25
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;
GROUP# size in MB
---------- ----------
6 50
4 50
5 50
7 50
SQL> select group#,status from v$standby_log;
GROUP# STATUS
---------- ------------------------------
4 UNASSIGNED
5 UNASSIGNED
6 UNASSIGNED
7 UNASSIGNED
SQL> alter database drop standby logfile group 4;
Database altered.
SQL> alter database drop standby logfile group 5;
Database altered.
SQL> alter database drop standby logfile group 6;
Database altered.
SQL> alter database drop standby logfile group 7;
Database altered.
SQL> alter database add standby logfile group 4 ('/u01/oracle/oradata/DGTEST/srl0.log') size 25M;
Database altered.
SQL> alter database add standby logfile group 5 ('/u01/oracle/oradata/DGTEST/srl1.log') size 25M;
Database altered.
SQL> alter database add standby logfile group 6 ('/u01/oracle/oradata/DGTEST/srl2.log') size 25M;
Database altered.
SQL> alter database add standby logfile group 7 ('/u01/oracle/oradata/DGTEST/srl3.log') size 25M;
Database altered.
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;
GROUP# size in MB
---------- ----------
6 25
4 25
5 25
7 25
SQL> select group#,status from v$standby_log;
GROUP# STATUS
---------- ------------------------------
4 UNASSIGNED
5 UNASSIGNED
6 UNASSIGNED
7 UNASSIGNED
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$log group by group#;
GROUP# size in MB
---------- ----------
1 25
2 25
3 25
SQL>
SQL> alter system set standby_file_management=auto;
System altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------------------------- ------------------------------------ ----------
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS IDLE 0
MRP0 WAIT_FOR_LOG 17151
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
PROCESS STATUS SEQUENCE#
--------------------------- ------------------------------------ ----------
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
PROCESS STATUS SEQUENCE#
--------------------------- ------------------------------------ ----------
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
PROCESS STATUS SEQUENCE#
--------------------------- ------------------------------------ ----------
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
39 rows selected.
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
17150
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
17152
沒有留言:
張貼留言