Saturday, February 22, 2014

True way to stop start primary and standby databases.

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 /
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
DGMGRL> 

Stop listener

lsnrctl stop

Shutdown OS


3. Shutdown Standby database

[oracle@fcdbdb_stby ~]$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

DGMGRL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
DGMGRL>

Stop listener

lsnrctl stop

Shutdown OS



After unmount servers, migrate to new place and mount follow below:

Standby side
1. After start OS and listener, startup database

[oracle@fcdbdb_stby ~]$ export ORACLE_SID=FCDBSTND
[oracle@fcdbdb_stby ~]$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

DGMGRL> startup mount
ORACLE instance started.
Database mounted.
DGMGRL>

2. Start primary database

[oracle@fcdbdb ~]$ export ORACLE_SID=FCDBPROD
[oracle@fcdbdb ~]$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

DGMGRL> startup
ORACLE instance started.
Database mounted.
Database opened.
DGMGRL>


3. Enable apply process

DGMGRL> edit database "FCDBSTND" set state="APPLY-ON";
Error: ORA-12543: TNS:destination host unreachable
Error: ORA-16625: cannot reach database "FCDBPROD"

Failed.
DGMGRL>

There is error. Let check tnsping. There is connection problem. In my situation, after OS restart firewall was enabled. Stop it

/etc/init.d/iptables status #check status

/etc/init.d/iptables stop

If you want to turn off firewall on boot do:

chkconfig iptables off


Try to enable again

DGMGRL> edit database "FCDBSTND" set state="APPLY-ON";
Succeeded.
DGMGRL> show configuration

Configuration - DGMANAGER

  Protection Mode: MaxPerformance
  Databases:
    FCDBPROD - Primary database
    FCDBSTND - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>  show database "FCDBSTND";

Database - FCDBSTND

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    FCDBSTND

Database Status:
SUCCESS

DGMGRL> show database "FCDBPROD";

Database - FCDBPROD

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    FCDBPROD

Database Status:
SUCCESS


Now check archive logs.

On primary:

[oracle@fcdbdb ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Sat Feb 22 15:04:15 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> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
  722

SQL>


On Standby side

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
  722



now on primary switch logfile

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
  724

SQL>



check on standby

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
  722

SQL> /

MAX(SEQUENCE#)
--------------
  724

SQL>



At the end check alertlogs on both side

Check alertlog

stnaby alert

Thread 1 advanced to log sequence 725 (LGWR switch)
  Current log# 2 seq# 725 mem# 0: /u01/app/oracle/oradata/FCDBPROD/redo02.log
Sat Feb 22 15:05:42 2014
Archived Log entry 963 added for thread 1 sequence 724 ID 0xfb8f2b6 dest 1:
LNS: Standby redo logfile selected for thread 1 sequence 725 for destination LOG_ARCHIVE_DEST_2


primary alert

Sat Feb 22 15:05:45 2014
Archived Log entry 461 added for thread 1 sequence 724 ID 0xfb8f2b6 dest 1:
Media Recovery Waiting for thread 1 sequence 725 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 725 Reading mem 0
  Mem# 0: /u01/app/oracle/fast_recovery_area/FCDBSTND/onlinelog/o1_mf_5_9f753t2h_.log


That`s all.

To configure data guard broker and to post this post I got some best practices video and strongly recommend to every one to watch my friend Mahir`s video series about data guard broker.

Tuesday, February 18, 2014

Data Guard Broker Configuration

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 PERFORMANCE

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
  674

SQL> 

Now, set dataguard broker process on both primary and standby databases.

SQL> alter system set dg_broker_start=true;

System altered.

SQL> 


Add a below lines in the listener.ora file on both the primary and standby servers.


On Primary:

   (SID_DESC=
      (GLOBAL_DBNAME=FCDBPROD_DGMGRL)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1)
      (SID_NAME=FCDBPROD)
   )

On Standby

   (SID_DESC=
      (GLOBAL_DBNAME=FCDBSTND_DGMGRL)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1)
      (SID_NAME=FCDBSTND)
   )

[oracle@fcdbdb admin]$ vi listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.3/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.178)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC=
      (GLOBAL_DBNAME=FCDBPROD_DGMGRL)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1)
      (SID_NAME=FCDBPROD)
   )
   (SID_DESC =
     (SID_NAME = FCDBPROD)
     (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/db_1)
   )
 )

ADR_BASE_LISTENER = /u01/app/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON





[oracle@fcdbdb_stby admin]$ vi listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.3/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.179)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC=
      (GLOBAL_DBNAME=FCDBSTND_DGMGRL)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1)
      (SID_NAME=FCDBSTND)
   )
   (SID_DESC =
     (SID_NAME = FCDBSTND)
     (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/db_1)
   )
 )

ADR_BASE_LISTENER = /u01/app/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON
[oracle@fcdbdb_stby admin]$ 



Or, instead of you may add

SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC =
     (SID_NAME = FCDBPROD)
     (GLOBAL_DBNAME=FCDBPROD_DGMGRL)
     (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/db_1)
   )
 )


reload listener on both side

[oracle@fcdbdb_stby admin]$ lsnrctl reload




On the primary server, create the Dataguard Broker configuration.


[oracle@fcdbdb admin]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle@FCDBPROD
Connected.
DGMGRL> create configuration 'DGMANAGER' 
> as primary database is 'FCDBPROD'
> connect identifier is FCDBPROD;
Configuration "DGMANAGER" created with primary database "FCDBPROD"
DGMGRL> 

Check status

DGMGRL> show configuration;

Configuration - DGMANAGER

  Protection Mode: MaxPerformance
  Databases:
    FCDBPROD - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> 



Add the standby database "FCDBSTND" to the configuration created above.


DGMGRL> add database 'FCDBSTND'
> as connect identifier is FCDBSTND
> maintained as physical;      
Database "FCDBSTND" added
DGMGRL> 


DGMGRL> show configuration;

Configuration - DGMANAGER

  Protection Mode: MaxPerformance
  Databases:
    FCDBPROD - Primary database
    FCDBSTND - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> 



The configuration is added, now needs to be enabled.

DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;

Configuration - DGMANAGER

  Protection Mode: MaxPerformance
  Databases:
    FCDBPROD - Primary database
    FCDBSTND - Physical standby database
      Error: ORA-16797: database is not using a server parameter file

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

DGMGRL> 

We have to restart standby database, create spfile and start from spfile.

SQL> show parameter pfile

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile     string
SQL> 

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> shut immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@fcdbdb_stby dbs]$ pwd
/u01/app/oracle/product/11.2.0.3/db_1/dbs

[oracle@fcdbdb_stby dbs]$ ls -lrt
total 9632
-rw-r--r--. 1 oracle oinstall    1610 Jan 13 10:01 initFCDBSTND.ora
-rw-r-----. 1 oracle oinstall    1536 Jan 13 10:17 orapwFCDBSTND
-rw-r-----. 1 oracle oinstall      24 Jan 13 10:17 lkFCDBSTND
-rw-r-----. 1 oracle oinstall 9814016 Jan 13 11:32 snapcf_FCDBSTND.f
-rw-r-----. 1 oracle oinstall   16384 Feb 18 10:41 dr1FCDBSTND.dat
-rw-r-----. 1 oracle oinstall   16384 Feb 18 10:41 dr2FCDBSTND.dat
-rw-rw----. 1 oracle oinstall    1544 Feb 18 10:44 hc_FCDBSTND.dat

[oracle@fcdbdb_stby dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 18 10:45:16 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> create spfile from pfile;

File created.

SQL> exit
Disconnected

[oracle@fcdbdb_stby dbs]$ ls -lrt
total 9640
-rw-r--r--. 1 oracle oinstall    1610 Jan 13 10:01 initFCDBSTND.ora
-rw-r-----. 1 oracle oinstall    1536 Jan 13 10:17 orapwFCDBSTND
-rw-r-----. 1 oracle oinstall      24 Jan 13 10:17 lkFCDBSTND
-rw-r-----. 1 oracle oinstall 9814016 Jan 13 11:32 snapcf_FCDBSTND.f
-rw-r-----. 1 oracle oinstall   16384 Feb 18 10:41 dr1FCDBSTND.dat
-rw-r-----. 1 oracle oinstall   16384 Feb 18 10:41 dr2FCDBSTND.dat
-rw-rw----. 1 oracle oinstall    1544 Feb 18 10:44 hc_FCDBSTND.dat
-rw-r-----. 1 oracle oinstall    4608 Feb 18 10:45 spfileFCDBSTND.ora

[oracle@fcdbdb_stby dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 18 10:45:32 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 5394837504 bytes
Fixed Size    2237896 bytes
Variable Size 1275071032 bytes
Database Buffers 4110417920 bytes
Redo Buffers    7110656 bytes
Database mounted.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.


SQL> show parameter pfile

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile     string /u01/app/oracle/product/11.2.0
.3/db_1/dbs/spfileFCDBSTND.ora
SQL> 

recheck again

DGMGRL> show configuration;

Configuration - DGMANAGER

  Protection Mode: MaxPerformance
  Databases:
    FCDBPROD - Primary database
    FCDBSTND - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> 

Friday, February 14, 2014

Fix ORA-30009: Not enough memory for CONNECT BY operation

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_tbl as
select ROWNUM n, dbms_random.value(100000, 999999) n2  from dual connect by level <= 100000000; 

Table created.

Done

Tuesday, February 4, 2014

Fix /var/adm/wtmp: Value too large to be stored in data type.

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         Dec 27 17:10     (xx.xx.xx.xx)
oracle      ssh         Dec 31 13:54     (xx.xx.xx.xx)
UNKNOWN_    ssh         Jan 08 14:12     (xx.xx.xx.xx)
UNKNOWN_    ssh         Jan 14 13:12     (xx.xx.xx.xx)
UNKNOWN_    ssh         Jan 14 13:12     (xx.xx.xx.xx)
UNKNOWN_    ssh         Jan 22 15:29     (xx.xx.xx.xx)
UNKNOWN_    ssh         Jan 24 12:26     (xx.xx.xx.xx)
UNKNOWN_    ssh         Jan 24 12:26     (xx.xx.xx.xx)
UNKNOWN_    ssh         Jan 24 12:26     (xx.xx.xx.xx)
oracle      ssh         Feb 03 17:09     (xx.xx.xx.xx)
bash-3.00#


P.S: you have to login as root to execute those commands.


Monday, February 3, 2014

Changing diagnostic_dest path

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 diagnostic_dest='/u01/app/oracle';

System altered.

It can take few seconds. (while creating folders)

So, check

SQL> show parameter diagnostic_dest

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest     string /u01/app/oracle


[oracle@fc-db-tst1 rdbms]$ pwd
/u01/app/oracle/diag/rdbms

[oracle@fc-db-tst1 rdbms]$ ls
fcdbdev

[oracle@fc-db-tst1 rdbms]$ cd fcdbdev/

[oracle@fc-db-tst1 fcdbdev]$ ls
FCDBDEV  i_1.mif



Cannot access dba_directories inside procedure

 Recently I faced one of familiar Oracle error ORA -00942 : table or view does not exist   I got it in while compiling procedure, becaus...