Manual Database creation in 10g

The steps to create a database manually on Linux.


Step 1:
First of all create the  directories as per requirement as below.
linux1]$ mkdir -p /opt/scripts/
mkdir -p /opt/prod/datafiles/
mkdir -p /opt/prod/datafiles/admin/adump
mkdir -p /opt/prod/datafiles/admin/bdump
mkdir -p /opt/prod/datafiles/admin/cdump
mkdir -p /opt/prod/datafiles/archive
mkdir -p /opt/prod/datafiles/admin/udump

Step 2:
 database creation script. Location /opt/scripts/db_cr.sql
CREATE DATABASE "prod"
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/opt/prod/datafiles/redo1.log' SIZE 10M,
GROUP 2 '/opt/prod/datafiles/redo2.log' SIZE 10M,
GROUP 3 '/opt/prod/datafiles/redo3.log' SIZE 10M
DATAFILE
'/opt/prod/datafiles/system01.dbf' size 100m,
'/opt/prod/datafiles/usr04.dbf' size 10m
sysaux datafile '/opt/prod/datafiles/sysaux01.dbf' size 100m
undo tablespace undotbs
datafile '/opt/prod/datafiles/undo01.dbf' size 50m
CHARACTER SET US7ASCII
;
Step 3:
Prepare the init file.

 vi  $ORACLE_HOME/dbs/initprod.ora
*.audit_file_dest='/opt/prod/datafiles/admin/adump'
*.background_dump_dest='/opt/prod/datafiles/admin/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/opt/prod/datafiles/control01.ctl',
'/opt/prod/datafiles/control02.ctl','/opt/prod/datafiles/control03.ctl'
*.core_dump_dest='/opt/prod/datafiles/admin/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='prod'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=indiaXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=/opt/prod/datafiles/archive'
*.log_archive_format='%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=200278016
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=601882624
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS'
*.user_dump_dest='/opt/prod/datafiles/admin/udump'

Step 4:
Now perform the following steps:
$ export ORACLE_SID=prod
$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Thu May 22 17:35:28 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup nomount pfile='$ORACLE_HOME/dbs/initprod.ora'
ORACLE instance started.
Total System Global Area 603979776 bytes
Fixed Size 1263176 bytes
Variable Size 167774648 bytes
Database Buffers 427819008 bytes
Redo Buffers 7122944 bytes
SQL> @/opt/scripts/db_cr.sql
Database created.
Step 5:
 run the catalog.sql and catproc.sql scripts.
from $ORACLE_HOME/rdbms/admin
SQL> @/?/rdbms/admin/catalog.sql
SQL> @/?/rdbms/admin/catproc.sql
SQL> select name from v$database;
NAME
---------
prod

Now create the listener and tns entries for your database

Upgrade 10204 RAC ( 2 node RAC on LINUX EL4 ) to 11g R2 RAC

Important Note : I will be publishing the Steps may be in next couple of days for installation steps of RAC on linux and then it will be upgraded to 11g R2 RAC.

My current setup is having 10204CRS and 10204 DB version. It is 2 node RAC running on EL4

how to manage standby database, monitoring of standby database

How to upgrade 10204 data Guard ( Primary and Standby database) to 11gR2

The environment is 2 node both are Linux EL4 and having database 10204


1)Make sure that standby database (STBY) is  in sync with primary database (PRMY)

Primary database (PRMY)
select 'PRIMARY SEQ IS ' ||sequence# ||chr(10), 'PRIMARY FIRST_TIME IS ' || to_char(first_time,'mm/dd/yy hh24:mi:ss')
 from v$log_history
 where sequence#=(select max(sequence#) from v$log_history);

Standby database (STBY)

select 'STANDBY SEQ IS ' ||sequence# ||chr(10), 'STANDBY FIRST_TIME IS ' || to_char(first_time,'mm/dd/yy hh24:mi:ss')
 from v$log_history
 where sequence#=(select max(sequence#) from v$log_history);


2). Stop the redo apply on standby database.
on STBY database
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;

3). Stop the redo logs from being shipped from PRMY database
Execute from PRMY
alter system set log_archive_dest_state_2=defer;

4). Install 11g R2 Binary on  primary database server
5).  Install 11g R2 Binary on standby database server 
6). Upgrade the primary database using upgrade scripts (see Note:316889.1) 
7). Mount the standby database ( Create new pfile and startup database in mount stage from new oracle 11g r2 home and mount the standby database)
8). enable redo shipping of redo from primary to standby
alter system set log_archive_dest_state_2=enable;

9. restart the managed recovery process on the standby database to progress redo apply
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

10) Check sync status


Primary database (PRMY)
select 'PRIMARY SEQ IS ' ||sequence# ||chr(10), 'PRIMARY FIRST_TIME IS ' || to_char(first_time,'mm/dd/yy hh24:mi:ss')
 from v$log_history
 where sequence#=(select max(sequence#) from v$log_history);

Standby database (STBY)

select 'STANDBY SEQ IS ' ||sequence# ||chr(10), 'STANDBY FIRST_TIME IS ' || to_char(first_time,'mm/dd/yy hh24:mi:ss')
 from v$log_history
 where sequence#=(select max(sequence#) from v$log_history);


Note : make sure that you create a new passowrd file for 11g home and copy and rename the same on standby database server

Step by step Switchover in 10g data guard / How to perform switcover in 10g Data Guard

Pri db_unique_name    =    'PRMY'
DG db_unique_name    =    'STBY'
Configuration(any name) =    'DG_STBY'
sys password        =    'sys'
Pri conn stg        =    'PRMY'

Please Ensure following parameters are set correctly on primary and standby
1. Ensure SPFILE is used
SQL> sho parameter spfile

2. Verify dmon process is running and broker parameters viz. DG_BROKER_START is set to TRUE and DG_BROKER_CONFIG_FILEn are set correctly
SQL> sho parameter broker
$ps -ef|grep dmon|grep -v grep

3. Check if LOCAL_LISTENER is set(This is needed only if you are non-default port other than 1521)
DGMGRL> show instance 'PRMYR' 'LocalListenerAddress';


4. Broker configuration is enabled and state of all members as intended is ONLINE
DGMGRL> show configuration

5. remote_login_passwordfile is set to 'EXCLUSIVE'
SQL> sho parameter remote_login_passwordfile

6. Verify PRMY and STBY tnspingable from both primary as well as standby and can connect to each other.

Now perform actual switchover now.



Broker config before switchover:
[oracle@linux2 dg_file]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.4.0 - Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/sys
Connected.
DGMGRL> show configuration

Configuration
  Name:                DG_PRMY
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    PRMY - Primary database
    STBY - Physical standby database

Current status for "DG_PRMY":
SUCCESS



Switchover Now:
DGMGRL> switchover to 'STBY';
Performing switchover NOW, please wait...
Operation requires shutdown of instance "PRMY" on database "PRMY"
Shutting down instance "PRMY"...
ORA-01017: invalid username/password; logon denied

You are no longer connected to ORACLE
Please connect again.
Unable to shut down instance "PRMY"
You must shut down instance "PRMY" manually
Operation requires shutdown of instance "STBY" on database "STBY"
You must shut down instance "STBY" manually
Operation requires startup of instance "PRMY" on database "PRMY"
You must start instance "PRMY" manually
Operation requires startup of instance "STBY" on database "STBY"
You must start instance "STBY" manually
Switchover succeeded, new primary is "STBY"
DGMGRL>

DGMGRL> exit


Broker config post switchover:
DGMGRL>  show configuration;

Configuration
  Name:                DG_PRMY
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    PRMY - Physical standby database
    STBY - Primary database

Current status for "DG_PRMY":
Warning: ORA-16607: one or more databases have failed


DGMGRL>


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

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Feb 4 13:10:57 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select open_mode, Database_role, DB_unique_name from v$database;

OPEN_MODE  DATABASE_ROLE    DB_UNIQUE_NAME
---------- ---------------- ------------------------------
MOUNTED    PHYSICAL STANDBY PRMY

SQL>


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

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Feb 4 13:11:32 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select open_mode, Database_role, DB_unique_name from v$database;

OPEN_MODE  DATABASE_ROLE    DB_UNIQUE_NAME
---------- ---------------- ------------------------------
MOUNTED    PRIMARY          STBY

SQL>
SQL>
SQL> alter database open;

Database altered.

SQL>  select open_mode, Database_role, DB_unique_name from v$database;

OPEN_MODE  DATABASE_ROLE    DB_UNIQUE_NAME
---------- ---------------- ------------------------------
READ WRITE PRIMARY          STBY


SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /
/
/
System altered.

SQL>
System altered.

SQL>



SQL> select 'PRIMARY SEQ IS ' ||sequence# ||chr(10), 'PRIMARY FIRST_TIME IS ' || to_char(first_time,'mm/dd/yy hh24:mi:ss')
 from v$log_history
 where sequence#=(select max(sequence#) from v$log_history);  2    3

'PRIMARYSEQIS'||SEQUENCE#||CHR(10)
--------------------------------------------------------
'PRIMARYFIRST_TIMEIS'||TO_CHAR(FIRST_TI
---------------------------------------
PRIMARY SEQ IS 240
PRIMARY FIRST_TIME IS 02/04/12 13:08:38


SQL>




SQL> select 'STANDBY SEQ IS ' ||sequence# ||chr(10), 'STANDBY FIRST_TIME IS ' || to_char(first_time,'mm/dd/yy hh24:mi:ss')
 from v$log_history
 where sequence#=(select max(sequence#) from v$log_history);
  2    3
'STANDBYSEQIS'||SEQUENCE#||CHR(10)
--------------------------------------------------------
'STANDBYFIRST_TIMEIS'||TO_CHAR(FIRST_TI
---------------------------------------
STANDBY SEQ IS 240
STANDBY FIRST_TIME IS 02/04/12 13:08:38


SQL>
SQL>  alter database recover managed standby database disconnect from session;



Database altered.

SQL> SQL> SQL>
SQL>
SQL> select 'PRIMARY SEQ IS ' ||sequence# ||chr(10), 'PRIMARY FIRST_TIME IS ' || to_char(first_time,'mm/dd/yy hh24:mi:ss')
 from v$log_history
 where sequence#=(select max(sequence#) from v$log_history);  2    3

'PRIMARYSEQIS'||SEQUENCE#||CHR(10)
--------------------------------------------------------
'PRIMARYFIRST_TIMEIS'||TO_CHAR(FIRST_TI
---------------------------------------
PRIMARY SEQ IS 240
PRIMARY FIRST_TIME IS 02/04/12 13:08:38


SQL>





Note : Make sure to start the standby and new primary database manually and check their status also , using database_role from v$database on both new primary & standby

Reduce No of log_archive_max_processes on both primary and standby database so that DB shut down takes minimum time

How to set up Data guard in oracle 10g

How to perform switchover operation in Data guard

Pri db_unique_name    =    'PRMY'
DG db_unique_name    =    'STBY'
Configuration(any name) =    'DG_PRMY'
sys password        =    'sys'
Pri conn stg        =    'PRMY'

1. Set up init parameters on primary to enable broker


SQL> alter system set dg_broker_start=false;
System altered.

SQL> alter system set dg_broker_config_file1='/opt/dg_file/dr1PRMY.dat';
System altered.

SQL> alter system set dg_broker_config_file2='/opt/dg_file/dr2PRMY.dat' ;
System altered.

SQL> alter system set dg_broker_start=true;
System altered.

2. Verify if DMON process has started on all the instances of primary. Example:

$ ps -ef|grep dmon|grep -v grep
oracle   16190     1  0 08:53 ?        00:00:00 ora_dmon_PRMY

3. Set up init parameters on standby

SQL> alter system set dg_broker_start=false;
System altered.

SQL> alter system set dg_broker_config_file1='/opt/dg_file/dr1STBY.dat' ;
System altered.

SQL> alter system set dg_broker_config_file2='/opt/dg_file/dr2STBY.dat';
System altered.

SQL> alter system set dg_broker_start=true ;
System altered.

$ ps -ef|grep dmon|grep -v grep
oracle   16190     1  0 08:53 ?        00:00:00 ora_dmon_STBY


Primary and standby Databases must be started from spfile


SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /ora_home/ora_home10g/dbs/spfi
                                                 lePRMY.ora

SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /ora_home/ora_home10g/dbs/spfi
                                                 leSTBY.ora
SQL>


dgmgrl command can be executed from primary or standby

5. DGMGRL Configuration
[oracle@linux1 ~]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.4.0 - Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/sys
Connected.
DGMGRL> CREATE CONFIGURATION 'DG_PRMY' AS PRIMARY DATABASE IS 'PRMY' CONNECT IDENTIFIER IS PRMY;
Configuration "DG_PRMY" created with primary database "PRMY"
DGMGRL> SHOW CONFIGURATION;

Configuration
  Name:                DG_PRMY
  Enabled:             NO
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    PRMY - Primary database

Current status for "DG_PRMY":
DISABLED

DGMGRL> ADD DATABASE 'STBY' AS CONNECT IDENTIFIER IS STBY MAINTAINED AS PHYSICAL;
Database "STBY" added
DGMGRL> SHOW CONFIGURATION;

Configuration
  Name:                DG_PRMY
  Enabled:             NO
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    PRMY - Primary database
    STBY - Physical standby database

Current status for "DG_PRMY":
DISABLED

DGMGRL> ENABLE CONFIGURATION;
Enabled.
DGMGRL> SHOW CONFIGURATION;

Configuration
  Name:                DG_PRMY
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    PRMY - Primary database
    STBY - Physical standby database

Current status for "DG_PRMY":
Warning: ORA-16610: command 'ENABLE DATABASE STBY' in progress


DGMGRL> SHOW CONFIGURATION;

Configuration
  Name:                DG_PRMY
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    PRMY - Primary database
    STBY - Physical standby database

Current status for "DG_PRMY":
SUCCESS

DGMGRL>

6. Troubleshooting
Let us see some sample issues and their fix
Issue
DGMGRL> CONNECT sys/sys
ORA-16525: the Data Guard broker is not yet available

Fix
Set dg_broker_start=true

Issue
After enabling the configuration, on issuing SHOW CONFIGURATION, this error comes
Warning: ORA-16608: one or more sites have warnings

Fix
To know details of the error, you may check log which will be generated at bdump with naming as drc{DB_NAME}.log or there are various monitorable properties that can be used to query the database status and assist in further troubleshooting.

Few Monitorable properties to troubleshoot
DGMGRL> SHOW DATABASE 'PRMY' 'StatusReport';
DGMGRL> SHOW DATABASE 'PRMY' 'LogXptStatus';
DGMGRL> SHOW DATABASE 'PRMY' 'InconsistentProperties';
DGMGRL> SHOW DATABASE 'PRMY' 'InconsistentLogXptProps';
DGMGRL> SHOW DATABASE 'STBY' 'StatusReport';
DGMGRL> SHOW DATABASE 'STBY' 'LogXptStatus';
DGMGRL> SHOW DATABASE 'STBY' 'InconsistentProperties';
DGMGRL> SHOW DATABASE 'STBY' 'InconsistentLogXptProps';

Issue
DGMGRL> SHOW DATABASE 'PRMY' 'StatusReport';
STATUS REPORT
       INSTANCE_NAME   SEVERITY ERROR_TEXT
          PRMYR2    WARNING ORA-16714: The value of property ArchiveLagTarget is inconsistent with the database setting.
          PRMYR2    WARNING ORA-16714: The value of property LogArchiveMaxProcesses is inconsistent with the database setting.

Issue
DGMGRL> SHOW DATABASE 'PRMY' 'InconsistentProperties';
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE
      PRMYR2     ArchiveLagTarget                    0                                         0
      PRMYR2 LogArchiveMaxProcesses                    4                    2                    4

Example
DGMGRL> SHOW DATABASE 'PRMY' 'LogArchiveMaxProcesses';
  LogArchiveMaxProcesses = '4'

Fix
DGMGRL> EDIT DATABASE 'PRMY' SET PROPERTY 'LogArchiveMaxProcesses'=2;

or

SQL> alter system set log_archive_max_processes=4 scope=spfile sid='*';
System altered.

DGMGRL> SHOW DATABASE 'PRMY' 'LogArchiveMaxProcesses';
  LogArchiveMaxProcesses = '4'

More commands
DGMGRL> SHOW DATABASE VERBOSE 'PRMY'
This will show all property values in detail

DGMGRL> HELP;
List of all broker commands with usage help

Equivalent Broker Commands to 'ALTER SYSTEM'
SQL> alter database recover managed standby database cancel;
DGMGRL> edit database 'STBY' set state='LOG-APPLY-OFF';

SQL> alter database recover managed standby database disconnect from session;
DGMGRL> edit database 'STBY' 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' set property 'LogShipping'='ON';

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

DGMGRL> edit database 'PRMY' set state='LOG-TRANSPORT-OFF';
This will defer all standby databases

Step by Step creation of Data Guard in 10g

Pri db_unique_name    =    'PRMY'
standby db_unique_name    =    'STBY'
Configuration(any name) =    'DG_PRMY'
sys password        =    'sys'
Pri conn stg        =    'PRMY'

1. Set up init parameters on primary to enable broker



SQL> alter system set dg_broker_start=false;
System altered.

SQL> alter system set dg_broker_config_file1='/opt/dg_file/dr1PRMY.dat';
System altered.

SQL> alter system set dg_broker_config_file2='/opt/dg_file/dr2PRMY.dat' ;
System altered.

SQL> alter system set dg_broker_start=true;
System altered.

2. Verify if DMON process has started on primary database. Example:

$ ps -ef|grep dmon|grep -v grep
oracle   16190     1  0 08:53 ?        00:00:00 ora_dmon_PRMY

3. Set up init parameters on standby

SQL> alter system set dg_broker_start=false;
System altered.

SQL> alter system set dg_broker_config_file1='/opt/dg_file/dr1STBY.dat' ;
System altered.

SQL> alter system set dg_broker_config_file2='/opt/dg_file/dr2STBY.dat';
System altered.

SQL> alter system set dg_broker_start=true ;
System altered.

$ ps -ef|grep dmon|grep -v grep
oracle   16190     1  0 08:53 ?        00:00:00 ora_dmon_STBY







SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /ora_home/ora_home10g/dbs/spfi
                                                 lePRMY.ora

SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /ora_home/ora_home10g/dbs/spfi
                                                 leSTBY.ora
SQL>


Dgmgrl can be executed from primary or standby

5. DGMGRL Configuration
[oracle@linux1 ~]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.4.0 - Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/sys
Connected.
DGMGRL> CREATE CONFIGURATION 'DG_PRMY' AS PRIMARY DATABASE IS 'PRMY' CONNECT IDENTIFIER IS PRMY;
Configuration "DG_PRMY" created with primary database "PRMY"
DGMGRL> SHOW CONFIGURATION;

Configuration
  Name:                DG_PRMY
  Enabled:             NO
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    PRMY - Primary database

Current status for "DG_PRMY":
DISABLED

DGMGRL> ADD DATABASE 'STBY' AS CONNECT IDENTIFIER IS STBY MAINTAINED AS PHYSICAL;
Database "STBY" added
DGMGRL> SHOW CONFIGURATION;

Configuration
  Name:                DG_PRMY
  Enabled:             NO
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    PRMY - Primary database
    STBY - Physical standby database

Current status for "DG_PRMY":
DISABLED

DGMGRL> ENABLE CONFIGURATION;
Enabled.
DGMGRL> SHOW CONFIGURATION;

Configuration
  Name:                DG_PRMY
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    PRMY - Primary database
    STBY - Physical standby database

Current status for "DG_PRMY":
Warning: ORA-16610: command 'ENABLE DATABASE STBY' in progress


DGMGRL> SHOW CONFIGURATION;

Configuration
  Name:                DG_PRMY
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    PRMY - Primary database
    STBY - Physical standby database

Current status for "DG_PRMY":
SUCCESS

DGMGRL>

6. Troubleshooting
Let us see some sample issues and their fix
Issue
DGMGRL> CONNECT sys/sys
ORA-16525: the Data Guard broker is not yet available

Fix
Set dg_broker_start=true

Issue
After enabling the configuration, on issuing SHOW CONFIGURATION, this error comes
Warning: ORA-16608: one or more sites have warnings

Fix
To know details of the error, you may check log which will be generated at bdump with naming as drc{DB_NAME}.log or there are various monitorable properties that can be used to query the database status and assist in further troubleshooting.

Few Monitorable properties to troubleshoot
DGMGRL> SHOW DATABASE 'PRMY' 'StatusReport';
DGMGRL> SHOW DATABASE 'PRMY' 'LogXptStatus';
DGMGRL> SHOW DATABASE 'PRMY' 'InconsistentProperties';
DGMGRL> SHOW DATABASE 'PRMY' 'InconsistentLogXptProps';
DGMGRL> SHOW DATABASE 'STBY' 'StatusReport';
DGMGRL> SHOW DATABASE 'STBY' 'LogXptStatus';
DGMGRL> SHOW DATABASE 'STBY' 'InconsistentProperties';
DGMGRL> SHOW DATABASE 'STBY' 'InconsistentLogXptProps';

Issue
DGMGRL> SHOW DATABASE 'PRMY' 'StatusReport';
STATUS REPORT
       INSTANCE_NAME   SEVERITY ERROR_TEXT
          PRMYR2    WARNING ORA-16714: The value of property ArchiveLagTarget is inconsistent with the database setting.
          PRMYR2    WARNING ORA-16714: The value of property LogArchiveMaxProcesses is inconsistent with the database setting.

Issue
DGMGRL> SHOW DATABASE 'PRMY' 'InconsistentProperties';
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE
      PRMYR2     ArchiveLagTarget                    0                                         0
      PRMYR2 LogArchiveMaxProcesses                    4                    2                    4

Example
DGMGRL> SHOW DATABASE 'PRMY' 'LogArchiveMaxProcesses';
  LogArchiveMaxProcesses = '4'

Fix
DGMGRL> EDIT DATABASE 'PRMY' SET PROPERTY 'LogArchiveMaxProcesses'=2;

or

SQL> alter system set log_archive_max_processes=4 scope=spfile sid='*';
System altered.

DGMGRL> SHOW DATABASE 'PRMY' 'LogArchiveMaxProcesses';
  LogArchiveMaxProcesses = '4'

More commands
DGMGRL> SHOW DATABASE VERBOSE 'PRMY'
This will show all property values in detail

DGMGRL> HELP;
List of all broker commands with usage help

Equivalent Broker Commands to 'ALTER SYSTEM'
SQL> alter database recover managed standby database cancel;
DGMGRL> edit database 'STBY' set state='LOG-APPLY-OFF';

SQL> alter database recover managed standby database disconnect from session;
DGMGRL> edit database 'STBY' 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' set property 'LogShipping'='ON';

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

DGMGRL> edit database 'PRMY' set state='LOG-TRANSPORT-OFF';
This will defer all standby databases

Physical Standby database Creation, step by step creation of physical standby database on 10g

Physical Standby database Creation, step by step 

Physical Standby database Creation, step by step

primary database name:  PRMY on Linux1 Enterprise Linux4 server

Standby database name: STBY on Linux2 Enterprise Linux4 server

Creating a Data Guard Physical Standby environment, General Review.
Manually setting up a Physical standby database is a simple task when all prerequisites and setup steps are carefully met and executed.
In this example
The Enviroment
     2 Linux servers, Oracle Distribution 2.6.9-55 EL i686 i386 GNU/Linux
     Oracle Database 10g Enterprise Edition Release 10.2.0.4.0

     Mount point is on identical path on both servers Linux1 and Linux2
Implementation notes:
     Once you have your primary database up and running these are the steps to follow:
     1.  Enable Forced Logging
     2.  Create a Password File
     3.  Configure a Standby Redo Log
     4.  Enable Archiving
     5.  Set Primary Database Initialization Parameters
     Having followed these steps to implement the Physical Standby you need to follow these steps:
     1. Create a Control File for the Standby Database
     2. Backup the Primary Database and transfer a copy to the Standby node.
     3. Prepare an Initialization Parameter File for the Standby Database
     4. Configure the listener and tnsnames to support the database on both nodes
     5. Set Up the Environment to Support the Standby Database on the standby node.
     6. Start the Physical Standby Database
     7. Verify the Physical Standby Database Is Performing Properly
Step by Step Implementation of a Physical Standby Environment
Primary Database Steps

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /opt/prmy/archive

Oldest online log sequence     1

Next log sequence to archive   3

Current log sequence           3

SQL> ! hostname

linux1.dbs.com

SQL> select name from v$database;

NAME

---------

PRMY
SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/opt/prmy/datafiles/PRMY/system01.dbf

/opt/prmy/datafiles/PRMY/undotbs01.dbf

/opt/prmy/datafiles/PRMY/sysaux01.dbf

/opt/prmy/datafiles/PRMY/users01.dbf

/opt/prmy/datafiles/PRMY/example01.dbf

SQL> show parameters unique

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_unique_name                       string      PRMY

Enable Forced Logging
  In order to implement Standby Database we must enable 'Forced Logging'.
  Once force logging is enabled than in that the event if a 'nologging' operation is done, force logging takes precedence and all operations are logged. It is better to enable force logging once the database is in mount stage, otherwise in open mode it may take long time
   SQL> ALTER DATABASE FORCE LOGGING;
  Database altered.
Create a Password File
  A password file must be created on the Primary database and should be copied to the Standby database server. The sys password must be identical on both sites. This is
  a key pre requisite in order to ship and apply archived logs from Primary to Standby.

 [oracle@linux1 ~]$ cd $ORACLE_HOME/dbs

[oracle@linux1 dbs]$ ls -lrt orapw*

-rw-r-----  1 oracle dba 1536 Feb  3 15:55 orapwPRMY

[oracle@linux1 dbs]$

[oracle@linux1 dbs]$orapwd file=orapwPRMY password=password force=y

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP

------------------------------ ----- -----

SYS                            TRUE  TRUE


Configure a Standby Redo Log
  A Standby Redo log is added to enable Data Guard Maximum Availability and Maximum Protection modes. It is important to configure the
  Standby Redo Logs (SRL) with the same size/more than the size  of the online redo logs. 

SQL> select group#,type,member from v$logfile;

    GROUP# TYPE    MEMBER
---------- ------- --------------------------------------------------
3 ONLINE  /opt/prmy/datafiles/PRMY/redo03.log

2 ONLINE  /opt/prmy/datafiles/PRMY/redo02.log

1 ONLINE  /opt/prmy/datafiles/PRMY/redo01.log

SQL> select bytes from v$log;
     BYTES

----------

  52428800

  52428800

  52428800
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4
    ' /opt/prmy/datafiles/PRMY/stbyredo4.log' size 50m;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/opt/prmy/datafiles/PRMY/stbyredo4.log' size 50m;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/opt/prmy/datafiles/PRMY/stbyredo5.log' size 50m;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/opt/prmy/datafiles/PRMY/stbyredo6.log' size 50m;

Database altered.

SQL> SELECT GROUP#,TYPE,MEMBER FROM V$LOGFILE;

    GROUP# TYPE    MEMBER
---------- ------- --------------------------------------------------
         3 ONLINE  /opt/prmy/datafiles/PRMY/redo03.log

         2 ONLINE  /opt/prmy/datafiles/PRMY/redo02.log

         1 ONLINE  /opt/prmy/datafiles/PRMY/redo01.log

         4 STANDBY /opt/prmy/datafiles/PRMY/stbyredo4.log

         5 STANDBY /opt/prmy/datafiles/PRMY/stbyredo5.log

         6 STANDBY /opt/prmy/datafiles/PRMY/stbyredo6.log       
6 rows selected.

Enabling  Primary Database Initialization Parameters
  Make sure your database is running from spfile, if not create a spfile from pfile;
 . As mentioned below the Primary database parameter file is configured so
  that it can work as both ( as Primary or Standby).

SQL> show parameter spfile;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

spfile                               string      /ora_home/ora_home10g/dbs/spfi

                                                 lePRMY.ora

SQL>
SQL> CREATE PFILE from spfile;

File created.

Edit the pfile to add the standby parameters, here shown highlighted:

PRMY.__db_cache_size=188743680

PRMY.__java_pool_size=4194304

PRMY.__large_pool_size=4194304

PRMY.__shared_pool_size=83886080

PRMY.__streams_pool_size=0

*.audit_file_dest='/ora_home/ora_home10g/admin/PRMY/adump'

*.background_dump_dest='/ora_home/ora_home10g/admin/PRMY/bdump'

*.compatible='10.2.0.3.0'

*.control_files='/opt/prmy/datafiles/PRMY/control01.ctl','/opt/prmy/datafiles/PRMY/control02.ctl','/opt/prmy/datafiles/PRMY/control03.ctl'

*.core_dump_dest='/ora_home/ora_home10g/admin/PRMY/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='PRMY'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=PRMYXDB)'

*.job_queue_processes=10

##*.log_archive_dest_1='LOCATION=/opt/prmy/archive'

*.log_archive_format='%t_%s_%r.arc'

*.open_cursors=300

*.pga_aggregate_target=94371840

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=285212672

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/ora_home/ora_home10g/admin/PRMY/udump'

#Standby realted parameters------------------------------------------

*.db_unique_name='PRMY'

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRMY,STBY)'

*.LOG_ARCHIVE_DEST_1='LOCATION=/opt/prmy/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRMY'

*.LOG_ARCHIVE_DEST_2='SERVICE=STBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STBY'

*.LOG_ARCHIVE_DEST_STATE_1=ENABLE

*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

*.LOG_ARCHIVE_MAX_PROCESSES=30

*.fal_server=STBY

*.fal_client=PRMY

standby_file_management=auto

*.db_file_name_convert='/opt/prmy/datafiles/PRMY/','/opt/prmy/datafiles/STBY/'

*.log_file_name_convert='/opt/prmy/datafiles/PRMY/','/opt/prmy/datafiles/STBY/'

Once the new init parameter file is ready on PRMY database we need to create the spfile:
SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL>  startup nomount pfile='/ora_home/ora_home10g/dbs/initPRMY.ora'

ORACLE instance started.

Total System Global Area  285212672 bytes

Fixed Size                  1267068 bytes

Variable Size              92277380 bytes

Database Buffers          188743680 bytes

Redo Buffers                2924544 bytes

SQL> create spfile from pfile;

File created.

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.

Enable Archiving if it is not enabled in our case it is already enabled
  On 10g you can enable archive log mode by mounting the database and executing the archivelog command:
SQL> startup mount

ORACLE instance started.

Total System Global Area  285212672 bytes

Fixed Size                  1267068 bytes

Variable Size              92277380 bytes

Database Buffers          188743680 bytes

Redo Buffers                2924544 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database archivelog;

Database altered.

SQL> Database log mode         Archive Mode

Automatic archival             Enabled

Archive destination            /opt/prmy/archive

Oldest online log sequence     2

Next log sequence to archive   4

Current log sequence           4

SQL>
Standby Database Steps
rman  backup of the primary database and  controlfile by rman.
Create an RMAN backup which we will use later to create the standby:

[oracle@linux1 rman_bkp_prmy]$ pwd

/opt/rman_bkp_prmy

[oracle@linux1 rman_bkp_prmy]$ rman target=/

Recovery Manager: Release 10.2.0.4.0 - Production on Fri Feb 3 19:07:56 2012

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

connected to target database: PRMY (DBID=722194705)

RMAN> backup full database format '/opt/rman_bkp_prmy/%d_%U.bckp' plus archivelog format '/opt/rman_bkp_prmy/%d_%U.bckp';

Starting backup at 03-FEB-12

current log archived

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=116 devtype=DISK

channel ORA_DISK_1: starting archive log backupset

channel ORA_DISK_1: specifying archive log(s) in backup set

input archive log thread=1 sequence=2 recid=1 stamp=774294184

input archive log thread=1 sequence=3 recid=2 stamp=774295879

input archive log thread=1 sequence=4 recid=3 stamp=774299369

channel ORA_DISK_1: starting piece 1 at 03-FEB-12

channel ORA_DISK_1: finished piece 1 at 03-FEB-12

piece handle=/opt/rman_bkp_prmy/PRMY_01n2dnnd_1_1.bckp tag=TAG20120203T190932 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04

Finished backup at 03-FEB-12

Starting backup at 03-FEB-12

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=/opt/prmy/datafiles/PRMY/system01.dbf

input datafile fno=00003 name=/opt/prmy/datafiles/PRMY/sysaux01.dbf

input datafile fno=00005 name=/opt/prmy/datafiles/PRMY/example01.dbf

input datafile fno=00002 name=/opt/prmy/datafiles/PRMY/undotbs01.dbf

input datafile fno=00004 name=/opt/prmy/datafiles/PRMY/users01.dbf

channel ORA_DISK_1: starting piece 1 at 03-FEB-12

channel ORA_DISK_1: finished piece 1 at 03-FEB-12

piece handle=/opt/rman_bkp_prmy/PRMY_02n2dnni_1_1.bckp tag=TAG20120203T190938 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including current control file in backupset

including current SPFILE in backupset

channel ORA_DISK_1: starting piece 1 at 03-FEB-12

channel ORA_DISK_1: finished piece 1 at 03-FEB-12

piece handle=/opt/rman_bkp_prmy/PRMY_03n2dnp0_1_1.bckp tag=TAG20120203T190938 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02

Finished backup at 03-FEB-12

Starting backup at 03-FEB-12

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archive log backupset

channel ORA_DISK_1: specifying archive log(s) in backup set

input archive log thread=1 sequence=5 recid=4 stamp=774299427

channel ORA_DISK_1: starting piece 1 at 03-FEB-12

channel ORA_DISK_1: finished piece 1 at 03-FEB-12

piece handle=/opt/rman_bkp_prmy/PRMY_04n2dnp3_1_1.bckp tag=TAG20120203T191027 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02

Finished backup at 03-FEB-12

RMAN>
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY format '/opt/rman_bkp_prmy/stby.ctl' ;

Starting backup at 03-FEB-12

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including standby control file in backupset

channel ORA_DISK_1: starting piece 1 at 03-FEB-12

channel ORA_DISK_1: finished piece 1 at 03-FEB-12

piece handle=/opt/rman_bkp_prmy/stby.ctl tag=TAG20120203T193143 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 03-FEB-12

RMAN> BACKUP ARCHIVELOG all  format '/opt/rman_bkp_prmy/archive_bkp_%d_%U.bckp';

Starting backup at 03-FEB-12

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archive log backupset

channel ORA_DISK_1: specifying archive log(s) in backup set

input archive log thread=1 sequence=2 recid=1 stamp=774294184

input archive log thread=1 sequence=3 recid=2 stamp=774295879

input archive log thread=1 sequence=4 recid=3 stamp=774299369

input archive log thread=1 sequence=5 recid=4 stamp=774299427

input archive log thread=1 sequence=6 recid=5 stamp=774300886

input archive log thread=1 sequence=7 recid=6 stamp=774301181

channel ORA_DISK_1: starting piece 1 at 03-FEB-12

channel ORA_DISK_1: finished piece 1 at 03-FEB-12

piece handle=/opt/rman_bkp_prmy/archive_bkp_PRMY_08n2dpfu_1_1.bckp tag=TAG20120203T193941 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02

Finished backup at 03-FEB-12

[oracle@linux1 rman_bkp_prmy]$ pwd

/opt/rman_bkp_prmy

[oracle@linux1 rman_bkp_prmy]$ ls -lrt

total 634904

-rw-r-----  1 oracle dba  10055680 Feb  3 19:09 PRMY_01n2dnnd_1_1.bckp

-rw-r-----  1 oracle dba 613384192 Feb  3 19:10 PRMY_02n2dnni_1_1.bckp

-rw-r-----  1 oracle dba   7143424 Feb  3 19:10 PRMY_03n2dnp0_1_1.bckp

-rw-r-----  1 oracle dba      3072 Feb  3 19:10 PRMY_04n2dnp3_1_1.bckp

-rw-r-----  1 oracle dba   7110656 Feb  3 19:31 stby.ctl

-rw-r-----  1 oracle dba  11776512 Feb  3 19:39 archive_bkp_PRMY_08n2dpfu_1_1.bckp

[oracle@linux1 rman_bkp_prmy]$ create same directory structure on linux2

As below

[oracle@linux2 ~]$ mkdir -p /opt/rman_bkp_prmy

[oracle@linux2 ~]$

Now use scp command to copy backupset from linux1 to linux2 server as below

[oracle@linux1 rman_bkp_prmy]$

Cd /opt/rman_bkp_prmy

[oracle@linux1 rman_bkp_prmy]$ scp * oracle@linux2:/opt/rman_bkp_prmy

oracle@linux2's password:

archive_bkp_PRMY_08n2dpfu_1_1.bckp                                                         100%   11MB   5.6MB/s   00:02

PRMY_01n2dnnd_1_1.bckp                                                                     100% 9820KB   4.8MB/s   00:02

PRMY_02n2dnni_1_1.bckp                                                                     100%  585MB   7.6MB/s   01:17

PRMY_03n2dnp0_1_1.bckp                                                                     100% 6976KB   6.8MB/s   00:00

PRMY_04n2dnp3_1_1.bckp                                                                     100% 3072     3.0KB/s   00:00

stby.ctl                                                                                   100% 6944KB   3.4MB/s   00:02

[oracle@linux1 rman_bkp_prmy]$0% 1315KB   1.3MB/s   00:01

Note :

On the standby node create the required directories to get the datafiles

mkdir -p /ora_home/ora_home10g/admin/STBY/adump

mkdir -p /ora_home/ora_home10g/admin/STBY/bdump

mkdir -p /opt/stby/datafiles/STBY/

mkdir -p /ora_home/ora_home10g/admin/STBY/cdump

mkdir -p /opt/STBY/archive

mkdir -p /opt/stby/datafiles/STBY/

mkdir –p /ora_home/ora_home10g/admin/STBY/udump
Prepare an Initialization Parameter File for the Standby Database

Copy from the primary pfile to the standby destination

Copy and edit the primary init.ora to set it up for the standby role,as here shown below highlighted:

STBY.__db_cache_size=188743680

STBY.__java_pool_size=4194304

STBY.__large_pool_size=4194304

STBY.__shared_pool_size=83886080

STBY.__streams_pool_size=0

*.audit_file_dest='/ora_home/ora_home10g/admin/STBY/adump'

*.background_dump_dest='/ora_home/ora_home10g/admin/STBY/bdump'

*.compatible='10.2.0.3.0'

*.control_files='/opt/stby/datafiles/STBY/control01.ctl','/opt/stby/datafiles/STBY/control02.ctl','/opt/stby/datafiles/STBY/control03.ctl'

*.core_dump_dest='/ora_home/ora_home10g/admin/STBY/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='PRMY'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=STBYXDB)'

*.job_queue_processes=10

##*.log_archive_dest_1='LOCATION=/opt/STBY/archive'

*.log_archive_format='%t_%s_%r.arc'

*.open_cursors=300

*.pga_aggregate_target=94371840

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=285212672

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/ora_home/ora_home10g/admin/STBY/udump'

*.db_unique_name='STBY'

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(RPMY,STBY)'

*.LOG_ARCHIVE_DEST_1='LOCATION=/opt/STBY/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STBY'

*.LOG_ARCHIVE_DEST_2='SERVICE=PRMY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRMY'

*.LOG_ARCHIVE_DEST_STATE_1=ENABLE

*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

*.LOG_ARCHIVE_MAX_PROCESSES=30

#Standby role parameters------------------------------------------

*.fal_server=PRMY

*.fal_client=STBY

*.standby_file_management=auto

*.db_file_name_convert='/opt/prmy/datafiles/PRMY/','/opt/stby/datafiles/STBY/'

*.log_file_name_convert='/opt/prmy/datafiles/PRMY/','/opt/stby/datafiles/STBY/'

Configure the listener and tnsnames to support the database on both linux1 and linux2

[oracle@linux1 admin]$ cat tnsnames.ora

PRMY =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.50)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = PRMY)

    )

  )

STBY =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.52)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = STBY)

    )

  )

[oracle@linux1 admin]$

Start the listener and check tnsping on both servers to both PRMY and STBY

# [oracle@linux1 admin]$

[oracle@linux1 admin]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 03-FEB-2012 20:05:15

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.50)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production

Start Date                03-FEB-2012 20:02:30

Uptime                    0 days 0 hr. 2 min. 44 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /ora_home/ora_home10g/network/admin/listener.ora

Listener Log File         /ora_home/ora_home10g/network/log/listener.log

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.50)(PORT=1521)))

Services Summary...

Service "PRMY" has 1 instance(s).

  Instance "PRMY", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

[oracle@linux1 admin]$
[oracle@linux1 admin]$ tnsping PRMY

TNS Ping Utility for Linux: Version 10.2.0.4.0 - Production on 03-FEB-2012 20:05:45

Copyright (c) 1997,  2007, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.50)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = PRMY)))

OK (10 msec)

[oracle@linux1 admin]$

[oracle@linux1 admin]$ tnsping STBY

TNS Ping Utility for Linux: Version 10.2.0.4.0 - Production on 03-FEB-2012 20:06:19

Copyright (c) 1997,  2007, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.52)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = STBY)))

OK (40 msec)

[oracle@linux1 admin]$
#on linux2 server
[oracle@linux2 admin]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 03-FEB-2012 20:02:37

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.52)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production

Start Date                03-FEB-2012 20:02:07

Uptime                    0 days 0 hr. 0 min. 29 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /ora_home/ora_home10g/network/admin/listener.ora

Listener Log File         /ora_home/ora_home10g/network/log/listener.log

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.52)(PORT=1521)))

Services Summary...

Service "STBY" has 1 instance(s).

  Instance "STBY", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

[oracle@linux2 admin]$

[oracle@linux2 admin]$ tnsping PRMY

TNS Ping Utility for Linux: Version 10.2.0.4.0 - Production on 03-FEB-2012 20:02:59

Copyright (c) 1997,  2007, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.50)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = PRMY)))

OK (0 msec)

[oracle@linux2 admin]$

[oracle@linux2 admin]$ tnsping STBY

TNS Ping Utility for Linux: Version 10.2.0.4.0 - Production on 03-FEB-2012 20:03:19

Copyright (c) 1997,  2007, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.52)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = STBY)))

OK (10 msec)

[oracle@linux2 admin]$

Set Up the Environment to Support the Standby Database on the standby node.

[oracle@linux1 dbs]$ scp  orapwPRMY oracle@linux2:/ora_home/ora_home10g

oracle@linux2's password:

orapwPRMY                                                                                  100% 1536     1.5KB/s   00:00

[oracle@linux1 dbs]$

[oracle@linux1 dbs]$ scp  orapwPRMY oracle@linux2:/ora_home/ora_home10g/dbs

oracle@linux2's password:

orapwPRMY                                                                                  100% 1536     1.5KB/s   00:00

[oracle@linux1 dbs]$

Login to linux2

[oracle@linux2 dbs]$ ls -lrt

total 40

-rw-r-----  1 oracle dba  8385 Sep 11  1998 init.ora

-rw-r--r--  1 oracle dba 12920 May  3  2001 initdw.ora

-rw-r--r--  1 oracle dba  1665 Feb  3 19:50 initSTBY.ora

-rw-rw----  1 oracle dba  1544 Feb  3 19:51 hc_STBY.dat

-rw-r-----  1 oracle dba  1536 Feb  3 20:07 orapwPRMY

[oracle@linux2 dbs]$ mv orapwPRMY orapwSTBY

 [oracle@linux2 dbs]$ ls -lrt orapwSTBY

-rw-r-----  1 oracle dba 1536 Feb  3 20:07 orapwSTBY

[oracle@linux2 dbs]$


insert the entry in oratab file on linux2 server for standby database name
Startup  the Standby database in nomount stage

[oracle@linux2 dbs]$ echo $ORACLE_SID

STBY

[oracle@linux2 dbs]$

SQL>  startup nomount;

ORACLE instance started.

Total System Global Area  285212672 bytes

Fixed Size                  1267068 bytes

Variable Size              92277380 bytes

Database Buffers          188743680 bytes

Redo Buffers                2924544 bytes

SQL>

SQL>

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create spfile from pfile;

File created.

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              92276304 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes

Create the standby database using rman:
[oracle@linux2 dbs]$ rman target=sys/password@PRMY auxiliary=/

Recovery Manager: Release 10.2.0.4.0 - Production on Fri Feb 3 20:16:41 2012

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

connected to target database: PRMY (DBID=722194705)

connected to auxiliary database: PRMY (not mounted)

RMAN>RMAN> DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK;

 Start the redo apply:

Starting Duplicate Db at 03-FEB-12

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=156 devtype=DISK

contents of Memory Script:

{

   restore clone standby controlfile;

   sql clone 'alter database mount standby database';

}

executing Memory Script

Starting restore at 03-FEB-12

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /opt/rman_bkp_prmy/stby.ctl

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/opt/rman_bkp_prmy/stby.ctl tag=TAG20120203T193143

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04

output filename=/opt/stby/datafiles/STBY/control01.ctl

output filename=/opt/stby/datafiles/STBY/control02.ctl

output filename=/opt/stby/datafiles/STBY/control03.ctl

Finished restore at 03-FEB-12

sql statement: alter database mount standby database

released channel: ORA_AUX_DISK_1

contents of Memory Script:

{

   set newname for tempfile  1 to

 "/opt/stby/datafiles/STBY/temp01.dbf";

   switch clone tempfile all;

   set newname for datafile  1 to

 "/opt/stby/datafiles/STBY/system01.dbf";

   set newname for datafile  2 to

 "/opt/stby/datafiles/STBY/undotbs01.dbf";

   set newname for datafile  3 to

 "/opt/stby/datafiles/STBY/sysaux01.dbf";

   set newname for datafile  4 to

 "/opt/stby/datafiles/STBY/users01.dbf";

   set newname for datafile  5 to

 "/opt/stby/datafiles/STBY/example01.dbf";

   restore

   check readonly

   clone database

   ;

}

executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to /opt/stby/datafiles/STBY/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 03-FEB-12

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=156 devtype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to /opt/stby/datafiles/STBY/system01.dbf

restoring datafile 00002 to /opt/stby/datafiles/STBY/undotbs01.dbf

restoring datafile 00003 to /opt/stby/datafiles/STBY/sysaux01.dbf

restoring datafile 00004 to /opt/stby/datafiles/STBY/users01.dbf

restoring datafile 00005 to /opt/stby/datafiles/STBY/example01.dbf

channel ORA_AUX_DISK_1: reading from backup piece /opt/rman_bkp_prmy/PRMY_02n2dnni_1_1.bckp

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/opt/rman_bkp_prmy/PRMY_02n2dnni_1_1.bckp tag=TAG20120203T190938

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:36

Finished restore at 03-FEB-12

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy recid=7 stamp=774303476 filename=/opt/stby/datafiles/STBY/system01.dbf

datafile 2 switched to datafile copy

input datafile copy recid=8 stamp=774303476 filename=/opt/stby/datafiles/STBY/undotbs01.dbf

datafile 3 switched to datafile copy

input datafile copy recid=9 stamp=774303476 filename=/opt/stby/datafiles/STBY/sysaux01.dbf

datafile 4 switched to datafile copy

input datafile copy recid=10 stamp=774303476 filename=/opt/stby/datafiles/STBY/users01.dbf

datafile 5 switched to datafile copy

input datafile copy recid=11 stamp=774303476 filename=/opt/stby/datafiles/STBY/example01.dbf

Finished Duplicate Db at 03-FEB-12

RMAN>
switch archive log on primry as below 20 times
On the Primary:

SQL> alter system switch logfile;

SQL> select name from v$database;

NAME

---------

PRMY

SQL> select 'PRIMARY SEQ IS ' ||sequence# ||chr(10), 'PRIMARY FIRST_TIME IS ' || to_char(first_time,'mm/dd/yy hh24:mi:ss')

 from v$log_history

 where sequence#=(select max(sequence#) from v$log_history);  2    3

'PRIMARYSEQIS'||SEQUENCE#||CHR(10)

--------------------------------------------------------

'PRIMARYFIRST_TIMEIS'||TO_CHAR(FIRST_TI

---------------------------------------

PRIMARY SEQ IS 27

PRIMARY FIRST_TIME IS 02/03/12 20:25:39

SQL>

 On the Standby database Linux2 (STBY):

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /opt/STBY/archive

Oldest online log sequence     4

Next log sequence to archive   6

Current log sequence           6

SQL>

SQL> select 'STANDBY SEQ IS ' ||sequence# ||chr(10), 'STANDBY FIRST_TIME IS ' || to_char(first_time,'mm/dd/yy hh24:mi:ss')

 from v$log_history

 where sequence#=(select max(sequence#) from v$log_history);  2    3

'STANDBYSEQIS'||SEQUENCE#||CHR(10)

--------------------------------------------------------

'STANDBYFIRST_TIMEIS'||TO_CHAR(FIRST_TI

---------------------------------------

STANDBY SEQ IS 5

STANDBY FIRST_TIME IS 02/03/12 19:09:29 

 Stop the managed recovery process on the standby:

Sqlplus >ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 

Sqlplus > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT from session;