Ulfat`s DataBase Blog

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