ORA-00358: Too many file members specified, the maximum is 5
Error:
ORA-00358: Too many file members specified, the maximum is 5
SQL> alter database add logfile group 1 (
2 'C:\Oracle\oradata\XE\REDO011.LOG',
3 'C:\Oracle\oradata\XE\REDO012.LOG',
4 'C:\Oracle\oradata\XE\REDO013.LOG',
5 'C:\Oracle\oradata\XE\REDO014.LOG',
6 'C:\Oracle\oradata\XE\REDO015.LOG',
7 'C:\Oracle\oradata\XE\REDO016.LOG') size 200m reuse;
alter database add logfile group 1 (
*
ERROR at line 1:
ORA-00358: Too many file members specified, the maximum is 5
Cause:
Maximum number of logfile setting reached.
Solution
1. You can add upto maximum 5 logfile in the database.
2. If you want to add more logfile, then you need to create the control file again for this database by getting trace of it.
For creating new control files, follows following steps:
1. Generate the Trace of control file.
SQL> alter database backup controlfile to trace as 'D:\control.txt';
2. Shutdown the clean database.
shutdown immediate;
startup nomount
3. Rename the existing control file in ORADATA folder.
4. Login with SQLPLUS window and open the control.txt file and choose NORESETLOG option because of clean shutdown.
Note:
1. Edit the CREATE CONTROLFILE command and increase the value MAXLOGFILES to 16 as you need and create control file.
Download the complete Control.txt file from here.
-- Edit the create control file and increase MAXLOGFILES value from 5 to 16
CREATE CONTROLFILE REUSE DATABASE "XE" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'C:\ORACLE\ORADATA\XE\REDO01.LOG' SIZE 200M BLOCKSIZE 512,
GROUP 2 'C:\ORACLE\ORADATA\XE\REDO02.LOG' SIZE 200M BLOCKSIZE 512,
GROUP 3 'C:\ORACLE\ORADATA\XE\REDO03.LOG' SIZE 200M BLOCKSIZE 512,
GROUP 4 'C:\ORACLE\ORADATA\XE\REDO04.LOG' SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'C:\ORACLE\ORADATA\XE\SYSTEM01.DBF',
'C:\ORACLE\ORADATA\XE\SYSAUX01.DBF',
'C:\ORACLE\ORADATA\XE\UNDOTBS01.DBF',
'C:\ORACLE\ORADATA\XE\PDBSEED\SYSTEM01.DBF',
'C:\ORACLE\ORADATA\XE\PDBSEED\SYSAUX01.DBF',
'C:\ORACLE\ORADATA\XE\USERS01.DBF',
'C:\ORACLE\ORADATA\XE\PDBSEED\UNDOTBS01.DBF',
'C:\ORACLE\ORADATA\XE\XEPDB1\SYSTEM01.DBF',
'C:\ORACLE\ORADATA\XE\XEPDB1\SYSAUX01.DBF',
'C:\ORACLE\ORADATA\XE\XEPDB1\UNDOTBS01.DBF',
'C:\ORACLE\ORADATA\XE\XEPDB1\USERS01.DBF',
'C:\ORACLE\ORADATA\XE\PDB1\SYSTEM01.DBF',
'C:\ORACLE\ORADATA\XE\PDB1\SYSAUX01.DBF',
'C:\ORACLE\ORADATA\XE\PDB1\UNDOTBS01.DBF',
'C:\ORACLE\ORADATA\XE\PDB1\USERS01.DBF',
CHARACTER SET AL32UTF8
;
-- Follows the step provided for temp files and database open
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE 'C:\ORACLE\ARCHIVE\ARC0000000001_0999319476.0001';
-- ALTER DATABASE REGISTER LOGFILE 'C:\ORACLE\ARCHIVE\ARC0000000001_1019317005.0001';
-- ALTER DATABASE REGISTER LOGFILE 'C:\ORACLE\ARCHIVE\ARC0000000001_1020770292.0001';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Open all the PDBs.
ALTER PLUGGABLE DATABASE ALL OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\ORACLE\ORADATA\XE\TEMP01.DBF' SIZE 135266304 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
ALTER SESSION SET CONTAINER = PDB$SEED;
ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\ORACLE\ORADATA\XE\PDBSEED\TEMP012019-09-18_15-38-03-475-PM.DBF'
SIZE 65011712 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
ALTER SESSION SET CONTAINER = XEPDB1;
ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\ORACLE\ORADATA\XE\XEPDB1\TEMP01.DBF'
SIZE 135266304 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
ALTER SESSION SET CONTAINER = PDB1;
ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\ORACLE\ORADATA\XE\PDB1\TEMP012019-09-18_15-38-03-475-PM.DBF' REUSE;
ALTER SESSION SET CONTAINER = CDB$ROOT;
-- End of tempfile additions.
5. Now you can add logfiles upto 16.
<code
alter database add logfile group 1 (
'C:\Oracle\oradata\XE\REDO011.LOG',
'C:\Oracle\oradata\XE\REDO012.LOG',
'C:\Oracle\oradata\XE\REDO013.LOG',
'C:\Oracle\oradata\XE\REDO014.LOG',
'C:\Oracle\oradata\XE\REDO015.LOG',
'C:\Oracle\oradata\XE\REDO016.LOG') size 200m reuse;
Database altered.
Smart way of Technology 