Oracle 12c Pluggable database (PDB) wise resource used query

 In Oracle 12c have multi tenant feature  i.e we can create multiple pluggable database and pluggable database used hardware resource (i.e memory) vary . Here given below different query we can find out  pdb wise resource.

set linesize 150
set pagesize 3000
set NUMWIDTH 15

col Parameter format a30
col component format a28
COLUMN DEFAULT_ATTR FORMAT A7
COLUMN OWNER FORMAT A15
COLUMN OBJECT_NAME FORMAT A15
COLUMN ALL_CONTAINERS FORMAT A3
COLUMN CONTAINER_NAME FORMAT A10
COLUMN CON_ID FORMAT 999
COLUMN pdb_name FORMAT A20
COLUMN memory Format A25
COLUMN spid HEADING 'OSpid' FORMAT a8
COLUMN pid HEADING 'Orapid' FORMAT 999999
COLUMN sid HEADING 'Sess id' FORMAT 99999
COLUMN serial# HEADING 'Serial#' FORMAT 999999
COLUMN status HEADING 'Status' FORMAT a8
COLUMN pga_alloc_mem HEADING 'PGA alloc' FORMAT 999,999,999
COLUMN pga_used_mem HEADING 'PGA used' FORMAT 999,999,999
COLUMN pga_max_mem HEADING 'PGA Max' FORMAT 999,999,999
COLUMN username HEADING 'oracleuser' FORMAT a12
COLUMN osuser HEADING 'OS user' FORMAT a12
COLUMN program HEADING 'Program' FORMAT a24
COLUMN Mbytes Heading 'Mbytes' FORMAT 999,999,999
COLUMN name FORMAT A22

----check pdb name , container Name and Id
select name, cdb, con_id from v$database;

--Information About Each Container (pluggable database ) Name
SELECT NAME, CON_ID, OPEN_MODE, RESTRICTED, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;

----PGA Memory over 10MB size
SELECT p.spid, s.sid, p.con_id, substr(n.name,1,25) memory, ROUND(s.value/1024/1024) as MBytes
FROM v$sesstat s, v$statname n, v$process p, v$session vs
WHERE s.statistic# = n.statistic#
AND n.name LIKE '%pga memory%'
AND s.sid=vs.sid
AND vs.paddr=p.addr
AND s.value > 10000000 ;                                                                      

-------List the large amount memory used process
SELECT pid, spid, con_id, substr(username,1,13) username, program, ROUND(pga_used_mem/1024/1024) pga_used, ROUND(pga_alloc_mem/1024/1024) pga_alloc, ROUND(pga_freeable_mem/1024/1024) pga_freeable, ROUND(pga_max_mem/1024/1024) pga_max
FROM v$process
WHERE pga_alloc_mem = (SELECT max(pga_alloc_mem)
                       FROM v$process
                       WHERE program NOT LIKE '%LGWR%');

-----Summation of ALL PGA based on v$process: allocated includes free PGA memory not yet ---released to the operating system by the server process

SELECT ROUND(SUM(pga_alloc_mem)/1024/1024) AS "Mbytes allocated", ROUND(SUM(PGA_USED_MEM)/1024/1024) AS "Mbytes used"
FROM v$process;

------Summation of each container PGA based on v$process:REM allocated includes free PGA ---memory not yet released to the operating system by the server process

compute sum of "Mbytes allocated" on report
break on report
SELECT con_id, ROUND(SUM(pga_alloc_mem)/1024/1024) AS "Mbytes allocated", ROUND(SUM(PGA_USED_MEM)/1024/1024) AS "Mbytes used"
FROM v$process
group by con_id
order by con_id;


--Summation of ALL PGA memory based on V$SESSTAT:

SELECT ROUND(SUM(value)/1024/1024) AS Mbytes
FROM v$sesstat s, v$statname n
WHERE n.STATISTIC# = s.STATISTIC#
AND n.name = 'session pga memory';

--Summation each container PGA memory based on V$SESSTAT:


compute sum of MBYTES on report
break on report
select con_id, ROUND(sum(bytes)/1024/1024) as MBYTES from (SELECT p.con_id, s.value as bytes
FROM v$sesstat s, v$statname n, v$process p, v$session vs
WHERE s.statistic# = n.statistic#
AND n.name = 'session pga memory'
AND s.sid=vs.sid
AND vs.paddr=p.addr)
group by con_id
order by con_id;

break on off


--PGA stats from V$PGASTAT:
--show max total pga allocated since instance startup

select name, ROUND(value/1024/1024) as Mbytes from v$pgastat
where name in ('maximum PGA allocated','aggregate PGA target parameter','aggregate PGA auto target');


--show max pga allocated from history
select * from (select name,SNAP_ID, ROUND(VALUE/1024/1024) Mbytes from CDB_HIST_PGASTAT
where name='maximum PGA allocated'
order by Mbytes desc,snap_id desc)
where rownum <11 br="">
----The example results below show the largest size a pga has reached is 24MB; the MMON process.
 
break on con_id skip 4
compute sum of pga_alloc_mem on con_id

SELECT p.con_id,
       p.spid,
       p.pid,
       s.sid,
       s.serial#,
       s.status,
       ROUND(p.pga_alloc_mem/1024/1024) as pga_alloc_mem,
       ROUND(p.pga_used_mem/1024/1024) as pga_used_mem,
       ROUND(p.PGA_MAX_MEM/1024/1024) as pga_max_mem,
       s.username,
       s.osuser,
       s.program
FROM v$process p, v$session s
WHERE s.paddr( + ) = p.addr
--AND p.background is null
ORDER BY con_id,pga_alloc_mem;


--user defined parameters sga,pga,memory_target,

select con_id, name as Parameter, value/1024/1024 as Mbytes from v$parameter
where name in ('pga_aggregate_target','memory_target','memory_max_target','sga_max_size','sga_target','pga_aggregate_limit')
order by name;

 Flash/Fast Recovery Area (FRA) in Oracle
------------------------------------------
FRA :
  FRA (flash or fast recovery area) is a storage area (directory on disk or ASM diskgroup) that contains
redo logs, control file, archived logs, backup pieces and copies, flashback logs and, in 11g, foreign archived logs. The flash recovery area was first introduced in Oracle 10g.

Choosing a Location for the Flash Recovery Area:
 When setting up a flash recovery area, you must choose a location (a directory or Automatic Storage Management disk group) to hold the files. The flash recovery area cannot be stored on a raw file system.

Planning the Size of the Flash Recovery Area:

The larger the flash recovery area is, the more useful it becomes. Ideally, the flash recovery area should be large enough to contain all of the following files:

   a) A copy of all datafiles

   b) Incremental backups, as used by your chosen backup strategy

    c) Online redo logs

    d) Archived redo logs not yet backed up to tape

    e) Control files

    f) Control file autobackups (which include copies of the control file and SPFILE)

If providing this much space is impractical, it is best to create an area large enough to keep a backup
of the most important tablespaces and all the archived logs not yet copied to tape. At an absolute minimum,
the flash recovery area must be large enough to contain the archived logs that have not been copied to tape/DISK.


Setting Step for FRA:

Step-1
After you start SQL*Plus and connect to the database, set the size of the flash recovery area

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 100G SCOPE=BOTH SID='*';

Set the location of the flash recovery area. For example, if the location is the file system directory /u01/app/oracle/fast_recovery_area, then you can do the following:

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u01/app/oracle/fast_recovery_area' SCOPE=BOTH SID='*';

If the flash recovery area location is an Automatic Storage Management disk group named FRA, for example, then you can do the following:

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '+FRA' SCOPE=BOTH SID='*';


Fast Recovery related data dictionary View:

Here given below Fast Recovery related data dictionary views
V$RECOVERY_FILE_DEST
V$FLASH_RECOVERY_AREA_USAGE
V$DBA_OUTSTANDING_ALERTS
V$FLASHBACK_DATABASE_LOGFILE

Some Query for Monitoring FRA:

-- Utilization (GB) the FRA ----
select
   name,
  floor(space_limit / 1024 / 1024) "Size MB",
  ceil(space_used / 1024 / 1024) "Used MB"
from v$recovery_file_dest;

-------FRA Occupants------------
select * from v$flash_recovery_area_usage;

/
-- Location and size of the FRA
show parameter db_recovery_file_dest
/

-- Size, used, Reclaimable --------------
SELECT
  ROUND((A.SPACE_LIMIT / 1024 / 1024 / 1024), 2) AS FLASH_IN_GB,
  ROUND((A.SPACE_USED / 1024 / 1024 / 1024), 2) AS FLASH_USED_IN_GB,
  ROUND((A.SPACE_RECLAIMABLE / 1024 / 1024 / 1024), 2) AS FLASH_RECLAIMABLE_GB,
  SUM(B.PERCENT_SPACE_USED)  AS PERCENT_OF_SPACE_USED
FROM
  V$RECOVERY_FILE_DEST A,
  V$FLASH_RECOVERY_AREA_USAGE B
GROUP BY
  SPACE_LIMIT,
  SPACE_USED ,
  SPACE_RECLAIMABLE ;

------FRA Size and Space available Avaiable ------------

 SELECT NAME,
SPACE_LIMIT/1024/1024/1024 AS SPACE_LIMIT_GB,
(SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE) /1024/1024/1024 AS SPACE_AVAILABLE_GB,
ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1) AS PERCENT_FULL
FROM V$RECOVERY_FILE_DEST;
/


Managing the Flash Recovery Area:

If you are facing flash_recovery_area full and resolving this error given below step:

The database issues a warning alert when reclaimable space is less than 15% and a critical alert when reclaimable space is less than 3%.
You can see the alerts in the alert.log and in DBA_OUTSTANDING_ALERTS.

SELECT object_type, message_type, message_level, reason, suggested_action
     FROM dba_outstanding_alerts;

If the flash recovery area becomes full, an error is issued. Beware of using the flash recovery area for log_archive_dest_n.
If the flash recovery becomes full and Oracle cannot archive redo logs then the instance will hang.

The following actions can be done to resolve the space issue :
  - Add disk space to the Flash Recovery Area or increase DB_RECOVERY_FILE_DEST_SIZE
         alter system set DB_RECOVERY_FILE_DEST_SIZE= ;
  - Use the command BACKUP RECOVERY AREA, to back up the contents of the Flash Recovery Area to a tertiary device such as tape.
          RMAN> backup device type 'sbt_tape' recovery area;
     or
         RMAN> backup recovery area;

  - Delete the files from the Flash Recovery Area using RMAN.
           The removal is desribed in the RMAN documentation but this is a quick and dirty way if you don't have an rman repository - but could endanger your ability to recover - so be careful.
           a) delete unwanted archive log files from disk ( rm /del )
           b) connect to rman
           c) rman> crosscheck archivelog all;         - marks the controlfile that the archives have been deleted
           d) rman> delete expired archivelog all;    - deletes the log entries identified above.
  - Changing RMAN retention policy.

NOTE= Manually removing fixed files from the FRA can have unexpected consequences. Oracle does not immediately detect the removal of these files, and thus the space is not reclaimed. If you end up manually removing files (or loose a disk perhaps), use the RMAN crosscheck command along with the delete command to cause Oracle to update the current control file information on the FRA. The folks at Oracle recommend that you not manually remove files managed by Oracle if at all possible.

Oracle does not delete eligible files from the Flash Recovery Area until the space must be reclaimed for some other purpose. The effect is that files recently moved to tape are often still available on disk for use in recovery. The recovery area can thus serve as a kind of cache for tape. Once the Flash Recovery Area is full, Oracle automatically deletes eligible files to reclaim space in the Flash Recovery Area as needed.




Happy configuring Oracle FRA...........

RMAN BACKUP On Database No Archive Mode

RMAN BACKUP On Database  No Archive Mode
========================================
When Database in No archive Mode we only can take consistent Backup by RMAN
Here the following step take consistent Backup by RMAN
 Step-1 : Start RMAN and connect to a target database.
  Step-2: Shut down the database consistently and then mount it.
Step-3: Run the BACKUP DATABASE command.
Step-4: Open the database and resume normal operations.

Here  Given Below Issue the Command and Output the Result

RMAN> connect target /

connected to target database: TESTDB (DBID=2598668270)
using target database control file instead of recovery catalog


Before Issue the Database Backup Command we check the RMAN Configuration


RMAN> show all ;

RMAN configuration parameters for database with db_unique_name TESTDB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\APP\USER\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFTESTDB.ORA'; # default

RMAN>

Then Issue Shutdown Command


RMAN> shutdown immediate;

database closed
database dismounted
Oracle instance shut down

RMAN> startup force dba;

Oracle instance started
database mounted
database opened

Total System Global Area     535662592 bytes

Fixed Size                     1375792 bytes
Variable Size                327156176 bytes
Database Buffers             201326592 bytes
Redo Buffers                   5804032 bytes

RMAN> shutdown immediate;

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     535662592 bytes

Fixed Size                     1375792 bytes
Variable Size                327156176 bytes
Database Buffers             201326592 bytes
Redo Buffers                   5804032 bytes

Issue the given below command full Database backup

RMAN> backup database;

Starting backup at 06-FEB-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=D:\APP\USER\TESTDB\SYSTEM01.DBF
input datafile file number=00002 name=D:\APP\USER\TESTDB\SYSAUX01.DBF
input datafile file number=00003 name=D:\APP\USER\TESTDB\UNDOTBS01.DBF
input datafile file number=00005 name=D:\APP\USER\TESTDB\EXAMPLE01.DBF
input datafile file number=00004 name=D:\APP\USER\TESTDB\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 06-FEB-14
channel ORA_DISK_1: finished piece 1 at 06-FEB-14
piece handle=D:\APP\USER\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2014_02_06\O1_MF_NNNDF_TAG20140206T180300_9H6YHOS4_.BKP tag=TAG20140206T180300 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 06-FEB-14
channel ORA_DISK_1: finished piece 1 at 06-FEB-14
piece handle=D:\APP\USER\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2014_02_06\O1_MF_NCSNF_TAG20140206T180300_9H6YKG2K_.BKP tag=TAG20140206T180300 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 06-FEB-14

The following variation of the command creates image copy backups of all datafiles in the database:

RMAN> backup as copy database;

Starting backup at 06-FEB-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=D:\APP\USER\TESTDB\SYSTEM01.DBF
output file name=D:\APP\USER\FLASH_RECOVERY_AREA\TESTDB\DATAFILE\O1_MF_SYSTEM_9H6ZR1FJ_.DBF tag=TAG20140206T182433 RECID=2 STAMP=838837492
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=D:\APP\USER\TESTDB\SYSAUX01.DBF
output file name=D:\APP\USER\FLASH_RECOVERY_AREA\TESTDB\DATAFILE\O1_MF_SYSAUX_9H6ZRTM6_.DBF tag=TAG20140206T182433 RECID=3 STAMP=838837518
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=D:\APP\USER\TESTDB\UNDOTBS01.DBF
output file name=D:\APP\USER\FLASH_RECOVERY_AREA\TESTDB\DATAFILE\O1_MF_UNDOTBS1_9H6ZSMS2_.DBF tag=TAG20140206T182433 RECID=4 STAMP=838837531
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=D:\APP\USER\TESTDB\EXAMPLE01.DBF
output file name=D:\APP\USER\FLASH_RECOVERY_AREA\TESTDB\DATAFILE\O1_MF_EXAMPLE_9H6ZT301_.DBF tag=TAG20140206T182433 RECID=5 STAMP=838837542
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=D:\APP\USER\TESTDB\USERS01.DBF
output file name=D:\APP\USER\FLASH_RECOVERY_AREA\TESTDB\DATAFILE\O1_MF_USERS_9H6ZTB6G_.DBF tag=TAG20140206T182433 RECID=6 STAMP=838837546
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=D:\APP\USER\FLASH_RECOVERY_AREA\TESTDB\CONTROLFILE\O1_MF_TAG20140206T182433_9H6ZTC9R_.CTL tag=TAG20140206T182433 RECID=7 STAMP=838837547
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 06-FEB-14
channel ORA_DISK_1: finished piece 1 at 06-FEB-14
piece handle=D:\APP\USER\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2014_02_06\O1_MF_NNSNF_TAG20140206T182433_9H6ZTDDZ_.BKP tag=TAG20140206T182433 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 06-FEB-14

RMAN> ALTER DATABASE OPEN;

database opened

RMAN>

Happy Reading.......

RMAN Connection step

Basic RMAN Connection step  in window:
=================================
Open windows CMD Tools and write rman for call the Oracle Utility tools for Backup and recovery purpose.
And connect the target database that while you want to take backup.
C:\Users\user>rman

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Feb 6 15:51:15 2014

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

RMAN> conn target sys@testdb

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "identifier": expecting one of: "advise, allocate, alter, backup, @, catalog, change, configure, connect, convert, copy, create, crosscheck, delete, drop, duplicate, exit, flashback, grant, host, import, list, mount, open, print, quit, recover, register, release, repair, replace, report, reset, restore, resync, revoke, run, send, set, show, shutdown, spool, sql, startup, switch, transport, unregister, upgrade, validate, {, "
RMAN-01008: the bad identifier was: conn
RMAN-01007: at line 1 column 1 file: standard input

Above rman connect command syntax are not connect database and return the error for I mistake the command "Connect" as " Conn" as like think as sql*plus.

So write again  correct rman connect command with target database name string: 

RMAN> connect target sys@testdb

target database Password:
connected to target database: TESTDB (DBID=2598668270)

RMAN> exit  

The following variation starts RMAN and then connects to a target database by using operating system authentication:

C:\Users\user>rman

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Feb 6 15:53:39 2014

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

RMAN> connect target /

connected to target database: TESTDB (DBID=2598668270)





if you want to exit from the RMAN  then write the exit command
RMAN> exit


Recovery Manager complete.

If you want to RMAN session Result output in text file. You are doing follwing step:

The following example appends the output from an RMAN session to a text file at

C:\Users\user>rman target / log d:/rmanlog06022014.log append
RMAN> connect target /
RMAN> show all;
RMAN> exit


Happy Reading............

Archive Log Mode Oracle 10g/11g Enable and Disable Step

 Archive Log Mode 10g/11g Enable and Disable

[oracle@rnddba ~]$ export ORACLE_SID=eradb11g
[oracle@rnddba ~]$
[oracle@rnddba ~]$
[oracle@rnddba ~]$
[oracle@rnddba ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 5 06:07:24 2014

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

SQL> conn / as sysdba
Connected.

Check The database is archive mode or No Archive mode

SQL> archive log list
Database log mode           No Archive Mode
Automatic archival           Disabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     88
Current log sequence           90

Check the archive log file destination path and size

SQL> show parameter recovery_file_dest

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest             string     /u01/app/oracle/flash_recovery
                         _area
db_recovery_file_dest_size         big integer 8480M

By default, archive logs will be written to the flash recovery area. If you do not want to write archive logs  to the flash recovery area you can set the parameter LOG_ARCHIVE_DEST_n to the location in which you wish to
write archive logs.

[root@rnddba ~]# mkdir /u02/eradb11g/fraeradb11g
[root@rnddba ~]# chown -R oracle:oinstall /u02/eradb11g/fraeradb11g
[root@rnddba ~]# chmod -R 775 /u02/eradb11g/fraeradb11g

SQL> alter system set log_archive_dest_1='LOCATION=/u02/eradb11g/fraeradb11g' scope = both;

SQL> show parameter log_archive_dest

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest             string
log_archive_dest_1             string     LOCATION=/u02/eradb11g/fraerad
                         b11g
SQL> archive log list
Database log mode           No Archive Mode
Automatic archival           Disabled
Archive destination           /u02/eradb11g/fraeradb11g
Oldest online log sequence     88
Current log sequence           90

Before doing Database archive mode please take consistent mode database backup for future recovery purpose.

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

Total System Global Area 3991842816 bytes
Fixed Size            2219512 bytes
Variable Size         2919236104 bytes
Database Buffers     1056964608 bytes
Redo Buffers           13422592 bytes
Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           /u02/eradb11g/fraeradb11g
Oldest online log sequence     88
Next log sequence to archive   90
Current log sequence           90

You can switch to the log file to see that an archive is written to archive log location.
SQL> alter system switch logfile;

Check the archive log file in archive destination location
SQL> host
[oracle@rnddba ~]$ ls /u02/eradb11g/fraeradb11g/
1_90_821585333.dbf
[oracle@rnddba ~]$ exit
exit


Doing Database No Archive Mode
===============================

Note: It is recommended to perform a backup of the database (after a shutdown normal or immediate) prior to changing the archive status of a database.

The following are the steps required to disable archive log mode on an Oracle 10g or 11g database.
[oracle@rnddba ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 5 07:03:53 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> archive log list
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           /u02/eradb11g/fraeradb11g
Oldest online log sequence     89
Next log sequence to archive   91
Current log sequence           91

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 3991842816 bytes
Fixed Size            2219512 bytes
Variable Size         2919236104 bytes
Database Buffers     1056964608 bytes
Redo Buffers           13422592 bytes
Database mounted.
SQL> alter database noarchivelog;

Database altered.

SQL> archive log list
Database log mode           No Archive Mode
Automatic archival           Disabled
Archive destination           /u02/eradb11g/fraeradb11g
Oldest online log sequence     89
Current log sequence           91
SQL>

Thanks For Updating....

Linux /def/shm related for Oracle 11g Automatic Memory Management

/def/shm does not use /etc/fstab attributes after reboot

There is a bug in Red Hat Linux 6 and Oracle Enterprise Linux 6 (UEK and RHEL-kernel) and probably all other Red Hat 6 related Linux Distro’s.

When you need more memory for SGA/PGA when using MEMORY_MAX_TARGET, you need to resize /dev/shm. By default this is 50% of total memory and Oracle tells you to add the following to /etc/fstab:

shmfs /dev/shm tmpfs size=4g 0 0

Which works…But, there is another line in /etc/fstab which in my opinion should be used and should work:

#tmpfs /dev/shm tmpfs defaults 0 0

tmpfs /dev/shm tmpfs size=4g 0 0

This works after a remount: ‘mount -o remount /dev/shm’,

but it doesn’t work after a reboot…

So  I do the another step with help of Oracle forums and its may be bug of Redhat 6.2,6.1 and OEL 6 Version

https://forums.oracle.com/message/10020717

[root@bl-sms ~]#sudo cp /etc/rc.d/rc.sysinit /etc/rc.d/rc.sysinit.bck

[root@bl-sms ~]#sudo sed -i 's/-f \/dev\/shm/\/dev\/shm/g'

/etc/rc.d/rc.sysinit

 and  then reboot the system

After check the system following command

[root@bl-sms ~]df –h /dev/shm OR

[root@bl-sms ~]df -h

Happy Updating .........

Automatically start Oracle 11g on Oracle Linux 6.2 after server reboot

Environment: Oracle database 11.2.0.3.0, Oracle Linux 6.2
Oracle 11g includes 2 scripts which can be used to start or shut down Oracle databases on Linux. Both scripts are installed in $ORACLE_HOME/bin and are called dbstart and dbshut. However, these scripts are not executed automatically after you reboot your server. I will explain here how you can configure that.
First, you need to make sure that any database instances you want to autostart are set to “Y” in the /etc/oratab file. This is how mine looks like:

#   # This file is used by ORACLE utilities.  It is created by root.sh # and updated by either Database Configuration Assistant while creating # a database or ASM Configuration Assistant while creating ASM instance.   # A colon, ':', is used as the field terminator.  A new line terminates # the entry.  Lines beginning with a pound sign, '#', are comments. # # Entries are of the form: #   $ORACLE_SID:$ORACLE_HOME:: # # The first and second fields are the system identifier and home # directory of the database respectively.  The third filed indicates # to the dbstart utility that the database should , "Y", or should not, # "N", be brought up at system boot time. # # Multiple entries with the same $ORACLE_SID are not allowed. # # baldbsms:/u01/app/oracle/product/11.2.0/db_1:Y

The /etc/oratab file is normally created by running the root.sh script at the end of the installation. If you don’t have the file, you can always add it to your system by creating it manually (with user root!).
Next, we are going to create 2 scripts under /home/oracle/scripts: ora_start.sh and ora_stop.sh. These scripts will call dbstart and dbshut and will also allow us to add some more actions, for example the start of the Enterprise Manager database control or any other services you might have.
$ su – oracle
$ vi /home/oracle/scripts/ora_start.sh

#!/bin/bash   # script to start the Oracle database, listener and dbconsole   . ~/.bash_profile   # start the listener and the database $ORACLE_HOME/bin/dbstart $ORACLE_HOME   # start the Enterprise Manager db console $ORACLE_HOME/bin/emctl start dbconsole   exit 0

$ vi /home/oracle/scripts/ora_stop.sh

#!/bin/bash   # script to stop the Oracle database, listener and dbconsole   . ~/.bash_profile    # stop the Enterprise Manager db console $ORACLE_HOME/bin/emctl stop dbconsole   # stop the listener and the database $ORACLE_HOME/bin/dbshut $ORACLE_HOME   exit 0

You see that inside the scripts, we are calling the .bash_profile file of the user “oracle”. This is needed to set the ORACLE_HOME environment variable.
Next, give execute rights to the scripts:
$  chmod u+x  /home/oracle/scripts/ora_start.sh 
$  chmod u+x  /home/oracle/scripts/ora_stop.sh
You could now test these scripts to see if they correctly shut down and start up your Oracle database.
We will now create a wrapper script that can be used to schedule as a service.
With user root, create a file called “oracle” under /etc/init.d.
$ vi /etc/init.d/oracle

#!/bin/bash # chkconfig: 345 99 10 # description: Oracle auto start-stop script.   # Set ORA_OWNER to the user id of the owner of the # Oracle database in ORA_HOME.   ORA_OWNER=oracle RETVAL=0   case "$1" in     'start')         # Start the Oracle databases:         # The following command assumes that the oracle login         # will not prompt the user for any values         su - $ORA_OWNER -c "/home/oracle/scripts/ora_start.sh"         touch /var/lock/subsys/oracle         ;;     'stop')         # Stop the Oracle databases:         # The following command assumes that the oracle login         # will not prompt the user for any values         su - $ORA_OWNER -c "/home/oracle/scripts/ora_stop.sh"         rm -f /var/lock/subsys/oracle         ;;     *)         echo $"Usage: $0 {start|stop}"         RETVAL=1 esac exit $RETVAL

$ chmod 750 /etc/init.d/oracle

To create a service of this script, run the following command:

$ chkconfig --add oracle

Next, check the script by running “service oracle stop” or “service oracle start” from the command line:
$ service oracle stop
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation. All rights reserved.
Stopping Oracle Enterprise Manager 11g Database Control …
… Stopped.
Processing Database instance “oratst”: log file /u01/app/oracle/product/11.2.0/db_1/shutdown.log
$ service oracle start
Processing Database instance “oratst”: log file /u01/app/oracle/product/11.2.0/db_1/startup.log
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation. All rights reserved.
Starting Oracle Enterprise Manager 11g Database Control …… started.
After this, it’s time for the final test: reboot your server and check if your Oracle database is automatically started after the reboot.

Happy Reading ..........