Thursday 19 February 2015

RECOVER LOSS OF ALL CONTROLFILES (NO CATALOG)

In this session will be discussing scenario of recovering database from loss of all controlfile using rman.

STEP1: CREATE A SCENARIO FOR TEST PURPOSE.

[oracle@NVMBD1BZY150D00 clonedb]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 19 15:50:38 2015

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> conn oracle/oracle

Connected.

SQL>CREATE TABLE myobjects AS SELECT * FROM DBA_OBJECTS;

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
MYOBJECTS

SQL> insert into myobjects select * from myobjects;

919664 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from myobjects;

  COUNT(*)
----------
   1839328   >>>> need to check this record count after recovery

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     5
Next log sequence to archive   7
Current log sequence           7

Note - current log sequence is 7 - not archived but contains the last committed changes that we made
Note - archive logs will not be found in $ARCV area, but in the flashback location

STEP2: STIMULATE A FAILURE

SQL> select name from v$controlfile;

NAME
-------------------------------------------------------------------------------
/u01/ORACLE/testdb/control01.ctl
/u01/ORACLE/testdb/control02.ctl
/u01/ORACLE/testdb/control03.ctl

SQL> !rm /u01/ORACLE/testdb/*.ctl


SQL> alter tablespace users online;
alter tablespace users online
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error.

STEP3: SHUT DONW THE DATABASE AND STARTUP IN NOMOUNT STAGE TO RECOVER CONTROL FILE.

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area  893386752 bytes
Fixed Size                  2076816 bytes
Variable Size             432017264 bytes
Database Buffers          452984832 bytes
Redo Buffers                6307840 bytes

Since we are not using a RMAN catalog we need to set the DBID

RMAN> set dbid=2415549446;

executing command: SET DBID


STEP4: RESTORE THE CONTROLFILE.

RMAN> run {
2> restore controlfile from autobackup;
3> }

Starting restore at 18-SEP-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

recovery area destination: /u01/ORACLE/flash_recovery_area
database name (or database unique name) used for search: TESTDB
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /u01/ORACLE/flash_recovery_area/TESTDB/autobackup/2007_09_18/o1_mf_s_633601094_3gynd74g_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/u01/ORACLE/testdb/control01.ctl
output filename=/u01/ORACLE/testdb/control02.ctl
output filename=/u01/ORACLE/testdb/control03.ctl
Finished restore at 18-SEP-07

STEP5: MOUNT AND RECOVER THE DATABASE;

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> recover database;

Starting recover at 18-SEP-07
Starting implicit crosscheck backup at 18-SEP-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 18-SEP-07

Starting implicit crosscheck copy at 18-SEP-07
using channel ORA_DISK_1
Finished implicit crosscheck copy at 18-SEP-07

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/ORACLE/flash_recovery_area/TESTDB/autobackup/2007_09_18/o1_mf_s_633601094_3gynd74g_.bkp

using channel ORA_DISK_1
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=155 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.2.4.0
datafile 4 not processed because file is offline

starting media recovery

archive log thread 1 sequence 6 is already on disk as file /u01/ORACLE/flash_recovery_area/TESTDB/archivelog/2007_09_18/o1_mf_1_6_3gyn7vnk_.arc
archive log thread 1 sequence 7 is already on disk as file /u01/ORACLE/testdb/redo03.log
archive log filename=/u01/ORACLE/flash_recovery_area/TESTDB/archivelog/2007_09_18/o1_mf_1_6_3gyn7vnk_.arc thread=1 sequence=6
archive log filename=/u01/ORACLE/testdb/redo03.log thread=1 sequence=7  >>>> current redo log with committed but unarchived changes applied
media recovery complete, elapsed time: 00:00:09
Finished recover at 18-SEP-07

STEP6: OPEN DATABASE WITH RESETLOGS OPTION.

SQL> alter database open resetlogs;

Database altered.

SQL> conn ORACLE/ORACLE

Connected.

SQL> select count(*) from myobjects;

  COUNT(*)
----------
   1839328





I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

Monday 16 February 2015

DATA GUARD BROKER CONFIGURATION

In this article we are going to learn how to set up Data Guard broker for managing data guard.
Primary and standby database server details:
Primary Server: ora1-1.mydomain Database: TESTDB
Standby Server: ora1-2.mydomain Database: STANDBY
Primary Database Details:
 
[oracle@NVMBD1BZY150D00 ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 12 16:06:52 2015
 
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 status,instance_name,database_role,protection_mode from v$database,v$instance;
 
STATUS       INSTANCE_NAME    DATABASE_ROLE    PROTECTION_MODE
------------ ---------------- ---------------- --------------------
OPEN         TESTDB           PRIMARY          MAXIMUM PERFORMANCE
 
SQL> select max(sequence#) from v$archived_log;
 
MAX(SEQUENCE#)
--------------
           803
 
Standby Database Details:
SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;
 
STATUS       INSTANCE_NAME    DATABASE_ROLE    PROTECTION_MODE
------------ ---------------- ---------------- --------------------
MOUNTED      STANDBY          PHYSICAL STANDBY MAXIMUM PERFORMANCE
 
SQL> select max(sequence#) from v$archived_log;
 
MAX(SEQUENCE#)
--------------
           803
 
On both primary and standby databases, enable the dataguard broker process by setting the value of the parmater “DG_BROKER_START” to TRUE.
SQL> alter system set dg_broker_start=true;
 
System altered.
 
Add a static entry for the DGMGRL in the listener.ora file on both the primary and standby servers.
On Primary:
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = TESTDB_DGMGRL)
      (ORACLE_HOME = /data/oracle/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = TESTDB)
    )
On Standby:
   (SID_DESC =
      (GLOBAL_DBNAME = STANDBY_DGMGRL)
      (ORACLE_HOME = /data/oracle/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = STANDBY)
    )
Listener.ora file contents on primary server:
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.9.2)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = TESTDB_DGMGRL )
      (ORACLE_HOME = /data/oracle/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = TESTDB)
    )
  )
Listener.ora file contents on standby server:
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.13.9.3)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
 
 
SID_LIST_LISTENER =
   (SID_DESC =
      (GLOBAL_DBNAME = STANDBY_DGMGRL )
      (ORACLE_HOME = /data/oracle/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = STANDBY)
    )
 
  )
On the primary server, create the Dataguard Broker configuration.
 
[oracle@NVMBD1BZY150D00 ~]$ 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/sys@TESTDB
 
Connected.
 
DGMGRL> create configuration 'dgtest' as primary database is 'TESTDB' connect identifier is TESTDB;
 
Configuration "dgtest" created with primary database "TESTDB"
 
DGMGRL> show configuration;
 
Configuration - dgtest
 
  Protection Mode: MaxPerformance
  Databases:
    TESTDB - Primary database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
DISABLED
 
Add the standby database “SRPSTB” to the configuration created above.
DGMGRL> add database 'STANDBY' as connect identifier is STANDBY maintained as physical;
 
Database "STANDBY" added
 
DGMGRL> show configuration;
 
Configuration - dgtest
 
  Protection Mode: MaxPerformance
  Databases:
    TESTDB  - Primary database
    STANDBY - Physical standby database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
DISABLED
 
The configuration added, needs to be enabled.
 
DGMGRL> enable configuration;
 
Enabled.
 
DGMGRL> show configuration
 
Configuration - dgtest
 
  Protection Mode: MaxPerformance
  Databases:
    TESTDB  - Primary database
    STANDBY - Physical standby database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS
 
 
Setup is complete, you are ready to use DG Broker.
 
Few Monitorable properties to troubleshoot

DGMGRL> SHOW DATABASE 'TESTPRI' 'StatusReport';
DGMGRL> SHOW DATABASE 'TESTPRI' 'LogXptStatus';
DGMGRL> SHOW DATABASE 'TESTPRI' 'InconsistentProperties';
DGMGRL> SHOW DATABASE 'TESTPRI' 'InconsistentLogXptProps';
DGMGRL> SHOW DATABASE 'TESTDG' 'StatusReport';
DGMGRL> SHOW DATABASE 'TESTDG' 'LogXptStatus';
DGMGRL> SHOW DATABASE 'TESTDG' 'InconsistentProperties';
DGMGRL> SHOW DATABASE 'TESTDG' 'InconsistentLogXptProps';


Equivalent Broker Commands to 'ALTER SYSTEM'

SQL> alter database recover managed standby database cancel;
DGMGRL> edit database 'stby_dbname' set state='LOG-APPLY-OFF';

SQL> alter database recover managed standby database disconnect;
DGMGRL> edit database 'stby_dbname' set state='ONLINE';

SQL> alter system set log_archive_max_processes=4;
DGMGRL> edit database 'dbname' set property 'LogArchiveMaxProcesses'=4;

SQL> alter system set log_archive_dest_state_2='enable' scope=both;
DGMGRL> edit database 'stby_dbname' set property 'LogShipping'='ON';

SQL> alter system set log_archive_dest_state_2='defer' scope=both;
DGMGRL> edit database 'stby_dbname' set property 'LogShipping'='OFF';

DGMGRL> edit database 'pri_dbname' set state='LOG-TRANSPORT-OFF';
This will defer all standby databases
 
 
I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!
 
 

Thursday 5 February 2015

Enable Oracle database to send emails via SMTP server



Sending e-mails from within the Oracle database using the UTL_MAIL PL/SQL package used to be quite easy in Oracle 10g. However, in Oracle 11gR2, things have changed.To enable SMTP package to send mail we need to perform following steps.

1.       First, you need to actually install the UTL_MAIL package. It’s not installed by default on 11g

2.       Next, you need to add the address and port of the e-mail server to the “smtp_out_server” initialization parameter.

3.       Next to create an Access Control List (ACL) for your e-mail server and grant the necessary users access to this ACL.

4.        Create the wrapper procedure in PL/SQL.

Let’s see above steps in detail.
STEP1: Install the UTL_MAIL package. It’s not installed by default on 11g
$ sqlplus /nolog
 
SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 6 10:21:07 2015
 
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 
SQL> connect / as sysdba
 
Connected.
 
SQL> @?/rdbms/admin/utlmail.sql
 
Package created.
 
Synonym created.
 
SQL> @?/rdbms/admin/prvtmail.plb
 
Package created.
 
Package body created.
 
Grant succeeded.
 
Package body created.
 
No errors.
 
SQL> grant execute on utl_mail to public;
 
Grant succeeded.
 
STEP2: add the address and port of the e-mail server to the “smtp_out_server” initialization parameter.


You need to add the address and port of the e-mail server to the “smtp_out_server” initialization parameter. If you do not do this, you will receive “ORA-06502: PL/SQL: numeric or value error” error when you try to use the UTL_MAIL package.

Execute the following with user SYS as SYSDBA:

SQL> alter system set smtp_out_server = 'mymailserver@mydomain.com:25' scope=both;
 
3. Next to create an Access Control List (ACL) for your e-mail server and grant the necessary user’s access to this ACL.
The network utility family of PL/SQL packages, such as UTL_TCP, UTL_INADDR, UTL_HTTP, UTL_SMTP, and UTL_MAIL, allow Oracle users to make network callouts from the database using raw TCP or using higher-level protocols built on raw TCP. As of Oracle 11g a new package DBMS_NETWORK_ACL_ADMIN allows fine-grained control over network hosts access using access control lists (ACL) implemented by XML DB. Below I show major steps that are required to enable ACL for user SCOTT. You have to run them as SYS or SYSDBA user. Read Oracle documentation for more details.

Without an ACL, you will receive the following error:

ORA-24247: network access denied by access control list (ACL)

Create an ACL with one database user in Oracle database
BEGIN
   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
    acl          => 'mail_access.xml',
    description  => 'Permissions to access e-mail server.',
    principal    => 'PUBLIC',
    is_grant     => TRUE,
    privilege    => 'connect');
   COMMIT;
END;
 
 Assign an ACL to one or more network hosts
BEGIN
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
    acl          => 'mail_access.xml',
    host         => 'mymailserver@mydomain.com',
    lower_port   => 25,
    upper_port   => 25
    );
   COMMIT;
END;
 
Confirm the created ACL is correct
SELECT host, lower_port, upper_port, acl
FROM   dba_network_acls
/
 
SELECT acl,
principal,
privilege,
is_grant,
TO_CHAR(start_date, 'DD-MON-YYYY HH24:MI') AS start_date,
TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM   dba_network_acl_privileges
/
 
4. Create the wrapper procedure in PL/SQL
 
CREATE OR REPLACE PROCEDURE SEND_MAIL (
   p_sender       IN   VARCHAR2,
   p_recipients   IN   VARCHAR2,
   p_cc           IN   VARCHAR2 DEFAULT NULL,
   p_bcc          IN   VARCHAR2 DEFAULT NULL,
   p_subject      IN   VARCHAR2,
   p_message      IN   VARCHAR2,
   p_mime_type    IN   VARCHAR2 DEFAULT 'text/plain; charset=us-ascii'
)
IS
 BEGIN
   UTL_MAIL.SEND (sender          => p_sender,
                  recipients      => p_recipients,
                  cc              => p_cc,
                  bcc             => p_bcc,
                  subject         => p_subject,
                  message         => p_message,
                  mime_type       => p_mime_type
                 );
EXCEPTION
   WHEN OTHERS
   THEN
      RAISE;
END send_mail;
/
 
5. After these steps, you should be able to successfully send e-mails from within the database:
 
Begin
send_mail(
p_sender => ‘ora11gtest@mydomain.com’,
p_recipients => ‘Amit@mydomain.com’,
p_subject => ‘This is the subject line!’,
p_message => ‘Hello World!’);
end;

*Action:
anonymous block completed
 
 
 
I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!