Error noticed while creating tablespace.
1 create tablespace tbs_moha 2 datafile '/MOHA/oradata/orcl/moha_01.dbf' size 5G 3 autoextend on 4* maxsize unlimited SQL> / create tablespace tbs_moha * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-01116: error in opening database file 15 ORA-01110: data file 15: '/home/oracle/BACKUP/undotbs02.dbf' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3
Find the name of tablespace associated with the missing datafile.
SQL> select d.name,t.name 2 from v$datafile d join v$tablespace t 3 using(ts#); NAME -------------------------------------------------------------------------------- NAME ------------------------------ /home/oracle/BACKUP/undotbs02.dbf UNDOTBS2
Check Undo Parameters.
SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS2
Shutdown the database, change undo_mangement to Manual and start the database
SQL> shutdown abort; ORACLE instance shut down. SQL> create pfile from spfile; File created. Modify pfile and set undo_management='Manual' SQL> create spfile from pfile; File created. SQL> startup ORACLE instance started. Total System Global Area 417546240 bytes Fixed Size 2228944 bytes Variable Size 322964784 bytes Database Buffers 88080384 bytes Redo Buffers 4272128 bytes Database mounted. Database opened.
Create new undo tablespace, shutdown database, set undo management auto and undo tablespace to newly created tablespace
1 create undo tablespace undotbs01 2 datafile '/u01/app/oracle/oradata/orcl/undotbs01_01.dbf' size 10m 3 autoextend on 4* maxsize unlimited SQL> / Tablespace created. SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string MANUAL undo_retention integer 900 undo_tablespace string UNDOTBS2 SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. open pfile and modify undo_management='Auto' and undo_tablespace='Undotbs01' SQL> create spfile from pfile; File created. SQL> startup ORACLE instance started. Total System Global Area 417546240 bytes Fixed Size 2228944 bytes Variable Size 322964784 bytes Database Buffers 88080384 bytes Redo Buffers 4272128 bytes Database mounted. Database opened. SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string undotbs01
Now create tablespace will create successfully
1 create tablespace tbs_moha 2 datafile '/MOHA/oradata/orcl/moha01.dbf' size 1G 3 autoextend on 4* maxsize unlimited SQL> / Tablespace created.
Following command failed due to existing rollback segment on the old undo tablespace.
SQL> grant dba to usr_moha; grant dba to usr_moha * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-00376: file 15 cannot be read at this time ORA-01110: data file 15: '/home/oracle/BACKUP/undotbs02.dbf'
So tried to drop old undo tablespace
SQL> drop tablespace UNDOTBS2; drop tablespace UNDOTBS2 * ERROR at line 1: ORA-01548: active rollback segment '_SYSSMU12_3370723747$' found, terminate dropping tablespace
Shutdown database and add following parameter
This parameter is oracle's hidden and undocumented parameters. So use at your own risk.
This parameter is oracle's hidden and undocumented parameters. So use at your own risk.
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> create pfile from spfile; File created. vi initorcl.ora *._offline_rollback_segments=(_SYSSMU12_3370723747$) SQL> create spfile from pfile; File created. SQL> startup; ORACLE instance started. Total System Global Area 417546240 bytes Fixed Size 2228944 bytes Variable Size 322964784 bytes Database Buffers 88080384 bytes Redo Buffers 4272128 bytes Database mounted. Database opened. SQL> drop tablespace UNDOTBS2; drop tablespace UNDOTBS2 * ERROR at line 1: ORA-01548: active rollback segment '_SYSSMU13_3758039048$' found, terminate dropping tablespace
Append _SYSSMU13_3758039048$ to the end of the _offline_rollback_segments. Keep going until the the rollback segment and drop tablespace successfully
*._offline_rollback_segments=(_SYSSMU32_3855527180$,_SYSSMU33_2662784219$,_SYSSMU34_2457069885$,_SYSSMU35_1632431741$,_SYSSMU36_3409971511$,_SYSSMU37_2689280970$,_SYSSMU38_389146150$,_SYSSMU39_3304332257$,_SYSSMU40_2103468367$,_SYSSMU41_2387847232$, _SYSSMU12_3370723747$ ,_SYSSMU13_3758039048$ ,_SYSSMU14_2103326955$ ,_SYSSMU15_3693451306$ ,_SYSSMU16_3316561802$ ,_SYSSMU17_2198165518$ ,_SYSSMU18_3012613150$ ,_SYSSMU19_1884560376$ ,_SYSSMU20_2031163871$ ,_SYSSMU21_1352513242$ ) SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. vi initorcl.ora SQL> create spfile from pfile; File created. SQL> startup; ORACLE instance started. Total System Global Area 417546240 bytes Fixed Size 2228944 bytes Variable Size 322964784 bytes Database Buffers 88080384 bytes Redo Buffers 4272128 bytes Database mounted. Database opened. SQL> drop tablespace UNDOTBS2; Tablespace dropped.
Finally remove all the parameters and restart the database.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string undotbs01
SQL> show parameter rollback
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_offline_rollback_segments string _SYSSMU32_3855527180$, _SYSSMU
33_2662784219$, _SYSSMU34_2457
069885$, _SYSSMU35_1632431741$
, _SYSSMU36_3409971511$, _SYSS
MU37_2689280970$, _SYSSMU38_38
9146150$, _SYSSMU39_3304332257
$, _SYSSMU40_2103468367$, _SYS
SMU41_2387847232$, _SYSSMU12_3
370723747$, _SYSSMU13_37580390
48$, _SYSSMU14_2103326955$, _S
YSSMU15_3693451306$, _SYSSMU16
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_3316561802$, _SYSSMU17_219816
5518$, _SYSSMU18_3012613150$,
_SYSSMU19_1884560376$, _SYSSMU
20_2031163871$, _SYSSMU21_1352
513242$
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
vi initorcl.ora
SQL> create spfile from pfile;
File created.
SQL> startup
ORACLE instance started.
Total System Global Area 417546240 bytes
Fixed Size 2228944 bytes
Variable Size 322964784 bytes
Database Buffers 88080384 bytes
Redo Buffers 4272128 bytes
Database mounted.
Database opened.
SQL> show parameter offline_rollback_segments;
SQL> grant dba to usr_moha;
Grant succeeded.
SQL>
0 comments:
Post a Comment