Manually apply log to standby in 11g

First copy the log from to standby log location i.e in my case flash recovery area >instance name

then cancel the log at standby by following command
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

1. Start up and mount the standby database (if it is not already mounted). For example, enter:

   SQL> STARTUP MOUNT PFILE=/oracle/admin/pfile/initSTBY.ora
   

2. Recover the database using the AUTOMATIC option:

   SQL> ALTER DATABASE RECOVER AUTOMATIC STANDBY DATABASE;
   

   The AUTOMATIC option automatically generates the name of the next archived redo log file needed to continue the recovery operation.
   After recovering the available log files, the Oracle database prompts for the name of a log file that does not exist. For example, you might see:

   ORA-00308: cannot open archived log '/oracle/standby/standby_logs/arcr_1_540.arc'
   ORA-27037: unable to obtain file status
   SVR4 Error: 2: No such file or directory
   Additional information: 3
   Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
   

3. Cancel recovery after the Oracle database applies the available log files by typing CTRL/C:

   SQL> <CTRL/C>
   Media recovery cancelled.
   

   The following error messages are acceptable after recovery cancellation and do not indicate a problem:

   ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
   ORA-01194: file 1 needs more recovery to be consistent
   ORA-01110: data file 1: 'some_filename'
   ORA-01112: media recovery not started
   

4. After you finish manually applying the missing log file, you can restart log apply services on the standby database, as follows:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database_role in Data Guard

select database_role from v$database

select max and open cursors in oracle

select max(a.value) as highest_open_cur, p.value as max_open_cur
from v$sesstat a, v$statname b, v$parameter p
where a.statistic# = b.statistic# 
and b.name = 'opened cursors current'
and p.name= 'open_cursors'
group by p.value

ORA-17629: Cannot connect to the remote database server

Starting backup at 17-JUL-13

using channel ORA_DISK_1

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 07/17/2013 14:06:

14

ORA-17629: Cannot connect to the remote database server

ORA-17627: ORA-01031: insufficient privileges

ORA-17629: Cannot connect to the remote database server

continuing other job steps, job failed will not be re-run

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 07/17/2013 14:06:15

RMAN-03015: error occurred in stored script Memory Script

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 07/17/2013 14:06:

15

ORA-17629: Cannot connect to the remote database server

ORA-17627: ORA-01031: insufficient privileges

ORA-17629: Cannot connect to the remote database server

RMAN>

Hello;

The orapwd file was copied...

Did you happen to rename it?

For example on my Primary system my password file is located here and name :

/u01/app/oracle/product/11.2.0/dbs/orapwPRIMARY

So I copy it to the same location on my Standby server and rename it :

/u01/app/oracle/product/11.2.0/dbs/orapwSTANDBY (So it becomes "orapwSTANDBY" )


Also make sure this parameter is set like this on both servers :


remote_login_passwordfile='EXCLUSIVE'


I have a short RMAN duplicate here : ( it assumes NET80, FORCED LOGGING, INIT, and LISTENER setup in advance)

http://www.visi.com/~mseberg/duprman.html

Oracle log location in 11g

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

SQL>  show parameter background

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_core_dump                 string      partial
background_dump_dest                 string      /oracle/app/oracle/diag/rdbms/PROD/PROD/trace

SQL> !ls -ltr /oracle/app/oracle/diag/rdbms/PROD/PROD/trace/al*
-rw-r----- 1 oracle oinstall 78512424 2011-10-03 02:00 /oracle/app/oracle/diag/rdbms/PROD/PROD/trace/alert_PROD.log

SQL>

Oracle Architecture

Primary Architecture Components

The figure shown above details the Oracle architecture.

Oracle server:  An Oracle server includes an Oracle Instance and an Oracle database.  

·        An Oracle database includes several different types of files:  datafiles, control files, redo log files and archive redo log files.  The Oracle server also accesses parameter files and password files. 

·        This set of files has several purposes. 

o   One is to enable system users to process SQL statements. 

o   Another is to improve system performance. 

o   Still another is to ensure the database can be recovered if there is a software/hardware failure.

·        The database server must manage large amounts of data in a multi-user environment. 

·        The server must manage concurrent access to the same data. 

·        The server must deliver high performance.  This generally means fast response times.

Oracle instance:  An Oracle Instance consists of two different sets of components:

·        The first component set is the set of background processes (PMON, SMON, RECO, DBW0, LGWR, CKPT, D000 and others). 

o   These will be covered later in detail – each background process is a computer program. 

o   These processes perform input/output and monitor other Oracle processes to provide good performance and database reliability. 

·        The second component set includes the memory structures that comprise the Oracle instance. 

o   When an instance starts up, a memory structure called the System Global Area (SGA) is allocated. 

o   At this point the background processes also start. 

·        An Oracle Instance provides access to one and only one Oracle database.   

Oracle database: An Oracle database consists of files. 

·        Sometimes these are referred to as operating system files, but they are actually database files that store the database information that a firm or organization needs in order to operate. 

·        The redo log files are used to recover the database in the event of application program failures, instance failures and other minor failures.

·        The archived redo log files are used to recover the database if a disk fails. 

·        Other files not shown in the figure include:

o   The required parameter file that is used to specify parameters for configuring an Oracle instance when it starts up. 

o   The optional password file authenticates special users of the database – these are termed privileged users and include database administrators. 

o   Alert and Trace Log Files – these files store information about errors and actions taken that affect the configuration of the database.

User and server processes:  The processes shown in the figure are called user and server processes.  These processes are used to manage the execution of SQL statements.

·        A Shared Server Process can share memory and variable processing for multiple user processes.

·        A Dedicated Server Process manages memory and variables for a single user process.
This figure from the Oracle Database Administration Guide provides another way of viewing the SGA.

Connecting to an Oracle Instance – Creating a Session

Create second standby with already existing standby in data guard

Applies to: 

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.1.0.8
Information in this document applies to any platform.

Goal

This document provides the step by step approach on adding a second standby to an existing dataguard configuration.

DGMGRL> show configuration; 

Configuration
Name: DRS001
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
prim - Primary database
mystd - Physical standby database

Current status for "DRS001":
SUCCESS

Lets add another standby with the name SSTD.

Solution

Procedure
========

1) Take RMAN backup of PRIMARY database and controlfile for standby
2) Create pfile from spfile in PRIMARY and make necessary changes
3) Copy the files (Rman Backups , PFILE ) to the new server (SSTD)
4) Use RMAN Duplicate to create the standby database
5) Set log_archive_config parameter in all the database
6) Create SRL - Standby Redo Logs if required in second standby (SSTD)
7) Update log_archive_Dest_n parameter in PRIMARY that will be the remote archival for second standby and enable the log_archive_dest_state_n
8) Enable DG Broker in second standby SSTD
9) Add the second standby (SSTD) to the broker from PRIMARY
10) Enable second standby (SSTD)
11) Verify by using show configuration.

Detailed Steps
==========
1) Take RMAN backup of PRIMARY database and controlfile for standby 

RMAN> BACKUP DATABASE FORMAT '/tmp/prim_db_%U';
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/tmp/prim_ctl_%U';

2) Create pfile from spfile in PRIMARY and make necessary changes

SQL> create pfile = '/tmp/initsstd.ora' from spfile;

Example of changes:
db_unique_name = 'sstd'
db_name='prim'
fal_server=prim
fal_client=sstd
db_file_name_convert = 'prim','sstd'
log_file_name_convert = 'prim','sstd'
log_archive_dest_1 =LOCATION=USE_DB_RECOVERY_FILE_DEST

3) Copy the files (Rman Backups , PFILE ) to the new server (SSTD) 

$ scp /tmp/* oracle@sstd:/tmp/

4) Use RMAN Duplicate to create the standby database

a) Configure tnsnames.ora in primary and second standby so that they can communicate each other and verify using tnsping.
example:  $ tnsping prim

b) In second standby server create spfile from pfile and STARTUP NOMOUNT the instance.connect to RMAN as below and use DUPLICATE

$ RMAN target sys@prim auxiliary /

RMAN> duplicate target database for standby;

5) Set log_archive_config parameter in all the database (prim, mystd, sstd)

SQL> alter system set log_archive_config='dg_config=(prim,mystd,sstd)';

6) Create SRL - Standby Redo Logs if required in second standby (SSTD) 

SQL> alter database add standby logfile group 4 '/u01/app/oradata/sstd/sredo1.log' size 50M ;

if ASM filesystem

SQL> alter database add standby logfile group 4 '+FRA' size 50M ; 

7) Update log_archive_Dest_n parameter in PRIMARY that will be the remote archival for second standby and enable the log_archive_dest_state_n 

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3 ='service=sstd lgwr async affirm valid_for=(online_logfiles,primary_role) db_unique_name=sstd';

8) Enable DG Broker in second standby SSTD 

SQL> ALTER SYSTEM SET DG_BROKER_START = TRUE;

9) Add the second standby (SSTD) to the broker from PRIMARY 

DGMGRL> add database sstd as connect identifier is sstd maintained as physical;

DGMGRL> show configuration;

Configuration
Name: DRS001
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
prim - Primary database
mystd - Physical standby database
sstd - Physical standby database (disabled)

10) Enable second standby (SSTD) 

DGMGRL> enable database sstd;

11) Verify by using show configuration. 

DGMGRL> show configuration; 

Configuration
Name: DRS001
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
prim - Primary database
mystd - Physical standby database
sstd - Physical standby database

Current status for "DRS001":
SUCCESS