Saturday, September 18, 2010

Setup Oracle Physical Standby Database Using Dataguard On Linux

1. INTRODUCTION

This document summarizes the steps of creating an Oracle Physical Standby Database on Linux servers using Oracle Data Guard features. Oracle 10g Data Guard supports both Logical and Physical Standby databases.

The database version that is used here is 10.2.0.3. The Primary database server is pcdb-1.databob.net and Standby database server is pcdb-2.databob.net.The Primary database (db_name) is called PCDB and the Standby database is called PCDBS.
Oracle 10g documentation regarding Data Guard Physical Standby Database can be found here:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/create_ps.htm


2. CONFIGURATIONS
2.1 Prerequisites
Following are the prerequisites need to be met before creating the standby database.

1. Make sure that the operating system and platform architecture on the primary and standby systems are the same. Oracle user was created in both servers and ssh is configured for Oracle user on both servers
2. Install Oracle database software on the standby server and patch it if necessary. We are using Oracle 10g, release 10.2.0.3. Make sure the same Oracle software release is used on the Primary and Standby databases server, and Oracle home paths are identical on both servers.
3. Test the Standby Database creation and make sure the server was installed properly. Also make sure that all the directories for data and logs are the same on both servers.

2.2 Planning
During the configuration processes, it requires restarting the primary database, creating backup copy of data and log files in the primary database, and restoring them in the standby database. Therefore, if the primary database is a production database, proper planning is needed to limit downtime.

Also, this document separates primary and standby database configuration tasks for clarity reason. In fact, some of these steps can be rearranged to limit database downtime.

2.3 Configure Primary Database Server, pcdb-1.databob.net
Before you create a standby database you must first ensure the primary database is properly configured. After the primary database is set up and running, these are additional configuration tasks need to complete:

1. Enable Forced Logging
2. Create a Password File
3. Configure a Standby Redo Log
4. Enable Archiving
5. Set Additional Primary Database Initialization Parameters

Please note that during the configuration processes, it requires to restart the primary database. Proper planning is needed to reduce down time if the primary database is already in production.

You need to be the Oracle sys user to perform most the configuration tasks. To become the sys user, do the following:

#> sqlplus /nolog
SQL>connect sys/password as sysdba

Following are steps to configure Primary Database

1. Enable forced logging on your primary database:

SQL> ALTER DATABASE FORCE LOGGING;

2. Create a password file if it doesn’t exist.
Create a password file if it dose not exist. Every database in a Data Guard configuration must use a password file, and the password for the SYS user must be identical on every system for redo data transmission to succeed.

1) To check if a password file already exists, run the following command:

SQL> select * from v$pwfile_users;
no rows selected
2) Create an Oracle password file in Linux:
Password files are created with the Oracle orapwd tool which is located in $ORACLE_HOME/bin directory. To create the password file, execute the following commands:
#>cd $ORACLE_HOME/bin
#>./orapwd file=/u0/oracle/product/10.2.0/db_1/dbs/orapwPCDB password= force=y

Replace with the password of the SYS user. The password file needs to be named exactly in orapw format in Linux. For example, if the sid is ‘PCDB’ then the password file name needs to be named as orapwPCDB.
The password file is located in $ORACLE_HOME/dbs directory.
3) Check again if the password file was created and perform the following query. You should get the same result as show bellow for sys user.
SQL> select * from v$pwfile_users;

USERNAME SYSDBA SYSOPER
-----------------------------------------
SYS TRUE TRUE

3. Configure a Standby Redo log.
1) The size of the standby redo log files should match the size of the current Primary database online redo log files. To find out the size of your online redo log files, run the following query:

SQL> select bytes from v$log;
BYTES
--------------
10485760
10485760
10485760
2) Use the following command to determine your current log file groups:
SQL> select group#, member from v$logfile:
GROUP# MEMBER
--------------------------------------------------------------------------------
1 /u0/oracle/log/PCDB/redo/PCDB_redo01_01.log
2 /u0/oracle/log/PCDB/redo/PCDB_redo02_01.log
3 /u0/oracle/log/PCDB/redo/PCDB_redo03_01.log

3) Create standby Redo log groups.
Data Guard can recover and apply more redo data from a standby redo log than from archived redo log files alone. Here, we are going to create same number log groups for standby log as of data redo log group.
Before creating the standby log groups, please make sure that:
• log file sizes are identical on the primary and standby databases.
The size of the current standby redo log files must exactly match the size of the current primary database online redo log files.
• Verify related database parameters and settings.
Verify the values used for the MAXLOGFILES and MAXLOGMEMBERS clauses on the SQL CREATE DATABASE statement will not limit the number of standby redo log file groups and members that you can add. The only way to override the limits specified by the MAXLOGFILES and MAXLOGMEMBERS clauses is to re-create the primary database or control file.
To create the standby log groups, user the following commands:

SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 10M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 10M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 10M;
4) To verify the results of the standby redo log groups creation, run the following query:
SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
GROUP# THREAD# SEQUENCE# ARCHIVED STATUS
---------- ---------- ---------- --------- ------------------------------
4 0 0 YES UNASSIGNED
5 0 0 YES UNASSIGNED
6 0 0 YES UNASSIGNED

4. Set Primary Database Initialization Parameters
Add (or edit) the following parameters to pFile, initPCDB.ora if they are not already in the pFile. If a pFile dose not exist, you can use the following command to create one from the existing spFile:
SQL>create pfile=’<$ORACLE_HOME>/dbs/initPCDB.ora’ from spfile;
(Note: replace oracle home with the full path.).
Add and verify if the following parameters are in the pFile, initPCDB.ora:
db_name=PCDB
db_unique_name=PCDB
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PCDB,PCDBS)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u0/oracle/log/PCDB/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=PCDB'
LOG_ARCHIVE_DEST_2=
'SERVICE=PCDBS LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=PCDBS'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
remote_login_passwordfile='EXCLUSIVE'
FAL_SERVER=PCDBS
FAL_CLIENT=PCDB
STANDBY_FILE_MANAGEMENT=AUTO
# Specify the location of the standby DB datafiles followed by the primary location;
DB_FILE_NAME_CONVERT=/u0/oracle/data/PCDB/data/datafile','/u0/oracle/data/PCDB/data/datafile'
# Specify the location of the standby DB online redo log files followed by the primary location LOG_FILE_NAME_CONVERT=‘/u0/oracle/data/PCDB/onlinelog’, ‘/u0/oracle/data/PCDB/onlinelog’

5. Create spfile from pfile, and restart primary database using the new spfile and enable archive log.
Data Guard must use SPFILE. Create the SPFILE and restart database.
SQL> shutdown immediate;
SQL>create spfile from pfile=’/u0/oracle/admin/PCDB/scripts/initPCDB.ora’;

6. Create a Control File for the standby database:
On Primary DB, create a control file for the standby to use:
SQL>startup mount;
SQL>alter database create standby controlfile as ‘/u0/oracle/log/PCDB/ctl/control-PCDBS-103.ctl’;
Database altered.

7. Make a backup copy of the primary database data file.
1) If the database is running, shutdown it.
SQL>shutdown immediate;
2) Make a copy of the data files and temp files and online log files and scp them over to the standby database server.
You can copy the file manually or follow the following steps to create scp scripts:
SQL> set pages 50000 lines 300 head off veri off flush off ti off
SQL> spool ‘/tmp/cpfiles_prim.sh’
SQL> select 'scp -p '||file_name||' ${stan_host}:’||file_name from dba_data_files;
SQL> select 'scp -p '||file_name||' ${stan_host}:’||file_name from dba_temp_files;
SQL> select 'scp -p '||member||' ${stan_host}:’||file_name from v$logfile;
SQL> spool off;
Edit the file /tmp/cpfiles_prim.sh to define the ${stan_host} variable. To do so, add these tow lines to the top of the script:
#!/bin/bash
stan_host=

Replace hostname with the hostname or IP address for the standby server.
#>/tmp/cpfiles_prim.sh
#!/bin/bash
stan_host= pcdb-2.databob.net
scp -p /u0/oracle/data/PCDB/sys/datafile/PCDB_system.dbf ${stan_host}:/u0/oracle/data/PCDB/sys/datafile/PCDB_system.dbf
…..
Execute the script, /tmp/cpfiles_prim.sh, to scp the file over to the standby database server. Note, depend on the files size and the network traffic, it may take some time. Therefore, you may want to change script to copy the files to a temp directory is the primary server, tar and gzip the files and scp the gziped files to the standby server. Then, copy the files to the pproper directories.

Before copying the files, verify that standby server file system setting are the same as the primary server. This means that all the directories need to be the same.
#>chomd 766 /tmp/cpfiles_prim.sh
#>./tmp/cpfiles_prim.sh
3)Copy the standby control file that was generated in step 6 above to the /tmp directory in the standby server
#>scp ‘/u0/oracle/log/PCDB/ctl/control-PCDBS-103.ctl’ standby_server:/tmp

8. Restart the Primary database using the newly created spfile and enable archive log.
SQL>Startup mount;
SQL>alter database archivelog;
SQL>alter database open;
SQL>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u0/oracle/log/PCDB/arch
Oldest online log sequence 343
Next log sequence to archive 345
Current log sequence 345
SQL>

Now the primary database is setup for transmitting data to standby database server.

2.4 Configure Standby Database Server, pcdb-2.databob.net
To configure the standby database server, pcdb-2 , db_unique_name PCDBS, these configuration tasks need to complete:

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

Following are steps to configure Standby Database.


1. Install the Primary database data files on the Standby Server:

If you had not copy the data files which you created in section 2.3, step 7, please do so to copy the files to the proper directories from the backup copy.

2. Copy the Primary DB pfile to Standby server and rename/edit the file.
1) Copy initPCDB.ora from Primary server to Standby server,
2) Rename it to initPCDBS.ora, and modify the file as follow.
Note that most the parameters here do not need to change since they will be the same as those mentioned in section 2.3, step 4.
*.audit_file_dest=/u0/oracle/admin/PCDB/adump
*.background_dump_dest=/u0/oracle/admin/PCDB/bdump
*.core_dump_dest=/u0/oracle/admin/PCDB/cdump
*.user_dump_dest=/u0/oracle/admin/PCDB/udump
*.compatible='10.2.0.3.0'
control_files = (/u0/oracle/admin/PCDB/pfile/control-PCDB-101.ctl,
/u0/oracle/backup/PCDB/ctl/control-PCDB-102.ctl,
/u0/oracle/log/PCDB/ctl/control-PCDB-103.ctl)
db_name='PCDB'
db_unique_name=PCDBS
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PCDB,PCDBS)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u0/oracle/log/PCDB/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=PCDBS'
LOG_ARCHIVE_DEST_2=
'SERVICE=PCDBS LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=PCDB'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
remote_login_passwordfile='EXCLUSIVE'
FAL_SERVER=PCDBS
FAL_CLIENT=PCDB
STANDBY_FILE_MANAGEMENT=AUTO
# Specify the location of the standby DB datafiles followed by the primary location;
DB_FILE_NAME_CONVERT=/u0/oracle/data/PCDB/data/datafile','/u0/oracle/data/PCDB/data/datafile'
# Specify the location of the standby DB online redo log files followed by the primary location LOG_FILE_NAME_CONVERT=‘/u0/oracle/data/PCDB/onlinelog’, ‘/u0/oracle/data/PCDB/onlinelog’

3 Copy the standby control file,which was scp’ed over from the primary server in section 2.3, step 7
to the following directories and filename ;
/u0/oracle/admin/PCDB/pfile/control-PCDB-101.ctl,
/u0/oracle/backup/PCDB/ctl/control-PCDB-102.ctl,
/u0/oracle/log/PCDB/ctl/control-PCDB-103.ctl

#>cp /tmp/control-PCDBS-103.ctl /u0/oracle/admin/PCDB/pfile/control-PCDB-101.ctl
#>cp /tmp/control-PCDBS-103.ctl /u0/oracle/backup/PCDB/ctl/control-PCDB-102.ctl
#>cp /tmp/control-PCDBS-103.ctl /u0/oracle/log/PCDB/ctl/control-PCDB-103.ctl

4. Create the password file for the standby server.
#>cd $ORACLE_HOME/bin
#>./orapwd file=/u0/oracle/product/10.2.0/db_1/dbs/orapwPCDBS password= force=y

5. Configure listeners for both the primary and standby databases.
Create or edit the listener.ora files on both primary and standby server. By default, the listener.ora file is located in the $ORACLE_HOME/network/admin directory.
1) On Primary server, pcdb-1, edit the listener.ora file for PCDB (primary) and PCDBS(standby).
# On primacy server, pcdb-1, PCDB
LISTENER_PCDB =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = pcdb-1)(PORT = 1521)(IP = FIRST))
)
)
SID_LIST_LISTENER_PCDB =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PCDB)
(ORACLE_HOME = /u0/oracle/product/10.2.0/db_1)
(SID_NAME = PCDB)
)
)
2) On Standby server, pcdb-2, edit the listener.ora file for PCDBS(standby).
# On primacy server, pcdb-2, PCDBS
LISTENER_PCDBS =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = pcdb-2)(PORT = 1521)(IP = FIRST))
)
)
SID_LIST_LISTENER_PCDBS =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PCDBS)
(ORACLE_HOME = /u0/oracle/product/10.2.0/db_1)
(SID_NAME = PCDBS)
)
)

6. Configure Oracle Net service names for both primary and standby servers.


Configure tnsnames.ora on both servers to hold entries for both databases. By default, the listener.ora file is located in the $ORACLE_HOME/network/admin directory.
1) On Primary database server, pcdb-1, edit the tnsnames.ora
LISTENER_PCDB =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = pcdb-1)(PORT = 1521)(IP = FIRST))
)
)

PCDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = pcdb-1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PCDB)
)
)
PCDBS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =pcdb-2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PCDB)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
2) On Standby server, pcdb-2, edit the tnsnames.ora
LISTENER_PCDBS=
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = pcdb-2)(PORT = 1521)(IP = FIRST))
)
)

PCDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = pcdb-1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PCDB)
)
)

PCDBS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =pcdb-2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PCDB)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
7. Start the listener and check tnsping on both nodes to both services
1) Restart listeners on primary servers
oracle@pcdb-1> lsnrctl stop
lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.3.0 - Production on 15-SEP-2010 10:26:32

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully
oracle@pcdb-1> lsnrctl start
2)Restart listener on standby server
oracle@pcdb-2> lsnrctl stop
oracle@pcdb-2> lsnrctl start

3)use tnsping to test both servers, PCDB and PCDBS

On primary server:
oracle@pcdb-1>tnsping PCDB
oracle@pcdb-1>tnsping PCDBS

On standby:
oracle@pcdb-2>tnsping PCDB
oracle@pcdb-2>tnsping PCDBS

8.Verify that the Oracle environment variables are set correctly on the standby server.
#>sudo – su oracle
#>env
ORACLE_SID=PCDB
ORACLE_BASE=/u0/oracle/product/10.2.0
….

9. Start up nomount the standby database and generate a spfile.
SQL> connect sys/password as sysdba;
Connected to an idle instance.
SQL> startup nomount pfile='/u0/oracle/admin/PCDB/scripts/initPCDB.ora';
ORACLE instance started.

Total System Global Area 1.2583E+10 bytes
Fixed Size 2094096 bytes
Variable Size 1728056304 bytes
Database Buffers 1.0838E+10 bytes
Redo Buffers 14680064 bytes

SQL> create spfile from pfile='/u0/oracle/admin/PCDB/scripts/initPCDB.ora';

File created.

SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.

10. Startup mount the standby database and perform recovery
1) Startup mount the instance
SQL> startup mount
ORACLE instance started.

Total System Global Area 1.2583E+10 bytes
Fixed Size 2094096 bytes
Variable Size 1728056304 bytes
Database Buffers 1.0838E+10 bytes
Redo Buffers 14680064 bytes
Database mounted.

2) Start apply the logs
Issue the following command to start apply the logs.

SQL>alter database recover managed standby database disconnect from session;
Database altered.

Now, if everything is setup correctly, the standby database should start to apply the logs from the primary database.

11. Verify the Physical Standby Database Is Performing Properly

1) Check alert log of the standby server to see if the operations taking place or there is any error

#> vi /u0/oracle/admin/PCDB/bdump/alert_PCDBS.log
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
Wed Sep 15 15:03:16 2010
Attempt to start background Managed Standby Recovery process (PCDBS)
MRP0 started with pid=56, OS id=6637
Wed Sep 15 15:03:16 2010
MRP0: Background Managed Standby Recovery process started (PCDBS)
Managed Standby Recovery not using Real Time Apply
parallel recovery started with 3 processes
Media Recovery Waiting for thread 1 sequence 360
Wed Sep 15 15:03:22 2010
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION

2) Check the alert log of the primary server to see if the operations taking place or there is any error

The alert log of the primary will show how it recognize the standby and start shipping archived logs
#> vi /u0/oracle/admin/PCDB/bdump/alert_PCDB.log
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
LNS: Standby redo logfile selected for thread 1 sequence 364 for destination LOG_ARCHIVE_DEST_2
Wed Sep 15 17:05:24 2010
Thread 1 advanced to log sequence 365
Current log# 2 seq# 365 mem# 0: /u0/oracle/log/PCDB/redo/PCDB_redo02_01.log
Wed Sep 15 17:05:25 2010
LNS: Standby redo logfile selected for thread 1 sequence 365 for destination LOG_ARCHIVE_DEST_2

3). Check the Standby severer:
3.1). Check database unique_name and database name.
SQL> show parameters db_unique_name;
NAME TYPE VALUE
---------------------------------------------------------------------------
db_unique_name string PCDBS

SQL> SELECT NAME FROM V$DATABASE
NAME
---------------------------
PCDB
3.2). Check archived redo log on Standby
SQL>SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME
---------- --------------- ---------------
367 15-SEP-10 15-SEP-10
368 15-SEP-10 15-SEP-10
369 15-SEP-10 15-SEP-10
370 15-SEP-10 16-SEP-10
371 16-SEP-10 16-SEP-10
372 16-SEP-10 16-SEP-10
3.3)Check archived redo applied on Standby
SQL>select sequence#, applied from v$archived_log order by sequence#;
SEQUENCE# APPLIED
---------- ---------
367 YES
368 YES
369 YES
370 YES
371 YES
372 YES

3.4) Check standby manager status
SQL> select process, status, sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------------------------- ------------------------------------ ----------
ARCH CLOSING 363
ARCH CLOSING 364
….
ARCH CONNECTED 0
ARCH CONNECTED 0
MRP0 WAIT_FOR_LOG 373
….
RFS IDLE 0
RFS IDLE 373
The above show MRP0 is waiting for log 372 from the primary server.
3.5) Switch log files on Primary.
Login to primary database and perform the following command to force a log switch.
SQL> alter system switch logfile;

System altered.

SQL>archive log list
archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u0/oracle/log/PCDB/arch
Oldest online log sequence 372
Next log sequence to archive 374
Current log sequence 374

3.6) In Standby Database server, repeat verification steps, 3.2, 3.3 and 3.4.

You show notice that log 373 had been applied and MRP0/LNS is waiting for log 374 (step 3.4).
12. Errors and Troubleshoot
1) Error: ORA-19527: physical standby redo log must be renamed (in Standby server)
If you have the above error message in the alert log after you issued “alter database recover managed standby database using current logfile disconnect;” statement, it was due to the fact that the standby database file structure is exactly the same as the primary database server. To resolve that, do the following:

SQL> alter system set log_file_name_convert='dummy','dummy' scope=spfile;
System altered.

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;

Now, you can start the recover again by issue the following statement:
SQL> alter system set log_file_name_convert='dummy','dummy' scope=spfile;

Check the alert log again and you should not see the same error again. If you see error, “ORA-27037: unable to obtain file status”, try to shutdown the server again one more time and startup mount, you will not see any more error.

3)ORA-16191: Primary log shipping client not logged on standby

ORA-16191: Primary log shipping client not logged on standby

PING[ARCr]: Heartbeat failed to connect to standby 'PCDBS'. Error is 16191.
Wed Sep 15 16:50:49 2010
Errors in file /u0/oracle/admin/PCDB/bdump/PCDB_arcr_29633.trc:
ORA-01017: invalid username/password; logon denied
Wed Sep 15 16:50:49 2010
Error 1017 received logging on to the standby

The error was caused, most likely, the missing of password file or the password for sys user was not entered correctly in the password file. Run the following query to check if the password file was created in both servers (primary and standby) and the password was correctly. You can always recreate the password files.(see step 2, section 2.3)

SQL> select * from v$pwfile_users;

2.5 Useful Commands and Queries

• Check archive log status on standby database:
SQL>select sequence#, first_time, next_time,applied from v$archived_log;
• Start archive log recover (log apply service):
SQL>alter database recover managed standby database using current logfile disconnect;
• Start real-time apply on Standby
SQL> alter database recover managed standby database using current logfile disconnect;
• Stop archive log recover:
SQL> alter database recover managed standby database cancel;
• Check dataguard messages
SQL> select error_code, timestamp, message from v$dataguard_status;
• Check standby messages
SQL> select process,status,sequence# from v$managed_standby;
• Enable archivelog
SQL>alter database archivelog;
• Show current archive log list
SQL>archive log list;
• Force a log switch on primary
SQL>alter system switch logfile
• Check log file size
SQL>select bytes from v$log;
• Check current log group
SQL>select group#, member from v$logfile;
• Many more here:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/toc.htm


Related Notes:

REFERENCES




Posted by data bob jr at 7:28 AM Labels: , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)