ORA-01017: invalid username/password during convert database to physical standby

 

Convert database to physical standby command errored with ORA-01017: invalid username/password; logon denied

 

So during execution of convert to physical standby using dgbroker utility , an error of ORA-01017: invalid username/password; logon denied is observed.

[oracle@server1]$ dgmgrl / DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Jan 19 02:11:48 2023 Version 19.16.0.0.0   Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.   Welcome to DGMGRL, type "help" for information. Connected to "PROD" Connected as SYSDG. DGMGRL> connect sys@PROD Password: Connected to "PROD" Connected as SYSDBA. DGMGRL> CONVERT DATABASE sbdb to PHYSICAL STANDBY; Converting database "sbdb" to a Physical Standby database, please wait... Operation requires shut down of instance "PROD" on database "sbdb" Shutting down instance "PROD"... ORA-01017: invalid username/password; logon denied

 

 

 

Solution:-

There could be a few possibilities.

1.       Make sure the sys user password you are using is correct.

2.       The password file in both primary and standby database is same and valid.

3.       Sometimes error can be observed if case sensitive parameter is different between primary and standby

 

However after making sure all the above points , we were still failing to convert the snapshot standby database into physical standby.

 

To resolve this, we need to connect to dgmgrl utility as sys user and then perform the conversion.

 

 

 

[oracle@az-pspddb2 flashback]$ dgmgrl / DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Jan 19 02:11:48 2023 Version 19.16.0.0.0   Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.   Welcome to DGMGRL, type "help" for information. Connected to "PROD" Connected as SYSDG. DGMGRL> connect sys@PROD Password:<Provide sys password> Connected to "PROD" Connected as SYSDBA.               DGMGRL> CONVERT DATABASE sbdb to PHYSICAL STANDBY;               Converting database "sbdb" to a Physical Standby database, please wait... Operation requires shut down of instance "PROD" on database "sbdb"               Shutting down instance "PROD"...              Connected to "SBDB"              Database closed. Database dismounted. ORACLE instance shut down. Operation requires start up of instance "PROD" on database "sbdb"               Starting instance "PROD"...              Connected to an idle instance. ORA-01034: ORACLE not available Process ID: 0 Session ID: 0 Serial number: 0               ORACLE instance started.             Connected to "SBDB"              Database mounted.

 

 

 

How to fix ORA-16853: apply lag has exceeded specified threshold in 19c

We had a situation where in multi standby database environment , one of the standby database was lagging behind from the primary database. The log gap between primary and standby database was pretty huge. However, in primary we had the archives present so eventually after disabling/enabling LOG_ARCHIVE_DEST_3(As this was pointing to the 2^nd standby site) the RFS process started transporting the archives to the 2^nd standby database.

 

After executing following command the MRP process started applying logs in the standby site

SQL> alter database recover managed standby database disconnect from session;

 

However, when we checked in dataguard broker console, it was throwing a warning

ORA-16853: apply lag has exceeded specified threshold

 

DGMGRL> show configuration;  Configuration - jde_dg_config    Protection Mode: MaxPerformance   Members:   prod   - Primary database     proddr - Physical standby database     sbdb   - Physical standby database       Warning: ORA-16853: apply lag has exceeded specified threshold  Fast-Start Failover:  Disabled  Configuration Status: WARNING   (status updated 52 seconds ago)

 

Solution:-

The reason of above warning is self explanatory, as one of our standby db (sbdb in this case) was out of sync for long time, the threshold value has been exceeded hence the warning.

 

[oracle@server1]$ dgmgrl / DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Jan 18 23:15:18 2023 Version 19.16.0.0.0  Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.  Welcome to DGMGRL, type "help" for information. Connected to "PROD" Connected as SYSDG.  DGMGRL> edit database sbdb set property ApplyLagThreshold=0; Property "applylagthreshold" updated DGMGRL> edit database sbdb set property TransportLagThreshold=0; Property "transportlagthreshold" updated

 

DGMGRL> show database verbose sbdb  Database - sbdb   Role:               SNAPSHOT STANDBY   Transport Lag:      0 seconds (computed 1 second ago)   Apply Lag:          60 minutes 16 seconds (computed 1 second ago)   Instance(s):     PROD    Properties:     DGConnectIdentifier             = 'sbdb'     ObserverConnectIdentifier       = ''     FastStartFailoverTarget         = ''     PreferredObserverHosts          = ''     LogShipping                     = 'ON'     RedoRoutes                      = ''     LogXptMode                      = 'ASYNC'     DelayMins                       = '0'     Binding                         = 'optional'     MaxFailure                      = '0'     ReopenSecs                      = '300'     NetTimeout                      = '30'     RedoCompression                 = 'DISABLE'     PreferredApplyInstance          = ''     ApplyInstanceTimeout            = '0'     ApplyLagThreshold               = '0'     TransportLagThreshold           = '0'     TransportDisconnectedThreshold  = '30'     ApplyParallel                   = 'AUTO'     ApplyInstances                  = '0'     StandbyFileManagement           = ''     ArchiveLagTarget                = '0'     LogArchiveMaxProcesses          = '0'     LogArchiveMinSucceedDest        = '0'     DataGuardSyncLatency            = '0'     LogArchiveTrace                 = '0'     LogArchiveFormat                = ''     DbFileNameConvert               = ''     LogFileNameConvert              = ''     ArchiveLocation                 = ''     AlternateLocation               = ''     StandbyArchiveLocation          = ''     StandbyAlternateLocation        = ''     InconsistentProperties          = '(monitor)'     InconsistentLogXptProps         = '(monitor)'     LogXptStatus                    = '(monitor)'     SendQEntries                    = '(monitor)'     RecvQEntries                    = '(monitor)'     HostName                        = 'az-psdvdb1'     StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=SBDB_DGMGRL)(INSTANCE_NAME=PROD)(SERVER=DEDICATED)))'     TopWaitEvents                   = '(monitor)'     SidName                         = '(monitor)'    Log file locations:     Alert log               : /u01/oracle/diag/rdbms/sbdb/PROD/trace/alert_PROD.log     Data Guard Broker log   : /u01/oracle/diag/rdbms/sbdb/PROD/trace/drcPROD.log  Database Status: SUCCESS  DGMGRL>

 

So, we can resolve  the warning by setting ApplyLagThreshold and TransportLagThreshold to Zero .          

How to fix AHF-00074: Perl Modules not found : Data::Dumper

 

While installing Autonomous Health Framework (AHF) on Oracle Linux 7 in azure vm, we faced the below error

[root@az-psdvdb1 u01]# ./ahf_setup   AHF Installer for Platform Linux Architecture x86_64   AHF Installation Log : /tmp/ahf_install_223200_6496_2023_01_11-23_33_23.log   Starting Autonomous Health Framework (AHF) Installation   AHF Version: 22.3.2 Build Date: 202212190711   PERL : /bin/perl   [ERROR] : AHF-00074: Perl Modules not found :  Data::Dumper

 

After going through the installation log mentioned above , it was found the system couldn’t find perl-Data-Dumper in the server.

To verify it,

[root@az-psdvdb1 u01]# rpm -q perl-Data-Dumper package perl-Data-Dumper is not installed

 

Also, tried finding the rpm, but looks like it could only find the missing perl module in existing installed oracle home.

[root@az-psdvdb1 u01]# find / -name Dumper.pm /u01/oracle/19.3.0/perl/lib/5.28.1/x86_64-linux-thread-multi/Data/Dumper.pm

 

So , let’s Install perl-Data-Dumper

[root@az-psdvdb1 u01]# yum install perl-Data-Dumper Loaded plugins: langpacks, ulninfo ksplice-uptrack                                                                                  | 2.9 kB  00:00:00 ol7_UEKR6                                                                                        | 3.0 kB  00:00:00 ol7_addons                                                                                       | 3.0 kB  00:00:00 ol7_epel                                                                                         | 3.6 kB  00:00:00 ol7_latest                                                                                       | 3.6 kB  00:00:00 ol7_optional_latest                                                                              | 3.0 kB  00:00:00 packages-microsoft-com-prod                                                                      | 3.0 kB  00:00:00 (1/2): ol7_epel/x86_64/updateinfo                                                                | 830 kB  00:00:00 (2/2): ol7_epel/x86_64/primary_db                                                                |  16 MB  00:00:00 Resolving Dependencies --> Running transaction check ---> Package perl-Data-Dumper.x86_64 0:2.145-3.el7 will be installed --> Finished Dependency Resolution   Dependencies Resolved   ========================================================================================================================  Package                          Arch                   Version                       Repository                  Size ======================================================================================================================== Installing:  perl-Data-Dumper                 x86_64                 2.145-3.el7                   ol7_latest                  47 k   Transaction Summary ======================================================================================================================== Install  1 Package   Total download size: 47 k Installed size: 97 k Is this ok [y/d/N]: y Downloading packages: perl-Data-Dumper-2.145-3.el7.x86_64.rpm                                                          |  47 kB  00:00:00 Running transaction check Running transaction test Transaction test succeeded Running transaction   Installing : perl-Data-Dumper-2.145-3.el7.x86_64                                                                  1/1   Verifying  : perl-Data-Dumper-2.145-3.el7.x86_64                                                                  1/1   Installed:   perl-Data-Dumper.x86_64 0:2.145-3.el7   Complete! [root@az-psdvdb1 u01]#

 

Now lets check the module again

[root@az-psdvdb1 u01]# find / -name Dumper.pm /usr/lib64/perl5/vendor_perl/Data/Dumper.pm /u01/oracle/19.3.0/perl/lib/5.28.1/x86_64-linux-thread-multi/Data/Dumper.pm

 

Now lets retry the installation

[oracle@az-psdvdb1 u01]$ ./ahf_setup   AHF Installer for Platform Linux Architecture x86_64   AHF Installation Log : /tmp/ahf_install_223200_29632_2023_01_12-00_28_15.log   Starting Autonomous Health Framework (AHF) Installation   AHF Version: 22.3.2 Build Date: 202212190711   Default AHF Location : /home/oracle/oracle.ahf   Do you want to install AHF at [/home/oracle/oracle.ahf] ? [Y]|N : n …. …

 

So this resolved the issue.

 

ORA-38781: cannot disable media recovery - have guaranteed restore points

 Scenario:- While trying to disable archivelog, following error is reported.

ORA-38781: cannot disable media recovery - have guaranteed restore points

Solution:-

Current environment details :-

OS	OEL 6.9
DB SID	Ora12c
DB Version	12.1.0.2 EE

SQL> alter database noarchivelog;

alter database noarchivelog

*

ERROR at line 1:

ORA-38781: cannot disable media recovery - have guaranteed restore points

Lets see what oerr utility says about this ora error.

[oracle@server3 u01]$ oerr ora 38781

38781, 00000, "cannot disable media recovery - have guaranteed restore points"

// *Cause: An attempt was made to disable media recovery while there is at

//         least one guaranteed restore point.

// *Action: Drop all guaranteed restore points and then disable media recovery.

SQL> select open_mode from v$database;

OPEN_MODE

--------------------

MOUNTED

SQL> alter database open;

Database altered.

Check the restore points in database.

SQL> select GUARANTEE_FLASHBACK_DATABASE,NAME ,TIME from v$restore_point;

GUARANTEE_FLASHBACK_DATABASE   NAME                           TIME

------------------------------ ------------------------------ ---------------------------------------------

YES                            TESTING                        14-SEP-20 08.56.24.000000000 AM

Check the status of flashback.

SQL> select name,flashback_on from v$database;

NAME                           FLASHBACK_ON

------------------------------ ------------------

ORA12C                         RESTORE POINT ONLY

Now drop the restore point and try disabling the archivelog.

SQL> drop restore point TESTING;

Restore point dropped.

SQL> select name,flashback_on from v$database;

NAME                           FLASHBACK_ON

------------------------------ ------------------

ORA12C                         NO

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

SQL> startup mount

ORACLE instance started.

Total System Global Area  629145600 bytes

Fixed Size                  2927528 bytes

Variable Size             482346072 bytes

Database Buffers          138412032 bytes

Redo Buffers                5459968 bytes

Database mounted.

SQL> alter database noarchivelog;

Database altered.

SQL> select log_mode from v$database;

LOG_MODE

------------

NOARCHIVELOG

ORA-16698 member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

In primary DATABASE:-

Primary DB:- orclcdb

Standby DB:- orclstd

oracle@primary flashback]$ dgmgrl sys/admin123#@orclcdb

DGMGRL for Linux: Release 19.0.0.0.0 - Production on Mon Apr 13 19:05:31 2020

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.

Connected to "orclcdb"

Connected as SYSDBA.

DGMGRL> CREATE CONFIGURATION my_dg_config AS PRIMARY DATABASE IS orclcdb CONNECT IDENTIFIER IS orclcdb;

ORA-01466: unable to read data - table definition has changed

ORA-01466: unable to read data - table definition has changed

01466, 00000, "unable to read data - table definition has changed"
// *Cause: Query parsed after tbl (or index) change, and executed
//         w/old snapshot
// *Action: commit (or rollback) transaction, and re-execute

While selecing a table for a specific point of time i faced the error.

15:23:07 SQL> SELECT * FROM soumya.test2 AS OF TIMESTAMP TO_TIMESTAMP('2014-10-14 15:22:28' , 'YYYY-MM-DD HH24:MI:SS');
SELECT * FROM soumya.test2 AS OF TIMESTAMP TO_TIMESTAMP('2014-10-14 15:22:28' , 'YYYY-MM-DD HH24:MI:SS')
                     *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed


Reason:- There could be few reasons behind it.
1. DDLs that alter the structure of a table (such as drop/modify column, move table, drop partition, truncate table/partition, and add constraint) invalidate any existing undo data for
the table. If you try to retrieve data from a time before such a DDL executed, error ORA-01466 occurs.

2.You need to have the time of your client (where you run sqlplus) set to a later (or same) value than the time of your database server.Else such error could generate.
3. This could be caused by a long running snapshot. Try committing or rolling-back all outstanding transactions and try again.
4.It also could happen if the table is newly created .


Please share your ideas and opinions about this topic.

If you like this post, then please share it with others.
Please subscribe on email for every updates on mail.

expdp throwing ORA-04031 (stream pool) error

Summary:-
Today while taking backup of a schema i faced a problem in expdp and the job was not done
successfully.Here is the error
UDE-04031: operation generated ORACLE error 4031
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select obj#,type#,ctime,mtim...","SQLA","tmp")
ORA-06512: at "SYS.KUPV$FT_INT", line 2904
ORA-06512: at "SYS.KUPC$QUE_INT", line 572
ORA-25254: time-out in LISTEN while waiting for a message
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3263
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4488
ORA-06512: at line 1

Solution:-
Datapump jobs (either expdp/impdp), when initiated, will use buffer queues in order to
transfer the data directly from the master table to the dump file. Instead of creating new
buffer queue, datapump operations will try to use the existing queues in stream pool memory
area.

So i increased the memory_max_size and resolved the error.
Previously it was having sga_max_target=396M and hence was not under Automatic memory management
feature(11g).
so i created a pfile from the spfile.
And change the value in pfile like this
SQL> create pfile from spfile;
$ vi initprim.ora
#*.sga_max_size=629145600
*.memory_target=629145600


#Here we enabled memory_target parameter by removing sga_max_size from the pfile.This will let
oracle automatically tune the sga and pga size.

SQL> startup mount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initprim.ora';
SQL> alter database open;
SQL> create spfile from pfile;

ORA-00054: resource busy and acquire with NOWAIT specified

ORA-00054: resource busy and acquire with NOWAIT specified
Cause: The NOWAIT keyword forced a return to the command prompt
because a resource was unavailable for a LOCK TABLE or SELECT FOR
UPDATE command.
Action: Try the command after a few minutes or enter the command without
the NOWAIT keyword.


Example:
SQL> alter table emp add (mobile varchar2(15));
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified


How to avoid the ORA-00054:
    - Execute DDL at off-peak hours, when database is idle.
    - Execute DDL in maintenance window.
    - Find and Kill the session that is preventing the exclusive lock.


Other Solutions:

Solution 1:
In Oracle 11g you can set ddl_lock_timeout i.e. allow DDL to wait for the object to
become available, simply specify how long you would like it to wait:

SQL> alter session set ddl_lock_timeout = 600;
Session altered.

SQL> alter table emp add (mobile varchar2(15));
Table altered.


Solution 2:
Also In 11g, you can mark your table as read-only to prevent DML:
SQL> alter table emp read only;
Session altered.

SQL> alter table emp add (mobile varchar2(15));
Table altered.


Solution 3 (for 10g):
DECLARE
 MYSQL VARCHAR2(250) := 'alter table emp add (mobile varchar2(15))';
 IN_USE_EXCEPTION EXCEPTION;
 PRAGMA EXCEPTION_INIT(IN_USE_EXCEPTION, -54);
BEGIN
 WHILE TRUE LOOP
  BEGIN
   EXECUTE IMMEDIATE MYSQL;
   EXIT;
  EXCEPTION
   WHEN IN_USE_EXCEPTION THEN
    NULL;
  END;
  DBMS_LOCK.SLEEP(1);
 END LOOP;
END;


Solution 4:

Step 1: Identify the session which is locking the object
select a.sid, a.serial#
from v$session a, v$locked_object b, dba_objects c
where b.object_id = c.object_id
and a.sid = b.session_id
and OBJECT_NAME='EMP';


Step 2: kill that session using
alter system kill session 'sid,serial#' immediate;

ORA-00027 cannot kill current session

How to kill session:
1. identify which session to kill using following query:
SQL>  select
  s.sid,
  s.serial#,
  spid,
  trim(s.machine) machine,
  trim(s.module) module,
  status
 from
  v$session s,
  v$process p
 where
  paddr=addr
  and module is not null
 order by 1,2

      SID    SERIAL# SPID                 MACHINE              MODULE                                           STATUS
---------- ---------- -------------------- -------------------- ------------------------------------------------ --------------------
         1          5 2311                 server1.soumya.com   sqlplus@server1.soumya.com (TNS V1-V3)           ACTIVE
        29          1 2323                 server1.soumya.com   STREAMS                                          ACTIVE
        35         14 2457                 server1.soumya.com   sqlplus@server1.soumya.com (TNS V1-V3)           INACTIVE
        37         16 2450                 server1.soumya.com   KTSJ                                             ACTIVE
        40          2 2359                 server1.soumya.com   STREAMS                                          ACTIVE
        44         27 2448                 server1.soumya.com   KTSJ                                             ACTIVE
        47          2 2361                 server1.soumya.com   STREAMS                                          ACTIVE

2. Killing a session
SQL> ALTER SYSTEM KILL SESSION '35,14' immediate;

System altered.

ORA-00018 maximum number of sessions exceeded

ORA-00018 maximum number of sessions exceeded

Cause: All session state objects are in use.
Action: Increase the value of the SESSIONS initialization parameter.

How to increase PROCESSES initialization parameter:

1.Login as sysdba
sqlplus / as sysdba

2. Check Current Setting of Parameters:-

SQL> show parameter sessions

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size           integer     0
java_soft_sessionspace_limit         integer     0
license_max_sessions                 integer     0
license_sessions_warning             integer     0
sessions                             integer     247
shared_server_sessions               integer


SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     0
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     1000
log_archive_max_processes            integer     4
processes                            integer     150

SQL> show parameter transactions

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
transactions                         integer     271
transactions_per_rollback_segment    integer     5

3.If you are planning to increase "sessions" parameter you should also plan to increase
"processes and "transactions" parameters.

A basic formula for determining  these parameter values is as follows:
  processes=x
  sessions=x*1.1+5
  transactions=sessions*1.1

4. These paramters can't be modified in memory. You have to modify the spfile only
(scope=spfile) and restart the instance.
SQL> alter system set processes=400 scope=spfile;

System altered.

SQL> alter system set sessions=445 scope=spfile;

System altered.

SQL> alter system set transactions=490 scope=spfile;

System altered.


Done..

ORA-00000: normal, successful completion

ORA-00000: normal, successful completion

This error may occur in many circumstances you need to check environment variables:
$ORACLE_HOME
$ORACLE_BASE
$ORACLE_SID
also check /etc/hosts file it must contains correct hostname and ip.
example:

[oracle@oel6 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 1 9:12:17 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected.
SQL> startup;
ORA-01012: not logged on
SQL> shutdown immediate;
ORA-00000: normal, successful completion
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup;
ORA-00000: normal, successful completion


After that i checked environment variables:
[oracle@server1 ~]$ echo $ORACLE_BASE
/u01/app/oracle
[oracle@server1 ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/db_1
[oracle@server1 ~]$ echo $ORACLE_SID
prim

Then i checked /etc/hosts
cat /etc/hosts
#192.168.2.102   server1.soumya.com      server1

So i found the issue. The hostname and ip was commented inside /etc/hosts file.

Then i started up the database and everything was right.
SQL> startup
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             331352056 bytes
Database Buffers           75497472 bytes
Redo Buffers                4308992 bytes
Database mounted.
Database opened.


P.S.Generally ORA-00000 comes under "Oracle Database Server Messages". These messages are generated by the Oracle database server when running any Oracle program.

ORA-28002: the password will expire within 7 days

ORA-28002: the password will expire within 7 days

Cause: The user's account is about to about to expire within 7days  and the password needs
to be changed.
Action: Change the password.

Solutions:-
sqlplus soumya/soumya
SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 2 08:52:52 2015
ERROR:
ORA-28002: the password will expire within 7 days
1. Simply change the password to avoid it temporary :-

SQL> password
Changing password for SOUMYA
Old password:
New password:
Retype new password:
Password changed

2. Set PASSWORD_LIFE_TIME of the profile assigned user to UNLIMITED then change the password to avoid it permanently:-

SQL> SELECT PROFILE FROM dba_users WHERE username = 'SOUMYA';

PROFILE
------------------------------
DEFAULT

SQL> SELECT  LIMIT FROM DBA_PROFILES WHERE PROFILE='DEFAULT' AND RESOURCE_NAME='PASSWORD_LIFE_TIME';

LIMIT
----------------------------------------

60


SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Profile altered.


SQL> SELECT  LIMIT FROM DBA_PROFILES WHERE PROFILE='DEFAULT' AND RESOURCE_NAME='PASSWORD_LIFE_TIME';

LIMIT
----------------------------------------
UNLIMITED

You may notice that even after setting the password expiry to unlimited you are still getting the “ERROR: ORA-28002: the password will expire” message. I’m guessing this is due to additional processes that Oracle does in the background for checking password age etc. So this forces us to “reset” the password to it’s current value to remove the error.

SQL> alter user SYSTEM identified by "password";(PASSWORD OF SOUMYA)

User altered.

ORA-00845: MEMORY_TARGET not supported on this system

ORA-00845: MEMORY_TARGET not supported on this system
Problem Description:-
While creating a startup database using dbca the database creation GUI gives error message in a pop up window,
ORA-00845: MEMORY_TARGET not supported on this system
from where you can ignore the error message.
The similar scenario also occurs whenever you try to start your database using startup command, shows error message like below.

SQL> STARTUP
ORA-00845: MEMORY_TARGET not supported on this system

Cause of the Problem
•Starting from Oracle 11g the automatic memory management feature is now defined with parameter MEMORY_TARGET and MEMORY_MAX_TARGET.

•On linux file system the shared memory need to be mounted on /dev/shm directory on the operating system.

•And the size of /dev/shm needs to be greater than MEMORY_TARGET or MEMMORY_MAX_TARGET.

•The AMM (Automatic Memory Management) now in 11g manages both SGA and PGA together by MMAN process.

•The MEMORY_TARGET parameter in 11g comes for (SGA_TARGET+PGA_AGGREGATE_TARGET) which was in 10g.

•And MEMORY_MAX_TARGET parameter in 11g comes instead of SGA_MAX_TARGET parameter which was in 10g.

•The ORA-00845:can arises for the following two reasons on linux system.

1)If the shared memory which is mapped to /dev/shm directory is less than the size of MEMORY_TARGET or MEMORY_MAX_TARGET.
or,
2)If the shared memory is not mapped to /dev/shm directory.

olution of the Problem
Make sure /dev/shm is properly mounted. You can see it by,
#df -h or df -Th
The output should be similar like
$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda2             8.7G  4.7G  3.6G  57% /
shmfs                 1G    512M 512M  50% /dev/shm


We see here for /dev/shm we have assigned 1G memory. Now if you set MEMORY_TARGET more than 1G then above ORA-845 will arise. For example if you have MEMORY_TARGET or MEMORY_MAX_TARGET set to 12G then you can mount shared memory to 13g like below.
As a root user,
# mount -t tmpfs shmfs -o size=20g /dev/shm
In order to make the settings persistence so that it will affect after restarting machine add an entry in /etc/fstab similar to the following:
vi /etc/fstab
shmfs /dev/shm tmpfs size=20g 0 0

:wq

#alter system set memory_target=20G scope=spfile;
and then bounce the database to affect the changes.