Ulfat`s DataBase Blog

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       ...