Wednesday, May 28, 2014

Oracle 11g : Adding New Redo log group and Multiplexing existing ones on Primary and Standby database

Objective:

1. Multiplex the existing log file groups  from currently one logfile member to two
2. Add one redo log group with two log file members on both primary and standby databases

Below is the existing number of log-files on primary database.

SQL >  SELECT a.group#, count(a.member) FROM v$logfile a, v$log b WHERE a.group# = b.group#
  2   group by a.group#
  3   order by 1;

    GROUP# COUNT(A.MEMBER)
---------- ---------------
         1               1
         2               1
         3               1

The above output shows that we have three logfile group with one member each. Same goes for the standby database mentioned below (group# 4,5,6) with one member each.

SQL> select * from logfile;

 GROUP# STATUS  TYPE    MEMBER                                  IS_
------- ------- ------- ------------------------------------------ ---
1 ONLINE   /u02/oracle/oradata/oradb/redo01.log             NO
2 ONLINE         /u02/oracle/oradata/oradb/redo02.log             NO
3 ONLINE         /u02/oracle/oradata/oradb/redo03.log             NO
4 STANDBY     /u02/oracle/oradata/oradb/sredo01.log            NO
5 STANDBY     /u02/oracle/oradata/oradb/sredo02.log            NO
6 STANDBY     /u02/oracle/oradata/orabb/sredo03.log            NO

Now lets get going with our two objectives at the start of this post

 1. below on both primary and standby

below needs to be done on both primary and standby

 SQL>alter system set standby_file_management=manual;
2. On Standby

cancel the MRP (recovery) process.

 SQL> alter database recover managed standby database cancel;

3. Now lets multiplex the existing groups first

On Primary:

 SQL>alter database add logfile member '/u03/oracle/oradata/oradb/redo01_b.log' to group 1;
 SQL>alter database add logfile member '/u03/oracle/oradata/oradb/redo02_b.log' to group 2;
 SQL>alter database add logfile member '/u03/oracle/oradata/oradb/redo03_b.log' to group 3;

On Standby:

 SQL>alter database add standby logfile member '/u03/oracle/oradata/oradb/sredo01_b.log' to group 4;
 SQL>alter database add standby logfile member '/u03/oracle/oradata/oradb/sredo02_b.log' to group 5;
 SQL>alter database add standby logfile member '/u03/oracle/oradata/oradb/sredo03_b.log' to group 6;

4. Add the additional redo log group with multiplexed members

Once multiplex of redo members done, add one more group as part of our second objective

On Primary:

SQL>ALTER DATABASE ADD LOGFILE GROUP 7 ('/u02/oracle/oradata/oradb/redo07.log', '/u03/oracle/oradata/oradb/redo07_b.log') SIZE 50M;

On Standby:

 ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 ('/u02/oracle/oradata/oradb/sredo08.log', '/u03/oracle/oradata/oradb/sredo08_b.log') SIZE 50M;

 5: restart the MRP pricess on Standby

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE disconnect;

Wednesday, April 23, 2014

RMAN: Concept and Usage of Recovery Catalog

Recovery catalog is an optional database schema consisting of database objects like tables and views. RMAN uses these objects to store its repository data. The control file of each target database always serves as the primary store for the repository, however, catalog can act as a secondary storage and provides repository redundancy

Information Maintained by Recovery Catalog:

The recovery catalog contains information about both RMAN backups and the target database. Below is what maintained by a typical recovery catalog

  1.  RMAN configuration settings
  2.  RMAN-stored scripts that you create
  3. Target database tablespace and datafile information
  4. Information pertaining to datafile and archived redo log backup sets and backup pieces, as well as datafile and archived redo log copies

Creating RMAN Recovery Catalog:

The recovery catalog isn’t created by defualt when you install oracle database rather we must create it manually and needs to be maintained like any other oracle database .

In this article I am going to create a Recovery Catalog and have a remote database registered in the catalog database.

Below are the steps I have taken to create the catalog.

1. Create a separate database e.g. CATDB. Please note that we could create the recovery catalog in a target database that you want to back up using the recovery catalog, but would be an unwise choice because in such a case, losing the target database means we'll lose the recovery catalog as well. This will make the  recovery much harder or even impossible

2. using SQL*Plus, connect as sys to the CATDB database created in step1 

connect  /  as sysdba

3.  Create a default tablespace for recovery catalog owner as below

 create tablespace cat_data  datafile '/u02/oracle/oradata/CATDB/cat_data_01.dbf' size 1500M;

4. Create recovery catalog owner

create user rman_cat identified by rman_cat
default tablespace cat_data
temporary tablespace temp
quota unlimited on cat_data;

5. Once you create the recovery catalog owner, you must grant that user the
recovery_catalog_owner privilege in order for that user to have the authority to work
with the recovery catalog you’ll create in the next step. 

grant recovery_catalog_owner to rman_cat;

6. Now create the Recovery Catalog by just connecting to the recovery cataog only (not the target database)

RMAN>connect catalog rman_cat/rman_cat@CATDB
connected to recovery catalog database
RMAN>

RMAN> create catalog;

Below is how we can register the database to the newly created catalog

Registering Target Databases:

You want to use a recovery catalog to manage the RMAN repository data for a new database.

rman target / catalog rman_cat/rman_cat@catdb

RMAN> register database;

You can ensure that you have successfully registered the target database by issuing the list incarnation command.

RMAN> list incarnation;

Make sure from the remote server from where you are logging in to connect to the recovery catalog has the recovert catalog database (CATDB) tns entry available in the tnsnames.ora file of  target database.


Friday, July 12, 2013

RMAN Incremental Backups to Roll Forward a Physical Standby Database (Lags far behind the primary database)

RMAN incremental backups can be used to sync a physical standby database with the primary database. We can use the RMAN BACKUP INCREMENTAL FROM SCN command to create a backup on the primary database that starts at the current SCN of the standby, which can then be used to roll the standby database forward in time.
So here are the steps,

Stop Redo Apply on the standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

On the standby database, get the SCN for the incremental backup.

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
     255797

Connect to the primary database as the RMAN target and create an incremental backup from the current SCN

RMAN> BACKUP INCREMENTAL FROM SCN  255797 DATABASE FORMAT '/tmp/ForStandby_%U' tag 'FORSTANDBY';

The BACKUP commands shown generate datafile backups, as well as a control file backup

If the backup pieces are not on shared storage, then transfer all the backup pieces created on the primary to the standby:

scp /tmp/ForStandby_* standby:/tmp

Connect to the standby database as the RMAN target

RMAN> REPORT SCHEMA;

Connect to the standby database as the RMAN target and apply incremental backups by executing the following commands.

RMAN> STARTUP FORCE NOMOUNT;
RMAN> RESTORE STANDBY CONTROLFILE FROM TAG 'FORSTANDBY';
RMAN> ALTER DATABASE MOUNT;
RMAN> RECOVER DATABASE NOREDO;

Note that the RESTORE STANDBY CONTROLFILE FROM TAG command only works if you are connected to the recovery catalog for the entire process. Otherwise, you must use the RESTORE STANDBY CONTROLFILE FROM '<control file backup filename>' command.

If a recovery catalog is used, then the RMAN RECOVER command will fix the path names for datafiles in the standby control file. If no recovery catalog is used, then you must manually edit the file names in your standby control file or use the RMAN SET NEWNAME command to assign the datafile names.

Start Redo Apply on the physical standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE -
> USING CURRENT LOGFILE DISCONNECT FROM SESSION;


ref: :http://docs.oracle.com/cd/E11882_01/server.112/e25608/rman.htm#SBYDB04700

Wednesday, July 3, 2013

Monitoring Primary and Physical Standby Databases

Before we go and run script to find out the primary or standby status, below is the list of processes involved in Primary and standby databases that need to understood

There are a number of Oracle background processes that play a key role, first the primary database
  • LGWR - log writer process flushes from the SGA to the ORL files
  • LNS - LogWriter Network Service reads redo being flushed from the redo buffers by the LGWR and performs a network send of the redo to the standby
  • ARCH - archives the ORL files to archive logs, that also used to fulfill gap resolution requests, one ARCH processes is dedicated to local redo log activity only and never communicates with a standby database
The standby database will also have key processes

  • RFS - Remote File Server process performs a network receive of redo transmitted from the primary and writes the network redo to the standby redo log (SRL) files.
  • ARCH - performs the same as the primary but on the standby
  • MRP - Managed Recover Process coordinates media recovery management, recall that a physical standby is in perpetual recovery mode
  • LSP - Logical Standby Process coordinates SQL apply, this process only runs in a logical standby
  • PR0x - recovery server process reads redo from the SRL or archive log files and apply this redo to the standby database.
the above mentioned process can be verified using the SQL script below. Corresponding process related to primary and standby will be displayed depends where this SQL is executed i.e. Primary or Standby

SELECT process,
       status,
       client_process,
       sequence#,
       block#,
       active_agents,
       known_agents
  FROM v$managed_standby;
Monitor Log shipping on Primary database

SET PAGESIZE 124
COL DB_NAME FORMAT A8
COL HOSTNAME FORMAT A12
COL LOG_ARCHIVED FORMAT 999999
COL LOG_APPLIED FORMAT 999999
COL LOG_GAP FORMAT 9999
COL APPLIED_TIME FORMAT A12

SELECT DB_NAME,
       HOSTNAME,
       LOG_ARCHIVED,
       LOG_APPLIED,
       APPLIED_TIME,
       LOG_ARCHIVED - LOG_APPLIED LOG_GAP
  FROM (SELECT NAME DB_NAME FROM V$DATABASE),
       (SELECT UPPER (
                  SUBSTR (
                     HOST_NAME,
                     1,
                     (DECODE (INSTR (HOST_NAME, '.'),
                              0, LENGTH (HOST_NAME),
                              (INSTR (HOST_NAME, '.') - 1)))))
                  HOSTNAME
          FROM V$INSTANCE),
       (SELECT MAX (SEQUENCE#) LOG_ARCHIVED
          FROM V$ARCHIVED_LOG
         WHERE DEST_ID = 1 AND ARCHIVED = 'YES'),
       (SELECT MAX (SEQUENCE#) LOG_APPLIED
          FROM V$ARCHIVED_LOG
         WHERE DEST_ID = 2 AND APPLIED = 'YES'),
       (SELECT TO_CHAR (MAX (COMPLETION_TIME), 'DD-MON/HH24:MI') APPLIED_TIME
          FROM V$ARCHIVED_LOG
         WHERE DEST_ID = 2 AND APPLIED = 'YES');

see if the MRP process is running on standby

SELECT PROCESS from V$MANAGED_STANDBY where PROCESS like 'MRP%';
last log applied and received along with log time on Standby database

SELECT 'Last Applied  : ' Logs,
       TO_CHAR (next_time, 'DD-MON-YY:HH24:MI:SS') Time
  FROM v$archived_log
 WHERE sequence# = (SELECT MAX (sequence#)
                      FROM v$archived_log
                     WHERE applied = 'YES')
UNION
SELECT 'Last Received : ' Logs,
       TO_CHAR (next_time, 'DD-MON-YY:HH24:MI:SS') Time
  FROM v$archived_log
 WHERE sequence# = (SELECT MAX (sequence#) FROM v$archived_log);

Last sequence # received and applied on the standby database

SELECT al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
  FROM (  SELECT thread# thrd, MAX (sequence#) almax
            FROM v$archived_log
           WHERE resetlogs_change# = (  SELECT resetlogs_change# FROM v$database)
        GROUP BY thread#) al,
       (  SELECT thread# thrd, MAX (sequence#) lhmax
            FROM v$log_history
           WHERE first_time = (  SELECT MAX (first_time) FROM v$log_history)
        GROUP BY thread#) lh
 WHERE al.thrd = lh.thrd;

Apply rate: To find out the speed of media recovery in a standby database, you can use this query:



set lines 200
col type format a30
col ITEM format a20
col comments format a20

select * from v$recovery_progress;

Archive Lag Histogram: The  V$STANDBY_EVENT_HISTOGRAM view came with 11gR2 and shows the historical occurance of archive lags in terms of seconds.

SQL> col name format a10

SQL> select * from  V$STANDBY_EVENT_HISTOGRAM;

Verify from Primary if the Real-time apply is being used

SQL> SELECT DEST_ID, RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS  WHERE DEST_ID=2;

DEST_ID RECOVERY_MODE
------- ------------------------
2 MANAGED REAL TIME APPLY

further reading:

BASH-DBA: Managing a Physical Standby Database
BASH-DBA: Snapshot standby database
BASH-DBA: Open Physical Standby For Read Write Testing and ...
BASH-DBA: Oracle Data Guard : Synchronous vs. Asynchronous ...

Tuesday, July 2, 2013

Managing a Physical Standby Database

Below steps are related to day-to-day operations of the Oracle Data Guard environment. These operations include starting the standby database, enabling managed recovery, opening the standby in read-only, as well as general maintenance tasks.

Starting a Physical Standby

Before Oracle 10g

SQL> startup nomount;
SQL> alter database mount standby database;


Starting with Oracle 10g, the start-up task can be done in single step

SQL> startup mount;

During startup, Oracle will read the controlfile when mounting the database to make the determination to mount the database as astandby database or as a primary database.

Starting  Managed Recovery:

Once the standby database has been started, it will begin receiving redo data from the primary database. This redo data will stack up in the form of archivelogs until we instruct the standby database to begin applying the redo data to the standby database. For a physical standby, the redo application is performed via the MRP.

SQL> alter database recovery managed standby database;

This above command will appear to hang because MRP is part of the session that it was started in. If we cancel out of this session, the MRP will also exit. To avoid this we need to run the MRP in the background as below

SQL> alter database recover managed standby database disconnect;

If the physical standby has standby redo logs configured, it is possible to have the MRP begin applying changes as soon as they arrive to the standby instead of waiting for the standby redo log to be archived. This functionality was introduced in 10g and is called Real-Time Apply. Real-Time Apply can shorten the role transition time by minimizing the redo that needs to be applied

To start Real-Time Apply, you initiate MRP by issuing the following command:

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


Stopping Managed Recovery:

We could simply perform a normal shutdown on the database, or we could cancel managed recovery,
leaving the standby database up and running. To cancel managed recovery, issue the following:

SQL> alter database recover managed standby database cancel;


Starting and Stopping Active Data Guard:

The actual process of enabling Active Data Guard is simple: Open the physical standby database in read-only mode and start Redo Apply. A physical standby database instance cannot be opened if Redo Apply is active on a mounted instance of that database. The Data Guard physical standby should be in
one of two states prior to enabling Active Data Guard:

1. The standby database is mounted and Redo Apply is running.

2. The standby database has been shut down cleanly and Redo Apply was stopped.

In the first scenario, proceed as follows using SQL*Plus

1. Stop Redo Apply:

SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;

2. Open the database read-only:

SQL> ALTER DATABASE OPEN READ ONLY;

3. Restart Redo Apply:

SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE;

In the second scenario, where the physical standby and Redo Apply are already shut down,
proceed as follow using SQL*Plus alone,

1. Start the physical standby in read-only mode.

SQL> STARTUP

2. Start Redo Apply.

SQL> SELECT open_mode FROM V$DATABASE;

OPEN_MODE
--------------------
READ ONLY 
SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE;

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

Further reading:

BASH-DBA: Monitoring Primary and Physical Standby Databases
BASH-DBA: Snapshot standby database
BASH-DBA: Open Physical Standby For Read Write Testing and ...
BASH-DBA: Oracle Data Guard : Synchronous vs. Asynchronous ...


Monday, June 24, 2013

ORA-15039: diskgroup not dropped

I had to drop one disk group on a two node RAC cluster and came up with this below error.

SQL> DROP DISKGROUP DATA_DG;
DROP DISKGROUP DATA_DG
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15073: diskgroup DATA_DIG is mounted by another ASM instance


Well the message tells it all, the same diskgroup is being  mounted on second node, therefore dismount it on node 2 first as below and drop it accordingly


SQL> alter diskgroup DATA_DG dismount;

Diskgroup altered.


Once the diskgroup is dsimounted above on node1, come back on node 2 and run  below

SQL> DROP DISKGROUP DATA_DG;

Diskgroup dropped.


Note: All above commands are executed as a 'grid' user by logging oto sqlplus as sysasm

Wednesday, June 12, 2013

12c Cloud Control: How to Modify the Password for SYSMAN at the OMS level

Below are the steps to modify 'SYSMAN' password at the OMS/WLS and Repository Database level in cloud control setup.

Please Not, you can not change the password at the database level using 'Alter user' command as the password is also stored in  WLS credential store. If done directly in the repository database then there will be a mis-match of password at the database level and OMS level leading to malfunctioning of OMS

oms_server:/u/oracle $ cd $ORACLE_HOME
oms_server:/u01/app/oracle/middleware/oms $ cd bin
oms_server:/u01/app/oracle/middleware/oms/bin $ emctl status oms


Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
WebTier is Up
Oracle Management Server is Up

oms_server:/u01/app/oracle/middleware/oms/bin $ emctl stop oms

Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
Stopping WebTier...
WebTier Successfully Stopped
Stopping Oracle Management Server...
Oracle Management Server Successfully Stopped
Oracle Management Server is Down

oms_server:/u01/app/oracle/middleware/oms/bin $ emctl config oms -change_repos_pwd

Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
Enter Repository User's Current Password : (enter current password)
Enter Repository User's New Password : (enter new password)

Changing passwords in backend ...
Passwords changed in backend successfully.
Updating repository password in Credential Store...
Successfully updated Repository password in Credential Store.
Restart all the OMSs using 'emctl stop oms -all' and 'emctl start oms'.
Successfully changed repository password.

oms_server:/u01/app/oracle/middleware/oms/bin $ emctl stop oms -all


Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
Stopping WebTier...
WebTier Successfully Stopped
Stopping Oracle Management Server...
Oracle Management Server Already Stopped
AdminServer Successfully Stopped
Oracle Management Server is Down

oms_server:/u01/app/oracle/middleware/oms/bin $ emctl start oms

Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
Starting Oracle Management Server...

Starting WebTier...
WebTier Successfully Started
Oracle Management Server Successfully Started
Oracle Management Server is Up