Step-by-Step Instructions for Creating a Physical Standby Database using SQL commands
1. General Overview :
The purpose of this document is to create a step by step guideline for using the Oracle Dataguard a
High available mechanism. I spent about 7 days investigating about a problem faced from
my first dataguard experience. Both primary and standby
servers are on linux redhat : same version of OS.
- Primary DB : 10.2.0.1
- Standby DB : 10.2.0.3
- Host Name of Primary DB : arcdb01.es.egwn.lan
- Host Name of Standby DB : x06.d15.lan
I m trying to setup oracle dataguard for 10G ; both primary and standby databases are in 10GR2.
Configuring Oracle DataGuard using SQL commands – Creating a physical standby database ; I can’t get the log files or Archive logs on my StandBy db.
Checking v$archive_dest_status view on PRIMARY DB, I found the error below :
FROM PRIMARY DB :
I getting an error Oracle not available
SELECT * FROM v$archive_dest_status
DEST_ID 2
DEST_NAME LOG_ARCHIVE_DEST_2
STATUS ERROR
TYPE PHYSICAL
DATABASE_MODE UNKNOWN
RECOVERY_MODE UNKNOWN
PROTECTION_MODE MAXIMUM PERFORMANCE
DESTINATION X06.D15.LAN
STANDBY_LOGFILE_COUNT 0
STANDBY_LOGFILE_ACTIVE 0
ARCHIVED_THREAD# 0
ARCHIVED_SEQ# 0
APPLIED_THREAD# 0
APPLIED_SEQ# 0
ERROR ORA-01034: ORACLE not available
SRL NO
DB_UNIQUE_NAME STANDBY
SYNCHRONIZATION_STATUS CHECK CONFIGURATION
SYNCHRONIZED NO
Logfiles were not applied on my standby DB even I thougth all confirgurations were succesfully done, until I realized the origin of my problem was the version of Oracle on both server. Even both oracle servers were in Oracle 10GR2, please always make sure that they have the SAME VERSION.
I spent a little bit more than hour to patch my Primary DB to 10.2.0.3 and now My dataguard works perfectly.
2- Step by Step :
In this section we will perform our Data Guard WorkShop, which outlines the procedure to create a Physical standby.
Steps are :
- Create a backup of the primary
- Create a Standby Control file
- adjust the pfile of the Primary
- Transfer the datafiles to the standby host
- create same directories for trace files
- edit the pfile of the standby
- mount the standby
Notes :
Both primary and standby servers are on linux redhat : same version of OS
- Primary DB : 10.2.0.3
- Standby DB : 10.2.0.3
- Host Name of Primary DB : arcdb01.es.egwn.lan
- Host Name of Standby DB : x06.d15.lan
Step 1 : Setup Listeners
Tnsnames.ora of the Primary DB :
[oracle@arcdb01 ~]$ export TNS_ADMIN=$ORACLE_HOME/network/admin
[oracle@arcdb01 ~]$ cat /u01/app/oracle/oracle/product/10.2.0/ARCDB01/network/admin/tnsnames.ora
X06.D15.LAN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = X06.D15.LAN)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ARCDB01)
(SERVER = DEDICATED)
)
)
ARCDB01.ES.EGWN.LAN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ARCDB01.ES.EGWN.LAN)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ARCDB01)
(SERVER = DEDICATED)
)
)
Tnsnames.ora of the Standby DB :
[oracle@x06 dbf]$ export TNS_ADMIN=$ORACLE_HOME/network/admin
[oracle@x06 dbf]$ cat $TNS_ADMIN/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/oracle/product/10.2.0/X06/oracle/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ARCDB01.ES.EGWN.LAN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ARCDB01.ES.EGWN.LAN)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ARCDB01)
(SERVER = DEDICATED)
)
)
X06.D15.LAN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = X06.D15.LAN)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ARCDB01)
(SERVER = DEDICATED)
)
)
[oracle@x06 dbf]$
Step 2 : check Listeners status
TNSPING of the STANDBY DB from the PRIMARY DB :
[oracle@arcdb01 ~]$ tnsping x06.d15.lan
TNS Ping Utility for Linux: Version 10.2.0.3.0 – Production on 26-MAR-2010 15:33:12
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/oracle/product/10.2.0/ARCDB01/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = X06.D15.LAN)(PORT = 1521))) (CONNECT_DATA = (SID = ARCDB01) (SERVER = DEDICATED)))
OK (0 msec)
[oracle@arcdb01 ~]$
TNSPING Of the Primary DB from the Standby DB :
[oracle@x06 bdump]$ tnsping ARCDB01.ES.EGWN.LAN
TNS Ping Utility for Linux: Version 10.2.0.3.0 – Production on 26-MAR-2010 15:34:05
Copyright (c) 1997, 2006, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/oracle/product/10.2.0/X06/oracle/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ARCDB01.ES.EGWN.LAN)(PORT = 1521))) (CONNECT_DATA = (SID = ARCDB01) (SERVER = DEDICATED)))
OK (20 msec)
[oracle@x06 bdump]$
Step 3 : Enable archiving and force logging
As a dataguard is dependent on redo to maintain the standby, we must assure that the primary database is in archivelog mode. To place the primary into archivelog, perform the following steps :
[oracle@arcdb01 ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.3.0 – Production on Mon Mar 22 20:26:19 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> connect sys as sysdba
Enter password:
Connected.
SQL>
SQL> show parameter spfile
NAME TYPE VALUE
———————————— ———– ——————————
spfile string /u01/app/oracle/oracle/product
/10.2.0/ARCDB01/dbs/spfileARCD
B01.ora
SQL> select force_logging from v$database
2 ;
FOR
—
NO
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/flash_recovery_area/ARCDB01/archivelog/
Oldest online log sequence 1418
Next log sequence to archive 1420
Current log sequence 1420
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 2021280 bytes
Variable Size 117442656 bytes
Database Buffers 293601280 bytes
Redo Buffers 6365184 bytes
Database mounted.
SQL> alter database archivelog ;
Database altered.
SQL> alter database force logging;
Database altered.
SQL> alter database open;
Database altered.
SQL>
SQL>
SQL> select log_mode, force_logging from v$database;
LOG_MODE FOR
———— —
ARCHIVELOG YES
Step 4 : Create a password file
Due to new log transport security and authentification features, it is madatory that every database in a Dataguard configuration utilize a password file. In addition, the password for the sys user must be identical on every system for log transport services to function, If the primary DB does not currently have a password file, create one with the following steps :
[oracle@arcdb01 ~]$ orapwd file=/u00/oracle/product/10.2.0/db_1/dbs/orapwarcdb01 password=orawiss entries=5 force=y
Once the password file is created, you must set the following parameter in the spfile while the database is in nomount state :
alter system set remote_login_passwordfile=exclusive scope=spfile;
Tansfer the password file to the standby DB :
[oracle@x06 dbf]$ cd /u01/app/oracle/oracle/product/10.2.0/X06/oracle/dbs/
[oracle@x06 dbs]$ ls
hc_X06.dat initdw.ora init.ora lkSTANDBY lkX06 orapwarcdb01 orapwX06 spfileX06.ora
[oracle@x06 dbs]$
Step 5 : Configure the primary initilization paraneters
We must configure the parameters to control log transport services and log apply services so that the database will operate in either role with no parameter modification. While the database is mounted on a primry controlfile , the standby parameters are not read and are not into effect, so they will not affect the operation of the database while in the primary role.
The parameters shown here are in bold, to be placed into a primary standby.ora pfile :
[oracle@arcdb01 ~]$ vi /home/oracle/ADVDB/standby.ora
ARCDB01.__db_cache_size=297795584
ARCDB01.__java_pool_size=4194304
ARCDB01.__large_pool_size=4194304
ARCDB01.__shared_pool_size=100663296
ARCDB01.__streams_pool_size=4194304
*.audit_file_dest=’/u01/app/oracle/admin/ARCDB01/adump’
*.background_dump_dest=’/u01/app/oracle/admin/ARCDB01/bdump’
*.compatible=’10.2.0.1.0'
*.control_file_record_keep_time=8
*.control_files=’/u01/app/oracle/oradata/ARCDB01/control01.ctl’,'/u01/app/oracle/oradata/ARCDB01/control02.ctl’,'/u01/app/oracle/oradata/ARCDB01/control03.ctl’
*.core_dump_dest=’/u01/app/oracle/admin/ARCDB01/cdump’
*.db_block_checking=’TRUE’
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’ARCDB01'
*.db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.db_unique_name=’PRIMARY’
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=ARCDB01XDB)’
*.FAL_Client=’ARCDB01.ES.EGWN.LAN’
*.FAL_Server=’X06.D15.LAN’
*.job_queue_processes=10
*.LOG_ARCHIVE_CONFIG=’DG_CONFIG=(PRIMARY,STANDBY)’
*.log_archive_dest_1=’location=/u01/app/oracle/flash_recovery_area/ARCDB01/archivelog/’
*.log_archive_dest_2=’Service=X06.D15.LAN LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=STANDBY’
*.log_archive_dest_state_1=’ENABLE’
*.log_archive_dest_state_2=’DEFER’
*.log_archive_format=’%s_arc_ln%r_db%d_%t.arc’
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=200
*.remote_login_passwordfile=’EXCLUSIVE’
*.SERVICE_NAMES=’PRIMARY’
*.sessions=205
*.sga_max_size=419430400
*.sga_target=419430400
*.Standby_File_Management=’AUTO’
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1'
*.user_dump_dest=’/u01/app/oracle/admin/ARCDB01/udump’
*.utl_file_dir=’/home/oracle/my_logminer’
Step 6 : Create a backup of the primary database
A physical standby can be created using either a hot or cold backup as long as all the necessary archivelogs are available to bring the database to be a consistent state. You can simply use RMAN to backup the primary database.
RMAN> backup database plus archivelog;
Step 7 : Create the Primary spfile and Standby controlfile
First, restard the primary DB using the pfile created in step 5 and then, with the primary DB in either a mount or open state, create a standby controlfile with the following syntaxes :
[oracle@arcdb01 ~]$ vi /home/oracle/ADVDB/standby.ora
[oracle@arcdb01 ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.3.0 – Production on Sun Mar 28 14:53:18 2010
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
SQL> connect sys as sysdba
Enter password:
Connected to an idle instance.
SQL> create spfile from pfile=’/home/oracle/ADVDB/standby.ora’;
File created.
SQL> startup
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 2073248 bytes
Variable Size 113249632 bytes
Database Buffers 297795584 bytes
Redo Buffers 6311936 bytes
Database mounted.
Database opened.
SQL> alter database create standby controlfile as ‘/home/oracle/ADVDB/standby_x06.ctl’;
Database altered.
SQL>
SQL> create pfile=’/home/oracle/ADVDB/standby.ora’ from spfile;
File created.
SQL>
As you can see in te last command, we create a standby pfile from the spfile, jut to be sure to have the correct pfile when this will be transferred to the standby Db, modified and the used as a pfile of the standby db.
Step 7 : Create the Standby spfile
Remember, at the previous step, we have created the pfile, this should be transferred to the standby host using for example scp or sftp commands.
Now, we should modify the pfile of the standby DB. below, are parameters that needed to be modified in our configuration.
ARCDB01.__java_pool_size=4194304
ARCDB01.__large_pool_size=4194304
ARCDB01.__shared_pool_size=100663296
ARCDB01.__streams_pool_size=4194304
*.audit_file_dest=’/u01/app/oracle/admin/ARCDB01/adump’
*.background_dump_dest=’/u01/app/oracle/admin/ARCDB01/bdump’
*.compatible=’10.2.0.1.0'
*.control_file_record_keep_time=8
*.control_files=’/home/oracle/dbf/standby_x06.ctl’
*.db_file_name_convert=’/u01/app/oracle/oradata/ARCDB01',’/home/oracle/dbf’,'/home/oracle/oradata’,'/home/oracle/dbf’
*.log_file_name_convert=’/u01/app/oracle/oradata/ARCDB01',’/home/oracle/dbf’,'/home/oracle/oradata’,'/home/oracle/dbf’
*.core_dump_dest=’/u01/app/oracle/admin/ARCDB01/cdump’
*.db_block_checking=’TRUE’
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’ARCDB01'
*.db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.db_unique_name=’STANDBY’
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=ARCDB01XDB)’
*.FAL_Server=’ARCDB01.ES.EGWN.LAN’
*.FAL_client=’X06.D15.LAN’
*.job_queue_processes=10
*.LOG_ARCHIVE_CONFIG=’DG_CONFIG=(PRIMARY,STANDBY)’
*.log_archive_dest_1=’location=/u01/app/oracle/flash_recovery_area/ARCDB01/archivelog/’
*.log_archive_dest_2=’Service=ARCDB01.ES.EGWN.LAN
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=PRIMARY’
*.log_archive_dest_state_1=’ENABLE’
*.log_archive_dest_state_2=’ENABLE’
*.log_archive_format=’%s_arc_ln%r_db%d_%t.arc’
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=200
*.remote_login_passwordfile=’EXCLUSIVE’
*.SERVICE_NAMES=’PRIMARY’
*.sessions=205
*.sga_max_size=419430400
*.sga_target=419430400
*.Standby_File_Management=’AUTO’
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1'
*.user_dump_dest=’/u01/app/oracle/admin/ARCDB01/udump’
*.utl_file_dir=’/home/oracle/my_logminer’
Please not that other parameters, such as dump destinations, may need to be modified depending on your environment.
Step 8 : Transfer files to the standby host
Using an operating system utility, transfer the files of the primary DB to the standby DB including :
- The controlfile generated previously (standby_x06.ctl)
- The Standby.ora pfile modified in the previous step
- All primary DB datafiles, you can use the steps below :
from the Primary DB, connect as a DBA :
SQL> SELECT *
FROM Dba_Data_Files ;
FILE_NAME
/u01/app/oracle/oradata/ARCDB01/users01.dbf
/u01/app/oracle/oradata/ARCDB01/sysaux01.dbf
/u01/app/oracle/oradata/ARCDB01/undotbs01.dbf
/u01/app/oracle/oradata/ARCDB01/system01.dbf
/u01/app/oracle/oradata/ARCDB01/rep_for_rman_devexen15.dbf
/u01/app/oracle/oradata/ARCDB01/REP_FOR_BACKRECK_TEST_01.dbf
/u01/app/oracle/oradata/ARCDB01/indx_01.dbf
SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
from the standby DB :
Using username “oracle”.
Authenticating with public key “imported-openssh-key”
Red Hat Enterprise Linux Server release 5.4 – Linux 2.6.18-164.11.1.el5xen
Four 2.83GHz Intel Pentium Xeon cpus with 4GB RAM
-> x06.d15.lan get *.dbf
Fetching /u01/app/oracle/oradata/ARCDB01/REP_FOR_BACKRECK_TEST_01.dbf to REP_FOR _BACKRECK_TEST_01.dbf
/u01/app/oracle/oradata/ARCDB01/REP_FOR_BACKRECK_TEST_01.dbf 100% 3315MB 24.0MB/s 02:18
Fetching /u01/app/oracle/oradata/ARCDB01/cp_indx_01.dbf to cp_indx_01.dbf
/u01/app/oracle/oradata/ARCDB01/cp_indx_01.dbf 100% 600MB 26.1MB/s 00:23
Fetching /u01/app/oracle/oradata/ARCDB01/indx_01.dbf to indx_01.dbf
/u01/app/oracle/oradata/ARCDB01/indx_01.dbf 100% 600MB 26.1MB/s 00:23
Fetching /u01/app/oracle/oradata/ARCDB01/rep_for_rman_devexen15.dbf to rep_for_rman_devexen15.dbf
/u01/app/oracle/oradata/ARCDB01/rep_for_rman_devexen15.dbf 100% 50MB 25.0MB/s 00:02
Fetching /u01/app/oracle/oradata/ARCDB01/sysaux01.dbf to sysaux01.dbf
/u01/app/oracle/oradata/ARCDB01/sysaux01.dbf 100% 530MB 21.2MB/s 00:25
Fetching /u01/app/oracle/oradata/ARCDB01/system01.dbf to system01.dbf
/u01/app/oracle/oradata/ARCDB01/system01.dbf 100% 520MB 27.4MB/s 00:19
Fetching /u01/app/oracle/oradata/ARCDB01/temp01.dbf to temp01.dbf
/u01/app/oracle/oradata/ARCDB01/temp01.dbf 100% 164MB 23.4MB/s 00:07
Fetching /u01/app/oracle/oradata/ARCDB01/undotbs01.dbf to undotbs01.dbf
/u01/app/oracle/oradata/ARCDB01/undotbs01.dbf 100% 655MB 26.2MB/s 00:25
Fetching /u01/app/oracle/oradata/ARCDB01/users01.dbf to users01.dbf
/u01/app/oracle/oradata/ARCDB01/users01.dbf 100% 5128KB 5.0MB/s 00:00
sftp>
Step 9 : Create an spfile for the standby instance
From the standby DB :
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> create spfile from pfile=’/home/oracle/dbf/standby.ora’;
File created.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 2073248 bytes
Variable Size 134221152 bytes
Database Buffers 276824064 bytes
Redo Buffers 6311936 bytes
SQL> alter database mount standby database;
Database altered.
SQL>
Step 10 : Begin shipping redo to the standby database
if you remember, earlier we deferred log_archive_dest_2 on the primary until we had the standby mounted. Now, it is time to enable that destination and begin shipping redo to the standby.
On the primary, enter the following command :
alter system set log_archive_dest_state_2=enable scope=both;
Sometimes it happens that you face the problem of Archive Gaps where by a range of archived redo log files is created. Archive gaps are created whenever the next archived redo log file generated by the primary database is not applied to the standby database. It is usually recommended to increase the LOG_ARCHIVE_MAX_PROCESSES parameter in order to resolve archive gaps by controlling the number of archive processes the instance uses.
I recommand you to increase your archive max processes parameter at your both primary and standby db :
alter system set log_archive_max_processes=4 scope=both;
From the primary DB, Check the sequence # and the archiving mode by executing following command :
SQL> Archive Log List
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/flash_recovery_area/ARCDB01/archivelog/
Oldest online log sequence 1592
Next log sequence to archive 1594
Current log sequence 1594
Next, peform a log switch on the primary and verify that the transmission of the log was successful :
SQL> alter system switch logfile;
System altered.
SQL> SELECT * FROM v$archive_dest where dest_id=2 ;
DEST_ID 2
DEST_NAME LOG_ARCHIVE_DEST_2
STATUS VALID
BINDING OPTIONAL
NAME_SPACE SYSTEM
TARGET STANDBY
ARCHIVER LGWR
SCHEDULE ACTIVE
DESTINATION X06.D15.LAN
LOG_SEQUENCE 1599
REOPEN_SECS 300
DELAY_MINS 0
MAX_CONNECTIONS 1
NET_TIMEOUT 180
PROCESS LGWR
REGISTER YES
FAIL_DATE
FAIL_SEQUENCE 0
FAIL_BLOCK 0
FAILURE_COUNT 0
MAX_FAILURE 0
ERROR
ALTERNATE NONE
DEPENDENCY NONE
REMOTE_TEMPLATE NONE
QUOTA_SIZE 0
QUOTA_USED 0
MOUNTID 0
TRANSMIT_MODE ASYNCHRONOUS
ASYNC_BLOCKS 61440
AFFIRM NO
TYPE PUBLIC
VALID_NOW YES
VALID_TYPE ONLINE_LOGFILE
VALID_ROLE PRIMARY_ROLE
DB_UNIQUE_NAME STANDBY
VERIFY NO
if the transmission was successful, the status of the destination should be valid. If the status is invalid, investigate the error listed in the error column to correct any issues.
Now, lets make a real test on our dataguard system :
From the primary DB :
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Connected as wissem
SQL> create table test (teste number);
Table created
SQL> insert into test values (1);
1 row inserted
SQL> commit;
Commit complete
SQL>
From the Standby DB :
SQL> select * from dba_users d where d.username = ‘WISSEM’;
USERNAME USER_ID PASSWORD
—————————— ———- ——————————
ACCOUNT_STATUS LOCK_DATE EXPIRY_DA
——————————– ——— ———
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED
—————————— —————————— ———
PROFILE INITIAL_RSRC_CONSUMER_GROUP
—————————— ——————————
EXTERNAL_NAME
——————————————————————————–
WISSEM 61 4531384AFBFF9B98
OPEN
USERS TEMP 21-AUG-08
USERNAME USER_ID PASSWORD
—————————— ———- ——————————
ACCOUNT_STATUS LOCK_DATE EXPIRY_DA
——————————– ——— ———
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED
—————————— —————————— ———
PROFILE INITIAL_RSRC_CONSUMER_GROUP
—————————— ——————————
EXTERNAL_NAME
——————————————————————————–
DEFAULT DEFAULT_CONSUMER_GROUP
SQL> select * from wissem.test;
select * from wissem.test
*
ERROR at line 1:
ORA-00942: table or view does not exist
BACK to the primary DB and perform a log switch :
SQL> alter system switch logfile;
System altered.
SQL> Archive Log List
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/flash_recovery_area/ARCDB01/archivelog/
Oldest online log sequence 1598
Next log sequence to archive 1600
Current log sequence 1600
SQL>exit
Check Log list from the STANDBY DB :
SQL> alter database recover managed standby database disconnect from session
2 ;
Database altered.
SQL> Archive Log List
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/flash_recovery_area/ARCDB01/archivelog/
Oldest online log sequence 1598
Next log sequence to archive 0
Current log sequence 1600
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> select * from wissem.test
2 ;
TESTE
———-
1
SQL>
Now, we can see the results of our new table from the standby DB.
Happy dataguard!
Scridb filter
1. General Overview :
The purpose of this document is to create a step by step guideline for using the Oracle Dataguard a
High available mechanism. I spent about 7 days investigating about a problem faced from
my first dataguard experience. Both primary and standby
servers are on linux redhat : same version of OS.
- Primary DB : 10.2.0.1
- Standby DB : 10.2.0.3
- Host Name of Primary DB : arcdb01.es.egwn.lan
- Host Name of Standby DB : x06.d15.lan
I m trying to setup oracle dataguard for 10G ; both primary and standby databases are in 10GR2.
Configuring Oracle DataGuard using SQL commands – Creating a physical standby database ; I can’t get the log files or Archive logs on my StandBy db.
Checking v$archive_dest_status view on PRIMARY DB, I found the error below :
FROM PRIMARY DB :
I getting an error Oracle not available
SELECT * FROM v$archive_dest_status
DEST_ID 2
DEST_NAME LOG_ARCHIVE_DEST_2
STATUS ERROR
TYPE PHYSICAL
DATABASE_MODE UNKNOWN
RECOVERY_MODE UNKNOWN
PROTECTION_MODE MAXIMUM PERFORMANCE
DESTINATION X06.D15.LAN
STANDBY_LOGFILE_COUNT 0
STANDBY_LOGFILE_ACTIVE 0
ARCHIVED_THREAD# 0
ARCHIVED_SEQ# 0
APPLIED_THREAD# 0
APPLIED_SEQ# 0
ERROR ORA-01034: ORACLE not available
SRL NO
DB_UNIQUE_NAME STANDBY
SYNCHRONIZATION_STATUS CHECK CONFIGURATION
SYNCHRONIZED NO
Logfiles were not applied on my standby DB even I thougth all confirgurations were succesfully done, until I realized the origin of my problem was the version of Oracle on both server. Even both oracle servers were in Oracle 10GR2, please always make sure that they have the SAME VERSION.
I spent a little bit more than hour to patch my Primary DB to 10.2.0.3 and now My dataguard works perfectly.
2- Step by Step :
In this section we will perform our Data Guard WorkShop, which outlines the procedure to create a Physical standby.
Steps are :
- Create a backup of the primary
- Create a Standby Control file
- adjust the pfile of the Primary
- Transfer the datafiles to the standby host
- create same directories for trace files
- edit the pfile of the standby
- mount the standby
Notes :
Both primary and standby servers are on linux redhat : same version of OS
- Primary DB : 10.2.0.3
- Standby DB : 10.2.0.3
- Host Name of Primary DB : arcdb01.es.egwn.lan
- Host Name of Standby DB : x06.d15.lan
Step 1 : Setup Listeners
Tnsnames.ora of the Primary DB :
[oracle@arcdb01 ~]$ export TNS_ADMIN=$ORACLE_HOME/network/admin
[oracle@arcdb01 ~]$ cat /u01/app/oracle/oracle/product/10.2.0/ARCDB01/network/admin/tnsnames.ora
X06.D15.LAN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = X06.D15.LAN)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ARCDB01)
(SERVER = DEDICATED)
)
)
ARCDB01.ES.EGWN.LAN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ARCDB01.ES.EGWN.LAN)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ARCDB01)
(SERVER = DEDICATED)
)
)
Tnsnames.ora of the Standby DB :
[oracle@x06 dbf]$ export TNS_ADMIN=$ORACLE_HOME/network/admin
[oracle@x06 dbf]$ cat $TNS_ADMIN/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/oracle/product/10.2.0/X06/oracle/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ARCDB01.ES.EGWN.LAN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ARCDB01.ES.EGWN.LAN)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ARCDB01)
(SERVER = DEDICATED)
)
)
X06.D15.LAN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = X06.D15.LAN)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ARCDB01)
(SERVER = DEDICATED)
)
)
[oracle@x06 dbf]$
Step 2 : check Listeners status
TNSPING of the STANDBY DB from the PRIMARY DB :
[oracle@arcdb01 ~]$ tnsping x06.d15.lan
TNS Ping Utility for Linux: Version 10.2.0.3.0 – Production on 26-MAR-2010 15:33:12
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/oracle/product/10.2.0/ARCDB01/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = X06.D15.LAN)(PORT = 1521))) (CONNECT_DATA = (SID = ARCDB01) (SERVER = DEDICATED)))
OK (0 msec)
[oracle@arcdb01 ~]$
TNSPING Of the Primary DB from the Standby DB :
[oracle@x06 bdump]$ tnsping ARCDB01.ES.EGWN.LAN
TNS Ping Utility for Linux: Version 10.2.0.3.0 – Production on 26-MAR-2010 15:34:05
Copyright (c) 1997, 2006, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/oracle/product/10.2.0/X06/oracle/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ARCDB01.ES.EGWN.LAN)(PORT = 1521))) (CONNECT_DATA = (SID = ARCDB01) (SERVER = DEDICATED)))
OK (20 msec)
[oracle@x06 bdump]$
Step 3 : Enable archiving and force logging
As a dataguard is dependent on redo to maintain the standby, we must assure that the primary database is in archivelog mode. To place the primary into archivelog, perform the following steps :
[oracle@arcdb01 ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.3.0 – Production on Mon Mar 22 20:26:19 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> connect sys as sysdba
Enter password:
Connected.
SQL>
SQL> show parameter spfile
NAME TYPE VALUE
———————————— ———– ——————————
spfile string /u01/app/oracle/oracle/product
/10.2.0/ARCDB01/dbs/spfileARCD
B01.ora
SQL> select force_logging from v$database
2 ;
FOR
—
NO
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/flash_recovery_area/ARCDB01/archivelog/
Oldest online log sequence 1418
Next log sequence to archive 1420
Current log sequence 1420
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 2021280 bytes
Variable Size 117442656 bytes
Database Buffers 293601280 bytes
Redo Buffers 6365184 bytes
Database mounted.
SQL> alter database archivelog ;
Database altered.
SQL> alter database force logging;
Database altered.
SQL> alter database open;
Database altered.
SQL>
SQL>
SQL> select log_mode, force_logging from v$database;
LOG_MODE FOR
———— —
ARCHIVELOG YES
Step 4 : Create a password file
Due to new log transport security and authentification features, it is madatory that every database in a Dataguard configuration utilize a password file. In addition, the password for the sys user must be identical on every system for log transport services to function, If the primary DB does not currently have a password file, create one with the following steps :
[oracle@arcdb01 ~]$ orapwd file=/u00/oracle/product/10.2.0/db_1/dbs/orapwarcdb01 password=orawiss entries=5 force=y
Once the password file is created, you must set the following parameter in the spfile while the database is in nomount state :
alter system set remote_login_passwordfile=exclusive scope=spfile;
Tansfer the password file to the standby DB :
[oracle@x06 dbf]$ cd /u01/app/oracle/oracle/product/10.2.0/X06/oracle/dbs/
[oracle@x06 dbs]$ ls
hc_X06.dat initdw.ora init.ora lkSTANDBY lkX06 orapwarcdb01 orapwX06 spfileX06.ora
[oracle@x06 dbs]$
Step 5 : Configure the primary initilization paraneters
We must configure the parameters to control log transport services and log apply services so that the database will operate in either role with no parameter modification. While the database is mounted on a primry controlfile , the standby parameters are not read and are not into effect, so they will not affect the operation of the database while in the primary role.
The parameters shown here are in bold, to be placed into a primary standby.ora pfile :
[oracle@arcdb01 ~]$ vi /home/oracle/ADVDB/standby.ora
ARCDB01.__db_cache_size=297795584
ARCDB01.__java_pool_size=4194304
ARCDB01.__large_pool_size=4194304
ARCDB01.__shared_pool_size=100663296
ARCDB01.__streams_pool_size=4194304
*.audit_file_dest=’/u01/app/oracle/admin/ARCDB01/adump’
*.background_dump_dest=’/u01/app/oracle/admin/ARCDB01/bdump’
*.compatible=’10.2.0.1.0'
*.control_file_record_keep_time=8
*.control_files=’/u01/app/oracle/oradata/ARCDB01/control01.ctl’,'/u01/app/oracle/oradata/ARCDB01/control02.ctl’,'/u01/app/oracle/oradata/ARCDB01/control03.ctl’
*.core_dump_dest=’/u01/app/oracle/admin/ARCDB01/cdump’
*.db_block_checking=’TRUE’
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’ARCDB01'
*.db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.db_unique_name=’PRIMARY’
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=ARCDB01XDB)’
*.FAL_Client=’ARCDB01.ES.EGWN.LAN’
*.FAL_Server=’X06.D15.LAN’
*.job_queue_processes=10
*.LOG_ARCHIVE_CONFIG=’DG_CONFIG=(PRIMARY,STANDBY)’
*.log_archive_dest_1=’location=/u01/app/oracle/flash_recovery_area/ARCDB01/archivelog/’
*.log_archive_dest_2=’Service=X06.D15.LAN LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=STANDBY’
*.log_archive_dest_state_1=’ENABLE’
*.log_archive_dest_state_2=’DEFER’
*.log_archive_format=’%s_arc_ln%r_db%d_%t.arc’
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=200
*.remote_login_passwordfile=’EXCLUSIVE’
*.SERVICE_NAMES=’PRIMARY’
*.sessions=205
*.sga_max_size=419430400
*.sga_target=419430400
*.Standby_File_Management=’AUTO’
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1'
*.user_dump_dest=’/u01/app/oracle/admin/ARCDB01/udump’
*.utl_file_dir=’/home/oracle/my_logminer’
Step 6 : Create a backup of the primary database
A physical standby can be created using either a hot or cold backup as long as all the necessary archivelogs are available to bring the database to be a consistent state. You can simply use RMAN to backup the primary database.
RMAN> backup database plus archivelog;
Step 7 : Create the Primary spfile and Standby controlfile
First, restard the primary DB using the pfile created in step 5 and then, with the primary DB in either a mount or open state, create a standby controlfile with the following syntaxes :
[oracle@arcdb01 ~]$ vi /home/oracle/ADVDB/standby.ora
[oracle@arcdb01 ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.3.0 – Production on Sun Mar 28 14:53:18 2010
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
SQL> connect sys as sysdba
Enter password:
Connected to an idle instance.
SQL> create spfile from pfile=’/home/oracle/ADVDB/standby.ora’;
File created.
SQL> startup
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 2073248 bytes
Variable Size 113249632 bytes
Database Buffers 297795584 bytes
Redo Buffers 6311936 bytes
Database mounted.
Database opened.
SQL> alter database create standby controlfile as ‘/home/oracle/ADVDB/standby_x06.ctl’;
Database altered.
SQL>
SQL> create pfile=’/home/oracle/ADVDB/standby.ora’ from spfile;
File created.
SQL>
As you can see in te last command, we create a standby pfile from the spfile, jut to be sure to have the correct pfile when this will be transferred to the standby Db, modified and the used as a pfile of the standby db.
Step 7 : Create the Standby spfile
Remember, at the previous step, we have created the pfile, this should be transferred to the standby host using for example scp or sftp commands.
Now, we should modify the pfile of the standby DB. below, are parameters that needed to be modified in our configuration.
ARCDB01.__java_pool_size=4194304
ARCDB01.__large_pool_size=4194304
ARCDB01.__shared_pool_size=100663296
ARCDB01.__streams_pool_size=4194304
*.audit_file_dest=’/u01/app/oracle/admin/ARCDB01/adump’
*.background_dump_dest=’/u01/app/oracle/admin/ARCDB01/bdump’
*.compatible=’10.2.0.1.0'
*.control_file_record_keep_time=8
*.control_files=’/home/oracle/dbf/standby_x06.ctl’
*.db_file_name_convert=’/u01/app/oracle/oradata/ARCDB01',’/home/oracle/dbf’,'/home/oracle/oradata’,'/home/oracle/dbf’
*.log_file_name_convert=’/u01/app/oracle/oradata/ARCDB01',’/home/oracle/dbf’,'/home/oracle/oradata’,'/home/oracle/dbf’
*.core_dump_dest=’/u01/app/oracle/admin/ARCDB01/cdump’
*.db_block_checking=’TRUE’
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’ARCDB01'
*.db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.db_unique_name=’STANDBY’
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=ARCDB01XDB)’
*.FAL_Server=’ARCDB01.ES.EGWN.LAN’
*.FAL_client=’X06.D15.LAN’
*.job_queue_processes=10
*.LOG_ARCHIVE_CONFIG=’DG_CONFIG=(PRIMARY,STANDBY)’
*.log_archive_dest_1=’location=/u01/app/oracle/flash_recovery_area/ARCDB01/archivelog/’
*.log_archive_dest_2=’Service=ARCDB01.ES.EGWN.LAN
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=PRIMARY’
*.log_archive_dest_state_1=’ENABLE’
*.log_archive_dest_state_2=’ENABLE’
*.log_archive_format=’%s_arc_ln%r_db%d_%t.arc’
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=200
*.remote_login_passwordfile=’EXCLUSIVE’
*.SERVICE_NAMES=’PRIMARY’
*.sessions=205
*.sga_max_size=419430400
*.sga_target=419430400
*.Standby_File_Management=’AUTO’
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1'
*.user_dump_dest=’/u01/app/oracle/admin/ARCDB01/udump’
*.utl_file_dir=’/home/oracle/my_logminer’
Please not that other parameters, such as dump destinations, may need to be modified depending on your environment.
Step 8 : Transfer files to the standby host
Using an operating system utility, transfer the files of the primary DB to the standby DB including :
- The controlfile generated previously (standby_x06.ctl)
- The Standby.ora pfile modified in the previous step
- All primary DB datafiles, you can use the steps below :
from the Primary DB, connect as a DBA :
SQL> SELECT *
FROM Dba_Data_Files ;
FILE_NAME
/u01/app/oracle/oradata/ARCDB01/users01.dbf
/u01/app/oracle/oradata/ARCDB01/sysaux01.dbf
/u01/app/oracle/oradata/ARCDB01/undotbs01.dbf
/u01/app/oracle/oradata/ARCDB01/system01.dbf
/u01/app/oracle/oradata/ARCDB01/rep_for_rman_devexen15.dbf
/u01/app/oracle/oradata/ARCDB01/REP_FOR_BACKRECK_TEST_01.dbf
/u01/app/oracle/oradata/ARCDB01/indx_01.dbf
SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
from the standby DB :
Using username “oracle”.
Authenticating with public key “imported-openssh-key”
Red Hat Enterprise Linux Server release 5.4 – Linux 2.6.18-164.11.1.el5xen
Four 2.83GHz Intel Pentium Xeon cpus with 4GB RAM
-> x06.d15.lan get *.dbf
Fetching /u01/app/oracle/oradata/ARCDB01/REP_FOR_BACKRECK_TEST_01.dbf to REP_FOR _BACKRECK_TEST_01.dbf
/u01/app/oracle/oradata/ARCDB01/REP_FOR_BACKRECK_TEST_01.dbf 100% 3315MB 24.0MB/s 02:18
Fetching /u01/app/oracle/oradata/ARCDB01/cp_indx_01.dbf to cp_indx_01.dbf
/u01/app/oracle/oradata/ARCDB01/cp_indx_01.dbf 100% 600MB 26.1MB/s 00:23
Fetching /u01/app/oracle/oradata/ARCDB01/indx_01.dbf to indx_01.dbf
/u01/app/oracle/oradata/ARCDB01/indx_01.dbf 100% 600MB 26.1MB/s 00:23
Fetching /u01/app/oracle/oradata/ARCDB01/rep_for_rman_devexen15.dbf to rep_for_rman_devexen15.dbf
/u01/app/oracle/oradata/ARCDB01/rep_for_rman_devexen15.dbf 100% 50MB 25.0MB/s 00:02
Fetching /u01/app/oracle/oradata/ARCDB01/sysaux01.dbf to sysaux01.dbf
/u01/app/oracle/oradata/ARCDB01/sysaux01.dbf 100% 530MB 21.2MB/s 00:25
Fetching /u01/app/oracle/oradata/ARCDB01/system01.dbf to system01.dbf
/u01/app/oracle/oradata/ARCDB01/system01.dbf 100% 520MB 27.4MB/s 00:19
Fetching /u01/app/oracle/oradata/ARCDB01/temp01.dbf to temp01.dbf
/u01/app/oracle/oradata/ARCDB01/temp01.dbf 100% 164MB 23.4MB/s 00:07
Fetching /u01/app/oracle/oradata/ARCDB01/undotbs01.dbf to undotbs01.dbf
/u01/app/oracle/oradata/ARCDB01/undotbs01.dbf 100% 655MB 26.2MB/s 00:25
Fetching /u01/app/oracle/oradata/ARCDB01/users01.dbf to users01.dbf
/u01/app/oracle/oradata/ARCDB01/users01.dbf 100% 5128KB 5.0MB/s 00:00
sftp>
Step 9 : Create an spfile for the standby instance
From the standby DB :
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> create spfile from pfile=’/home/oracle/dbf/standby.ora’;
File created.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 2073248 bytes
Variable Size 134221152 bytes
Database Buffers 276824064 bytes
Redo Buffers 6311936 bytes
SQL> alter database mount standby database;
Database altered.
SQL>
Step 10 : Begin shipping redo to the standby database
if you remember, earlier we deferred log_archive_dest_2 on the primary until we had the standby mounted. Now, it is time to enable that destination and begin shipping redo to the standby.
On the primary, enter the following command :
alter system set log_archive_dest_state_2=enable scope=both;
Sometimes it happens that you face the problem of Archive Gaps where by a range of archived redo log files is created. Archive gaps are created whenever the next archived redo log file generated by the primary database is not applied to the standby database. It is usually recommended to increase the LOG_ARCHIVE_MAX_PROCESSES parameter in order to resolve archive gaps by controlling the number of archive processes the instance uses.
I recommand you to increase your archive max processes parameter at your both primary and standby db :
alter system set log_archive_max_processes=4 scope=both;
From the primary DB, Check the sequence # and the archiving mode by executing following command :
SQL> Archive Log List
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/flash_recovery_area/ARCDB01/archivelog/
Oldest online log sequence 1592
Next log sequence to archive 1594
Current log sequence 1594
Next, peform a log switch on the primary and verify that the transmission of the log was successful :
SQL> alter system switch logfile;
System altered.
SQL> SELECT * FROM v$archive_dest where dest_id=2 ;
DEST_ID 2
DEST_NAME LOG_ARCHIVE_DEST_2
STATUS VALID
BINDING OPTIONAL
NAME_SPACE SYSTEM
TARGET STANDBY
ARCHIVER LGWR
SCHEDULE ACTIVE
DESTINATION X06.D15.LAN
LOG_SEQUENCE 1599
REOPEN_SECS 300
DELAY_MINS 0
MAX_CONNECTIONS 1
NET_TIMEOUT 180
PROCESS LGWR
REGISTER YES
FAIL_DATE
FAIL_SEQUENCE 0
FAIL_BLOCK 0
FAILURE_COUNT 0
MAX_FAILURE 0
ERROR
ALTERNATE NONE
DEPENDENCY NONE
REMOTE_TEMPLATE NONE
QUOTA_SIZE 0
QUOTA_USED 0
MOUNTID 0
TRANSMIT_MODE ASYNCHRONOUS
ASYNC_BLOCKS 61440
AFFIRM NO
TYPE PUBLIC
VALID_NOW YES
VALID_TYPE ONLINE_LOGFILE
VALID_ROLE PRIMARY_ROLE
DB_UNIQUE_NAME STANDBY
VERIFY NO
if the transmission was successful, the status of the destination should be valid. If the status is invalid, investigate the error listed in the error column to correct any issues.
Now, lets make a real test on our dataguard system :
From the primary DB :
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Connected as wissem
SQL> create table test (teste number);
Table created
SQL> insert into test values (1);
1 row inserted
SQL> commit;
Commit complete
SQL>
From the Standby DB :
SQL> select * from dba_users d where d.username = ‘WISSEM’;
USERNAME USER_ID PASSWORD
—————————— ———- ——————————
ACCOUNT_STATUS LOCK_DATE EXPIRY_DA
——————————– ——— ———
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED
—————————— —————————— ———
PROFILE INITIAL_RSRC_CONSUMER_GROUP
—————————— ——————————
EXTERNAL_NAME
——————————————————————————–
WISSEM 61 4531384AFBFF9B98
OPEN
USERS TEMP 21-AUG-08
USERNAME USER_ID PASSWORD
—————————— ———- ——————————
ACCOUNT_STATUS LOCK_DATE EXPIRY_DA
——————————– ——— ———
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED
—————————— —————————— ———
PROFILE INITIAL_RSRC_CONSUMER_GROUP
—————————— ——————————
EXTERNAL_NAME
——————————————————————————–
DEFAULT DEFAULT_CONSUMER_GROUP
SQL> select * from wissem.test;
select * from wissem.test
*
ERROR at line 1:
ORA-00942: table or view does not exist
BACK to the primary DB and perform a log switch :
SQL> alter system switch logfile;
System altered.
SQL> Archive Log List
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/flash_recovery_area/ARCDB01/archivelog/
Oldest online log sequence 1598
Next log sequence to archive 1600
Current log sequence 1600
SQL>exit
Check Log list from the STANDBY DB :
SQL> alter database recover managed standby database disconnect from session
2 ;
Database altered.
SQL> Archive Log List
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/flash_recovery_area/ARCDB01/archivelog/
Oldest online log sequence 1598
Next log sequence to archive 0
Current log sequence 1600
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> select * from wissem.test
2 ;
TESTE
———-
1
SQL>
Now, we can see the results of our new table from the standby DB.
Happy dataguard!
Scridb filter
No comments:
Post a Comment