Renaming datafiles and creating control files

Oracle RAC

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 2137886720 bytes

Fixed Size                  2230072 bytes

Variable Size            1509951688 bytes

Database Buffers          620756992 bytes

Redo Buffers                4947968 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE “TEST” NORESETLOGS  ARCHIVELOG

    MAXLOGFILES 192

  2    3      MAXLOGMEMBERS 3

  4      MAXDATAFILES 1024

  5      MAXINSTANCES 32

  6      MAXLOGHISTORY 292

  7  LOGFILE

  8    GROUP 1 ‘/u05/app/oracle/test/redo01.log’  SIZE 50M BLOCKSIZE 512,

  9    GROUP 2 ‘/u05/app/oracle/test/redo02.log’  SIZE 50M BLOCKSIZE 512,

 10    GROUP 3 ‘/u05/app/oracle/test/redo03.log’  SIZE 50M BLOCKSIZE 512,

 11    GROUP 4 ‘/u05/app/oracle/test/redo04.log’  SIZE 50M BLOCKSIZE 512

 12  — STANDBY LOGFILE

 13  DATAFILE

 14    ‘/u05/app/oracle/test/system01.dbf’,

 15    ‘/u05/app/oracle/test/sysaux01.dbf’,

 16    ‘/u05/app/oracle/test/undotbs_2.dbf’,

 17    ‘/u05/app/oracle/test/users01.dbf’

 18  CHARACTER SET WE8MSWIN1252

 19  ;

Control file created.

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01139: RESETLOGS option only valid after an incomplete database recovery

SQL> recover database until cancel;

Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ‘/u04/app/oracle/test/temp01.tmp’ siz                                                                                       e 1429M REUSE;

Tablespace altered.

SQL> select open_mode from v$database;

OPEN_MODE

——————–

READ WRITE

SQL> select name from v$controlfile;

NAME

——————————————————————————–

/u05/app/oracle/test/control01.ctl

/u05/app/oracle/test/control02.ctl

SQL>

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

ALTER DATABASE RENAME FILE ‘/u04/app/oracle/test/system01.dbf’ TO ‘/u05/app/oracle/test/system01.dbf’;

ALTER DATABASE RENAME FILE ‘/u04/app/oracle/test/sysaux01.dbf’ TO ‘/u05/app/oracle/test/sysaux01.dbf’;

ALTER DATABASE RENAME FILE ‘/u04/app/oracle/test/undotbs_2.dbf’ TO ‘/u05/app/oracle/test/undotbs_2.dbf’;

ALTER DATABASE RENAME FILE ‘/u04/app/oracle/test/users01.dbf’ TO ‘/u05/app/oracle/test/users01.dbf’;

ALTER DATABASE RENAME FILE ‘/u04/app/oracle/test/redo01.log’ TO ‘/u05/app/oracle/test/redo01.log’;

ALTER DATABASE RENAME FILE ‘/u04/app/oracle/test/redo02.log’ TO ‘/u05/app/oracle/test/redo02.log’;

ALTER DATABASE RENAME FILE ‘/u04/app/oracle/test/redo03.log’ TO ‘/u05/app/oracle/test/redo03.log’;

ALTER DATABASE RENAME FILE ‘/u04/app/oracle/test/redo04.log’ TO ‘/u05/app/oracle/test/redo04.log’;

ALTER DATABASE RENAME FILE ‘/u04/app/oracle/test/temp01.tmp’ TO ‘/u05/app/oracle/test/temp01.tmp’;

ALTER SYSTEM SET control_files=’/u05/app/oracle/test/control01.ctl’,’/u05/app/oracle/test/control02.ctl’ SCOPE=SPFILE;

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

CREATE CONTROLFILE REUSE DATABASE “TEST” NORESETLOGS  ARCHIVELOG

    MAXLOGFILES 192

    MAXLOGMEMBERS 3

    MAXDATAFILES 1024

    MAXINSTANCES 32

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 ‘/u05/app/oracle/test/redo01.log’  SIZE 50M BLOCKSIZE 512,

  GROUP 2 ‘/u05/app/oracle/test/redo02.log’  SIZE 50M BLOCKSIZE 512,

  GROUP 3 ‘/u05/app/oracle/test/redo03.log’  SIZE 50M BLOCKSIZE 512,

  GROUP 4 ‘/u05/app/oracle/test/redo04.log’  SIZE 50M BLOCKSIZE 512

— STANDBY LOGFILE

DATAFILE

  ‘/u05/app/oracle/test/system01.dbf’,

  ‘/u05/app/oracle/test/sysaux01.dbf’,

  ‘/u05/app/oracle/test/undotbs_2.dbf’,

  ‘/u05/app/oracle/test/users01.dbf’

CHARACTER SET WE8MSWIN1252

;

ALTER TABLESPACE TEMP ADD TEMPFILE ‘/mnt/TESTDB/oracledata/TESTDB/temp02.dbf’ size 100M REUSE;

To Drop A TEMP datafile

———————–

alter database tempfile ‘/mnt/TESTDB/oracledata/TESTDB/temp02.dbf’ offline ;

alter database tempfile ‘/mnt/TESTDB/oracledata/TESTDB/temp02.dbf’ drop including datafiles;