Sunday, December 9, 2012

Adding new redo logs to Oracle RAC on ASM



SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 ('+HOMEDBDATA/HOMEdb/onlinelog/group_5_log1.log','+HOMEDBFLASH/HOMEdb
nlinelog/group_5_log1.log') SIZE 500m;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 6 ('+HOMEDBDATA/HOMEdb/onlinelog/group_6_log1.log','+HOMEDBFLASH/HOMEdb
nlinelog/group_6_log1.log') SIZE 500m;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 7 ('+HOMEDBDATA/HOMEdb/onlinelog/group_7_log1.log','+HOMEDBFLASH/HOMEdb
nlinelog/group_7_log1.log') SIZE 500m;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 8 ('+HOMEDBDATA/HOMEdb/onlinelog/group_8_log1.log','+HOMEDBFLASH/HOMEdb
nlinelog/group_8_log1.log') SIZE 500m;

Database altered.

SQL>
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> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL>
======================================================


SQL> ALTER DATABASE DROP LOGFILE GROUP 8;
ALTER DATABASE DROP LOGFILE GROUP 8
*
ERROR at line 1:
ORA-00350: log 8 of instance HOMEdb2 (thread 2) needs to be archived
ORA-00312: online log 8 thread 2:
'+HOMEDBDATA/HOMEdb/onlinelog/group_8_log1.log'
ORA-00312: online log 8 thread 2:
'+HOMEDBFLASH/HOMEdb/onlinelog/group_8_log1.log'


SQL> ALTER SYSTEM ARCHIVE LOG GROUP 8;
ALTER SYSTEM ARCHIVE LOG GROUP 8
*
ERROR at line 1:
ORA-16014: log 8 sequence# 27331 not archived, no available destinations
ORA-00312: online log 8 thread 2:
'+HOMEDBDATA/HOMEdb/onlinelog/group_8_log1.log'
ORA-00312: online log 8 thread 2:
'+HOMEDBFLASH/HOMEdb/onlinelog/group_8_log1.log'


SQL> ALTER SYSTEM ARCHIVE LOG GROUP 8 TO '+HOMEDBFLASH';

System altered.

SQL> ALTER SYSTEM ARCHIVE LOG GROUP 6 TO '+HOMEDBFLASH';

System altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 8;

Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 7;

Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 6;

Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 5;

Database altered.

SQL>

Error:-

After adding new log members Is Recovery Destination property set to No

Solution

Don't give any destination path just add a logfile group

ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 8 SIZE 500MB;

Let OMF Manage everything, it will take parameter db_create_file_dest and create file and then create log file group in online redo log directory.

and it will add a multiplexed copy of file on FRA. Now Is Recovery Destination will be set to YES.

=======================================================================


SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
ALTER SYSTEM ARCHIVE LOG CURRENT
*
ERROR at line 1:
ORA-16014: log 1 sequence# 29866 not archived, no available destinations
ORA-00312: online log 1 thread 1:
'+HOMEDBDATA/HOMEdb/onlinelog/group_1.267.801652589'
ORA-00312: online log 1 thread 1:
'+HOMEDBFLASH/HOMEdb/onlinelog/group_1.3518.801652593'



Cause:  An attempt was made to archive the named log, but the archive was unsuccessful. The archive failed because there were no archive log destinations specified or

all destinations experienced debilitating errors.

Action: Verify that archive log destinations are being specified and/or take the necessary step to correct any errors that may have ocurred.

Setting your archive log destination is elementary, run this to see your setting:

 SQL> show parameter archive_dest  

If you have not set a log archive destination, you need to define the file and set the log_archive_dest_n parameter:

ARCHIVE_LOG_DEST = destination

Where "destination" is your operating system specific path to archive log destination.

For example:

SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';

SQL > alter system switch logfile;


















No comments:

Post a Comment