#oraclescripts

[brioteq]

Details should be taken before stopping ORACLE DB for planned activity:

Details should be taken before stopping ORACLE DB for planned activity:

In UNIX and Linux, any planned downtime activity at OS/database level needs the oracle database to shutdown prior to the activity. If its a planned activity, taking below details helps to verify the database after activity completed. Also, it helps to validate and find the reason if any critical error occured after downtime activity. Collect the details and save it in notepad which can be removed…

[brioteq]

Resolving Tablespace alerts- with real time example

Resolving Tablespace alerts- with real time example

An Oracle database consists of one or more logical storage units called Tablespaces, which collectively store all of the database’s data.Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures that conform to the operating system in which Oracle is running.

For example, the simplest Oracle database would have one tablespace and one…

[brioteq]

Troubleshooting ORACLE Database Out of Memory Errors-ORA 4031:

Troubleshooting ORACLE Database Out of Memory Errors-ORA 4031:

Below error will be registered in alert log of ORACLE database when out of memory error occurs: ORA 4031

An ORA-4031 error is raised when memory is unavailable for use or reuse in the System Global Area (SGA). The error message will indicate the memory pool getting errors and high level information about what kind of allocation failed and how much memory was unavailable.

The problem with…

[brioteq]

Real time Capacity planning for ORACLE database- step by step:

Real time Capacity planning for ORACLE database- step by step:

Capacity planning for ORACLE database comes into play when ORACLE file system reaches above threshold level. The UNIX team will receive file system utilization alert and since its ORACLE database disk, they will ask us to clear some space to bring it under threshold.

Before asking for space, we need to check last one year database growth, tablespace utilization in database and how much space…

[brioteq]

Production and standby database- Real time Queries and Scripts:

Production and standby database- Real time Queries and Scripts:

Below are basic SQL commands and scripts to check whether database is production or standby and whether production & standby database are in sync.

1.Query to check database is production or standby:

SQL> select database_role from v$database;

DATABASE_ROLE

PRIMARY

2.Scripts to check archivelog gap in production and standby:

Execute the below query on the Primary database

SQL>…

[brioteq]

Scripts to find table, schema and database size:

Scripts to find table, schema and database size:

Application team will ask ORACLE DB’s to take export backup of tables, schema and database to import in another place. To take backup, we need to know what is the size of the table or schema or database. So that we can check if we have enough space at file system level for saving export backup. Below are SQL Queries,commands and scripts to find schema size, table size and database size.

To find…

[brioteq]

How to find Archivelog destination?

How to find Archivelog destination?

If archivelog destination is 100% full then backup fails, which can be resolved by moving archivelog files to other location. Each and every database have unique archivelog destination.

The archive log location can be found by below commands. Either the directory name mentioned directly or stored in parameter file.

Scenario 1: IF archive location is mentioned in DB_Recovery

UNIX…

[brioteq]

How to enable or disable Archive log mode in Oracle database 11g?

How to enable or disable Archive log mode in Oracle database 11g?

How to enable Archivelog mode in Oracle database 11g Mode of Logging

There are two types of logging modes in Oracle database :-

ARCHIVELOG :- In this type of logging whatever oracle writes in a redo log file related to transactions in database, saved to another location after a log file has been filled . This location is called Archive location. if database is in Archive log mode then in…