Import terminated successfully with warnings.
ALTER DATABASE DATAFILE 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF' RESIZE 3000M;
Or by adding a second datafile to the tablespace:
ALTER TABLESPACE SYSTEM
ADD DATAFILE 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF'
SIZE 1000M;
Or just allow the datafile to auto extend:
ALTER DATABASE
DATAFILE 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF'
AUTOEXTEND ON
MAXSIZE UNLIMITED;
APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.1.0.6 and
later
Oracle Database - Standard Edition - Version 11.2.0.1 to 11.2.0.1 [Release
11.2]
Linux x86
Linux x86-64
***Checked for relevance on 30-Sep-2014***
SYMPTOMS
During the startup of the database instance the following
error is raised:
SQL> connect sys as sysdba
Enter password: *****
Connected to an idle instance.
SQL> STARTUP NOMOUNT
ORA-00845: MEMORY_TARGET not supported on this system
The instance alert file shows:
Starting ORACLE instance (normal)
WARNING: You are trying to use the MEMORY_TARGET feature.
This feature requires the /dev/shm file system to be mounted for at Least
<size> bytes.The /dev/shm is either not mounted or is mounted with
available space less than this size.
Please fix this so that MEMORY_TARGET can work as expected. Current available
is <size> and used is <size> bytes.memory_target needs larger
/dev/shm
Also error messages like ORA-4031 may be reported and no new connections can be
established to the database.
CHANGES
The Automatic Memory Management (AMM) has been implemented
by setting parameters like MEMORY_TARGET and/or MEMORY_MAX_TARGET.
CAUSE
The new Automatic Memory Management functionality uses
/dev/shm on Linux for SGA and PGA management. The errors occur if either MEMORY_TARGET
or MEMORY_MAX_TARGET is configured larger than the configured /dev/shm size, or
if /dev/shm is mounted incorrectly.
SOLUTION
Please confirm that ORACLE_HOME is set correctly. This error
sometimes happens when it is not set correctly.
Make sure that the /dev/shm size is configured large enough, like in:
# mount -t tmpfs shmfs -o size=7g /dev/shm
In this case, the size of the shared memory device is
configured to be 7GB.
In order to make the same change persistent across system reboots, add an entry
for this to the /etc/fstab mount table, as in:
shmfs /dev/shm tmpfs size=7g 0
Note:
You should check with your System Administrator what the
"best" size for /dev/shm is, based on what has been reported in the
alert file.
Also, many best practices now suggest disabling AMM
especially in Exa* Engineered boxes that have larger memory capability and can
use Huge / Large pages.
This is because AMM and Huge / Large pages are mutually exclusive and overall
performance will be better using Huge pages
Make sure that the df output shows the correct /dev/shm configuration when
using Oracle on the system:
$ df -k
Filesystem 1K-blocks Used Available Use% Mounted on
...
shmfs 6291456 832356 5459100 14% /dev/shm
This document is taken from Oracle Support Doc ID 465048.1
Open command prompt and execute the below commands:
set oracle_sid=DATABASE NAMEsqlplus /nologconn sys/sys as sysdbashutdown abortstartup
Problem Description Users cant able to login to
database server. Though it allow sysdba to login, it does not allow to run any
query. Sometimes it will show database is connected to an idle instance. But
when we startup database it shall throw error ORA-01081: cannot start
already-running ORACLE - shut it down first.
$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 12 07:53:11 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 -
64bit Production
With the Partitioning, OLAP, Data Mining and Real
Application Testing options
SQL> select instance_name,status from v$instance;
ERROR at line 1:
ORA-01012: not logged onSQL> desc v$instance
ERROR:
ORA-01012: not logged on
SQL> startup
ORA-01012: not logged on
Changes
Oracle has been forcefully shutdown at OS level or crashed.
CAUSE
An orphaned shared memory segment belonging to the
ORACLE_SID still exists from a previous instance startup.
The command
ps -ef | grep $ORACLE_SID
shows no processes but with ORACLE_SID set the Oracle 'sysresv' utility shows a shared memory segment for a non-existing instance, e.g.
$ sysresv
IPC Resources for ORACLE_SID "TEST" :
Shared Memory:
ID KEY
5963794 0x00000000
5996563 0x00000000
6029332 0xb2e3c9ac
Semaphores:
ID KEY
No semaphore resources used
Oracle Instance not alive for sid "TEST"
Solution
On OS level, remove the orphaned shared memory segment
using:
ipcrm -m <problem shared memory id>
$ ipcrm -m 5963794
$ ipcrm -m 5996563
$ ipcrm -m 6029332
$sqlplus '/as sysdba'
Connected to an idle instance.
SQL> startup Oracle instance started
Total System Global Area 10689474560 bytes
Fixed Size 2237776 bytes
Variable Size 6375344816 bytes
Database Buffers 4294967296 bytes
Redo Buffers 16924672 bytes
Oracle Instance Started.
Oracle Database Opened.
Possible Remedies:
* If running on the server (or workstation) that is running the database (i.e.
if the database is local to the machine) then try explicitly setting
'ORACLE_SID'. e.g.
set ORACLE_SID=<sid>
and then try again. If this works then consider whether to make the change
permanent by specifying it as a system variable.
Due to the power outage, one of my production database giving the following error.
When I try to startup the database the ORA-00214 controlfile
inconsistent error occurred:
SQL> startup
ORACLE instance started.
Total System Global Area 662700032 bytes
Fixed
Size
1250716 bytes
Variable Size
281021028
bytes
Database
Buffers 373293056 bytes
Redo
Buffers
7135232 bytes
ORA-00214: control file
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\SYMP\CONTROL03.CTL'
version 1021304 inconsistent with file
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\SYMP\CONTROL01.CTL' version
1021301
Error: ORA-00214
Cause: An ORA-00214 is issued whenever Oracle detects
an inconsistency between two mirrored copies of the control file.
All copies of the control file must have the same internal
sequence number for Oracle to start up the database or shut it down in normal
or immediate mode.
Action: Use a consistent set of control files.
That is, all the
files must be for the same database and from same time period.
Before going to troubleshoot the above error, Please follow the Metalink Doc ID 1014751.6
Action plan:
OS: Windows 2003 R2 32-bit
Database version: 10.2.0.1
Step1: Find the controlfile location:
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
control_file_record_keep_time
integer
7
control_files
string
C:\ORACLE\PRODUCT\10.2.0\ORADA
TA\SYMP\CONTROL01.CTL,
C:\ORAC
LE\PRODUCT\10.2.0\ORADATA\SYMP
\CONTROL02.CTL, C:\ORACLE\PROD
UCT\10.2.0\ORADATA\SYMP\CONTRO
L03.CTL
Step2: Create and Edit the pfile from spfile:
SQL> create
pfile='C:\oracle\product\10.2.0\db_1\dbs\inittest.ora' from spfile;
File created.
LINUX
SQL> SHOW PARAMETER spfile;
NAME
TYPE
VALUE
------------------------------------ ----------- ------------------------------
spfile
string
/u01/app/oracle/product/11.2.0
/db_1/dbs/spfilesh1.ora
SQL> create pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initest.ora'
from spfile;
File created.
Step3: Remove the multiplexing controlfile of control_files parameter
Control_file=c:\oracle\product\10.2.0\oradata\symp\control01.ctl
Save
LINUX
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
control_file_record_keep_time
integer 7
control_files
string /u01/app/oracle/oradata/sh1/co
ntrol01.ctl, /u01/app/oracle/f
lash_recovery_area/sh1/control
02.ctl
control_management_pack_access
string DIAGNOSTIC+TUNING
SQL>
Note:
Copy control02 from flash_recovery_area (sid folder) to
oradata (sid folder)
Rename control02 as control01 in oradata foler.
LINUX
Rename
[oracle@localhost ~]$mv /u01/app/oracle/oradata/sr/control01.ctl
/u01/app/oracle/oradata/sr/control01_bk.ctl
Copy
[oracle@localhost ~]$cp /u01/app/oracle/flash_recovery_area/sr/control02.ctl
/u01/app/oracle/oradata/sr/control01.ctl
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL>
Now try to start the edited pfile:
SQL> startup nomount
pfile='C:\oracle\product\10.2.0\db_1\dbs\inittest.ora'
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initest.ora'
ORACLE instance started.
Total System Global Area 662700032 bytes
Fixed
Size
1250716 bytes
Variable
Size
281021028 bytes
Database
Buffers 373293056 bytes
Redo
Buffers
7135232 bytes
OR
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 535662592 bytes
Fixed Size
1337720 bytes
Variable Size 176162440 bytes
Database Buffers 352321536 bytes
Redo Buffers 5840896
bytes
SQL>
Step4: Try to mount & open the database with pfile:
SQL> alter database mount;
Database altered.
SQL> alter database open;
Database altered.
Step5: Now try to remove the multiplexed controlfile at
os level.
C:\oracle\product\10.2.0\oradata>cd symp
C:\oracle\product\10.2.0\oradata\symp>dir
Volume in drive C has no label.
Volume Serial Number is 8048-4083
Directory of C:\oracle\product\10.2.0\oradata\symp
7,290,880 CONTROL01.CTL
7,290,880 CONTROL02.CTL
7,290,880 CONTROL03.CTL
524,296,192 IDCARD_TS.DBF
52,429,312 REDO01.LOG
52,429,312 REDO02.LOG
52,429,312 REDO03.LOG
377,495,552 SYSAUX01.DBF
513,810,432 SYSTEM01.DBF
269,492,224 TEMP01.DBF
68,165,632 UNDOTBS01.DBF
17,047,552 USERS01.DBF
12 File(s) 1,949,468,160 bytes
2 Dir(s) 245,401,767,936 bytes free
C:\oracle\product\10.2.0\oradata\symp>del CONTROL02.CTL
C:\oracle\product\10.2.0\oradata\symp>del CONTROL03.CTL
C:\oracle\product\10.2.0\oradata\symp>dir
Volume in drive C has no label.
Volume Serial Number is 8048-4083
Step6: Multiplex the controlfile with existing copy of controlfile (controlfile01.ctl) :
C:\oracle\product\10.2.0\oradata\symp>copy CONTROL01.CTL CONTROL02.CTL
1 file(s) copied.
C:\oracle\product\10.2.0\oradata\symp>copy CONTROL01.CTL
CONTROL03.CTL
1 file(s) copied.
Step7: Start the database with existing default spfile:
SQL> STARTUP
Database started.
Now the database was successfully started without
controlfile error.
let see the controlfile location:
SQL> Show parameter control
control_files='C:\oracle\product\10.2.0\oradata\symp\control01.ctl','C:\oracle\product\10.2.0\oradata\symp\control02.ctl','C:\oracle\product\10.2.0\oradata\symp\control03.ctl'
Please follow the Metalink Doc ID 1014751.6
OR
SQL> startup mount pfile='c:\p.txt';
SQL>rman target /
RMAN>restore controlfile from autobackup;
RMAN> quit
SQL>shut immediate
SQL>startup mount;
ORACLE instance started.
Total System Global Area 3206836224 bytes
Fixed Size
2180024 bytes
Variable Size
2415922248 bytes
Database Buffers
771751936 bytes
Redo Buffers
16982016 bytes
ORA-00214: control file
'D:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL'
version 145625
inconsistent with file 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL01.CTL'
version 144010
SQL> shut immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL>startup force mount pfile='c:\p.txt';
ORACLE instance started.
Total System Global Area 3206836224 bytes
Fixed Size
2180024 bytes
Variable Size
2415922248 bytes
Database Buffers
771751936 bytes
Redo Buffers
16982016 bytes
Database mounted.
SQL>host rman target /
RMAN>recover database;
RMAN> alter database open resetlogs;
database opened.
LINUX
[oracle@localhost ~]$ sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Thu May 31 17:09:35 2018
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter user-name: / as sysdba
SQL> shutdown abort
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1640484864 bytes
Fixed Size
1336876 bytes
Variable Size 1090521556 bytes
Database Buffers 536870912 bytes
Redo Buffers 11755520
bytes
SQL>
SQL> SHOW PARAMETER spfile;
SQL> create pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/pfileshisb.ora' from spfile='/u01/app/oracle/product/11.2.0/db_1/dbs/spfileshisb.ora';
File created.
SQL> show parameter control
NAME
TYPE
VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer
7
control_files
string /u01/app/oracle/oradata/sh1/co
ntrol01.ctl, /u01/app/oracle/f
lash_recovery_area/sh1/control
02.ctl
control_management_pack_access string
DIAGNOSTIC+TUNING
SQL>
SQL>
control_file=/u01/app/oracle/oradata/shisb/control01.ctl
SQL> shutdown abort;
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/pfileshisb.ora';
ORACLE instance started.
Total System Global Area 1640484864 bytes
Fixed Size
1336876 bytes
Variable Size
1090521556 bytes
Database Buffers 536870912
bytes
Redo Buffers
11755520 bytes
Error log:
[oracle@localhost ~]$ sqlplus
Enter user-name: / as sysdba
SQL> shutdown abort;
SQL> startup nomount;
ORA-01507: database not mounted
SQL> shutdown abort;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1:
'/u01/app/oracle/oradata/RTS_NEW/system_new.dbf'
Workaround for this error is to provide all the available
archive log files to the recovery:
SQL> recover database using backup controlfile until
cancel;
...
Specify log: {<RET>=suggested | filename | AUTO |
CANCEL}
AUTO
Above command will apply all the available archive logs
automatically. Now try to open database with resetlogs:
SQL> alter database open resetlogs;
If the error persists due to insufficient archive logs, do
the following workaround:
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Startup database in mount mode:
SQL> startup mount
ORACLE instance started.
Total System Global Area 530288640 bytes
Fixed Size 2131120 bytes
Variable Size 310381392 bytes
Database Buffers 209715200 bytes
Redo Buffers 8060928 bytes
Database mounted.
Change “_allow_resetlogs_corruption” parameter to TRUE
and undo_management parameter to MANUAL:
SQL> ALTER SYSTEM SET
"_allow_resetlogs_corruption"= TRUE SCOPE = SPFILE;
SQL> ALTER SYSTEM SET undo_management=MANUAL SCOPE =
SPFILE;
After doing above changes, shutdown database, and startup:
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 530288640 bytes
Fixed Size 2131120 bytes
Variable Size 310381392 bytes
Database Buffers 209715200 bytes
Redo Buffers 8060928 bytes
Database mounted.
Now try resetlogs:
SQL> alter database open resetlogs;
Database altered.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
When database started successfully and up then perform the
following steps
Create new undo tablespace and set “undo_tablespace”
parameter to the new undo tablespace and change “undo_management”
parameter to AUTO:
Undo space once allocated will be available for reuse but
will not be deallocated to the OS. The best way to shrink Undo tablespace is to
switch to a new Undo tablespace and drop the old Undo tablespace. The steps
are:
1. Create a new undo tablespace of the same size (larger or
smaller) depending on your database requirements.
SQL> show parameter control;
SQL> CREATE UNDO TABLESPACE undo datafile
'/u01/app/oracle/oradata/sa/undo_df1.dbf' size 200m autoextend on maxsize 30G;
Tablespace created.
SQL> alter system set undo_tablespace = undo
scope=spfile;
System altered.
SQL> alter system set undo_management=auto scope=spfile;
System altered.
SQL> shutdown immediate
SQL> startup
Cheers!! Database started successfully.
Check the status of the undo segments and determine if all the segments in the old undo tablespace are offline.
sql> select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name, status;
TABLESPACE_NAME STATUS COUNT(*)
------------------------------ ---------------- ----------
UNDOTBS1 OFFLINE 10
SYSTEM ONLINE 1
UNDO ONLINE 10
If there are Undo segments with a status other than OFFLINE
in the tablespace to be dropped, we need to wait till they become OFFLINE. You
may have to wait for the duration of the tuned_undoretention (from v$undostat)
to ensure all Undo segments have become OFFLINE.
sql> select status,segment_name from dba_rollback_segs
where status not in ('OFFLINE') and tablespace_name=UNDOTBS1;
sql> select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='UNDO';
For example:
sql> select status,segment_name from dba_rollback_segs
where status not in ('OFFLINE') and tablespace_name='UNDOTBS1';
If all the Undo segments in the old Undo tablespace to the
dropped is of status OFFLINE, then drop the tablespace.
sql> select tablespace_name, status, count(*) from
dba_rollback_segs group by tablespace_name, status;
Verify and then drop:
sql> drop tablespace [tablespace_name] including contents
and datafiles;
For example:
sql> drop tablespace UNDOTBS1 including contents and
datafiles;
Tablespace dropped.
SQL> select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name, status;
TABLESPACE_NAME STATUS COUNT(*)
------------------------------ ---------------- ----------
SYSTEM ONLINE 1
UNDO ONLINE 10
SQL>
Solved
connect / as sysdba
SQL> SELECT *
FROM V$RECOVERY_FILE_DEST;
SQL> SELECT *
FROM V$FLASH_RECOVERY_AREA_USAGE;
SQL> ALTER SYSTEM
SET DB_RECOVERY_FILE_DEST_SIZE = 200G SCOPE=BOTH SID='*';
If still not
resolved then
1. rename
flash_recovery_area as flash_recovery_area_bck
2. create new folder
flash_recovery_area on same location.
3. connect / as
sysdba
4. SQL>
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 200G SCOPE=BOTH SID='*';
5. restart/reboot
the server and check database will be up.
After hours of misdirection from official Oracle
support, I dove into this on my own and fixed it. I am documenting it here
in case someone else has this problem.
To do any of this, you must be the oracle user:
$ su - oracle
Step 1: You need to look at the alert log. It isn't in /var/log as expected.
You have to run an Oracle log reading program:
$ adrci
ADRCI: Release 11.2.0.1.0 - Production on Wed Sep 11 18:27:56 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
ADR base = "/u01/app/oracle"
adrci>
Notice the ADR base. That is not the install. You need to see the homes so you
can connect to the one that you use.
adrci> show homes
ADR Homes:
diag/rdbms/cci/CCI
diag/tnslsnr/cci/listener
diag/tnslsnr/cci/start
diag/tnslsnr/cci/reload
CCI is the home. Set that.
adrci> set home diag/rdbms/cci/CCI
adrci>
Now, you can look at the alert logs. It would be very nice if they were in
/var/log so you could easily parse the logs.
Just stop wanting and deal with this interface. At least you can tail (and I
hope you have a scrollback buffer):
adrci> show alert -tail 100
Scroll back until you see errors. You want the FIRST error. Any errors after
the first error are likely being caused by the
first error. In my case, the first error was:
ORA-19815: WARNING: db_recovery_file_dest_size of 53687091200 bytes is 100.00%
used, and has 0 remaining bytes available.
This is caused by transactions. Oracle is not designed to be used. If you do
push a lot of data into it, it saves
transaction logs. Those go into the recovery file area. Once that is full (50GB
full in this case). Then, Oracle just dies.
By design, if anything is messed up, Oracle will respond by shutting down.
There are two solutions, the proper one and the quick and dirty one. The quick
and dirty one is to increase
db_recovery_file_dest_size. First, exit adrci.
adrci> exit
Now, go into sqlplus without opening the database, just mounting it (you may be
able to do this without mounting the
database, but I mount it anyway).
$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 11 18:40:25 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> connect / as sysdba
Connected.
SQL> startup mount
Now, you can increase your current db_recovery_file_dest_size, increased to 75G
in my case:
SQL> alter system set db_recovery_file_dest_size = 120G scope=both
Now, you can shutdown and startup again and that previous error should be gone.
The proper fix is to get rid of the recovery files. You do that using RMAN, not
SQLPLUS or ADRCI.
$ rman
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Sep 11 18:45:11 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
RMAN> backup archivelog all delete input;
Wait a long time and your archivelog (that was using up all that space) will be
gone. So, you can shutdown/startup your
database and be beck in business.
$ rman target /
RMAN> SHOW ALL;
RMAN> LIST BACKUP SUMMARY;
RMAN> CROSSCHECK BACKUP;
RMAN> DELETE OBSOLETE;
RMAN> DELETE NOPROMPT OBSOLETE;
RMAN> DELETE OBSOLETE RECOVERY WINDOW OF 10 DAYS;
RMAN> LIST BACKUP SUMMARY;
RMAN> DELETE EXPIRED BACKUP;
log in as root and execute
command "ipcs". should you see ipcs owned by the oracle user, delete
them using the "ipcrm" command, and everything will work fine again
OR 01/04/2023 I resolved this problem in this way:
Fix for ORA-03113: end-of-file on communication channelTotal System Global Area 2147483648 bytes
OFF FIRE WALL ON BOTH DATABASE AND CLIENT
If needed
TNS ADMIN
TNS_ADMIN is an
environment variable that points to the directory where
the SQL*Net configuration files
(like sqlnet.ora and tnsnames.ora) are located.
Windows:
set TNS_ADMIN=%ORACLE HOME%\network\admin
Unix/ Linux:
export TNS_ADMIN=$ORACLE HOME/network/admin
Solution:
SQL> alter system set LOCAL_LISTENER="(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.240)(PORT=1521))";
SQL> alter system register;
