What is mounting the databases in Oracle?

After starting the instance, the Oracle software associates the instance with specific database. This is called mounting the database.

Oracle Database Memory Structure

Oracle Instance & Database Structure

ORA-00942: table or view does not exist or Warning: View altered with compilation errors.

Error:

SQL> ALTER VIEW HR.TMP_TABLE_REORG COMPILE; Warning: View altered with compilation errors.

How to find out the cause:

Use the query below to find the query which makes the View

SQL> set long 10000 SQL> set pages 0 SQL> select dbms_metadata.get_ddl('VIEW','TMP_TABLE_REORG','HR') from dual;

Output will look like;

SQL>  select "TABLE_NAME","COLUMN_NAME","CONSTRAINT_NAME","T_TABLE_NAME","START_TIME  ","END_TIME","RELOAD_START_TIME","RELOAD_END_TIME","OBJECT_SIZE_MB","T_F_CW02TMP _MB","N_REQ_CW02TMP_MB","PRE_ORIG_ROWCNT","PRE_TMP_ROWCNT","POST_ORIG_ROWCNT","
P  OST_TMP_ROWCNT" from REORG_USR.tmp_table_reorg;
OST_TMP_ROWCNT" from REORG_USR.tmp_table_reorg

Try to run the “Select statement” which makes the query. You will get error message as; It means that physical table that view depends on does not exist cause the issue.

ORA-00942: table or view does not exist

ASM – Automatic Storage Management

 

ASM : is the vertical integration of File system and Volume Manager, specifically built for Oracle Database files.

Advantages:

1. Dynamic storage management, without shut downs of the Oracle database, storage can be adjusted.
2. I/O Load balancing : distribute the I/O Load across all available resource to optimize the performance, while removing the manual need.
3. Provide fault tolerance by maintaining redundant copy of data, per file basis.
   Supports mirroring at file level not at volume level.
4. Supports online disk reconfiguration and dynamic rebalancing
5. stripes files not the logical volumes
6. Supports RAC and eliminates need of
   Cluster File system or Cluster Logical Volume Manager.

ASM divides data files into extent. This extent is different from data file extent.
Spread each extents across all the disks. It uses indexes to track the placement of each extent.

Oracle Database Storage Structure

 

Oracle DB Storage Structure

Relationship of Database, Tablespace and Data files

 

How table data is stored in Oracle Database

Content of Database Data Block

How to find the instance names of ASM on the cluster nodes

 

crsctl status resource ora.asm –f | grep servername

How to current directory to be displayed in the prompt, while using ASMCMD command?

asmcmd –p

-p causes the current directory to be displayed in the prompt.

+ signs denotes the root directory

To list the Disk group details

ASMCMD [+] > ls -l +

State    Type    Rebal  Name
MOUNTED  EXTERN  N      DG_Test_DATA_01/
MOUNTED  EXTERN  N      DG_Test_FLASH_01/

To list the sizing information for each Disk group

ASMCMD [+] > ls -s +

Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Name
  1024   4096  8388608   3407872   665384                0          665384              0  DG_Test_DATA_01/
  1024   4096  8388608    524288   152944                0          152944              0  DG_Test_FLASH_01/

To display the content of current directory and its subdirectory

ASMCMD [+] > ls *

To find  file name matching the given query

ASMCMD [+] > find + *.dbf

How to find the Oracle databases running in a Unix Server?

 

First Option:
It will list all the currently running instances

ps -eaf | grep pmon | grep -v grep | awk ' {print $9}' | cut -c10-18

Second Option:
Check for the Oratab entries

Difference between Oracle Database Crash and Oracle Instance Crash both in Standalone and RAC Environment

 

	Instance Crash	Database Crash
RAC	Instance and DB crash are different. In RAC, there is not a single instance mounting the database. It's always more than one. Now, as long as you have at least one instance up and running and have the db opened via it, the other instance's crash is called instance crash only.	If all the instances of the cluster get crashed, it eventually would mean that the db has to go down , this would be called database crash.
Stand alone	Instance and DB crash are same	Instance and DB crash is one because, there would be just a single instance linked with a database. So if that crashes, you would have the db going down, aka, getting crashed as well.
Definition	An instance crash will therefore mean that the crash occurs in the instance due to an Oracle bug or similar. In other words a s/w error and not a database error	Database crash means that the database itself is damaged in some way. A disk is no more. Data files are corrupted. Etc. The database itself thus causes the crash - and a database crash will also cause all database instances (RAC or single) for that database to crash too.

Oracle Enterprise Manager – OEM

 

OEM Management Framework configuration consists of

1. Managed Targets
2. Management Services
3. Grid Control
4. DB Console
5. Application Server Console

OEM Services in Windows.

OEM DB Console in Unix

Start dbconsole

emctl start dbconsole

Stop dbconsole

emctl stop dbconsole

Status dbconsole

emctl status dbconsole

Accessing Enterprise Manager Database Control, using browser client

http://hostname:portnumber/em

ORA-19511: Error received from media manager layer

 

RMAN Backup Failed with

Error Message:
ORA-19506: failed to create sequential file, name="<SYS00S_1846:751266012:1:33>.df", parms=""
ORA-27028: skgfqcre: sbtbackup returned error
ORA-19511: Error received from media manager layer, error text:
   Vendor specific error: OB2_StartObjectBackup() failed ERR(-17)

Cause:
The root cause of the backup failures was that the VLS ran out of tapes. 

Resolution:
Free up tapes from expired backups or add new tapes. 
Reboot the VLS to get the backups working again.

How to find the Preferred node in Oracle RAC?

 

To find the preferred node in Oracle RAC from the configuration. It find the details from Oracle Cluster Registry (OCR). It displays the Oracle Restart configuration information

$ srvctl config service -d db01 db01_Kline PREF: db0101t1 AVAIL: db01t2 db01_watch PREF: db01t2 db01t1 AVAIL:

To find the status of the services, on which instances, it  is running

$ srvctl status service -d db01 Service db01_Kline is running on instance(s) db01t2 Service db01_watch is running on instance(s) db01t2, db01t1

To relocate the services from one node to another or one instance to another

srvctl relocate service -d db_unique_name -s service_name {-c source_node -n target_node | -i old_instance_name -t new_instance_name} [-f]

What is Service in Oracle RAC?

Services allow you granular definition of workload and the DBA can dynamically define which instances provide the service. Connection Load Balancing (provided by Oracle Net Services) still needs to be set up to allow the user connections to be balanced across all instances providing a service.

GV$ Views on Oracle RAC

When administrating a RAC system use the dictionary views of GV$ and not V$.
GV$ has additional columns inst_id .

For almost every V$ view described in this chapter, Oracle has a corresponding GV$ (global V$) view. In Real Application Clusters, querying a GV$ view retrieves the V$ view information from all qualified instances.

In addition to the V$ information, each GV$ view contains an extra column named INST_ID of datatype NUMBER. The INST_ID column displays the instance number from which the associated V$ view information was obtained. The INST_ID column can be used as a filter to retrieve V$ information from a subset of available instances

GV$	V$
Across all the nodes: The beauty of system dictionary views of GV$ which reflect information across the various nodes of a RAC system	Only the current node The V$ views of the data dictionary, which relate to the current status of the single node you are connected to

For example;

select distinct(username) from v$session;

It only reflects connections to the connected node and not the entire RAC.
Whereas;

select distinct(username) from gv$session;

It gives the view of all sessions within the RAC.
The GV$ dictionary views have the additional INST_ID column which give the node that the action is occurring in

How to reset the Unix Prompt to default? ( PS1)

How do I get my prompt to show the current directory, like on DOS?

PS1 – stands for Prompt String 1

In korn shells

PS1='$PWD$ '

in Bourne shells

PS1="`pwd` "

WARNING: db_recovery_file_dest is same as db_create_online_log_dest_2

 

The warning message on Alert log file mean both the FRA – Flash Recovery Area and Online Log are same.

It can be verified by

SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +DG_DEV_FLASH_01
db_recovery_file_dest_size         big integer 220G
recovery_parallelism                 integer     0

SQL> show parameter online

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_online_log_dest_1          string      +DG_DEV_DATA_01
db_create_online_log_dest_2          string      +DG_DEV_FLASH_01
db_create_online_log_dest_3          string
db_create_online_log_dest_4          string
db_create_online_log_dest_5          string

Are Oracle instances down on the host?

This script would be of useful as a Pre-Check for Patching and Linking

#!/bin/sh cat <<eof Check if Oracle Down? Pre-Check for Patching and Linking Remember to also stop all cron and scheduled jobs eof ps -ef|egrep -i "oracle| sqlplus| rman| sqlldr| imp| exp| impdp| expdp| dgmgrl|tnslsnr|fastreader"

How to find if the instance using PFILE or SPFILE?

 

Execute the following query to see if the instance was started with a PFILE or SPFILE:

SELECT value, DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
FROM sys.v_$spparameter WHERE name = 'spfile';

drop database command

The Drop Database command, that made a fast and efficient cleanup of all files below in one command.

1. Data files
2. Redo logs
3. Control files
4. spfile  -  If the database used a server parameter file (spfile), it is also deleted

Archived logs and backups are not removed, but you can use Recovery Manager (RMAN) to remove them.

Drop database do require to mount the database in exclusive restricted mode, that made quite difficult to execute it by mistake on the wrong database.

The steps are as follows:

shutdown abort;
startup mount exclusive restrict;
drop database;

This is a sample session using this feature:

1) Check Datafile, Controlfile and Online log names before drop database

  select name from v$database;
  select name from v$datafile;
  select member from v$logfile;
  select name from v$controlfile;

2) Drop Database

shutdown abort
ORACLE instance shut down.
startup mount exclusive restrict;
ORACLE instance started.

Total System Global Area  989855744 bytes
Fixed Size                  2077552 bytes
Variable Size             864029840 bytes
Database Buffers          117440512 bytes
Redo Buffers                6307840 bytes
Database mounted.
drop database;
Database dropped.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

3) Check Datafile/Redologs/Controlfile after drop database

 ls -ltr