Monday 19 September 2016

Frequently Used Queries for Oracle DBA

Check Database Status

col name for a8 
col open_mode for a13 
col status for a8 
col logins for a8 
col host_name for a20 
set lines 500 
alter session set NLS_DATE_FORMAT='dd-mm-yyyy hh24:mi:ss'; 
select name,host_name, open_mode, status, logins, startup_time from gv$instance, v$database;

Check Oracle 12c PDB Database Status

SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID;

SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM V$PDBS;

Check Database Size

select
    ( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
    ( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
     ( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
    ( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
    from dual;

Check Invalid Objects

set lines 200
set pages 999
col OBJECT_NAME for a40
select owner, object_name, object_type, status from dba_objects where status like 'INVALID';

Check RMAN Backup Status 

set lines 300 pages 500
col command_id format a25
col status format a35
col start_time format a25
col time_taken_display format a25
col INPUT_TYPE format a20
col output_device_type format a50
SELECT   b.command_id,b.status, to_char(b.start_time, 'MM-DD-YYYY HH24:MI') "Start_TIME", b.time_taken_display,b.input_type, b.output_device_type
FROM v$rman_backup_job_details b
WHERE (b.start_time > (SYSDATE - 20)) order by 3 desc;

Check Oracle Compononets Status 

set lines 200
set pages 999 
col comp_name for a40
select COMP_NAME, VERSION, STATUS, MODIFIED from dba_registry;

Check Table/Index/Segment Size

set lines 200
col segment_name for a20
select owner, segment_name, segment_type, bytes/1024/1024 "Size" from dba_segments where segment_name like 'TABLE_NAME';

Check Tablespace Usage

set pages 999 lines 999
column Tablespace_Name format A30
column "Free %" format 999.99
column "Largest Chunk" format 9999.999
column "Free Space" format 9,999,999.999 
column "Tot Space" format 9,999,999.999
column auto_mb_left hea 'Auto_MB_Left' format 9,999,999.999
compute sum of "Free Space" on report
compute sum of "Tot Space" on report
break on report
ttitle off
select Tablespace_Name,
        nvl(Sum_Alloc_Bytes,0)/(1024*1024) "Tot_Space_GB",
        nvl(Sum_Free_Bytes,0)/(1024*1024) "Free_Space_GB",
        nvl(Max_Bytes,0)/(1024*1024) "Largest Chunk",
        Count_Chunks,
        auto_mb_left,
        (100*((nvl(Sum_Free_Bytes,0)/1024/1024)+nvl(auto_mb_left,0))/((nvl(Sum_Alloc_Bytes,0)/1024/1024)+nvl(auto_mb_left,0))) AS "Free %"
from
        (select Tablespace_Name, SUM(Bytes) Sum_Alloc_Bytes
                from DBA_DATA_FILES
                group by Tablespace_Name),
        (select Tablespace_Name FS_TS_NAME,
                MAX(Bytes)  AS Max_Bytes,
                COUNT(Bytes)  AS Count_Chunks,
                SUM(Bytes) AS Sum_Free_Bytes
                from DBA_FREE_SPACE
                group by Tablespace_Name),
        ( SELECT d.tablespace_name auto_tbspname,
                floor(sum(decode(sign( ((d.maxbytes-d.bytes)/1024/1024) - (d.increment_by*to_number(p.value)/1024/1024) ),
                        -1,0,((d.maxbytes-d.bytes)/1024/1024)))) auto_MB_Left
          FROM dba_data_files d,  v$parameter p
          WHERE p.name='db_block_size'
            and d.autoextensible='YES'
          GROUP BY d.tablespace_name) b
where Tablespace_Name = FS_TS_NAME (+)
and Tablespace_name = auto_tbspname (+)
order by 7 desc;


Tuesday 30 August 2016

Configure Listener Using NETCA

Networking for Oracle Database

Oracle Net is a software layer that maintains connection between the client and the database over the network. Client application identifies the database it wants to connect to, through the service name which is the logical representation of the database. Multiple services can be configured on the single database.

What is Oracle Listener?

Oracle listener is a process to listen request from client connection to database server. Default listener configuration is stored in the listener.ora file located at $ORACLE_HOME/network/admin directory. 
  When listener starts, database and its services gets dynamically registered with the listener. PMON is responsible for database service registration to the listener. When services gets registered there is no need of any modifications to the listener.ora file. 

Listener Commands

1) Starting the listener
[oracle@cobra01 ~]$ lsnrctl start

2) Stopping the listener
[oracle@cobra01 ~]$ lsnrctl stop

3) Check the listener status
[oracle@cobra01 ~]$ lsnrctl status

4) Check if listener process is running
[oracle@cobra01 ~]$ ps -ef|grep tns

What is NETCA?

NETCA is the utility used to configure listener and also used to add an entry to tnsnames.ora file. Connection descriptors gets stored on the tnsnames.ora file that identifies their net service names

Configure Listener using NETCA utility


1) Run netca command to invoke the network configuration assistant 

[oracle@cobra01 ~]$ netca
 

2) Select Add option and click on Next


3) Provide Oracle service name or database name then click on Next


4) We will select TCP protocol option in the list box and click on Next


5) Provide the Hostname and port number. Default port is 1521, in our case will will keep port number as it is. Then click on Next


6) Select Option Yes, perform a test. This is to make sure that a connection can access the database


7) Here in the screen shot provided, tests have been cleared. In case the test fails check if you have entered the correct login credentials. Login credentials can be updated by pressing change login button. Everything seems to be fine, so click on Next to proceed forward


8) Click on No Option and click Next


9) We have successfully completed listener configuration, finally click on Next


10) Click on the Finish button, this will close the netca window


11) Now check the listener status, it must have started already 
[oracle@cobra01 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 30-AUG-2016 19:14:05

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=cobra01.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                30-AUG-2016 19:13:01
Uptime                    0 days 0 hr. 1 min. 4 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/cobra01/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=cobra01.localdomain)(PORT=1521)))
Services Summary...
Service "KMDB" has 1 instance(s).
  Instance "KMDB", status READY, has 1 handler(s) for this service...
Service "KMDBXDB" has 1 instance(s).
  Instance "KMDB", status READY, has 1 handler(s) for this service...
The command completed successfully

Monday 22 August 2016

Oracle Database Architecture

Oracle database server is combination of instance(Memory component) and database(Storage component). Initial set of database files created during database creation. When instance starts SGA gets allocated and background process starts. Database comprises of physical and logical structure.


Shared Global Area - SGA

SGA is a dynamic pool which consists of various memory components. It contains control and data information for oracle database instance. SGA is shared by server process as well as background processes. SGA memory allocation and deallocation is done in terms of granules(contiguous memory) except for redolog buffer. Components of SGA are as follows
  • Database Buffer Cache
  • Redo Log Buffer
  • Shared Pool
  • Large Pool
  • Java Pool
  • Streams Pool
  • Fixed SGA
I have posted another article with detailed information on process architecture


Database Buffer cache is the memory component that stores copies of data blocks that are read from data files. A buffer is a memory that temporarily caches a current or recently used data blocks. This component takes care of the following 
  1. Optimize the physical I/O
  2. Keep frequently used blocks in buffer cache
  3. Write least frequently used blocks to disk
Buffers in the buffer cache has different states
  1. Unused :- The buffer is available for use
  2. Clean :- Buffers that were previously used but now it has read-consistent block
  3. Dirty : These are modified buffers that are not yet written to the disk
Allocation and de-allocation of buffers work on LRU(Least recently used) basis. Frequently used buffers are called hot buffer and cold buffer are those that are not used recently. Lets say when a query executes if server process can find the required data in buffer cache its called cache hit. If the data is not available in buffer cache its cache miss. Physical read happens when data block is copied from data file to memory. 
Buffer cache has different pools
  1. Default cache :- Defaults location for caching the blocks
  2. Keep cache :- Frequently used blocks as usually kept here, to avoid aging out of blocks from cache
  3. Recycle cache :- Infrequently used blocks are kept here, or to phase it out of cache from consuming memory
  4. Cache to keep non standard blocks (2k, 4k, 16k, 32k blocks) :- This pool keeps blocks that are not standard block size
Redo log buffer is a circular buffer having changed vectors to the database. These changes are made through DML or DDL operations. It is the smallest memory component in SGA. Log writer process writes changed vectors from redolog buffer to online redolog files.
Shared Pool caches various control information such as parsed SQL, PL/SQL code, system parameters, and data dictionary information. Shared pool has different components

  1. Library cache :- It stores executable SQL and PL/SQL code, along with control structures such as locks and library cache handles. 
  2. Data dictionary cache :- It holds metadata of tables and reference information about the database.
  3. Result Cache :- This component contains the SQL query result cache and PL/SQL function result cache. In simple words it stores the result of processed SQL statement
  4. Reserved pool :- is a shared pool component that Oracle Database can use to allocate large contiguous chunks of memory. This pool can be used to Allocate memory from the shared pool which is performed in chunks. It will allow large object(over 5 KB) to be loaded into the cache without needing a single contiguous area.
Large pool is an optional memory area intended for memory allocations that are larger than is appropriate for the shared pool. 
Java Pool is used along with JVM component. 
Streams Pool is used if Oracle streams is configured or used. This pool is exclusively for the use of Oracle streams. 
Fixed SGA is used for internal housekeeping tasks like keeping information about locks etc.

Background Process

Process Monitor - PMON (Mandatory/Instance Critical) :- PMON is a background process that monitors all other background processes Performs process recovery for sessions terminated abnormally. It registers instance information to listener
System Monitor - SMON (Mandatory/Instance Critical) :- SMON is a background process responsible for instance recovery and clean up duties. It recovers transactions that were skipped during instance recovery due to file-read or tablespace offline errors. SMON cleans unused temporary segment from temp (temp tablespace)
Log Writer - LGWR (Mandatory/Instance Critical) :- LGWR is a background process writes changed vectors from buffer cache(redolog buffer) to online redolog files. Write happens from redolog buffer to redolog group in the following conditions
  1. when transaction is comitted
  2. when log switch occurs
  3. every 3 seconds
  4. 1/3rd of redolog buffer is full
  5. redolog buffer contains 1MB of data
Database Writer - DBWn 0-9 (Mandatory/Instance Critical) :- DBWR process writes dirty buffers in buffer cache to disk or datafiles. Writing happens in the following conditions
  1. Clean reusable buffers is not available in cache after checking threshold number of blocks
  2. To advance the check point, this is the position in redolog file where recovery of the instance starts
Check Point - CKPT (Mandatory/Instance Critical) :- CKPT is a background process which is responsible for 
  1. Updating latest SCN to control file and data file headers
  2. Writing dirty buffers from buffer cache to disk
Memory Monitor - MMON (New in Oracle 10g) :- MMON collects the statistics required for AWR reports
Memory Monitor Light - MMNL (New in Oracle 10g) :- MMNL writes the statistics collected to the disk and statistics are used for ASH reports as well
Memory Manager - MMAN (New in Oracle 10g) :- MMAN background process manages the dynamic resizing of SGA memory components depending on load for database
Diagnostic Capture Process - DIAG (New in Oracle 11g) :- DIAG process executes oradebug commands and performs diagnostic dumps requested by other processes or termination of instance. Alertlog file and all trace files will be written to a single location
Recoverer - RECO :- Its a background process run on distributed database environment
General Task Execution process - GENo (New in Oracle 11g) :- This process performs required task for DMLs and SQL. GENo process handles the processes that are blocking other processes
Diagnosability Process 0 - DIA0 (New in Oracle 11g) :- DIA0 is responsible for hang detection and deadlock resolution
Virtual Keeper of Time Process - VKTM (New in Oracle 11g) :- VKTM acts as a time publisher for an Oracle instance. VKTM publishes two sets of time: a wall clock time using a seconds interval and a higher resolution time (which is not wall clock time) for interval measurements
Process Spawner - PSPO :- Process Spawner spawns oracle background process after initial startup of the instance. It was introduced in Oracle 10g. This process creates and manages other oracle background processes
Job Queue Coordinator Process - CJQO :- This process selects jobs that need to be run from the data dictionary and spawns job queue slave processes (Jnnn) to run the jobs. JQ0 is automatically started and stopped as needed by Oracle Scheduler. CJQ0 starts only as many job queue processes as required by the number of jobs to run and available resources
Space Management Coordinator - SMCO :-This background process is responsible for coordinating the execution of various space management tasks, including proactive space allocation and space reclamation. SMCO dynamically spawns slave processes (Wnnn) to implement these tasks.
Archiver - ARCH (Optional) :- Archiver is the background process that copies exact copy of the online redolog file to the disk (destination provided in the parameter file log_archive_dest_1). This background process run only when the database is in archivelog mode Click Here - How to enable or Disable archivelog mode
Recovery Writer - RVWR (Optional) (New in Oracle 10g) :- RVWR background process runs if database is in flashback mode. It writes flashback data to the flashback logs in the fast recovery area(FRA). FRA destination parameter db_recovery_file_dest is set in database pfile Click Here - How to enable or disable flashback mode
Change Tracking Writer - CTWR (Optional)(New in Oracle 10g) :- CTWR process writes changed block information from buffer to the change tracking file. Block change tracking is enabled to improve the performance of RMAN incremental backup
Database resource manager - DBRM (Optional) :- Database resource manager takes care of setting plans to users and all other database resource management activities. It controls the use of computing resources
Virtual Scheduler for Resource Manager - VKRM (Optional) :- VKRM process Serves as centralized scheduler for Resource Manager activity.CPU scheduling for all managed Oracle processes is handled by VKRM. The process schedules managed processes in according to an active resource plan.

Logical Structure


Data Blocks :- Its the smallest unit of space allocation in the data files and also minimum unit of I/O for database. Default block size for the database is set in initialization parameter DB_BLOCK_SIZE. The standard block size of the database is 4KB or 8KB, most of the databases use 8K as its default block size. We can create the tablespace with non standard block size like 16KB, 32KB etc
Space allocation in data blocks 
Top most section of the block holds block header information, data fills up from bottom up
  1. PCTUSED : Its the parameter specified at the block level. PCTUSED is the minimum percentage of the block that can be used when new data gets added during insert operation
  2. PCTFREE : Its the parameter specified to reserve the space in the block for future update
Extents :- It comprises of group of data blocks which is the default unit of space allocation. When a datafile is added and even though no data is inserted, database allocates initial extent for data segment during segment creation. When the initial extent is full, database allocates incremental extent/next extent for this segment.
De-allocation of extents
Extents belonging to the user segment returned only if object is dropped via DROP command. Example :- Lets say, if 1000 rows of table sample_emp are deleted that doesn't claim data blocks to be used by other objects in the tablespace. Drop or truncating table will help reclaim space, another way to do so is defragmenting table
Segments :- It is a group of extents containing all the data for logical storage(table, index etc) within a tablespace. There are different kinds of segments
  • User segments
  • Temporary segments
  • Undo segments
User segment stores the data for one user object in the form of table, index etc. When user inserts first row to a table, oracle database creates segments to that table.
Temporary segment gets allocated during query processing(like sorting operation). Temporary tables and their indexes consumes temporary segments which is session specific. 
Undo segment: Oracle maintains record of previous image of data when it gets changed. Record of the previous data stored is undo data. Undo segment gets allocated to consume undo data
Tablespace :-  Its the logical space where all segment is contained. Database contains SYSTEM, SYSAUX, UNDO, TEMP and user created tablespaces.
Permanent tablespace or user created tablespace contains user data stored in the form of tables or indexes, usually its application data
System tablespace contains data dictionary information and metadata information for user created objects
Undo tablespace is locally managed tablespace that stores undo data
Temporary tablespace contains session specific temporary data used for sorting operations. It also contains session specific data stored in temporary tables and indexes

Physical Structure

Datafiles :- Data gets stored in the datafiles under the tablespaces. Tablespace has one or more datafiles. User data spans across multiple datafiles
Control file :- Its the most important file which is required for the database to run, which is in binary format. Control file information such as 
  • Database name
  • Database creation time stamp
  • Data files, online redo log files, and archived redo log files location
  • Latest SCN information
  • Tablespace information
  • RMAN backups information etc
Redolog File :- This is the file where LGWR writes the database changes from redolog buffer to redolog files
Archivelog File :- Its the exact copy of the redolog file which is used in case of instance recovery
Parameter file :- It contains parameters required for database initialization

Tuesday 2 August 2016

Enable or Disable Flashback Mode in Oracle11g Database

Enable Flashback Mode 


Step 1:- Log on to the database and check archivelog mode for database. Its mandatory that database must have enabled archivelog mode 

[oracle@pract1 fra]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 28 18:03:43 2016


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> select instance_name, status from v$instance;


INSTANCE_NAME    STATUS

---------------- ------------
KMDB             OPEN

SQL> archive log list

Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence     1
Current log sequence           2

Step 2:- Looks like archivelog destination parameter is set to default location. We will change the archivelog destination to new location 

SQL> alter system set log_archive_dest_1='LOCATION=/u01/oradata/KMDB/arch' scope = both;

System altered.


Step 3:- Verify the archivelog destination which is pointing to new location

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/oradata/KMDB/arch
Oldest online log sequence     1
Current log sequence           2

Step 4:- Shutdown the database and start it in mount state

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

SQL> startup mount

ORACLE instance started.

Total System Global Area  839282688 bytes

Fixed Size                  2217992 bytes
Variable Size             494929912 bytes
Database Buffers          339738624 bytes
Redo Buffers                2396160 bytes
Database mounted.

Step 5:- We need to set archivelog mode and open the database

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            /u01/oradata/KMDB/arch
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence           2

Step 6:- Check the flashback mode and flash recovery area parameter for the database

SQL> select name,  flashback_on from v$database;

NAME      FLASHBACK_ON

--------- ------------------
KMDB      NO

SQL> sho parameter recovery


NAME                                 TYPE        VALUE

------------------------------------ ----------- --------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0
recovery_parallelism                 integer     0

Step 7:- Now we will set the flash recovery area parameter

SQL> alter system set db_recovery_file_dest_size=2g scope = both;

System altered.


SQL> alter system set db_recovery_file_dest='/u01/oradata/KMDB/fra' scope = both;


System altered.


Step 8:- Enable flashback mode and verify if its enabled

SQL> alter database flashback on;

Database altered.


SQL> select name,  flashback_on from v$database;


NAME      FLASHBACK_ON

--------- ------------------
KMDB      YES


Disable Flashback Mode


Step 1:- Disable the flashback mode using below mentioned command and verify the status

SQL> alter database flashback off;

Database altered.

SQL> select name,  flashback_on from v$database;

NAME      FLASHBACK_ON
--------- ------------------
KMDB      NO


Possible Errors during Activity 

ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38707: Media recovery is not enabled.
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-19802: cannot use DB_RECOVERY_FILE_DEST without DB_RECOVERY_FILE_DEST_SIZE
ORA-01126: database must be mounted in this instance and not open in any
instance

Case 1:- Archivelog not enabled
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38707: Media recovery is not enabled.

Cause:- 
This error occurs when we try to enable the flashback mode without enabling archivelog mode 

Solution:- 
After the enabling the archivelog mode, issue should be resolved. Click Here on How to enable archivelog mode

Case 2:- DB_RECOVERY_FILE_DEST parameter no set in database parameter file
SQL> alter system set db_recovery_file_dest='/u01/oradata/KMDB/fra' scope = both;
alter system set db_recovery_file_dest='/u01/oradata/KMDB/fra' scope = both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-19802: cannot use DB_RECOVERY_FILE_DEST without DB_RECOVERY_FILE_DEST_SIZE

Cause:- 
This error occurs when we try to set DB_RECOVERY_FILE_DEST without setting the database recovery destination size parameter DB_RECOVERY_FILE_DEST_SIZE 

Solution:-
Set the database recovery destination size parameter DB_RECOVERY_FILE_DEST_SIZE 
SQL> alter system set db_recovery_file_dest_size=2g scope = both;

Case 3:- Database is open while enabling archivelog mode  
SQL> alter database archivelog; 
*
ERROR at line 1:

ORA-01126: database must be mounted in this instance and not open in any
instance

Cause:-
This error happens when you try to enable archivelog mode when the database is in open state 

Solution:- 
Shutdown the database and start the database in mount state. Then try to enable archivelog mode, it should work

Thursday 28 July 2016

Enable or Disable Archivelog Mode in Oracle 11g Database

Enable Archivelog Mode 


Step 1:- Log on to the database and check archivelog mode for database

[oracle@pract1 fra]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 28 18:03:43 2016

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> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
KMDB             OPEN

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence     1
Current log sequence           2

Step 2:- Looks like archivelog destination parameter is set to default location. We will change the archivelog destination to new location 

SQL> alter system set log_archive_dest_1='LOCATION=/u01/oradata/KMDB/arch' scope = both;

System altered.

Step 3:- Verify the archivelog destination which is pointing to new location

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/oradata/KMDB/arch
Oldest online log sequence     1
Current log sequence           2

Step 4:- Shutdown the database and start it in mount state

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

SQL> startup mount
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size                  2217992 bytes
Variable Size             494929912 bytes
Database Buffers          339738624 bytes
Redo Buffers                2396160 bytes
Database mounted.

Step 5:- We need to enable archivelog mode and open the database

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            /u01/oradata/KMDB/arch
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence           2

Disable Archivelog Mode


Step 1:- Check the archivelog mode

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oradata/KMDB/arch
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence           2

Step 2:- Shutdown the database and start it in mount state

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

SQL> startup mount
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size                  2217992 bytes
Variable Size             494929912 bytes
Database Buffers          339738624 bytes
Redo Buffers                2396160 bytes
Database mounted.

Step 3:-We need to set archivelog mode and open the database

SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

Step 4:- Verify archivelog mode

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/oradata/KMDB/arch
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence           2

Possible Errors during activity


ORA-01126: database must be mounted in this instance and not open in any
instance

Case:- 
SQL> alter database archivelog; 
*

ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any
instance

Cause:-
This error happens when you try to enable archivelog mode when the database is in open state 

Solution:- 
Shutdown the database and start the database in mount state