Monday, April 9, 2018

Database Basic Commands


List of Topics (Linux, Database, RAC, EBS)
================================================================================================================
Oracle Home Location on Windows Registry:
-->Registry-->HKEY_LOCAL_MACHINE-->SOFTWARE-->WOW6432Node-->Oracle-->KEY_DevSuiteHome1

================================================================================================================
To find exact error line number 257 in script execution:
Get ORA-01031: Insufficient Privileges Running Ademusr.Sql On WF_ERROR Table ( Doc ID 437760.1 )
It will tell you the exact grant which is failing.

declare
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at line 168
ORA-06512: at line 257 <<<<<<<<<
SQL> set echo on;
SQL> spool ademusr.log
SQL> @$AD_TOP/patch/115/sql/ademusr.sql
SQL> spool off;
================================================================================================================


================================================================================================================
List of Background process used by Database-DELL:
[oracle@rac1 ~]$ ps -ef | grep -i DELL|wc -l
36

[oracle@rac1 ~]$
[oracle@rac1 ~]$ ps -ef | grep -i DELL

[oracle@rac1 ~]$

================================================================================================================
To check patch installed by any superseded patch:

Set the appropriate Oracle_Home (Exm-opatch checkInstalledOneOffs -id 23623015)
[root@rac1 ~]# /u01/SC/APPS122/fs2/FMW_Home/oracle_common/OPatch/opatch checkInstalledOneOffs -id 23623015

================================================================================================================
Latest Applied DB Bundle Patch & Policies in DB:
SQL> select * from dba_registry_sqlpatch;--last applied BUNDLE PATCH (BP) in EBS DB

SQL> select POLICY_GROUP, POLICY_NAME, OBJECT_NAME, PACKAGE, FUNCTION from DBA_POLICIES;--security policies in DB
================================================================================================================
Alert Log File Location:
SQL> 
SQL> select value from v$diag_info where name='Diag Trace';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/dell/DELL/trace

SQL>
SQL> show parameter diag

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest                      string      /u01/app/oracle
SQL>

[oracle@rac1 ~]$ find $ORACLE_HOME -name *alert*.log -print

================================================================================================================

orapwd file query from Database: 


-bash-4.4$ orapwd file=orapwPTEST password=sys4nwr entries=30 force=y

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ---------
SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE 0
SQL>

================================================================================================================

EXPDP (Data pump) Directory Recreation and location from Database: 


SQL> SELECT * FROM dba_directories where DIRECTORY_NAME='EXPDP';

SQL> drop directory expdp;
Directory dropped.
SQL> 
SQL> create directory expdp as '/u01/app/oracle/expdp';
Directory created.
SQL> exit
[oracle@rac1  dbs]$ mkdir -p /u01/app/oracle/expdp
[oracle@rac1  dbs]$ chmod -R 777 /u01/app/oracle/expdp/

================================================================================================================
TIMESTAMP_TO_SCN Convert:

Check CURRENT SCN of the Oracle Database

select CURRENT_SCN from v$database;

Example of TIMESTAMP_TO_SCN & SCN_TO_TIMESTAMP function

select timestamp_to_scn(to_timestamp('22-11-2020 19:40:00','DD-MM-YYYY HH24:MI:SS')) "SCN" from dual;

select scn_to_timestamp(39999823174) from dual;


----------------------------------------------
TIMESTAMP on SQL Prompt:
SQL> set time on
09:30:37 SQL>
09:30:39 SQL> set time off
SQL>
SQL>

================================================================================================================

opatch lsinventory output file location: 

-bash-4.4$ $ORACLE_HOME/Opatch/opatch lsinventory -detail

-bash-4.4$ ls -ltr $ORACLE_HOME/cfgtoollogs/opatch/

-bash-4.4$
===============================================================================================================
Creation of User and table in Database:
SQL>

SQL> 
grant connect, resource to u1 identified by u1;
conn u1/u1

create table emp (EName varchar(15), ENo number);
insert into emp (EName, ENo) values ('aaa',10);
insert into emp (EName, ENo) values ('bbb',20);
insert into emp (EName, ENo) values ('ccc',30);
commit;
select * from emp;

ENAME                  ENO
--------------- ----------
aaa                     10
bbb                     20
ccc                     30

SQL>
SQL> 

Commit complete.

SQL> 
================================================================================================================
Datafiles Count in Database
=============================================================
select count(*) from dba_data_files;

select count(*) from v$datafile;


To check Database size:
===================================
An oracle database consists of data files, redo log files, control files, temporary files. The size of the database actually means the total size of all these files. 
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


TO check Actual size of Tablespace in Database in GB
=========================================================
select SUM (bytes)/1024/1024/1024 AS "Size in GB" FROM dba_data_files;


TO check Space Occupied by Data in the Database in GB
=========================================================
select SUM (bytes)/1024/1024/1024 AS "Size in GB" FROM dba_segments;


TO check Control file Size in MB
=========================================================
select SUM (block_size * file_size_blks)/1024/1024 "Size in (MB)" FROM v$controlfile;


To check free space of Tablespace by percentage wise
=============================================================
select a.tablespace_name,
A.Allocated,
B.Freespace,round(b.freespace/a.allocated*100) "% Free"
from
(select tablespace_name ,sum(bytes)/1024/1024 Allocated from dba_data_files group by tablespace_name) A ,
(select tablespace_name,sum(bytes)/1024/1024 Freespace
from dba_free_space group by tablespace_name) B
where a.tablespace_name=b.tablespace_name(+)
ORDER BY "% Free";

TO check utilization of Individual Datafile of tablespace, execute this script
====================================================
SQL> col file_name for a25;

SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;

======================================================
select tablespace_name, ROUND(bytes_free,2) free,ROUND(bytes_total-bytes_free,2) used, ROUND(bytes_total,2) total,perused from (select a.tablespace_name, a.bytes_free,b.bytes_total, ROUND((100-(100*a.bytes_free/b.bytes_total)),2)  perused from (Select tablespace_name,sum(bytes)/1024/1024 bytes_free From dba_free_space Group by tablespace_name) a, (Select tablespace_name,sum(bytes)/1024/1024 bytes_total From dba_data_files Group by tablespace_name) b where a.tablespace_name=b.tablespace_name) where perused >= 0 ORDER BY tablespace_name;

================================================================================================================
TO check CPU Count for Database

SQL> show parameter cpu_count;


NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

cpu_count                            integer     1

================================================================================================================
Automatic Tuning of Undo_retention Causes Space Problems (Doc ID 420525.1)

2.    Set the following instance parameter:
_smu_debug_mode=33554432

With this setting, V$UNDOSTAT.TUNED_UNDORETENTION is not calculated based on a percentage of the fixed size undo tablespace. Instead it is set to the maximum of (MAXQUERYLEN secs + 300) and UNDO_RETENTION.

================================================================================================================
UNDO Tablespace Managment
SQL>
SQL> sho user
USER is "SYS"
SQL>
SQL>
SQL> SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)",
SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;

example output :

Tablespace   Size (MB)    Free (MB)   % Free   % Used
-----------  ---------    ---------   -------  -------
UNDO          10005        474.25       5        95


Output above shows that UNDO tablespace have around 10G which consume 95% from actual size.


Check extents status that allocated for UNDO tablespace :
SQL> 
SQL> SELECT DISTINCT STATUS, SUM(BYTES)/1024/1024, COUNT(*) FROM DBA_UNDO_EXTENTS where tablespace_name = 'UNDO' GROUP BY STATUS;

example output:

STATUS     SUM(BYTES)/1024/1024    COUNT(*)
---------  --------------------  ----------
EXPIRED        2846.3125            684
UNEXPIRED       6676.125            391
ACTIVE              8                 1

Output point 2 and 3 explain that UNDO tablespace is 5% free but more than 25% (2.8 GB) of the space used is because of expired segments which are not yet purged. The problem is not one of space but the issue is caused because UNDO tablespace is holding old data


3.  Managing Undo Tablespace
Several options to deal with this UNDO tablespace problem:

1.  Having UNDO tablespace reporting 100% used, means that the tablespace has allocated all the space in the data files . This does not mean that the undo tablespace is actually "full". Because Oracle will re-use this allocated space when it's necessary.  The actual use can be checked as per the steps listed above in 2. Checking Mechanism.

2.  By adding more UNDO data files, it will help to avoid the monitoring system from complaining about table space. 


The approach to add datafiles to the database is different depending on if the filesystem is managed by Veritas Volume Manager or by the OS. Please contact Oracle Software Support through Service Request to help or give assistance if you face same problem.

 ================================================================================================================
Invalid Objects Check and running utlrp.sql
SQL>
SQL> show user
USER is "SYS"
SQL>
SQL> select count(*) from dba_objects where status='INVALID';

  COUNT(*)
----------
         1

SQL> sql result provides sum at last 
col NVL(OWNER,'GRAND_TOTALS_OF_ABOVE------>>>>') for a40;
set pagesize 100
select NVL(owner,'GRAND_TOTALS_OF_ABOVE------>>>>'),object_type,count(*)
from dba_objects where status='INVALID' 
--and object_type= 'SYNONYM' -- VIEW 
--group by ROLLUP (owner,object_type);
group by grouping sets((),(owner,object_type));

SQL>
column owner format A12
column object_type format A13
select owner,object_type,COUNT(*)
from dba_objects
where status='INVALID'
group by owner, object_type;

SQL>
SQL>
spool invalids.lst
set linesize 200 pagesize 1000
column owner format A11
column object_name format A31
column object_type format A15
column last_ddl_time format A10
select OWNER, OBJECT_NAME,OBJECT_TYPE,LAST_DDL_TIME
from all_objects where status='INVALID'
order by owner;
SQL>
SQL>
SQL> sql query with customized compile result
select o.owner, o.object_type, o.object_name, 'alter '||o.OBJECT_TYPE||' "'||o.OWNER||'"."'||o.OBJECT_NAME||'" compile;'
FROM dba_objects o where o.status='INVALID' and object_type not like 'PACKAGE BODY'--and o.OWNER=''
UNION
select o.owner, o.object_type, o.object_name, 'alter package "'||o.OWNER||'"."'||o.OBJECT_NAME||'" compile body;' 
FROM dba_objects o where o.status='INVALID' and object_type like 'PACKAGE BODY'--and o.OWNER=''
order by 2,1

SQL> sql result for synonym compileation
##### compile the synonyms using the following scripts
set pagesize 10000
set trimspool on
set head off
set echo off
spool /tmp/compiling.lst

select 'alter public synonym "'||synonym_name||'" compile;'
from dba_synonyms where owner='PUBLIC';
select 'alter synonym "'||owner||'"."'||synonym_name||'" compile;'
from dba_synonyms where owner!='PUBLIC';

SQL> Spool off
SQL> @/tmp/compiling.lst

SQL> @/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/utlrp.sql

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2018-03-08 14:32:41

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2018-03-08 14:32:44

DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                  0

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0


Function created.


PL/SQL procedure successfully completed.


Function dropped.


PL/SQL procedure successfully completed.

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

  COUNT(*)
----------
         0

SQL>

================================================================================================================
Stats:
select s.name, m.value/1024/1024 MB FROM V$SYSSTAT s, V$MYSTAT m
WHERE s.statistic# = m.statistic# AND
(s.name LIKE 'physical%total bytes' OR s.name LIKE 'cell phys%'
OR s.name LIKE 'cell IO');


NAME                                                                     MB
---------------------------------------------------------------- ----------
physical read total bytes                                                 0
physical write total bytes                                                0
cell physical IO interconnect bytes                                       0
cell physical IO bytes saved during optimized file creation               0
cell physical IO bytes saved during optimized RMAN file restore           0
cell physical IO bytes eligible for predicate offload                     0
cell physical IO bytes saved by storage index                             0
cell physical IO bytes sent directly to DB node to balance CPU            0
cell physical IO interconnect bytes returned by smart scan                0

9 rows selected.

SQL>


Wait events:
SELECT DISTINCT event total_waits,
time_waited/100 wait_secs, average_wait/100 avg_wait_secs
FROM V$SESSION_EVENT e, V$MYSTAT s
WHERE event LIKE 'cell%' AND e.sid = s.sid;

no rows selected

 All Wait events:
select event, total_waits,
time_waited/100 wait_secs, average_wait/100 avg_wait_secs
from v$system_event where event like 'cell%';

 no rows selected

 SQL>

================================================================================================================
Forcefully Create Snapshot for Database to Generate AWR Report

 SQL> show user;
USER is "SYS"
SQL>
SQL> exec DBMS_WORKLOAD_REPOSITORY.create_snapshot;

 PL/SQL procedure successfully completed.

SQL> exec DBMS_WORKLOAD_REPOSITORY.create_snapshot;

 PL/SQL procedure successfully completed.

 SQL> 
================================================================================================================
Redo Transport Compression in a Data Guard Environment (Doc ID 729551.1)

 Oracle Database has the ability to compress redo as it is transmitted over a network by Data Guard Redo Transport Services.

 By setting this parameter "compression=enable" in datagaurd configuration, it allows to compress archive log on air and send to Standby Database server

 SQL> show parameter dest_2

 NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      service="ahcstby1", ASYNC NOAF
                                                 FIRM delay=0 optional compress
                                                 ion=enable max_failure=0 max_c
                                                 onnections=1 reopen=30 db_uniq
                                                 ue_name="ahcstby1" net_timeout
                                                 =300, valid_for=(online_logfil

                                                 es,all_roles)

OR

DGMGRL> edit database 'STDBY' set property 'RedoCompression' = enable;






Posted by at
Labels: , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)