ERROR: failed to establish dependency between database and diskgroup resource

Issue:
ERROR: failed to establish dependency between database and diskgroup resource

has found in alert log

Explanation:
This means your database is not configured in clusterware. If you run command "crsctl stat res -t" you will not probably find it in the list.

Solution:
Register your database in clusterware like:
srvctl add database -d -o -p -a "," -r -s
Example:
srvctl add database -d standby -o /u01/app/oracle/product/11.2.0/db_1 -p /u01/app/oracle/product/11.2.0/db_1/dbs/spfilestandby.ora -a "DATA,FRA" -r PHYSICAL_STANDBY -s mount

Oracle Operating System Statistics

-- Operating System Statistics

1. CPU Statistics

V$OSSTAT

                - view captures machine-level information in the database, making it easier for you to determine if hardware-level resource issues exist.

V$SYSMETRIC_HISTORY

                - view shows a one-hour history of the Host CPU Utilization metric, a representation of percentage of CPU usage at each one-minute interval

V$SYS_TIME_MODEL

                - view supplies statistics on the CPU usage by the Oracle database. Using both sets of statistics enable you to determine whether the Oracle database or other system activity is the cause of the CPU problems

--

2. Memory

                This command below includes a great deal of detail, including the size of the mapped segment, how much if it is RSS (Real memory), how much of that is shared, the sizes and permissions of the segment. It is the output above that can be ‘compared’ over time to see if a given process is growing, or leaking memory.

                $ pmap -x $$

                where $$ - process ID

3. Disk I/O Statistics

                - Consumer group

                                V$IOSTAT_CONSUMER_GROUP view captures I/O statistics for all consumer groups that are part of the currently enabled resource plan

                - Database file

                                I/O statistics of database files that are or have been accessed are captured in the V$IOSTAT_FILE view

                                select * from V$IOSTAT_FILE

                - Database function

                                I/O statistics for database functions (such as the LGWR and DBWR) are captured in the V$IOSTAT_FUNCTION view.

                                select * from V$IOSTAT_FUNCTION

4. Network Statistics

                V$IOSTAT_NETWORK - network I/O statistics

                select * from V$IOSTAT_NETWORK

Operating System Data Gathering Tools

Table shows the various tools for gathering operating statistics on *NIX.

                Component        | UNIX Tool

                CPU                       | sar, vmstat, mpstat, iostat

                Memory                              | sar, vmstat

                Disk                        | sar, iostat

                Network                              |netstat

sqlplus: error while loading shared libraries

Issue

sqlplus: error while loading shared libraries: /u01/app/oracle/product/11.2.0/dbhome_1/lib/libclntsh.so.11.1: cannot restore segment prot after reloc: Permission denied

Solution

1.

                [root@linux_pc ~]# cat /etc/selinux/config

                # This file controls the state of SELinux on the system.

                # SELINUX= can take one of these three values:

                #       enforcing - SELinux security policy is enforced.

                #       permissive - SELinux prints warnings instead of enforcing.

                #       disabled - SELinux is fully disabled.

                SELINUX=enforcing

                # SELINUXTYPE= type of policy in use. Possible values are:

                #       targeted - Only targeted network daemons are protected.

                #       strict - Full SELinux protection.

                SELINUXTYPE=targeted

                # SETLOCALDEFS= Check local definition changes

                SETLOCALDEFS=0

                [root@linux_pc ~]# cp /etc/selinux/config /etc/selinux/config.bak

                [root@linux_pc ~]# vi /etc/selinux/config

                [root@linux_pc ~]# cat /etc/selinux/config

                # This file controls the state of SELinux on the system.

                # SELINUX= can take one of these three values:

                #       enforcing - SELinux security policy is enforced.

                #       permissive - SELinux prints warnings instead of enforcing.

                #       disabled - SELinux is fully disabled.

                SELINUX=disabled

                # SELINUXTYPE= type of policy in use. Possible values are:

                #       targeted - Only targeted network daemons are protected.

                #       strict - Full SELinux protection.

                SELINUXTYPE=targeted

2.

                echo 0 >/selinux/enforce

Database compatible parameter is set to "11.2.0.0.0" and diskgroup database compatibility attribute is set to "11.2.0.2.0".

Issue:

Database compatible parameter is set to “11.2.0.0.0” and diskgroup database compatibility attribute is set to “11.2.0.2.0” while creating new database.
Solution
$ cd $ORACLE_HOME/assistants/dbca/templates/
[oracle@db01 templates]$ ls -lha
total 286M
drwxr-xr-x 2 oracle oinstall 4.0K Oct  5 19:03 .
drwxr-xr-x 6 oracle oinstall 4.0K Jun 30 13:01 ..
-rw-r--r-- 1 oracle oinstall 5.0K Oct  5 19:03 Data_Warehouse.dbc
-rw-r--r-- 1 oracle oinstall 5.0K Oct  5 18:56 Data_Warehouse.dbc.bk
-rwxr-xr-x 1 oracle oinstall  21M Sep 22  2011 example01.dfb
-rwxr-xr-x 1 oracle oinstall 1.5M Sep 22  2011 example.dmp
-rw-r--r-- 1 oracle oinstall 4.9K Sep 17  2011 General_Purpose.dbc
-rw-r--r-- 1 oracle oinstall  12K Feb 15  2010 New_Database.dbt
-rwxr-xr-x 1 oracle oinstall 9.3M Sep 22  2011 Seed_Database.ctl
-rwxr-xr-x 1 oracle oinstall 254M Sep 22  2011 Seed_Database.dfb
Edit template
Find
   <InitParamAttributes>
      <InitParams>
         <initParam name="db_name" value=""/>
         <initParam name="dispatchers" value="(PROTOCOL=TCP) (SERVICE={SID}XDB)"/>
         <initParam name="audit_file_dest" value="{ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/adump"/>

Enable DDL Audit

ALTER SYSTEM SET enable_ddl_logging=TRUE
The result will go to
$ORACLE_BASE/diag/rdbms/db1/DB1/alert
log.xml

ORA-1113 when open database

Issue:
alter database open
ORA-1113 signalled during: alter database open...
Starting ORACLE instance (normal)
Solution:
startup mount
ALTER DATABASE RECOVER  database;
or
Check backup mode
        -- Hot backup status
        SELECT  COUNT(*) FROM v$backup WHERE status ='ACTIVE';
        -- Tablespaces in hot backup mode
        SELECT  distinct ddf.tablespace_name tablespace_name
        FROM    v$backup vb, DBA_DATA_FILES ddf
        WHERE   vb.status ='ACTIVE' and vb.file#  = ddf.file_id;
ALTER TABLESPACE (<tbs_name from the query before>) END BACKUP ;

How to convert to cluster database

Convert to cluster DB, make database clustered
1. copy init<>.ora and orapwd<> to another host
2. save spfile to init<>.ora
3. create UNDOTBS2,..
4. add logfiles for thread 2,..
5. assign UNDOTBS for each instance
6. assign instance_number for each instance;

ORA-600 [kkfi.qbcvfr]

Issue: ORA-600 [kkfi.qbcvfr]

Environment:
RDBMS: 11.2.0.2
OS: Linux 5.5

Error in alert log:
ORA-600 [kkfi.qbcvfr]

Solution:
Patch 9965278

Possible solution (temporary):
alter system/session set "_replace_virtual_columns" = false;
alter system/session set "_disable_function_based_index"=true;
alter system/session set "_fix_control"="6754080:off";
alter system/session set "_fix_control"="8706652:off";

Alert log from DB side

--
-- view alert log from DB side
--
-- 0. get info
select * from dba_directories
select * from v$instance
-- 1. create dir
create directory ALERT_LOG as 'G:\SBLSYS\ORADBA\TRACE\BDUMP'
-- 2. create external table
create table alert_log_f (txt varchar2(2000))
organization external (type oracle_loader -- oracle_datapump <-for 10g -- oracle_loader <-for Oracle 9i
default directory ALERT_LOG
--      access parameters (
--          --records delimited by newline
--          nologfile nobadfile
--          fields terminated by 2000 ltrim
--      )
  location('alert_orcl.log'))
reject limit unlimited;
drop table alert_log_f
-- 3. check
select * from alert_log_f where lower(txt) like '%ora-%'

How to enable Automatic Memory Management in Oracle 11G

How to enable Automatic Memory Management in Oracle 11G
1. Set MEMORY_TARGET as (memory_target = sga_target + max(pga_aggregate_target, maximum PGA allocated))
        ALTER SYSTEM SET MEMORY_TARGET = mM SCOPE = SPFILE;
2. Set parameter MEMORY_MAX_TARGET
        ALTER SYSTEM SET MEMORY_MAX_TARGET = nM SCOPE = SPFILE;
Note:   1. n > m
                2. if don't specify MEMORY_TARGET it sets to zero by default
                        and you can manipulate its value dynamicaly not exceeding MEMORY_MAX_TARGET
3. Set SGA_TARGET to zero
        ALTER SYSTEM SET SGA_TARGET = 0;
4. Set PGA_AGGREGATE_TARGET to zero
        ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0;
       
Note:
The preceding steps instruct you to set SGA_TARGET and PGA_AGGREGATE_TARGET to zero
so that the sizes of the SGA and instance PGA are tuned up and down as required,
without restrictions. You can omit the statements that set these parameter values to zero
and leave either or both of the values as positive numbers.
In this case, the values act as minimum values for the sizes of the SGA or instance PGA.       

Quiescing mode of database state

Quiesced state
Aim: perform DBA actions (compile objects, alter tables ...)
Conditions:
Active non-DBA sessions go on until they are becoming inactive. No active sessions are allowed to be active, but they will be hung. When the database is unquiesced, all hung sessions are resumed and blocking actions are processed. Issued command will wait  for active sessions to become inactive.
RAC: Quiesce affects all instances.
Command:
ALTER SYSTEM QUIESCE RESTRICTED;
ALTER SYSTEM UNQUIESCE;
Control:
V$BLOCKING_QUIESCE - view where DBA can check the info about sessions that are blocking.

Recover database

RECOVER (SQL Plus command) repost

Recover a Database, Tablespace, Data or Log file.

Syntax:

Full recovery:
  
   RECOVER [AUTOMATIC] [FROM 'location']
      [STANDBY] DATABASE 
         [UNTIL CANCEL] | [UNTIL TIME date] | [UNTIL CHANGE int] | [USING BACKUP CONTROLFILE]
              [TEST | ALLOW int CORRUPTION ]

   RECOVER [AUTOMATIC] [FROM 'location']
      [STANDBY] DATABASE 
         CANCEL

Renaming an Oracle database

The following log shows how a database name was renamed from ORA9 to CAT. SQL> select name from v$database;

Oracle hash function

use of function
select dbms_utility.get_hash_value('your string', 999999, 32768) from dual

Oracle read/write into file

Write to file
declare
  output utl_file.file_type;
  line   varchar2(20000);
begin
--  execute immediate 'alter session set NLS_CHARACTERSET = CL8MSWIN1251';
  output := utl_file.fopen(location => 'directory_name', filename => to_char(sysdate, 'file_name', open_mode => 'A');
  for rec in
    ()
  loop
    line := rec.field1|| '|' || rec.field2 || '|' || rec.field3 || '|' || rec.field4 || '|' || rec.field5;
    utl_file.put(output, line); 
    utl_file.new_line(file => output);
  end loop;
  utl_file.fclose(file => output);
end;

Tkprof

TKPROF: Yes, it is still used but the FOREGROUND_DUMP_DEST is no longer
used. There is info in the Oracle docs on this. Here is the syntax:

Восстановить удаленную процедуру в Oracle и т.п.
select text
from dba_source as of timestamp (systimestamp - interval '3' hour) -- interval '5' minute)
where owner =
   and name =
   and type = 'PACKAGE' --'FUNCTION', 'PROCEDURE'
order by name
        , type
        , line;

Example of how to trace a session and use tkprof

Enable trace for a user session:

SQLPLUS> exec sys.dbms_system.set_sql_trace_in_session(SID, SERIAL, true);
Use tkprof to analyze the trace file:
tkprof [TRACE FILE] [OUTPUT FILE] explain=/ sort=prsela,exeela,fchela
table=ops\$oracle.plan_table