Enable/Disable Archivelog mode in oracle RAC DB

Enable Archivelog Mode

1. Connect to DB from one node

$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 27 20:16:10 2015
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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

2. Check the archivelog mode

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/product/11.2.0/db_1/dbs/arch
Oldest online log sequence     8
Current log sequence           9

3. Disable the RAC

SQL> alter system set cluster_database=false scope=spfile sid='RAC1';
System altered.

4. Shutdown the All RAC instances

[oracle@ol5-112-rac1 ~]$ srvctl stop database -d RAC

5. Connect to DB 

[oracle@ol5-112-rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 27 20:18:19 2015
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to an idle instance.

6. Mount the DB

SQL> startup mount;
ORACLE instance started.
Total System Global Area 1023004672 bytes
Fixed Size                  2219752 bytes
Variable Size             721420568 bytes
Database Buffers          293601280 bytes
Redo Buffers                5763072 bytes
Database mounted.

7. Enable Archivelog mode

SQL> alter database archivelog;
Database altered.

8. Enable the RAC

SQL> alter system set cluster_database=true scope=spfile sid='RAC1';
System altered.

9. Shutdown the DB

SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

10. Start all the RAC instances

[oracle@ol5-112-rac1 ~]$ srvctl start database -d RAC

Disable Archivelog Mode

1. Connect to DB

[oracle@ol5-112-rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 27 20:28:41 2015
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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

2. Check the archivelog mode

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/product/11.2.0/db_1/dbs/arch
Oldest online log sequence     8
Next log sequence to archive   9
Current log sequence           9

3. Disable the RAC

SQL> alter system set cluster_database=false scope=spfile sid='RAC1';
System altered.

4. Shutdown the All RAC instances

[oracle@ol5-112-rac1 ~]$ srvctl stop database -d RAC

5. Connect to DB

[oracle@ol5-112-rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 27 20:11:10 2015
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to an idle instance.

6. Mount the DB

SQL> startup mount;
ORACLE instance started.
Total System Global Area 1023004672 bytes
Fixed Size                  2219752 bytes
Variable Size             721420568 bytes
Database Buffers          293601280 bytes
Redo Buffers                5763072 bytes
Database mounted.

7. Disable Archivelog mode

SQL> alter database noarchivelog;
Database altered.

8. Enable the RAC

SQL> alter system set cluster_database=true scope=spfile sid='RAC1';
System altered.

9. Shutdown the DB

SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

10. Start the All RAC instances

[oracle@ol5-112-rac1 ~]$ srvctl start database -d RAC

E-mail Generation for Daily Health Check Report via mailx OS utility

For e-mail generation we need SQL script and shell script files. SQL script extract data from DB to health check report and the shell script generate e-mail via mailx utility with sql output and then scheduled this for daily. 

SQL Scripts

[oracle@kiawiz ~]$ cat ts.sql
set feedback off echo off
set linesize 100
set pagesize 200
set colsep |
SET TRIMSPOOL ON
spool /home/oracle/tablespace.log
column tablespace_name format a25
column FreePC format a15
column FullPC format a15
select b.tablespace_name,round(tbs_size,0) SizeMb,round((tbs_size- a.free_space),0)UsedMb,round(a.free_space,0) FreeMb,to_char((round(((a.free_space*100)/tbs_size),2)),'00.00')||'%' FreePC,to_char((100-round(((a.free_space*100)/tbs_size),2)),'00.00')||'%' FullPC
from (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
from dba_free_space group by tablespace_name
UNION
select tablespace_name,((free_space/1024)/1024) free_space
from dba_temp_free_space) a,
(select tablespace_name, sum(bytes)/1024/1024 as tbs_size
from dba_data_files group by tablespace_name
UNION
select tablespace_name, sum(bytes)/1024/1024 tbs_size
from dba_temp_files
group by tablespace_name ) b
where a.tablespace_name=b.tablespace_name
order by 100-round(((a.free_space*100)/tbs_size),2) desc;

select count(*) Invalid_objects from dba_objects where status ='INVALID';

column object_name format a30
column owner format a15
select object_name,owner,object_type from dba_objects where status ='INVALID';
spool off
exit

Schell Script

[oracle@kiawiz ~]$ cat tablespace_new.sh
#!/bin/sh
. /finsys/db/tech_st/11.1.0/PROD_kiawiz.env
cat /home/oracle/tablespace.log
sqlplus '/ as sysdba' << EOF
@ts.sql;
EOF
if [ `cat /home/oracle/tablespace.log|wc -l` -gt 0 ]
then

echo -e "\n"  2>&1>>/home/oracle/tablespace.log
df -h 2>&1>>/home/oracle/tablespace.log

cat /home/oracle/tablespace.log |mailx -s "Production instance - Health Check" test@gmail.com
rm -rf /home/oracle/tablespace.log
fi

Schedule .sh file via crontab

00 09 * * 0-6 . /home/oracle/tablespace_new.sh

ORA-00020: maximum number of processes (%s) exceeded

Problem

The issue is due to process/sessions parameter exceeds the limit. Need to increase those parameter to solve this issue.


Solution

When ORA-00020 occurred then you wont able to  connect to the DB via sys user to change the parameter. You can use following steps to overcome the issue.

1. Login to DB using following command

[oracle@prod 11.1.0]$ sqlplus -prelim "/ as sysdba"

2. Shutdown the DB

SQL>shutdown abort;

3. Exit from session

SQL>exit;

4. Connect to DB via sys

[oracle@prod 11.1.0]$ sqlplus / as sysdba

5. Start the DB

SQL>startup;

6. Check the Value for Process and sessions parameter

SQL>select resource_name, current_utilization, max_utilization, limit_value from v$resource_limit where resource_name in ('sessions', 'processes');

7. Change the Parameter values

SQL> alter system set processes=300 scope=spfile;
SQL> alter system set sessions=500 scope=spfile;

Tablespace Related Queries

Create Tablespace   

SQL> create tablespace xxli_ts01 datafile '/finsys/UAT1/db/apps_st/data/xxli_data.dbf' SIZE 1500M autoextend on next 256M maxsize 10000M;

Resize Tablespace 

SQL> alter database datafile '/finsys/UAT1/db/apps_st/data/sysaux02.dbf' resize 7000M;

Add Datafile

SQL> alter TABLESPACE APPS_TS_TX_DATA add datafile '/finsys/UAT1/db/apps_st/data/a_txn_data05.dbf' SIZE 1800M autoextend on next 256M maxsize 3500M;

Auto extend on/off

SQL> ALTER DATABASE DATAFILE '/finsys/UAT1/db/apps_st/data/xxkia01.dbf' AUTOEXTEND ON maxsize unlimited;

SQL> ALTER DATABASE DATAFILE '/finsys/UAT1/db/apps_st/data/xxkia01.dbf' AUTOEXTEND OFF;

Unlimited Quota

SQL> alter user apps quota unlimited on apps_ts_tx_data;

Drop datafile

SQL> alter tablespace APPS_TS_TX_DATA drop datafile '/finsys/UAT1/db/apps_st/data/xxkia01.dbf';

Move Datafile

1)

SQL> ALTER TABLESPACE XXTEST OFFLINE NORMAL;

2)

Copy the datafile (/tmp/test.dbf) to their new location (/finsys/UAT1/db/apps_st/data/) and rename (XXTEST1.DBF) them using the operating system command

3)

SQL> ALTER TABLESPACE XXTEST RENAME DATAFILE '/tmp/test.dbf' TO '/finsys/UAT1/db/apps_st/data/XXTEST1.DBF';

4)

SQL> ALTER TABLESPACE XXTEST ONLINE;

Tablespace Related Views

dba_data_files

dba_free_space

v$tablespace

Create Temp Tablespace

SQL> create temporary tablespace temp4 tempfile '/finsys/UAT1/db/apps_st/data/temp04.dbf' size 1000M tablespace group TEMP;

Resize Temp Tablespace

SQL> ALTER DATABASE TEMPFILE '/finsys/UAT1/db/apps_st/data/temp01.dbf' RESIZE 1000M;

Add Temp File

SQL> alter TABLESPACE TEMP1 add tempfile '/finsys/UAT1/db/apps_st/data/temp01.dbf' SIZE 2000M autoextend on next 256M maxsize 10240M;

Drop Temp File

SQL> alter TABLESPACE TEMP1 drop tempfile '/finsys/UAT1/db/apps_st/data/temp01.dbf';

Drop Temp Tablespace

SQL> DROP TABLESPACE temp1 including CONTENTS AND DATAFILES;

Make  Temp Tablespace default

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp4;

Adding tablespce to group

SQL> ALTER TABLESPACE temp01 TABLESPACE GROUP TEMP;

Remove tablespce from group

SQL> ALTER TABLESPACE temp01 TABLESPACE GROUP '';

Temp Tablespace Related Views

DBA_TEMP_FILES

dba_temp_free_space

DBA_TABLESPACE_GROUPS;

V$TEMP_SPACE_HEADER

Details/Usage of Tablespaces

set linesize 5000;
col tablespace_name for a30;
col FreePC for a10;
set pagesize 1000;
select
b.tablespace_name,
round(tbs_size,0) SizeMb,
round((tbs_size- a.free_space),0)UsedMb,
round(a.free_space,0) FreeMb,
to_char((round(((a.free_space*100)/tbs_size),2)),'00.00')||'%' FreePC,
to_char((100-round(((a.free_space*100)/tbs_size),2)),'00.00')||'%' FullPC
from 
(  select 
     tablespace_name, 
     round(sum(bytes)/1024/1024 ,2) as free_space 
   from dba_free_space 
   group by tablespace_name
 UNION
   select 
     tablespace_name,
     ((free_space/1024)/1024) free_space
   from dba_temp_free_space
)a, 

(  select 
     tablespace_name,
     sum(bytes)/1024/1024 as tbs_size 
   from dba_data_files 
   group by tablespace_name
 UNION
   select 
     tablespace_name,
     sum(bytes)/1024/1024 tbs_size
   from dba_temp_files
   group by tablespace_name 
)b
where a.tablespace_name=b.tablespace_name
order by 100-round(((a.free_space*100)/tbs_size),2) desc;

OR

set linesize 5000;
col tablespace_name for a30;
col FreePC for a10;
set pagesize 1000;
select
b.tablespace_name,
round(tbs_size,0) SizeMb,
round((a.tot_used),0)UsedMb,
round((tbs_size-a.tot_used),0) FreeMb,
decode((round(((a.tot_used*100)/tbs_size),2)),0,(100-round(((a.tot_used*100)/tbs_size),2)||'%'),(to_char((100-round(((a.tot_used*100)/tbs_size),2)),'00.00')||'%')) FreePC,
to_char((round(((a.tot_used*100)/tbs_size),2)),'00.00')||'%' FullPC
from 
(  select 
     tablespace_name, 
     sum(bytes/1024/1024) tot_used
   from dba_segments
   group by tablespace_name
 UNION
   select 
     tablespace_name,
     round(used_blocks*8/1024) tot_used
   from v$sort_segment 
)a, 
(  select 
     tablespace_name,
     sum(bytes)/1024/1024 as tbs_size 
   from dba_data_files 
   group by tablespace_name
 UNION
   select 
     tablespace_name,
     sum(bytes)/1024/1024 tbs_size
   from dba_temp_files
   group by tablespace_name 
)b
where a.tablespace_name=b.tablespace_name
order by round(((a.tot_used*100)/tbs_size),2) desc;