Maximum Performance data guard
The steps for creating a maximum performance physical standby
database are:
1. Ensure your primary database is in archive log mode.
To make the primary database in archive mode
C:\Documents and Settings\Administrator>sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Thu Aug 4 13:17:54 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn sys@primary as sysdba
Enter password:
Connected.
>create pfile=’c:\test.ora’ from spfile
goto c: and open test.ora and add the following line
*.log_archive_dest_1 = ‘c:\archive’
*.log_archive_start = TRUE
>shutdown immediate
>create spfile from pfile=’c:\test.ora’
>startup
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination C:\archive
Oldest online log sequence 21
Next log sequence to archive 23
Current log sequence 23
SQL>
2. Take a backup of your primary database and copy it to the standby server.
identify the datafile
SQL> select name from v$datafile;
NAME
D:\ORA9IDB\ORADATA\ORCL\SYSTEM01.DBF
D:\ORA9IDB\ORADATA\ORCL\UNDOTBS01.DBF
D:\ORA9IDB\ORADATA\ORCL\CWMLITE01.DBF
D:\ORA9IDB\ORADATA\ORCL\DRSYS01.DBF
D:\ORA9IDB\ORADATA\ORCL\EXAMPLE01.DBF
D:\ORA9IDB\ORADATA\ORCL\INDX01.DBF
D:\ORA9IDB\ORADATA\ORCL\ODM01.DBF
D:\ORA9IDB\ORADATA\ORCL\TOOLS01.DBF
D:\ORA9IDB\ORADATA\ORCL\USERS01.DBF
D:\ORA9IDB\ORADATA\ORCL\XDB01.DBF
D:\ORA9IDB\ORADATA\ORCL\ICAI.DBF
11 rows selected.
shutdown your primary and secondary database
>shutdown immediate
copy the files over the network or any other possible means
3. On your primary database create a standby controlfile:
>alter database create standby controlfile as ‘c:\standby.ctl’;
4. Copy the binary standby controlfile from your primary server to your standby server into the location where the control files are present.
5 Configure your tnsnames.ora file on both the primary and the standby so that each database can establish an Oracle Net connection to each other:
standby =
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)
(PORT=1521) (HOST=10.0.0.14))
(CONNECT_DATA=(SID=orcl)))
primary =
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)
(PORT=1521) (HOST=10.0.0.17))
(CONNECT_DATA=(SID=orcl)))
6 Configure your listener.ora file on both the primary and standby database so that each database has a listener.
7. Configure your primary init.ora (or spfile) log_archive_dest_2 to point at your standby database:
LOG_ARCHIVE_DEST_2=’SERVICE= standby optional reopen=120’
8. Copy the primary standby database init.ora (or spfile) to the standby server and enable automatic archive gap management in your standby
init.ora file:
*.FAL_CLIENT=standby
*.FAL_SERVER=primary
9. Ensure your standby init.ora (or spfile) CONTROL_FILES parameter points at the standby controlfile that you created.
*.control_files='d:\ora9idb\oradata\orcl\standby.ctl'
10. Start your standby database and enable managed recovery:
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect;
Verify that archive redo logs are being copied and applied:
• Switch log files a few times on your primary database
• Check the alert.log file for application of the logs on your standby database
• Your maximum performance protection mode can be verified on both the standby and primary database by selecting the PROTECTION_MODE column from the V$DATABASE view.
OPENING THE STANDBY DATABASE IN READ ONLY MODE AND ENABLING IT IN MANAGED RECOVERY AGAIN
C:\Documents and Settings\Administrator>sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Mon Aug 1
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn sys@standby as sysdba
Enter password:
Connected.
SQL> alter database open read only
2 ;
alter database open read only
*
ERROR at line 1:
ORA-01154: database busy. Open, close, mount, and dismount not allowed now
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open read only;
Database altered.
SQL> conn icai/icai
ERROR:
ORA-12560: TNS:protocol adapter error
Warning: You are no longer connected to ORACLE.
SQL> conn icai/icai@standby
Connected.
SQL> select * from cat
2 ;
TABLE_NAME TABLE_TYPE
------------------------------ -----------
CT_CPA_DOCUMENT TABLE
ICAI_BANK_ALLOCATION05 TABLE
ICAI_BULLETIN_CATEGORY TABLE
ICAI_BULLETIN_MESSAGE TABLE
ICAI_BULLETIN_SUB_CATEGORY TABLE
ICAI_COMPANY_REG TABLE
ICAI_COMP_VACANCY TABLE
ICAI_COMP_VACANCY_BAK TABLE
ICAI_CONTENT_ADMIN TABLE
ICAI_CONTENT_CATEGORY TABLE
ICAI_CONTENT_LOG TABLE
TABLE_NAME TABLE_TYPE
------------------------------ -----------
ICAI_CONTENT_URL TABLE
ICAI_DISCUSSION_FORUM_DETAIL TABLE
ICAI_DISCUSSION_FORUM_MASTER TABLE
ICAI_GENERAL_ADMIN TABLE
ICAI_ISA_APPLICATION TABLE
ICAI_MEMBER_INFO TABLE
ICAI_ONLINE_TEST TABLE
ICAI_PAYMENT_DETAILS TABLE
ICAI_POLL_QUESTIONS TABLE
ICAI_POLL_RESULT TABLE
ICAI_POLL_TOPIC TABLE
TABLE_NAME TABLE_TYPE
------------------------------ -----------
ICAI_PYMNT_GTY TABLE
ICAI_REG TABLE
ICAI_RESUME TABLE
ICAI_RESUME_BK TABLE
ICAI_STUDENT_MEMBER TABLE
ICAI_USER_REGISTER TABLE
RAMESH TABLE
SEARCH_ENGINE_KEYWORDS TABLE
30 rows selected.
SQL>
CONNECT TO THE PRIMARY DATABASE
SQL>
Connected.
SQL> SELECT * FROM CAT;
TABLE_NAME TABLE_TYPE
------------------------------ -----------
CT_CPA_DOCUMENT TABLE
ICAI_BANK_ALLOCATION05 TABLE
ICAI_BULLETIN_CATEGORY TABLE
ICAI_BULLETIN_MESSAGE TABLE
ICAI_BULLETIN_SUB_CATEGORY TABLE
ICAI_COMPANY_REG TABLE
ICAI_COMP_VACANCY TABLE
ICAI_COMP_VACANCY_BAK TABLE
ICAI_CONTENT_ADMIN TABLE
ICAI_CONTENT_CATEGORY TABLE
ICAI_CONTENT_LOG TABLE
ICAI_CONTENT_URL TABLE
ICAI_DISCUSSION_FORUM_MASTER TABLE
ICAI_GENERAL_ADMIN TABLE
ICAI_ISA_APPLICATION TABLE
ICAI_MEMBER_INFO TABLE
ICAI_ONLINE_TEST TABLE
ICAI_PAYMENT_DETAILS TABLE
ICAI_POLL_QUESTIONS TABLE
ICAI_POLL_RESULT TABLE
ICAI_POLL_TOPIC TABLE
TABLE_NAME TABLE_TYPE
------------------------------ -----------
ICAI_PYMNT_GTY TABLE
ICAI_REG TABLE
ICAI_RESUME TABLE
ICAI_RESUME_BK TABLE
ICAI_STUDENT_MEMBER TABLE
ICAI_USER_REGISTER TABLE
RAMESH TABLE
SEARCH_ENGINE_KEYWORDS TABLE
ENABLING LOGGING IN STANDBY AGAIN
SQL> alter database open read only;
alter database open read only
ERROR at line 1:
ORA-01154: database busy. Open, close, mount, and dismount not allowed now
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
12
13
11
14
15
16
17
18
19
9 rows selected.
CONNECT TO THE PRIMARY DATABASE
SQL>
SQL> conn sys@primary as sysdba
Enter password:
Connected.
SQL> select sequence#,first_time,next_time from v$archived_log;
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
6
7
7
8
8
9
9
10
10
12
12
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
13
13
11
11
14
14
15
15
16
16
17
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
17
18
18
19
19
SQL>
No comments:
Post a Comment