Ulfat`s DataBase Blog

Today I faced with new ORA error. After solving I want to share this experience with yours. So, today 5`th datafile of my database was corrupted (/u01/app/oracle/oradata/ulfet_db/example01.dbf). After recover via RMAN I saw strange error. RMAN> recover datafile 5 block 443; Starting recover at 24-MAR-13 using channel ORA_DISK_1 channel ORA_DISK_1: restoring block(s) channel ORA_DISK_1: specifying block(s) to restore from backup set restoring blocks of datafile 00005 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ULFET_DB/backupset/2013_03_24/o1_mf_nnndf_TAG20130324T223233_8nykp220_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ULFET_DB/backupset/2013_03_24/o1_mf_nnndf_TAG20130324T223233_8nykp220_.bkp tag=TAG20130324T223233 channel ORA_DISK_1: restored block(s) from backup piece 1 channel ORA_DISK_1: block restore complete, elapsed time: 00:00:03 starting media recovery media recovery complete, elapsed ti...

Today I will show you how to rename existing database name. Before it take backup of your database and also please note that this step will change only database name not dbid. My database name is prod2 and oracle version is 11g R2. I will change it to prod name. SQL> select name from v$database; NAME --------- PROD2 SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production PL/SQL Release 11.2.0.1.0 - Production CORE    11.2.0.1.0      Production TNS for Linux: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production SQL> [oracle@localhost ~]$ uname -n localhost.localdomain [oracle@localhost ~]$ more /etc/redhat-release  Red Hat Enterprise Linux Server release 5.5 (Tikanga) Firstly shutdown database and open it on mount mode. [oracle@localhost ~]$ sql SQL*Plus:...

If in your production database configured audit, DBA should maintain audit tables. Because audit records may grows up to undesired size. Today I will show you one of the easy way to care aud$ table. Scenario: Create new tablespace Create new archive table Create procedure Create Scheduler or Cron Job Execute and check result  Firstly I will create new tablespace for my achived aud table. SQL> create tablespace arch_tbs datafile '/u01/app/oracle/oradata/ulfet_db/arch_tbs01.dbf' size 500M; Tablespace created. -- you can set more space SQL> alter tablespace arch_tbs add datafile '/u01/app/oracle/oradata/ulfet_db/arch_tbs02.dbf' size 500M; Tablespace altered. Now I will create new table with aud$ table structure but new table will be range partitioned. --You can create daily/monthly/yearly partition also subpartition too. To get table structure I use dbms_metadata.get_ddl package`s procedure. SQL> set pagesize 1000 SQL...

Today I tried to get structure of aud$ table using dbms_metadata.get_ddl. But result shows only a few lines not entire table. SQL> select dbms_metadata.get_ddl('TABLE', 'AUD$') from dual; DBMS_METADATA.GET_DDL('TABLE','AUD$') ------------------------------------------------------------- CREATE TABLE "SYS"."AUD$" ( "SESSIONID" NUMBER NOT NULL ENABLE, "ENTRYI SQL> But to get all source I used to_char function and it provide me all source. SQL> select to_char(dbms_metadata.get_ddl('TABLE', 'AUD$')) from dual; CREATE TABLE "SYS"."AUD$" ( "SESSIONID" NUMBER NOT NULL ENABLE, "ENTRYID" NUMBER NOT NULL ENABLE, "STATEMENT" NUMBER NOT NULL ENABLE, "TIMESTAMP#" DATE, "USERID" VARCHAR2(30), "USERHOST" VARCHAR2(128), "TERMINAL...

Creating simple function we can get table`s size. CREATE OR REPLACE FUNCTION ULFET.CHECK_TABLE_SIZE(TAB_NAME VARCHAR) RETURN varchar IS    tab_size number;    str varchar2(50); BEGIN        SELECT  SUM (bytes) / (1024 * 1024)  INTO tab_size      FROM sys.dba_extents WHERE segment_type = 'TABLE' AND segment_name = TAB_NAME GROUP BY segment_name; str := tab_name||' size is '||tab_size||' MB'; RETURN str; END; / P.S: If compilation will error grant select on sys.dba_extents to desired user and repeat creation. SQL> conn /as sysdba SQL> grant select on sys.dba_extents to ulfet; Grant succeeded. SQL> --call select ULFET.CHECK_TABLE_SIZE('TEST_TAB') from dual; or set serveroutput on declare l_answvarchar2(100); begin l_answ:=ULFET.CHECK_TABLE_SIZE('TEST_TAB'); dbms_output.put_line(l_answ); end; select ULFET.CHECK_TABLE_SIZE('TEST_TAB') from dual; P.S: Please note that table_name s...

As you know using DBCA (Database Configuration Assistant) we can easily delete database. Alternatively we can drop database using RMAN utility. Before to do that, database should be opened as restricted mode. [oracle@localhost bin]$ rman target / Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jan 5 22:10:40 2013 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved. connected to target database (not started) RMAN> startup nomount Oracle instance started Total System Global Area     146472960 bytes Fixed Size                     1335080 bytes Variable Size                 92274904 bytes Database Buffers              50331648 bytes Redo Buffers                   2531328 bytes RMAN> alter database mount; using target data...

Every DBA at least one time faced ORA-02055 exception. It may be occur on several reasons. Here is symptom and reason. ORA-02055: distributed update operation failed; rollback required Cause: a failure during distributed update operation may not have rolled back all effects of the operation. Since some sites may be inconsistent, the transaction must roll back to a savepoint or entirely Action: rollback to a savepoint or rollback transaction and resubmit It will be helpful checking DBA_2PC_PENDING data dictionary view  select * from DBA_2PC_PENDING order by fail_time --take local_tran_id or global_tran_id example 8.44.2012311 Then enable distributed recovery and execute below statements using:        ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY      ...