Dba daily struggle

ADRCI is an Oracle command-line tool used to manage Oracle Database diagnostic data. $ adrci ADRCI: Release 12.1.0.2.0 - Production on Fri Sep 29 14:28:26 2017 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. ADR base = "/oracle/BBDD1/saptrace" We should set adrci_home we want to work with, in case of show control command and other is needed to work with only one ADR Home.. adrci> show home ADR Homes: diag/rdbms/bbdd1/BBDD1 diag/tnslsnr/hostname10p/listener_bbdd1 If we try to work without selecting one... this will hapen: adrci> show control DIA-48448: This command does not support multiple ADR homes So i selected one on them: adrci> set home diag/rdbms/bbdd1/BBDD1 adrci> show home ADR Homes: diag/rdbms/bbdd1/BBDD1 adrci> show control and now, we can see adrci> show control ADR Home = /oracle/BBDD1/saptrace/diag/rdbms/bbdd1/BBDD1: ************************************************************************* ADR

Hi, in this post, i´m going to explain how to tune Oracle database buffer cache. It is a SGA memory portion that holds some data blocks, is usually the largest structure within the SGA. All SGA are shared with all database users, so if any user needs any data holds in buffer cache he can read them directly instead of reading disk. The goal is to minimize the number of reads that an instance needs to perform from disks.. Blocks in database buffer cache works with LRU (Least Recently Used) algorithm which means, that blocks most used will be in memory, and least used will get out. Of course, if you are using automatic memory management (AMM) you don´t need to set it because this Oracle feature does it for you. When a oracle server process need some block, it first searches in the Buffer cache, if this block was not find in buffer cache server process get it from disk and load it into buffer cache as MRU (Most Recently Used)... Every time any server process fails to find a

First check that your database is working on archivelog mode: SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 32 Next log sequence to archive 34 Current log sequence 34 If not, follow this post database in archivelog mode Second, perform a full backup of your database. RMAN> backup database; Starting backup at 27-SEP-17 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=29 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00003 name=/oracle/db/oradata/orcl/sysaux01.dbf input datafile file number=00001 name=/oracle/db/oradata/orcl/system01.dbf input datafile file number=00013 name=/oracle/db/oradata/orcl/WCPVM_iasactivities.dbf input datafile file number=00012 name=/oracle/db/oradata/orcl/WCPVM_ocs.dbf input da

Today i´m going to tell you how to perform an export data pump launched from crontab. If you has a database in noarchivelog mode an do not want to shutdown it first, in order to make and rman backup it can be a good solution to you. However, if you want to backup your database with rman without shutdown it, you´ll have to activate archivelog mode first see this article: activate archivelog mode on database First step to perform an expdp is to select directory in whitch we are going to locate this export: SQL> set linesize 190 col OWNER for a15 col DIRECTORY_NAME for a35 col DIRECTORY_PATH for a70 select OWNER, DIRECTORY_NAME, DIRECTORY_PATH from dba_directories;SQL> SQL> SQL> SQL> OWNER DIRECTORY_NAME DIRECTORY_PATH --------------- ----------------------------------- ---------------------------------------------------------------------- SYS ORACLE_HOME / SYS ORACLE_BASE

To resize a tablespace in a SAP-Oracle system there are two ways. if you want to resize an existing datafile , you can do it with sqlplus as explained in http://bbdd-error.blogspot.com.es/2017/09/extend-tablespace.html If you want to add another datafile to an existing tablespace you can´t do it with sqlplus because it will generate differences between database catalog and SAP catalog... so it is mandatory to do it with brtools First of all you need to know tablespace name you are going to resize and path in witch you are going to add the new datafile, also it is important follow existing naming policy.. Tablespace Name MBytes Used Free Used Largest --------------------------------- ---------------- ---------------- ---------------- ------ ---------------- SYSTEM 2,048 285 1,763 13.9 1,762 SYSAUX 2,0

for some tasks you need to know witch version of brtools and patched applied your brtools has: unix1:orasap 28> brtools -V BR0651I BRTOOLS 7.20 (42) Patch Date Info 1 2010-01-26 BR*Tools support for Oracle 11g (note 1430669) 9 2010-10-27 BR*Tools support for eSourcing databases (note 1523205) 18 2011-09-07 BR*Tools support for Oracle ASM and Exadata (note 1627541) 25 2012-06-28 Corrections in BR*Tools 7.20 patch 25 (note 1735811) 26 2012-09-12 Corrections in BR*Tools 7.20 patch 26 (note 1763972) 27 2012-09-12 Support for secure storage in BR*Tools (note 1764043) 28 2012-10-25 Corrections in BR*Tools 7.20 patch 28 (note 1780057) 29 2012-12-03 Corrections in BR*Tools 7.20 patch 29 (note 1795814) 30 2013-01-31 Corrections in BR*Tools 7.20 patch 30 (note 1816874) 31 2013-03-26 Corrections in BR*Tools 7.20 patch 31 (note 1838852) 32 2013-05-28 Corrections in BR*Tools 7.20 patch 32 (note 1865897) 33 2013-07-03 Corrections in BR*Tools 7.20 patch 33 (n

When i try to startup i found this error: [oracle@wcp12cr2 ~]$ sqlplus "/ as sysdba" SQL*Plus: Release 12.1.0.1.0 Production on Tue Sep 19 23:31:42 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup; ORA-00845: MEMORY_TARGET not supported on this system this is due to memory target parameter is higher than memory machine, so to startup this database we are going to initiate it with a new init.ora, because direct modifications with any text editor in spfileorcl.ora is not possible (file will become corrupt) [oracle@wcp12cr2 dbs]$ cd $ORACLE_HOME/dbs [oracle@wcp12cr2 dbs]$ ls -ltr total 32 -rw-r--r--. 1 oracle oinstall 2992 Feb 3 2012 init.ora -rw-r-----. 1 oracle oinstall 24 Nov 11 2015 lkORCL -rw-r-----. 1 oracle oinstall 7680 Nov 11 2015 orapworcl -rw-rw----. 1 oracle oinstall 1544 Sep 19 23:41 hc_orcl.dat -rw-r-----. 1 oracle oinstall 2560 Sep 19 23:42 spfileorcl.ora [oracle@wcp12cr2 dbs]$ cat spfi

This is the first thing and Oracle Database Administrator needs to know. First, check with this query tablespace occupation state of all tablespaces in this database: set linesize 170 set pages 60 column pct_used format 999.9 heading "%|Used" column ts_name format a33 heading "Tablespace Name" column Mbytes format 999,999,999,999 heading "MBytes" column used format 999,999,999,999 heading "Used" column free format 999,999,999,999 heading "Free" column largest format 999,999,999,999 heading "Largest" break on report compute sum of Mbytes on report compute sum of free on report compute sum of used on report select nvl(b.tablespace_name,nvl(a.tablespace_name,'UNKNOWN')) ts_name , kbytes_alloc/1024 Mbytes , (kbytes_alloc-nvl(kbytes_free,0))/1024 used , nvl(kbytes_free,0)/1024 free , ((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100 pct_used , nvl(larg

Sometimes there is a table in your database that has a low performance, maybe it is big and contains a lot of historical old data, or maybe a lot of queries are running against this table. Is time to think about Oracle Table Partitioning... I´m going to use Online Redefinition method and RANGE partitioning which is good for historical tables, but you can do same for RANGE or LIST partitioning 1-Online Redefinition: Fisrt, we are going to extract hr.test_table DDL . TEST_TABLE is a table that contains 5000 rows stored in tablespace USERS SQL> set long 3000 SQL> set pages 200 SQL> set linesize 190 SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','TEST_TABLE', 'HR') FROM dual; DBMS_METADATA.GET_DDL('TABLE','TEST_TABLE','HR') -------------------------------------------------------------------------------- CREATE TABLE "HR"."TEST_TABLE" ( "ID" NUMBER, "SMALL_NUMBER" N

Sooner or later every Oracle dba need to change some configuration parameter... First thing i do is to check if database is running with spfile or init file. If database is running with spfile when you execute (if your database is 10g or higher your database probably has spfile) SQL> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/11.2.0 /orcl/dbs/spfileorcl.ora SQL> spfile entry will give you the path where spfile is located, like above, if not, value column will be null.. 1-Modify WITH INIT: show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string initORCL.ora file: db_name='ORCL'

I use this procedure when it is needed to test some DDL and DML statements on a test database. If changes did not work properly, you can flashback database to a point in time before changes started. SQL> SELECT INST_ID, flashback_on, log_mode FROM gv$database; INST_ID FLASHBACK_ON LOG_MODE ---------- ------------------ ------------ 1 NO NOARCHIVELOG So, it is needed to activate flashback and archivelog mode to do that, please see Activate archivelog mode on a database Second step is to enable flashback: SQL> alter database flashback on; Database altered. And after that, we can see RVWR oracle process is started. [oracle@ODIGettingStarted flash_recovery_area]$ ps -ef | grep -i rvwr oracle 10163 1 0 06:04 ? 00:00:00 ora_rvwr_orcl To make sure we can rollback database to restoration point, we make a guarantee restore point, witch means that database will stop in case flashback destination becomes full.. SQL> create res

As you can see, database is in "No Archive Mode" and Archive destination is "/u01/app/oracle/flash_recovery_area" SQL> archive log list Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 308 Current log sequence 309 SQL> show parameter db_recovery_file_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u01/app/oracle/flash_recovery_area db_recovery_file_dest_size big integer 2G We can change archive destination by doing: SQL> alter system set DB_RECOVERY_FILE_DEST='' scope=both SID='*'; System altered. SQL> alter system set log_archive_dest='/u01/app/oracle/prueba' scope=both SID='*'; System altered. We also should to check destination f

ASM detect all disk added to the machine on asm_diskstring path (+ASM database): SQL>; show parameter asm_diskstring NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ asm_diskstring string /dev/oraasm/* But before add any disk to ASM it is highly recommended you validate those disks: query on ASM cadidate and former disks: select INST_ID, NAME, HEADER_STATUS, path , TOTAL_MB, mount_status, OS_MB , GROUP_NUMBER from gV$ASM_DISK where HEADER_STATUS in ('CANDIDATE', 'FORMER');SQL> SQL> SQL> INST_ID NAME HEADER_STATU PATH TOTAL_MB MOUNT_S OS_MB GROUP_NUMBER ---------- ----- ------------ ----------------------------------- ---------- ------- ---------- ------------ 1 CANDIDATE /dev/oraasm/LUN_66E_DG_BBDD 0 CLOSED 1017 0 1 CA

If you have a CANDIDATE or FORMER disk on RAC you can verify if disk is configured properly on both servers this way. Do not do this with a MEMBER disk!! Example of a candidate disk with kfed read: % kfed read /dev/oraasm/LUN_672_DG_BBDD kfbh.endian: 0 ; 0x000: 0x00 kfbh.hard: 0 ; 0x001: 0x00 kfbh.type: 0 ; 0x002: KFBTYP_INVALID kfbh.datfmt: 0 ; 0x003: 0x00 kfbh.block.blk: 0 ; 0x004: blk=0 kfbh.block.obj: 0 ; 0x008: file=0 kfbh.check: 0 ; 0x00c: 0x00000000 kfbh.fcn.base: 0 ; 0x010: 0x00000000 kfbh.fcn.wrap: 0 ; 0x014: 0x00000000 kfbh.spare1: 0 ; 0x018: 0x00000000 kfbh.spare2: 0 ; 0x01c: 0x00000000 100399800 00000000 00000000 00000000 00000000 [................] Repeat 255 times KFED-00322: Invalid content enc