Ulfat`s DataBase Blog

Today when I tried to restart my test database I got strange but known error. SQL> startup ORA-32004: obsolete and/or deprecated parameter(s) specified ORACLE instance started. Total System Global Area 5344731136 bytes Fixed Size 2163168 bytes Variable Size 2298482208 bytes Database Buffers 3036676096 bytes Redo Buffers 7409664 bytes Database mounted. ORA-01122: database file 22 failed verification check ORA-01110: data file 22: '/u01/app/oracle/oradata/???/archive_tbs01.dbf' ORA-01200: actual file size of 65536 is smaller than correct size of 402176 After researching it means inconsistency of controlfile. So, we need recreate or restore it from backup. Do one of the following: SQL > alter database backup controlfile to trace; Database altered. or SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/u01/app/oracle/admin/PROD/bdump/controlfile.bkp'; Database altered. or SQL> ALTER DATABAS...

Today I could not enabled autorace and reason was SP2-0618. After looking for in google I found solution and now want to share it. SQL> set autotrace traceonly; SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled SP2-0611: Error enabling STATISTICS report To enable autotrace PLUSTRACE role should be exists! If not exists create it. Under $ORACLE_HOME\sqlplus\admin\ you may execute by SYS user  SQL> @$ORACLE_HOME\sqlplus\admin\plustrce.sql;  Another way you may just manually create plustrace role and assign select on necessary views; SQL> create role plustrace; Role created. SQL> grant select on v_$sesstat to plustrace; Grant succeeded. SQL> grant select on v_$statname to plustrace; Grant succeeded. SQL> grant select on v_$mystat to plustrace; Grant succeeded. SQL> grant plustrace to dba with admin option; Grant succeeded. At the end grant plustrace role to needed user; SQL> ...

Today I want to show you how to enable archivelog mode on Oracle 11g R2 RAC environment. 1. Checking log_mode: [oracle@rac1 acfs]$ export ORACLE_SID=RAC1 [oracle@rac1 acfs]$ sqlplus "/as sysdba" SQL> archive log list Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 9 Current log sequence 10 SQL> [oracle@rac2 acfs]$ export ORACLE_SID=RAC2 [oracle@rac2 acfs]$ sqlplus "/as sysdba" SQL*Plus: Release 11.2.0.1.0 Production on Mon Oct 13 18:13:53 2014 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> select name, open_mode, log_mode from v$database; NAME OPEN_MODE LOG_MODE --------- -...

Today after restarting primary and standby database servers I faced with ORA-16810 error DGMGRL> show configuration; Configuration - DGMANAGER   Protection Mode: MaxAvailability   Databases:     XXXPROD - Primary database       Error: ORA-16810: multiple errors or warnings detected for the database     XXXSTND - Physical standby database       Error: ORA-01017: invalid username/password; logon denied Fast-Start Failover: DISABLED Configuration Status: ERROR I did not understand what and why it happened... After research I found that it was related password file of standby side. DGMGRL> show database 'XXXPROD'   Database - XXXPROD   Role:            PRIMARY   Intended State:  TRANSPORT-ON   Instance(s):     FCDBPROD       Error: ORA-16737: the redo transport service for standby database "XXXSTND" has an error ...

Today when I tested dataguard configuration I saw ORA-16826 error. DGMGRL> show configuration; Configuration - DGMANAGER   Protection Mode: MaxPerformance   Databases:     PROD   - Primary database     PRODST - Physical standby database       Warning: ORA-16826: apply service state is inconsistent with the DelayMins property Fast-Start Failover: DISABLED Configuration Status: WARNING DGMGRL> This is because I managed my standby database with NODELAY option. ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  NODELAY; To fix this cancel recover managed: SQL> alter database recover managed standby database cancel; Database altered. Start with current logfile option: SQL> alter database recover managed standby database using current logfile disconnect; Database altered. Try again DGMGRL> show configuration; Configuration - DGMANAGER   Pr...

Hello, Today I want to share my experience about online patching. Download needed patch : https://support.oracle.com For tutorial I got p16928674_112030_Generic.zip file. 1. Go to $ORACLE_HOME/Opatch dir and call opatch with parameters [oracle@dbserver OPatch]$ pwd /u01/app/oracle/product/11.2.0/db_1/OPatch [oracle@dbserver OPatch]$ ./opatch lsinventory Invoking OPatch 11.2.0.1.7 Oracle Interim Patch Installer version 11.2.0.1.7 Copyright (c) 2011, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/11.2.0/db_1 Central Inventory : /u01/app/oraInventory from : /etc/oraInst.loc OPatch version : 11.2.0.1.7 OUI version : 11.2.0.3.0 Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2014-10-08_11-53-07AM.log Lsinventory Output file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2014-10-08_11-53-07AM.txt -------------------------...

Today I could not remember sysman password for database grid control. How carefully may I change sysman password ? Just follow. 1. Stop OMS (go to OMS dir) [oracle@gridrepo bin]$ ./emctl stop oms Oracle Enterprise Manager 11g Release 1 Grid Control Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved. Stopping WebTier... WebTier Successfully Stopped Stopping Oracle Management Server... Oracle Management Server Successfully Stopped Oracle Management Server is Down 2. Config oms password with -change_repos_pwd parameter [oracle@gridrepo bin]$ ./emctl config oms -change_repos_pwd Oracle Enterprise Manager 11g Release 1 Grid Control Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved. -change_in_db option not specified. Password not changed in backend. Enter Repository User's New Password : Updating repository password in Credential Store... Repository password in Credential Store updated successfully. ...

A brief post about add||enable||disable service using SRVCTL. [oracle@oralab1 dbhome_1]$ srvctl config database orcl [oracle@oralab1 dbhome_1]$ srvctl config database -d orcl Database unique name: orcl Database name: orcl Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1 Oracle user: oracle Spfile: +DATA/orcl/spfileorcl.ora Domain: Start options: open Stop options: immediate Database role: PRIMARY Management policy: AUTOMATIC Database instance: orcl Disk Groups: DATA Services: --There is no any service(s). --create new service: SQL> begin 2 dbms_service.create_service('NEWORCL','NEWORCL'); 3 end; 4 / PL/SQL procedure successfully completed. SQL> select name, enabled from dba_services; NAME ENA --------------------------------- --------------------------- SYS$BACKGROUND NO SYS$USERS NO NEWORCL ...

After got API for sending SMS and developed packages we tried to send URL, but we faced below known error. SQL> select alarmsender.pkg_sms_sender.send_sms('Ulfet','99450???????','test') from dual; ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1722 ORA-24247: network access denied by access control list (ACL) ORA-06512: at line 1 Starting Oracle 11g Oracle introduce FGA (Fine Grained Access) for using UTTL_HTTP, UTL_MAIL etc packages. After reading Oracle notes , we need to create ACL and then assgin it. Let`s check our db registery. SQL> set linesize 400 SQL> col comp_name format a40 SQL> select comp_name, status from dba_registry; COMP_NAME STATUS ---------------------------------------- -------------------------------------------- OWB VALID Oracle Application Express VALID Oracle Enterprise Manager VALID OLAP Catalog VALID Spatial VALID Oracle Multimedia V...

Today suddenly I saw more disk usage. After investigation I found what used more space. [oracle@fc-db-tst1 FPREPROD]$ du -sh * 9.4G alert 4.0K cdump 4.0K hm 4.0K incident 4.0K incpkg 4.0K ir 4.0K lck 3.5M metadata 4.0K metadata_dgif 4.0K metadata_pv 4.0K stage 4.0K sweep 7.2G  trace [oracle@fc-db-tst1 alert]$ adrci ADRCI: Release 11.2.0.3.0 - Production on Fri Mar 28 08:17:10 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. ADR base = "/u01/app/oracle" adrci> show homes ADR Homes: diag/rdbms/fpreprod/FPREPROD adrci> show control ADR Home = /u01/app/oracle/diag/rdbms/fpreprod/FPREPROD: ************************************************************************* ADRID                 SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                       ...

Today I found on my friend`s database some application user`s default tablespace is SYSTEM which is not recommended by Oracle. Select users list which default_tablespace is SYSTEM. In my case, application users prefix are APP. SELECT username, default_tablespace FROM dba_users WHERE username like 'APP%'; USERNAME DEFAULT_TABLESPACE APPLWEB SYSTEM APPLDESK SYSTEM APPLTEST SYSTEM Now, use below little bit PLSQL block. BEGIN FOR i IN ( SELECT username FROM dba_users WHERE default_tablespace in ('SYSTEM') and username like 'APP%' ) LOOP EXECUTE IMMEDIATE 'ALTER USER ' || i.username || ' DEFAULT TABLESPACE USERS'; END LOOP; END; / Check SELECT username, default_tablespace FROM dba_users WHERE username like 'APP%'; USERNAME DEFAULT_TABLESPACE APPLWEB USERS APPLDESK USERS APPLTEST USERS

Today when I want to change max size of datafile I got strange error. ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/MYDB/users01.dbf' AUTOEXTEND ON NEXT 1280K MAXSIZE 32GB ORA-03206: maximum file size of (5242880) blocks in AUTOEXTEND clause is out of range Here is reason: The maximum file size for an autoextendable file has exceeded the maximum number of blocks allowed. After research internet I found that instead of using 32GB we can use 32767M. (same values but in MB). Or you may use 31GB too. ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/MYDB/users01.dbf' AUTOEXTEND ON NEXT 1280K MAXSIZE 32767M;

There are have a lot of ways to stop/start primary and standby databases. I will show you one of them. We needed to migrate physically servers to new plaza. So, I had to stop all with clean. Parameters: Primary Stanby db_name FCDBPROD FCDBPROD instance_name FCDBPROD FCDBSTND open_mode READ WRITE MOUNTED database_role PRIMARY PHYSICAL STANDBY ip 10.10.10.10 10.10.10.20 Data guard broker was configured. DGMGRL> show configuration Configuration - DGMANAGER   Protection Mode: MaxPerformance   Databases:     FCDBPROD - Primary database     FCDBSTND - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS 1. Stop applying. # I do it on Standby side DGMGRL> edit database "FCDBSTND" set state="APPLY-OFF"; 2. Shutdown Primary database [oracle@fcdbdb ~]$ export ORACLE_SID=FCDBPROD [oracle@fcdbdb ~]$ dgmgrl / DG...

Today I decided to use Oracle`s magic dgmgrl utility and configure my standby database. Below you can see my environment: PRIMARY SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance; STATUS     INSTANCE_NAME    DATABASE_ROLE    PROTECTION_MODE ------------ ---------------- ---------------- -------------------- OPEN     FCDBPROD      PRIMARY       MAXIMUM PERFORMANCE SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) --------------   674 SQL>  STANBDY SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance; STATUS     INSTANCE_NAME    DATABASE_ROLE    PROTECTION_MODE ------------ ---------------- ---------------- -------------------- MOUNTED      FCDBSTND      PHYSICAL STANDBY MAXIMUM PERF...

How to fix ORA-30009: SQL> create table too_big_tbl as select ROWNUM n, dbms_random.value(100000, 999999) n2  from dual connect by level <= 100000000; 2  select ROWNUM n from dual connect by level <= 100000000                       * ERROR at line 2: ORA-30009: Not enough memory for CONNECT BY operation Check pga_aggregate_target`s value. In my test instance it was not set.      SQL> show parameter pga NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ pga_aggregate_target                 big integer 0 SQL> alter system set pga_aggregate_target = 100 scope = both; System altered. Then try again SQL> create table too_big_t...

Today morning while I checked who last loged on my dbserver I saw strange error. bash-3.00# last /var/adm/wtmp: Value too large to be stored in data type. My OS is AiX bash-3.00# uname -a AIX ?????? 1 6 00F6A54E4C00 Check size of file: -bash-3.00$ ls -lrt /var/adm/wtmp -rw-rw-r--    1 adm      adm      2148154327 Feb 05 08:47 /var/adm/wtmp -bash-3.00$ It is around 2GB. For next analyze copy file to your backup location bash-3.00# cp /var/adm/wtmp /u01/BackUpFiles/ Now you may reset file. bash-3.00# cat </dev/null >/var/adm/wtmp Check again the last command -bash-3.00$ last oracle    pts/1        xx.xx.xx.xx            Feb 05 09:13   still logged in. wtmp begins     Feb 05 09:09 If you want to see the last 10 failed logins. bash-3.00#  who /etc/security/failedlogin | tail -10 oracle      ssh       ...

Hi, after duplicated test databases I found out that location of diagnostic_dest is under $ORACLE_HOME/log/. To change it just type alter system set diagnostic_dest=new_path. It will create related folders under there. Firstly check current path: [oracle@fc-db-tst1 rdbms]$ export ORACLE_SID=FCDBDEV [oracle@fc-db-tst1 rdbms]$ sqlplus "/as sysdba" SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 4 09:53:35 2014 Copyright (c) 1982, 2011, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL>  show parameter diag NAME     TYPE VALUE ------------------------------------ ----------- ------------------------------ diagnostic_dest     string /u01/app/oracle/product/11.2.0 .3/db_1/log Change path. In my case I use Oracle recommended path SQL> alter system set ...

Today I found in my backup log file strange error. RMAN-06207: WARNING: 1 objects could not be deleted for DISK channel(s) due RMAN-06208:          to mismatched status.  Use CROSSCHECK command to fix status RMAN-06210: List of Mismatched objects RMAN-06211: ========================== RMAN-06212:   Object Type   Filename/Handle RMAN-06213: --------------- --------------------------------------------------- RMAN-06214: Datafile Copy   /u01/app/oracle/product/11.2.0.3/db_1/dbs/snapcf_FCDBPROD.f RMAN> Report obsolete; RMAN retention policy will be applied to the command RMAN retention policy is set to recovery window of 3 days Report of obsolete backups and copies Type                 Key    Completion Time    Filename/Handle -------------------- ------ ------------------ -------------------- Control File Copy     1      13-JAN-14   ...

Yesterday I suddenly faced ORA-38761 and ORA-38754 errors. I checked all logs and backups which were available, but why I could not flashback db. Let`s simulate. -- First I will create new user and will create table under new user. This is for checking purpose after flashback. SQL> create user ulfet identified by ulfet; User created. SQL> grant dba to ulfet; Grant succeeded. SQL> conn ulfet/ulfet Connected. -- Note date. If you desire you may note SCN SQL> select systimestamp from dual; SYSTIMESTAMP --------------------------------------------------------------------------- 09-JAN-14 09.13.23.721393 AM +04:00 SQL> SQL> create table test (id number); Table created. SQL> insert into test values (1); 1 row created. SQL> commit; Commit complete. SQL> select systimestamp from dual; SYSTIMESTAMP --------------------------------------------------------------------------- 09-JAN-14 09.15.10.917401 AM +04:00 SQL...