Shulou(Shulou.com)05/31 Report--

This article introduces 11g RAC and increases the size and number of log groups. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

Prepare the database to close the log application:

SQL > select status from v$instance

STATUS

-

MOUNTED

SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

Database altered.

Log error:

ORA-16037: user requested cancel of managed recovery operation

Managed Standby Recovery not using Real Time Apply

Recovery interrupted!

Recovered data files to a consistent state at change 2463385381

Thu Oct 12 23:11:02 2017

MRP0: Background Media Recovery process shutdown (SCPRDDG)

Managed Standby Recovery Canceled (SCPRDDG)

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

Main library:

SQL > select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log

GROUP# THREAD# MEMBERS STATUS SIZE_M

--

1 1 1 INACTIVE 50

2 1 1 CURRENT 50

3 2 1 INACTIVE 50

4 2 1 CURRENT 50

SQL > select group#,member from v$logfile order by 1

GROUP#

-

MEMBER

one

+ DATA/scprd/onlinelog/group_1.262.929789943

two

+ DATA/scprd/onlinelog/group_2.261.929789943

three

+ DATA/scprd/onlinelog/group_3.258.929790053

GROUP#

-

MEMBER

four

+ DATA/scprd/onlinelog/group_4.257.929790053

five

+ DATA/scprd/onlinelog/standby05.log

six

+ DATA/scprd/onlinelog/standby06.log

GROUP#

-

MEMBER

seven

+ DATA/scprd/onlinelog/standby07.log

eight

+ DATA/scprd/onlinelog/standby08.log

nine

+ DATA/scprd/onlinelog/standby09.log

GROUP#

-

MEMBER

ten

+ DATA/scprd/onlinelog/standby10.log

10 rows selected.

SQL > alter database add logfile thread 1 group 11'+ DATA' size 250m

Database altered.

SQL > select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log

GROUP# THREAD# MEMBERS STATUS SIZE_M

--

1 1 1 INACTIVE 50

2 1 1 CURRENT 50

3 2 1 INACTIVE 50

4 2 1 CURRENT 50

11 11 UNUSED 250

SQL > alter database add logfile thread 2 group 15'+ DATA' size 250m

Database altered.

SQL > select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log

GROUP# THREAD# MEMBERS STATUS SIZE_M

--

1 1 1 INACTIVE 50

2 1 1 CURRENT 50

3 2 1 INACTIVE 50

4 2 1 CURRENT 50

11 11 UNUSED 250

12 1 1 UNUSED 250

13 1 1 UNUSED 250

14 1 1 UNUSED 250

15 2 1 UNUSED 250

9 rows selected.

SQL > select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log

GROUP# THREAD# MEMBERS STATUS SIZE_M

--

1 1 1 INACTIVE 50

2 1 1 CURRENT 50

3 2 1 INACTIVE 50

4 2 1 CURRENT 50

11 11 UNUSED 250

12 1 1 UNUSED 250

13 1 1 UNUSED 250

14 1 1 UNUSED 250

15 2 1 UNUSED 250

16 2 1 UNUSED 250

17 2 1 UNUSED 250

GROUP# THREAD# MEMBERS STATUS SIZE_M

--

18 2 1 UNUSED 250

Delete log group

Note: if the status of the log group to be deleted is current or active, change it to inactive

Alter system switch logfile

Alter system checkpoint

SQL > alter system switch logfile

System altered.

SQL > select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log

GROUP# THREAD# MEMBERS STATUS SIZE_M

--

1 1 1 INACTIVE 50

2 1 1 ACTIVE 50

3 2 1 INACTIVE 50

4 2 1 CURRENT 50

11 11 CURRENT 250

12 1 1 UNUSED 250

13 1 1 UNUSED 250

14 1 1 UNUSED 250

15 2 1 UNUSED 250

16 2 1 UNUSED 250

17 2 1 UNUSED 250

GROUP# THREAD# MEMBERS STATUS SIZE_M

--

18 2 1 UNUSED 250

SQL > alter database drop logfile group 2

Database altered.

SQL > alter database drop logfile group 3

Database altered.

SQL > select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log

GROUP# THREAD# MEMBERS STATUS SIZE_M

--

1 1 1 CURRENT 50

4 2 1 ACTIVE 50

11 11 ACTIVE 250

12 1 1 ACTIVE 250

13 1 1 ACTIVE 250

14 1 1 ACTIVE 250

15 2 1 CURRENT 250

16 2 1 UNUSED 250

17 2 1 UNUSED 250

18 2 1 UNUSED 250

Turn active into inactive

SQL > alter system checkpoint

System altered.

SQL > /

GROUP# THREAD# MEMBERS STATUS SIZE_M

--

4 2 1 INACTIVE 50

11 11 INACTIVE 250

12 1 1 CURRENT 250

13 1 1 INACTIVE 250

14 1 1 INACTIVE 250

15 2 1 CURRENT 250

16 2 1 INACTIVE 250

17 2 1 INACTIVE 250

18 2 1 INACTIVE 250

9 rows selected.

SQL > alter database drop logfile group 4

Database altered.

Delete standby log

SQL > alter database drop standby logfile group 5

Database altered.

SQL > alter database drop standby logfile group 7

Database altered.

SQL > alter database drop standby logfile group 8

Database altered.

SQL > alter database drop standby logfile group 9

Database altered.

SQL > alter database drop standby logfile group 10

Database altered.

SQL > alter database add standby logfile thread 1 group 1'+ DATA/scprd/onlinelog/standby01.log' size 250m

Database altered.

SQL > alter database add standby logfile thread 1 group 2'+ DATA/scprd/onlinelog/standby02.log' size 250m

Database altered.

SQL > alter database add standby logfile thread 1 group 3'+ DATA/scprd/onlinelog/standby03.log' size 250m

Database altered.

SQL > alter database add standby logfile thread 1 group 4'+ DATA/scprd/onlinelog/standby04.log' size 250m

Database altered.

SQL > alter database add standby logfile thread 2 group 10'+ DATA/scprd/onlinelog/standby10.log' size 250m

Alter database add standby logfile thread 2 group 10'+ DATA/scprd/onlinelog/standby10.log' size 250m

*

ERROR at line 1:

ORA-00301: error in adding log file'+ DATA/scprd/onlinelog/standby10.log'-

File cannot be created

ORA-17502: ksfdcre:4 Failed to create file + DATA/scprd/onlinelog/standby10.log

ORA-15005: name "scprd/onlinelog/standby10.log" is already used by an existing

Alias

SQL > alter database add standby logfile thread 2 group 10'+ DATA/scprd/onlinelog/standby10.log' size 250m reuse

Database altered.

Add a completion log group.

Prepare the library for execution:

SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT

Database altered.

Start synchronization:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT

Attempt to start background Managed Standby Recovery process (SCPRDDG)

Fri Oct 13 00:10:53 2017

MRP0 started with pid=48, OS id=30390

MRP0: Background Managed Standby Recovery process started (SCPRDDG)

Started logmerger process

Fri Oct 13 00:10:58 2017

Managed Standby Recovery not using Real Time Apply

Parallel Media Recovery started with 4 slaves

Waiting for all non-current ORLs to be archived...

All non-current ORLs have been archived.

Media Recovery Log / oracle/arch/1_45614_930582072.dbf

Media Recovery Log / oracle/arch/2_46396_930582072.dbf

Media Recovery Log / oracle/arch/1_45615_930582072.dbf

Media Recovery Log / oracle/arch/1_45616_930582072.dbf

Media Recovery Log / oracle/arch/1_45617_930582072.dbf

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT

Media Recovery Log / oracle/arch/2_46397_930582072.dbf

Media Recovery Log / oracle/arch/1_45618_930582072.dbf

Media Recovery Log / oracle/arch/1_45619_930582072.dbf

Media Recovery Log / oracle/arch/1_45620_930582072.dbf

Media Recovery Log / oracle/arch/2_46398_930582072.dbf

Media Recovery Log / oracle/arch/1_45621_930582072.dbf

Media Recovery Log / oracle/arch/1_45622_930582072.dbf

Media Recovery Log / oracle/arch/1_45623_930582072.dbf

Media Recovery Log / oracle/arch/2_46399_930582072.dbf

Media Recovery Log / oracle/arch/1_45624_930582072.dbf

SQL > show parameter standby

NAME TYPE VALUE

-

Standby_archive_dest string? / dbs/arch

Standby_file_management string AUTO

SQL > show parameter convert

NAME TYPE VALUE

-

Db_file_name_convert string + DATA/scprd/datafile, / oracle/

Oradata

Log_file_name_convert string + DATA/scprd/onlinelog, / oracle

/ oradata

Modify the synchronization parameters of DG:

SQL > alter system set standby_file_management = manual

System altered.

SQL > show parameter standby

NAME TYPE VALUE

-

Standby_archive_dest string? / dbs/arch

Standby_file_management string MANUAL

SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

Database altered.

Manually add log groups

Clean up logfile

SQL > alter database clear logfile group 2

Database altered.

SQL > alter database clear logfile group 3

Database altered.

SQL > alter database clear logfile group 4

Database altered.

View status:

SQL > select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log

GROUP# THREAD# MEMBERS STATUS SIZE_M

--

1 1 1 UNUSED 50

2 1 1 UNUSED 50

3 2 1 UNUSED 50

4 2 1 UNUSED 50

Add a log group:

SQL > alter database add logfile thread 1 group 11'/ oracle/oradata/group_11.399.957222953' size 250m

Database altered.

SQL > alter database add logfile thread 1 group 12'/ oracle/oradata/group_12.400.957223041' size 250m

Database altered.

SQL > alter database add logfile thread 1 group 13'/ oracle/oradata/group_13.401.957223051' size 250m

Database altered.

SQL > alter database add logfile thread 1 group 14'/ oracle/oradata/group_14.402.957223059' size 250m

Database altered.

SQL > alter database add logfile thread 2 group 15'/ oracle/oradata/group_15.403.957223209' size 250m

Database altered.

SQL > alter database add logfile thread 2 group 16'/ oracle/oradata/group_16.404.957223315' size 250m

Database altered.

SQL > alter database add logfile thread 2 group 17'/ oracle/oradata/group_17.405.957223323' size 250m

Database altered.

SQL > alter database add logfile thread 2 group 18'/ oracle/oradata/group_18.406.957223331' size 250m

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 drop standby logfile group 8

Database altered.

SQL > select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log

GROUP# THREAD# MEMBERS STATUS SIZE_M

--

1 1 1 UNUSED 50

2 1 1 UNUSED 50

12 1 1 CURRENT 250

14 1 1 UNUSED 250

13 1 1 UNUSED 250

11 11 UNUSED 250

16 2 1 UNUSED 250

17 2 1 UNUSED 250

18 2 1 UNUSED 250

15 2 1 CURRENT 250

4 2 1 UNUSED 50

GROUP# THREAD# MEMBERS STATUS SIZE_M

--

3 2 1 UNUSED 50

12 rows selected.

SQL > alter database drop logfile group 1

Database altered.

SQL > alter database drop logfile group 2

Database altered.

SQL > alter database drop logfile group 3

Database altered.

SQL > alter database drop logfile group 4

Database altered.

SQL > select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log

GROUP# THREAD# MEMBERS STATUS SIZE_M

--

11 11 UNUSED 250

12 1 1 CURRENT 250

13 1 1 UNUSED 250

14 1 1 UNUSED 250

15 2 1 CURRENT 250

16 2 1 UNUSED 250

17 2 1 UNUSED 250

18 2 1 UNUSED 250

8 rows selected.

SQL > alter database drop standby logfile group 9

Database altered.

SQL > alter database drop standby logfile group 10

Database altered.

Add a standby log group:

SQL > alter database add standby logfile thread 1 group 1'/ oracle/oradata/standby01.log' size 250m

Database altered.

SQL > alter database add standby logfile thread 1 group 2'/ oracle/oradata/standby02.log' size 250m

Database altered.

SQL > alter database add standby logfile thread 1 group 3'/ oracle/oradata/standby03.log' size 250m

Database altered.

SQL > alter database add standby logfile thread 1 group 4'/ oracle/oradata/standby04.log' size 250m

Database altered.

SQL > alter database add standby logfile thread 1 group 5'/ oracle/oradata/standby05.log' size 250m

Alter database add standby logfile thread 1 group 5'/ oracle/oradata/standby05.log' size 250m

*

ERROR at line 1:

ORA-00301: error in adding log file'/ oracle/oradata/standby05.log'-file

Cannot be created

ORA-27038: created file already exists

Additional information: 1

SQL > alter database add standby logfile thread 1 group 5'/ oracle/oradata/standby05.log' size 250m reuse

Database altered.

SQL > alter database add standby logfile thread 2 group 6'/ oracle/oradata/standby06.log' size 250m

Alter database add standby logfile thread 2 group 6'/ oracle/oradata/standby06.log' size 250m

*

ERROR at line 1:

ORA-00301: error in adding log file'/ oracle/oradata/standby06.log'-file

Cannot be created

ORA-27038: created file already exists

Additional information: 1

SQL > alter database add standby logfile thread 2 group 6'/ oracle/oradata/standby06.log' size 250m reuse

Database altered.

SQL > alter database add standby logfile thread 2 group 7'/ oracle/oradata/standby07.log' size 250m

Alter database add standby logfile thread 2 group 7'/ oracle/oradata/standby07.log' size 250m

*

ERROR at line 1:

ORA-00301: error in adding log file'/ oracle/oradata/standby07.log'-file

Cannot be created

ORA-27038: created file already exists

Additional information: 1

SQL > alter database add standby logfile thread 2 group 7'/ oracle/oradata/standby07.log' size 250m reuse

Database altered.

SQL > alter database add standby logfile thread 2 group 8'/ oracle/oradata/standby08.log' size 250m reuse

Database altered.

SQL > alter database add standby logfile thread 2 group 9'/ oracle/oradata/standby09.log' size 250m reuse

Database altered.

SQL > alter database add standby logfile thread 2 group 10'/ oracle/oradata/standby10.log' size 250m reuse

Database altered.

Enable synchronization for slave database:

SQL > alter system set standby_file_management = AUTO

System altered.

SQL > show parameter standby

NAME TYPE VALUE

-

Standby_archive_dest string? / dbs/arch

Standby_file_management string AUTO

SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT

Database altered.

Use the log to see if the master and standby are synchronized normally.

SQL > alter system switch logfile

System altered.

So much for sharing about 11g RAC and increasing the size and number of log groups. I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.