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.

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> 

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

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.

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