Seems like you have media corruption. I would consult Automatic Diagnostic Repository (ADR) contents if there are open failures in your database. You can do that via Enterprise Manager or using RMAN command-line utility:
[oracle@oca ~]$ rman target=/
RMAN> list faliure;
If there are failures with status OPEN
listed, you can ask Data Recovery Advisor (DRA) to analyze them and recommend you the solution to repair the failures:
RMAN> advise failure;
Since you have block corruption, DRA will probably suggest you to recover the corrupted blocks and will create the appropriate script which you can run manually or in the same flow with RMAN:
RMAN> repair failure;
After failures are repaired they're automatically closed by DRA. You can check if all failures are closed with one more list failure
command in RMAN, and if not you can get another advice from DRA.
EDIT:
Since the recovery you performed didn't last long (elapsed time: 00:00:00
), I infer that the datafile wasn't written to by RMAN, and maybe even checked. Thus I would recommend you to proactively validate the database; to do that, just issue validate database
in RMAN and analyze the output. RMAN will check all datafiles (including control file and spfile) and show you if there are any corrupted blocks.
In your comment, you said that RMAN returns the message "Block Media Recovery requires Enterprise Edition" in response to one of your commands, and based on the response from RMAN to validate database
command I suspect this feature is also not available in your software edition. If your database is running in ARCHIVELOG
mode (check with select log_mode from v$database
) you could just completely restore and recover your database without loss of committed transaction, but before doing this I would make sure that your media (hard disk) is not damaged.
First look what other parameters are set:
CREATE pfile='pfile.txt' FROM spfile;
Maybe db_cache_size, shared_pool_size, sga_target or other memory related parameters are set to non zero? Remember that when using AMM those parameters specify minimum memory allocated for particular pool. So if sga_target is 6GB you will not be allowed to set memory_target to 4GB.
Also sum of internal variables __sga_target, __db_cache_size, etc. may be more than your specified value of 4GB.
If you see those symptoms you can cleanup pfile bounce Oracle with pfile and recreate spfile. In the same step you can also set PGA_AGGREGATE_TARGET
to zero.
STARTUP pfile='pfile.txt'
CREATE spfile from pfile='pfile.txt';
Best Answer
inmemory_size
can not be modified online. Modify it in the spfile:alter system set inmemory_size=200M scope=spfile;
Then restart the database.