create controlfile to increase maxdatafiles in contro file

setp 1. Before increase it, notice the column RECORDS_TOTAL of REDO LOG and DATAFILE, 16 and 100 before increament.


SQL> select * from v$controlfile_record_section;

TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX
---------------------------- ----------- ------------- ------------ -----------
LAST_INDEX LAST_RECID
---------- ----------
DATABASE 316 1 1 0
0 0

CKPT PROGRESS 8180 11 0 0
0 0

REDO THREAD 256 8 1 0
0 0

REDO LOG 72 16 16 0
0 12

DATAFILE 428 100 5 0
0 36

FILENAME 524 2298 44 0
0 0

TABLESPACE 68 100 6 0
0 1

TEMPORARY FILENAME 56 100 1 0
0 1

RMAN CONFIGURATION 1108 50 1 0
0 1

LOG HISTORY 56 292 42 1
42 42

OFFLINE RANGE 200 163 0 0
0 0

ARCHIVED LOG 584 308 49 1
49 49

BACKUP SET 40 409 7 1
7 7

BACKUP PIECE 736 200 7 1
7 7

BACKUP DATAFILE 116 282 6 1
6 6

BACKUP REDOLOG 76 215 10 1
10 10

DATAFILE COPY 660 223 16 1
16 16

BACKUP CORRUPTION 44 371 0 0
0 0

COPY CORRUPTION 40 409 0 0
0 0

DELETED OBJECT 20 818 10 1
10 10

PROXY COPY 852 249 0 0
0 0

BACKUP SPFILE 36 454 2 1
2 2

DATABASE INCARNATION 56 292 5 1
5 5

FLASHBACK LOG 84 2048 35 0
0 0

RECOVERY DESTINATION 180 1 1 0
0 0

INSTANCE SPACE RESERVATION 28 1055 1 0
0 0

REMOVABLE RECOVERY FILES 32 1000 7 0
0 0

RMAN STATUS 116 141 40 1
40 40

THREAD INSTANCE NAME MAPPING 80 8 8 0
0 0

MTTR 100 8 1 0
0 0

DATAFILE HISTORY 568 57 0 0
0 0

STANDBY DATABASE MATRIX 400 10 10 0
0 0

GUARANTEED RESTORE POINT 212 2048 0 0
0 0

RESTORE POINT 212 2083 0 0
0 0


34 rows selected.

step 2: generate trace file

SQL> alter database backup controlfile to trace as '/tmp/cntl_recreate.trc' noresetlogs;

Database altered.



[oracle@localhost ~]$ cat /tmp/cntl_recreate.trc
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=OCP10G_%t_%s_%r.arc
--
-- DB_UNIQUE_NAME="OCP10G"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=2
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_1='LOCATION=/apps/oracle/oraarch/OCP10G'
-- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE

--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.

-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "OCP10G" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/apps/oracle/oradata/OCP10G/redo01.log' SIZE 50M,
GROUP 2 '/apps/oracle/oradata/OCP10G/redo02.log' SIZE 50M,
GROUP 3 '/apps/oracle/oradata/OCP10G/redo03.log' SIZE 50M
-- STANDBY LOGFILE

DATAFILE
'/apps/oracle/oradata/OCP10G/system01.dbf',
'/apps/oracle/oradata/OCP10G/undotbs01.dbf',
'/apps/oracle/oradata/OCP10G/sysaux01.dbf',
'/apps/oracle/oradata/OCP10G/users01.dbf',
'/apps/oracle/oradata/OCP10G/example01.dbf'
CHARACTER SET WE8ISO8859P1
;

-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1');
-- 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 '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651015758.arc';
-- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651853690.arc';
-- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651854542.arc';
-- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651856491.arc';
-- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651862296.arc';
-- 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;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/apps/oracle/oradata/OCP10G/temp01.dbf'
SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.



step 3: prepare the sql modified from trace file

--my comments
--STARTUP NOMOUNT
--remove below line among "CREATE CONTROLFILE". it seems doesn't support remarks within the CREATE statement



cp ./cntrl_receate.sql cntrl_receate_new.sql

[oracle@localhost create-cntrl]$ cat cntrl_receate_new.sql
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=OCP10G_%t_%s_%r.arc
--
-- DB_UNIQUE_NAME="OCP10G"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=2
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_1='LOCATION=/apps/oracle/oraarch/OCP10G'
-- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE

--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.

-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

--my comments
--STARTUP NOMOUNT
--remove below line among "CREATE CONTROLFILE". it seems doesn't support remarks within the CREATE statement

-- "-- STANDBY LOGFILE"

CREATE CONTROLFILE REUSE DATABASE "OCP10G" NORESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 3
MAXDATAFILES 1000
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/apps/oracle/oradata/OCP10G/redo01.log' SIZE 50M,
GROUP 2 '/apps/oracle/oradata/OCP10G/redo02.log' SIZE 50M,
GROUP 3 '/apps/oracle/oradata/OCP10G/redo03.log' SIZE 50M
DATAFILE
'/apps/oracle/oradata/OCP10G/system01.dbf',
'/apps/oracle/oradata/OCP10G/undotbs01.dbf',
'/apps/oracle/oradata/OCP10G/sysaux01.dbf',
'/apps/oracle/oradata/OCP10G/users01.dbf',
'/apps/oracle/oradata/OCP10G/example01.dbf'
CHARACTER SET WE8ISO8859P1
;

-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1');
-- 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 '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651015758.arc';
-- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651853690.arc';
-- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651854542.arc';
-- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651856491.arc';
-- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651862296.arc';
-- 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;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/apps/oracle/oradata/OCP10G/temp01.dbf'
SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
--



step 4: shutdown current database
step 5: backup existing control file

[oracle@localhost oradata]$ cd OCP10G
[oracle@localhost OCP10G]$ mkdir cntrl_bak
[oracle@localhost OCP10G]$ ls con*
control01.ctl control02.ctl control03.ctl
[oracle@localhost OCP10G]$ mv con*.ctl ./cntrl_bak/
[oracle@localhost OCP10G]$ ls*.ctl ./cntrl_bak/
bash: ls*.ctl: command not found
[oracle@localhost OCP10G]$ ls *.ctl ./cntrl_bak/
ls: *.ctl: No such file or directory
./cntrl_bak/:
control01.ctl control02.ctl control03.ctl


step 6: startup nomount


SQL> spool recreate_cntrl.log
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1593835520 bytes
Fixed Size 2073088 bytes
Variable Size 872418816 bytes
Database Buffers 704643072 bytes
Redo Buffers 14700544 bytes

step 7: invoke the scripts

SQL> @cntrl_recreate_new.sql
SQL> -- The following are current System-scope REDO Log Archival related
SQL> -- parameters and can be included in the database initialization file.
SQL> --
SQL> -- LOG_ARCHIVE_DEST=''
SQL> -- LOG_ARCHIVE_DUPLEX_DEST=''
SQL> --
SQL> -- LOG_ARCHIVE_FORMAT=OCP10G_%t_%s_%r.arc
SQL> --
SQL> -- DB_UNIQUE_NAME="OCP10G"
SQL> --
SQL> -- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
SQL> -- LOG_ARCHIVE_MAX_PROCESSES=2
SQL> -- STANDBY_FILE_MANAGEMENT=MANUAL
SQL> -- STANDBY_ARCHIVE_DEST=?/dbs/arch
SQL> -- FAL_CLIENT=''
SQL> -- FAL_SERVER=''
SQL> --
SQL> -- LOG_ARCHIVE_DEST_1='LOCATION=/apps/oracle/oraarch/OCP10G'
SQL> -- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'
SQL> -- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'
SQL> -- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE NODEPENDENCY'
SQL> -- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
SQL> -- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
SQL> -- LOG_ARCHIVE_DEST_STATE_1=ENABLE
SQL>
SQL> --
SQL> -- The following commands will create a new control file and use it
SQL> -- to open the database.
SQL> -- Data used by Recovery Manager will be lost.
SQL> -- Additional logs may be required for media recovery of offline
SQL> -- Use this only if the current versions of all online logs are
SQL> -- available.
SQL>
SQL> -- After mounting the created controlfile, the following SQL
SQL> -- statement will place the database in the appropriate
SQL> -- protection mode:
SQL> -- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
SQL>
SQL> --STARTUP NOMOUNT
SQL> CREATE CONTROLFILE REUSE DATABASE "OCP10G" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 32
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 1000
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/apps/oracle/oradata/OCP10G/redo01.log' SIZE 50M,
9 GROUP 2 '/apps/oracle/oradata/OCP10G/redo02.log' SIZE 50M,
10 GROUP 3 '/apps/oracle/oradata/OCP10G/redo03.log' SIZE 50M
11 DATAFILE
12 '/apps/oracle/oradata/OCP10G/system01.dbf',
13 '/apps/oracle/oradata/OCP10G/undotbs01.dbf',
14 '/apps/oracle/oradata/OCP10G/sysaux01.dbf',
15 '/apps/oracle/oradata/OCP10G/users01.dbf',
16 '/apps/oracle/oradata/OCP10G/example01.dbf'
17 CHARACTER SET WE8ISO8859P1
18 ;

Control file created.

SQL>
SQL> -- Configure RMAN configuration record 1
SQL> VARIABLE RECNO NUMBER;
SQL> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1');

PL/SQL procedure successfully completed.

SQL> -- Commands to re-create incarnation table
SQL> -- Below log names MUST be changed to existing filenames on
SQL> -- disk. Any one log file from each branch can be used to
SQL> -- re-create incarnation records.
SQL> -- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651015758.arc';
SQL> -- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651853690.arc';
SQL> -- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651854542.arc';
SQL> -- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651856491.arc';
SQL> -- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651862296.arc';
SQL> -- Recovery is required if any of the datafiles are restored backups,
SQL> -- or if the last shutdown was not normal or immediate.
SQL> RECOVER DATABASE
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required


SQL>
SQL> -- All logs need archiving and a log switch is needed.
SQL> ALTER SYSTEM ARCHIVE LOG ALL;

System altered.

SQL>
SQL> -- Database can now be opened normally.
SQL> ALTER DATABASE OPEN;

Database altered.

SQL>
SQL> -- Commands to add tempfiles to temporary tablespaces.
SQL> -- Online tempfiles have complete space information.
SQL> -- Other tempfiles may require adjustment.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/apps/oracle/oradata/OCP10G/temp01.dbf'
2 SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.

SQL> -- End of tempfile additions.
SQL> --

step 8: verify the result of REDO LOG and DATAFILE

SQL> select * from v$controlfile_record_section;
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX
---------------------------- ----------- ------------- ------------ -----------
LAST_INDEX LAST_RECID
---------- ----------
DATABASE 316 1 1 0
0 0

CKPT PROGRESS 8180 11 0 0
0 0

REDO THREAD 256 8 1 0
0 0

REDO LOG 72 32 3 0
0 0

DATAFILE 428 1000 5 0
0 0

FILENAME 524 4146 9 0
0 0

TABLESPACE 68 1000 6 0
0 1

TEMPORARY FILENAME 56 1000 1 0
0 1

RMAN CONFIGURATION 1108 50 1 0
0 1

LOG HISTORY 56 292 1 1
1 1

OFFLINE RANGE 200 1063 0 0
0 0

ARCHIVED LOG 584 308 3 1
3 3

BACKUP SET 40 1227 0 0
0 0

BACKUP PIECE 736 1000 0 0
0 0

BACKUP DATAFILE 116 1128 0 0
0 0

BACKUP REDOLOG 76 215 0 0
0 0

DATAFILE COPY 660 1016 0 0
0 0

BACKUP CORRUPTION 44 1115 0 0
0 0

COPY CORRUPTION 40 1227 0 0
0 0

DELETED OBJECT 20 818 0 0
0 0

PROXY COPY 852 1017 0 0
0 0

BACKUP SPFILE 36 454 0 0
0 0

DATABASE INCARNATION 56 292 1 1
1 1

FLASHBACK LOG 84 2048 0 0
0 0

RECOVERY DESTINATION 180 1 1 0
0 0

INSTANCE SPACE RESERVATION 28 1055 0 0
0 0

REMOVABLE RECOVERY FILES 32 1000 0 0
0 0

RMAN STATUS 116 141 0 0
0 0

THREAD INSTANCE NAME MAPPING 80 8 8 0
0 0

MTTR 100 8 1 0
0 0

DATAFILE HISTORY 568 57 0 0
0 0

STANDBY DATABASE MATRIX 400 10 10 0
0 0

GUARANTEED RESTORE POINT 212 2048 0 0
0 0

RESTORE POINT 212 2083 0 0
0 0


34 rows selected.

SQL> spool off
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1593835520 bytes
Fixed Size 2073088 bytes
Variable Size 872418816 bytes
Database Buffers 704643072 bytes
Redo Buffers 14700544 bytes
Database mounted.
Database opened.

step 9: note that the size of controlfile is creaased

[oracle@localhost OCP10G]$ ls -lrt
total 1625676
drwxrwxr-x 2 oracle oracle 4096 Oct 28 13:57 cntrl_bak
-rw-r----- 1 oracle oracle 52429312 Oct 28 14:03 redo02.log
-rw-r----- 1 oracle oracle 52429312 Oct 28 14:03 redo01.log
-rw-r----- 1 oracle oracle 30416896 Oct 28 14:03 temp01.dbf
-rw-rw---- 1 oracle oracle 5251072 Oct 28 14:04 users01.dbf
-rw-r----- 1 oracle oracle 492838912 Oct 28 14:04 undotbs01.dbf
-rw-r----- 1 oracle oracle 524296192 Oct 28 14:04 system01.dbf
-rw-r----- 1 oracle oracle 304095232 Oct 28 14:04 sysaux01.dbf
-rw-r----- 1 oracle oracle 52429312 Oct 28 14:04 redo03.log
-rw-r----- 1 oracle oracle 104865792 Oct 28 14:04 example01.dbf
-rw-rw---- 1 oracle oracle 14630912 Oct 28 14:04 control03.ctl
-rw-rw---- 1 oracle oracle 14630912 Oct 28 14:04 control02.ctl
-rw-rw---- 1 oracle oracle 14630912 Oct 28 14:04 control01.ctl

[oracle@localhost OCP10G]$ ll -l cntrl_bak/
total 21696
-rw-rw---- 1 oracle oracle 7389184 Oct 28 13:56 control01.ctl
-rw-rw---- 1 oracle oracle 7389184 Oct 28 13:56 control02.ctl
-rw-rw---- 1 oracle oracle 7389184 Oct 28 13:56 control03.ctl


--fallback

In case of fallback, just
a). shutdown abort the instance
b). cp over the backed control files and startup again