How resolve Error ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET

ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET

[oracle@localhost ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 6 21:43:27 2018
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET
SQL>
SQL> create pfile='/tmp/db.ora' from spfile;

File created.

SQL> exit
Disconnected
[oracle@localhost ~]$

Change memory_max_target,memory_target parameter in pfile

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ducatXDB)'
*.memory_max_target=1000M
*.memory_target=1000M
*.open_cursors=300
*.os_authent_prefix='OPS$'

start instance using pfile 

[oracle@localhost ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 6 21:46:36 2018

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

Connected to an idle instance.

SQL> startup pfile=/tmp/db.ora;
ORACLE instance started.

Total System Global Area 1043886080 bytes
Fixed Size                  2259840 bytes
Variable Size             654312576 bytes
Database Buffers          381681664 bytes
Redo Buffers                5632000 bytes
Database mounted.
Database opened.

SQL> show parameter mem

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 1000M
memory_target                        big integer 1000M
shared_memory_address                integer     0
SQL>
SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 1000M
sga_target                           big integer 0
SQL>
SQL>

Create spfile from pfile 

SQL> create spfile from pfile='/tmp/db.ora';

File created.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1043886080 bytes
Fixed Size                  2259840 bytes
Variable Size             654312576 bytes
Database Buffers          381681664 bytes
Redo Buffers                5632000 bytes
Database mounted.
Database opened.
SQL> show parameter target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     0
db_flashback_retention_target        integer     4320
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
memory_max_target                    big integer 1000M
memory_target                        big integer 1000M
parallel_servers_target              integer     16
pga_aggregate_target                 big integer 0
sga_target                           big integer 0
SQL>
SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
DUCAT     READ WRITE


SQL>

How resolve Error ORA-00845: MEMORY_TARGET not supported on this system

ORA-00845: MEMORY_TARGET not supported on this system

This error happen when system is running out of temporary storage. While starting up the database oracle uses /dev/shm to store temporary files but when it runs out of memory you will get this error MEMORY_TARGET not supported on this system.

In My case:

MEMORY_TARGET=1000M  and /dev/shm  is 584M


[oracle@localhost ~]$
[oracle@localhost ~]$
[oracle@localhost ~]$ sqlplus "/as sysdba"
star
SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 6 22:14:11 2018

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

tup
Connected to an idle instance.

SQL> ORA-00845: MEMORY_TARGET not supported on this system
SQL> exit
Disconnected
[oracle@localhost ~]$
[oracle@localhost ~]$ df -h
Filesystem                    Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root   26G   24G  1.5G  95% /
tmpfs                         584M   72K  584M   1% /dev/shm
/dev/sda1                     477M   55M  397M  13% /boot
[oracle@localhost ~]$
[oracle@localhost ~]$



[root@localhost ~]#
[root@localhost ~]# df -h
Filesystem                    Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root   26G   24G  1.5G  95% /
tmpfs                         584M   72K  584M   1% /dev/shm
/dev/sda1                     477M   55M  397M  13% /boot
[root@localhost ~]#

To increase the size 


[root@localhost ~]# mount -o remount,size=1G /dev/shm
[root@localhost ~]#

Verify the size

[root@localhost ~]# df -h
Filesystem                    Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root   26G   24G  1.5G  95% /
tmpfs                         1.0G   72K  1.0G   1% /dev/shm
/dev/sda1                     477M   55M  397M  13% /boot
[root@localhost ~]#


To make permanent changes to your file system update your fstab file


[root@localhost ~]# cat /etc/fstab

#
# /etc/fstab
# Created by anaconda on Tue Dec 19 18:20:28 2017
#
# Accessible filesystems, by reference, are maintained under '/dev/disk'
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info
#
/dev/mapper/VolGroup-lv_root /                       ext4    defaults        1 1
UUID=a0d50c7a-876c-4d10-ae50-3caf8cbe182a /boot                   ext4    defaults        1 2
/dev/mapper/VolGroup-lv_swap swap                    swap    defaults        0 0
tmpfs                   /dev/shm                tmpfs   defaults        0 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0
[root@localhost ~]#



[root@localhost ~]# cat /etc/fstab

#
# /etc/fstab
# Created by anaconda on Tue Dec 19 18:20:28 2017
#
# Accessible filesystems, by reference, are maintained under '/dev/disk'
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info
#
/dev/mapper/VolGroup-lv_root /                       ext4    defaults        1 1
UUID=a0d50c7a-876c-4d10-ae50-3caf8cbe182a /boot                   ext4    defaults        1 2
/dev/mapper/VolGroup-lv_swap swap                    swap    defaults        0 0
tmpfs                   /dev/shm                tmpfs   defaults,size=1G        0 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0
[root@localhost ~]#


Update the new fstab file ,mount -a command to immediate mount all disk defined in /etc/fstab file

root@localhost ~]#
[root@localhost ~]# mount -a
[root@localhost ~]#
[root@localhost ~]#



[oracle@localhost ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 6 22:35:35 2018

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1043886080 bytes
Fixed Size                  2259840 bytes
Variable Size             654312576 bytes
Database Buffers          381681664 bytes
Redo Buffers                5632000 bytes
Database mounted.
Database opened.
SQL> show parameter target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_max_target                    big integer 1000M
memory_target                        big integer 1000M
parallel_servers_target              integer     16
pga_aggregate_target                 big integer 0
sga_target                           big integer 0
SQL>



verify /dev/shm after reboot OS:

[oracle@localhost ~]$
[oracle@localhost ~]$ df -h
Filesystem                    Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root   26G   24G  1.5G  95% /
tmpfs                         1.0G     0  1.0G   0% /dev/shm
/dev/sda1                     477M   55M  397M  13% /boot
[oracle@localhost ~]$

Recover database using Drop offline non-system datafile : ORA-01157: cannot identify/lock data file string - see DBWR trace file

Recover database  non-system datafile if datafile associated with tablespace do not contain important segments, that can be dropped offline - 

ORA-01157: cannot identify/lock data file string - see DBWR trace file

 Database Writer (DBWR) is unable to find and lock a Datafile. This may be due to various reasons like 

• Datafile is deleted or corrupt
• Datafile is renamed or moved 
• Mount point is incorrect
• Issues with Read/write permission on Datafile

To solve ORA-01157 we can use one of the following steps:

• If datafile is deleted or corrupt and is not of TEMP or UNDO tablespace then we need to recovered it by using a valid backup.
• If datafile is deleted or corrupt and is not of TEMP or UNDO tablespace but that tablespace do not contain important segments, that can be dropped offline
• If datafile is renamed or moved then we need to get it in its original position
• If Mount point is incorrect, simply recreate the mount point
• if it is due to permission then we need to grant the permission at OS level

SQL> column TABLESPACE_NAME for a15
SQL> column FILE_NAME for a50
SQL> set lines 200
SQL> column TABLESPACE_NAME for a15
column FILE_NAME for a50
set lines 200
select HXFIL File_num,substr(HXFNM,1,40) File_name,FHTYP Type,HXERR Validity,
FHSCN CHK, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence from X$KCVFH;SQL> SQL> SQL>   2

  FILE_NUM FILE_NAME                                                TYPE   VALIDITY CHK              TABLESPACE_NAME     STATUS   SEQUENCE
---------- -------------------------------------------------- ---------- ---------- ---------------- --------------- ---------- ----------
         1 /u01/app/oracle/oradata/ducat/system01.d                    3          0 1874371          SYSTEM                8196         30
         2 /u01/app/oracle/oradata/ducat/sysaux01.d                    3          0 1874371          SYSAUX                   4         30
         3 /u01/app/oracle/oradata/ducat/undotbs01.                    3          0 1874371          UNDOTBS1                 4         30
         4 /u01/app/oracle/oradata/ducat/users01.db                    3          0 1874371          USERS                    4         30
         5 /u01/app/oracle/oradata/ducat/example01.                    3          0 1874371          EXAMPLE                  4         30
         6 /u01/app/oracle/oradata/ducat/test01.dbf                    3          0 1874371          TEST                     4         30
         7 /u01/app/oracle/oradata/ducat/fda01.dbf                     3          0 1874371          FDA                      4         30
         8 /u01/app/oracle/oradata/ducat/test02.dbf                    3          0 1874371          TEST                     4         30

8 rows selected.

SQL>
SQL>
SQL> select hxfil FILENUMBER, fhsta STATUS, fhscn SCN, fhrba_seq SEQUENCE from x$kcvfh;

FILENUMBER     STATUS SCN                SEQUENCE
---------- ---------- ---------------- ----------
         1       8196 1874371                  30
         2          4 1874371                  30
         3          4 1874371                  30
         4          4 1874371                  30
         5          4 1874371                  30
         6          4 1874371                  30
         7          4 1874371                  30
         8          4 1874371                  30

8 rows selected.

SQL>
SQL>
SQL> !rm /u01/app/oracle/oradata/ducat/test02.dbf

SQL> !ls -lrt /u01/app/oracle/oradata/ducat/test02.dbf
ls: cannot access /u01/app/oracle/oradata/ducat/test02.dbf: No such file or directory

SQL>
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1         28   52428800        512          1 YES INACTIVE               1853792 03-FEB-18      1853797 03-FEB-18
         2          1         29   52428800        512          1 YES INACTIVE               1853797 03-FEB-18      1873800 03-FEB-18
         3          1         30   52428800        512          1 NO  CURRENT                1873800 03-FEB-18   2.8147E+14

SQL>
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /
alter system switch logfile
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 4775
Session ID: 1 Serial number: 5


SQL>



SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: '/u01/app/oracle/oradata/ducat/test02.dbf'


SQL>



In Alert log


alter database open
Sat Feb 03 23:48:41 2018
Errors in file /u01/app/oracle/diag/rdbms/ducat/ducat/trace/ducat_dbw0_4918.trc:
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: '/u01/app/oracle/oradata/ducat/test02.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/ducat/ducat/trace/ducat_ora_4987.trc:
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: '/u01/app/oracle/oradata/ducat/test02.dbf'
ORA-1157 signalled during: alter database open...




SQL>
SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
         8 ONLINE  ONLINE  FILE NOT FOUND                                                             0

SQL>


SQL> column TABLESPACE_NAME for a15
column FILE_NAME for a50
set lines 200
select HXFIL File_num,substr(HXFNM,1,40) File_name,FHTYP Type,HXERR Validity,
FHSCN CHK, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence from X$KCVFH;SQL> SQL> SQL>   2

  FILE_NUM FILE_NAME                                                TYPE   VALIDITY CHK              TABLESPACE_NAME     STATUS   SEQUENCE
---------- -------------------------------------------------- ---------- ---------- ---------------- --------------- ---------- ----------
         1 /u01/app/oracle/oradata/ducat/system01.d                    3          0 1874921          SYSTEM                8196         31
         2 /u01/app/oracle/oradata/ducat/sysaux01.d                    3          0 1874921          SYSAUX                   4         31
         3 /u01/app/oracle/oradata/ducat/undotbs01.                    3          0 1874921          UNDOTBS1                 4         31
         4 /u01/app/oracle/oradata/ducat/users01.db                    3          0 1874921          USERS                    4         31
         5 /u01/app/oracle/oradata/ducat/example01.                    3          0 1874921          EXAMPLE                  4         31
         6 /u01/app/oracle/oradata/ducat/test01.dbf                    3          0 1874921          TEST                     4         31
         7 /u01/app/oracle/oradata/ducat/fda01.dbf                     3          0 1874921          FDA                      4         31
         8                                                             0          4 0                                         0          0

8 rows selected.

SQL>



SQL> select name, recover, fuzzy, checkpoint_change# from v$datafile_header;

NAME                                               REC FUZ CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------------------
/u01/app/oracle/oradata/ducat/system01.dbf         NO  YES            1874921
/u01/app/oracle/oradata/ducat/sysaux01.dbf         NO  YES            1874921
/u01/app/oracle/oradata/ducat/undotbs01.dbf        NO  YES            1874921
/u01/app/oracle/oradata/ducat/users01.dbf          NO  YES            1874921
/u01/app/oracle/oradata/ducat/example01.dbf        NO  YES            1874921
/u01/app/oracle/oradata/ducat/test01.dbf           NO  YES            1874921
/u01/app/oracle/oradata/ducat/fda01.dbf            NO  YES            1874921
                                                                            0

8 rows selected.


SQL>
SQL> select NAME,STATUS from v$datafile;

NAME                                               STATUS
-------------------------------------------------- -------
/u01/app/oracle/oradata/ducat/system01.dbf         SYSTEM
/u01/app/oracle/oradata/ducat/sysaux01.dbf         ONLINE
/u01/app/oracle/oradata/ducat/undotbs01.dbf        ONLINE
/u01/app/oracle/oradata/ducat/users01.dbf          ONLINE
/u01/app/oracle/oradata/ducat/example01.dbf        ONLINE
/u01/app/oracle/oradata/ducat/test01.dbf           ONLINE
/u01/app/oracle/oradata/ducat/fda01.dbf            ONLINE
/u01/app/oracle/oradata/ducat/test02.dbf           ONLINE

8 rows selected.

SQL>


SQL>  alter database datafile 8 offline drop;

Database altered.

SQL> alter database open;

Database altered.

SQL> select name,open_mode,log_mode from v$database;

NAME                                               OPEN_MODE            LOG_MODE
-------------------------------------------------- -------------------- ------------
DUCAT                                              READ WRITE           NOARCHIVELOG

SQL>


*In this case we loss data related to Tablespace/datafile .





Using RMAN Recovery advisor:

[oracle@localhost ~]$
[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Feb 3 23:30:52 2018

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

connected to target database: DUCAT (DBID=3782973220, not open)

RMAN> list failure;

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
5          HIGH     OPEN      03-FEB-18     One or more non-system datafiles are missing

RMAN> list failure detail;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
5          HIGH     OPEN      03-FEB-18     One or more non-system datafiles are missing
  Impact: See impact for individual child failures
  List of child failures for parent failure ID 5
  Failure ID Priority Status    Time Detected Summary
  ---------- -------- --------- ------------- -------
  2434       HIGH     OPEN      03-FEB-18     Datafile 8: '/u01/app/oracle/oradata/ducat/test02.dbf' is missing
    Impact: Some objects in tablespace TEST might be unavailable

RMAN>

RMAN> advise failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
5          HIGH     OPEN      03-FEB-18     One or more non-system datafiles are missing
  Impact: See impact for individual child failures
  List of child failures for parent failure ID 5
  Failure ID Priority Status    Time Detected Summary
  ---------- -------- --------- ------------- -------
  2434       HIGH     OPEN      03-FEB-18     Datafile 8: '/u01/app/oracle/oradata/ducat/test02.dbf' is missing
    Impact: Some objects in tablespace TEST might be unavailable

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
1. If file /u01/app/oracle/oradata/ducat/test02.dbf was unintentionally renamed or moved, restore it
2. If you have an export of tablespace TEST, offline its data files, open the database read/write, then drop and re-create the tablespace and import the data.
3. Contact Oracle Support Services if the preceding recommendations cannot be used, or if they do not fix the failures selected for repair

Optional Manual Actions
=======================
no manual actions available

Automated Repair Options
========================
no automatic repair options available

RMAN>

Oracle DBA Roles and Responsibilities

Who is ORACLE DBA ?

Oracle Database administrator is the cumulative role of multiple responsibilities Oracle DBA can play as Application support in development or may play as system analysis with system administrator or network administrator. Oracle DBA has capability to plan capacity management with storage administrator etc.


Prerequisite 

• Basic SQL
• Basic Linux/Unix OS knowledge

Database Administrators (DBAs) are responsible for :-

• Installation, configuration and maintain Oracle database software 
• Upgrading ,migration and Patch(fix bug) oracle database software
• Troubleshooting problems regarding the databases alert, applications and development tools.
• Maintain sound backup and recovery policies and procedures to make database available 24*7
• Take care of the Database design and implementation
• Implement and maintain strong database security policy (create and maintain users and roles, assign privileges)
• Perform database tuning,Oracle instance components including SQL and PL/SQL, and performance monitoring and database maintenance 
• Perform schema refresh non-prod with production environments 
• Plan database growth and changes (capacity planning)
• Perform general technical trouble shooting and give consultation to development teams
• Work with Oracle Corporation for technical support
• Setup and maintain documentation and standards for future purpose
• Deployment of strong disaster recovery plans as per SLA (service level agreement)

DBA Skills Required

• Good understanding of the Oracle database, related utilities and tools
• Good understanding of the underlying operating system
• Good knowledge of the physical database design
• Ability to perform both Oracle and operating system performance tuning and monitoring
• Knowledge of Oracle backup and recovery scenarios
• Good knowledge of Oracle security management
• Sound knowledge of both database and system performance tuning
• DBA should have sound communication skills with management, development teams, vendors and systems administrators
• DBA should have the ability to handle multiple projects and deadlines
• DBA should possess a sound understanding of the business

Level of DBA's

• Junior DBA    :L1
• Mid-level DBA :L2
• Senior DBA    :L3

Popular database use in industry:

• Oracle
• MySql
• DB2
• Microsoft SQL Server
• PostgreSQL

Certification available 

• Oracle Certified Associate (OCA)
• Oracle Certified Professional (OCP)
• Oracle Certified Manager (OCM)

Daily Activity

1. Verify all instances are up & working
2. Look into new alert log entries and checking for any errors
3. Verify the success of the Database backup
4. Verify the success of the Database archiving to tape
5. Check table space usage
6. Checking if there is enough disk space for the data files to grow
7. Monitoring temporary segments usage
8. Checking for DB links that are inaccessible
9. Finding invalid objects and fixing them
10. Checking session locks
11. Monitoring overall Database health & performance
12. Identifying tables with chained rows & rectifying them
13. Monitoring users and transactions
14. Escalating issues and providing feedback and suggestions
15. Work on assigned

Weekly Activities

1. Listen users with objects in system table space
2. Listen users except Sys & System as a default table space
3. Listen disabled constraints, procedures, functions, packages & triggers
4. Listen foreign keys with non matching column definitions
5. Identify De-fragmentation in table space
6. Look for security policy violations
7. Look in SQL *Net logs for errors
8. Archive all alert logs, Trace files to history



Monthly Activities

1. Checking redo log optimal size based on transactional activities
2. Analyze tables and indexes for better performance
3. Identifying non-system tables with no primary key
4. Identifying non-system tables with excessive indexes causing performance issue
5. Listing unusable indexes and index usage
6. Preparing comprehensive monthly review and reports
7. Review tuning opportunities based on stats pack report

Quarterly Activities

1. Measuring Database growth summary
2. Backup verification on test database


Adhoc Activities

1. Creating project specific (objects, tables, indexes etc) in test and production schema
2. Applying user-creation and any other security policies as defined
3. Identifying and optimizing the queries causing bottlenecks
4. Scheduling jobs & monitoring
5. Participation in any database design discussion and providing feedback and reviews
6. Aligning the backup and recovery process according to the client requirements.