Data Guard Active Standby Database Setup using RMAN Duplicate on Windows Server Introduction ................................................................................................................................................... 2 Configuration Steps ....................................................................................................................................... 2 Network configuration .................................................................................................................................. 2 Hosts File ....................................................................................................................................................... 3 Creating "oracle" User ................................................................................................................................... 3 Assigning Full Control to "oracle" User to the Drives .................................................................................... 4 Environment Variables .................................................................................................................................. 4 Windows Firewall .......................................................................................................................................... 4 Oracle Installation ......................................................................................................................................... 5 Primary Server Setup ..................................................................................................................................... 5 Service Setup ................................................................................................................................................. 7 Create Standby Controlfile and PFILE ............................................................................................................ 8 STANDBY Server ............................................................................................................................................ 9 Change Computer Name ............................................................................................................................... 9 Network configuration .................................................................................................................................. 9 Creating directories on the Standby Server .................................................................................................. 9 Create Service .............................................................................................................................................. 10 Creation of Service using "oradim" ............................................................................................................. 10 Copy Control File and PFILE on Standby Server .......................................................................................... 11 Create Standby Redo Logs on Primary Server ............................................................................................. 12 RMAN Duplicate .......................................................................................................................................... 13 Start REDO APPLY ........................................................................................................................................ 13 Test Log Transport ....................................................................................................................................... 13 References ................................................................................................................................................... 14
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
Introduction In this set up I used : Two Oracle VMs/Boxes with Windows Server 2008 Server operating system already installed Oracle Database 11.2.0.4 11.2.0.4 will be used and Domain name is not used in this setup
Configuration Steps 1st Server Name "orasys1" (physical)
Oracle Enterprise Database (11.2.0.4) will be created on this host 3 Drives were assigned (Drives E, F and G) Drive E - Oracle Home Drive F - oradata Drive G - fast_recovery_area/recovery_area
To Change Computer Name
Login as Administrator to change Computer name to "orasys1" Navigation: From "Start" -> Computer -> System Properties -> Change Settings - click on Change Type "orasys1" in the "computer name" box In the "workgroup" box "WORKGROUP" is already selected. If it is not then select "workgroup" Restart the System
Network configuration I choose to use the default DNS Server IP of Oracle Virtual Box. See the section "VirtualBox Network Setup" from the below link, If you want to set up Networking on "Oracle Virtual Box" http://www.oracle-base.com/arti http://www.oracle-base. com/articles/12c/ cles/12c/oracle-db-12c oracle-db-12cr1-rac-install r1-rac-installation-on-oracl ation-on-oracle-linux-6-using e-linux-6-using-virtualbox.php Navigation :
From "Start" ->on "Network" -> "Network and Sharing Center" -> "Manage Network Connections" - right click on "Local Area Connections" -> uncheck "internet protocol version 6" (We configure with Internet Protocol Version 4)
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
Default Gateway - 192.168.56.1 and In the preferred DNS Server - 192.168.56.1 Click "OK" and again "OK" to exit
Hosts File
This may not be a compulsion on Windows but I want to make sure that I mention the IP and Host name in this Hosts file. To amend host name in the host file we use "notepad" at the prompt to open the Hosts File. C:\> notepad \windows\system32\driv \window s\system32\drivers\etc\hosts ers\etc\hosts We write the IP Address and Host Name (we are not using any domain in this setup and we will mention only Host Name and it works). In this way " 192.168.56.10 192.168.56.101 1 orasys1 ". I usually type the IP and press "TAB" "TAB" Key once and then write the hostname. To keep things easier I typed the second Host Name and IP in the next line in advance. It was " 192.168.56.102 orasys2 ". Save the Hosts file.
Creating "oracle" User
To open "Server Manager " directly from CMD Window, type at the prompt : %systemroot%\system32\compmgmtlauncher.exe OR Navigate: open "Server manager" -> expand "Configuration" and "Local Users and Groups" and finally right click on "Users" and open o pen "New User" In the "User Name" box we write "oracle" Check only "password never expires" Click on "create" to create "oracle" User In the window "Users" we should now see the user "oracle" Double click on "oracle" User "Oracle Properties" window pops up. Click on "Member Of" (we assign the User "oracle" in the Administrator Group. It is a must.) Click on "add" and now "select groups" window should pop up Click on "advanced " and again on "find now" In the column "Name" we would see "Administrators". Click on "Administrators" and now click "OK" We should now see "the_host_name\administrators" in the box "enter the object names to select" Click " OK " and "Apply"
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
Assigning Full Control Control to "oracle" User to the Drives Drives Assigning Full Control to "oracle" User to the Drives E, F and G :
Click "start" -> "computer" -> right click on Drive "E" and select "properties" Click on "security" "security" and on "edit" "edit" Click on "add" Write "oracle" in the box "enter the object names to select " and click on "check names". "the_host_name\oracle" should be now seen Click on "ok" Click on the User "oracle" and now we click or put a check mark to "full control" under "allow" Click "ok". This way the User "oracle" has full control over the Drive Verify: In the "group or usernames" find the user "oracle" and click on it. We will see a "right check mark" next to "full control" We repeat the same step for the Drives "F" and "G"
Environment Variables
Log in as "oracle" User Open the "System Properties" Window (Navigation: Start -> Control Panel -> System and Security -> System -> Change Settings) We do the following : Click on the Advanced tab and then on "Environment Variables" Variables" button Edit both the "TEMP" and "TMP" Environment Variables to be "%WINDIR%\temp", which is "C:\Windows\temp" To set ORACLE_HOME Variable: Click "New" write ORACLE_HOME in "Variable Name" and the Path of it in "Variable Value" In my case, I typed "E:\app\oracle\product\11.2.4\dbhome" in the "Variable Value" To set PATH Variable: Click "New" and type PATH in "Variable Name" and in "Variable Value" the "%ORACLE_HOME%\bin;%PATH%" Click the "OK" and then "Apply" and "OK" to exit from of the window
Windows Firewall
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
off"
Oracle Installation
Login as "oracle" User and we install Oracle Software and create Database The Oracle Software is saved on my Shared Folder of Oracle Virtual Box. So, this folder is a Network Folder Navigation: From "Start" open "network" and open the shared folder accessed from the guest system OR start -> computer -> open the folder in "network location" Double click on "set up" (Note: we use the same "sys" password for Primary and Standby) Prefer the "Desktop Class" or "Server Class" type of Installation that is suitable to you during the Installation. I prefer to test always with "Server class " installation I set Oracle SID as "db11g" and Global Database Name as "db11g.world" I completed the installation with : Drive E - Oracle Home Drive F - oradata Drive G - fast_recovery_area/recovery_area We should now make make a note or check the locations locations of oradata (that means, means, the location of of database files), admin, diag and fast_recovery_area/r fast_recovery_area/recovery_area ecovery_area (if enabled)
Primary Server Setup Note: Some steps are copied copied from the below link and modified modified as suitable for this installation http://www.oracle-base.com/articles/11g/data-guard-setup-11gr2.php Logging
Check that the primary database is in Archivelog mode SELECT log_mode FROM v$database; LOG_MODE -----------NOARCHIVELOG SQL> if it is not in Archivelog mode, we switch the primary database to Archivelog mode, it is done in this manner : SQL>shutdown immediate;
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
SQL> show parameter log_archive_dest_1; SQL> show parameter log_archive_dest_2; SQL> show parameter log_archive_format; Enabled forced logging by issuing the following command SQL > ALTER DATABASE FORCE LOGGING Initialization Parameters
Check the setting for the DB_NAME and DB_UNIQUE_NAME parameters. In this case they are both set to "DB11G" on the primary database.
SQL> show parameter db_name NAME TYPE VALUE ----------------------------------------------------------------------- ----------- -----------------------------db_name string DB11G SQL> show parameter db_unique_name NAME TYPE VALUE ----------------------------------------------------------------------- ----------- -----------------------------db_unique_name db_unique_ name string DB11G
The DB_NAME of the standby database will be the same as that of the primary, but it must have a different DB_UNIQUE_NAME value. The DB_UNIQUE_NAME values of the primary and standby database should be used in the DG_CONFIG setting of the LOG_ARCHIVE_CONFIG parameter. For this example, the standby database will have the value "DB11G_STBY". ALTER SYSTEM SET LOG_ARCHIVE_CONFIG= 'DG_CONFIG=(DB11G,DB11G_STBY)' ; Set suitable remote archive log destinations. In this case I'm using the fast recovery area for the local location, but you could specify an location explicitly explicitly if you prefer. Notice the SERVICE and the DB_UNIQUE_NAME for the remote location reference the standby location.
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
The LOG_ARCHIVE_FORMAT and LOG_ARCHIVE_MAX_PROCESSES parameters must be set to appropriate values and the REMOTE_LOGIN_PASSWORDFILE must be set to exclusive. ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE; ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30; ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE; In addition to the previous setting, it is recommended to make sure the primary is ready to switch roles to become a standby. For that to work properly we need to set the following parameters. Adjust the *_CONVERT parameters to account for your filename and path differences between the servers. ALTER SYSTEM SET FAL_SERVER=DB11G_STBY; --ALTER SYSTEM SET DB_FILE_NAME_CONVERT='DB11G_STBY','DB11G' SCOPE=SPFILE; --ALTER SYSTEM SYSTEM SET LOG_FILE_NAME_CONVERT= LOG_FILE_NAME_CONVERT='DB11G_STBY','DB11G' 'DB11G_STBY','DB11G' SCOPE=SPFILE; ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO; Remember, some of the parameters are not modifiable, so the database will need to be restarted before they take effect.
Service Setup Entries for the primary and standby databases are needed in the tnsnames.ora files on both servers. First we setup this on Primary. "C:\ notepad %ORACLE_HOME%\networ %ORACLE_HOME%\network\admin\tnsna k\admin\tnsnames.ora". mes.ora". You can create these using the Network Configuration Utility (netca) or manually. The following entries were used during this setup. DB11G =
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
(SERVICE_NAME = DB11G.WORLD) ) )
Create Standby Controlfile and PFILE Create a controlfile for the standby database by issuing the following command on the primary database. ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'E:\db11g_stby.ctl'; Create a parameter file for the standby database. CREATE PFILE='E:\initDB11G_stby.ora' FROM SPFILE; Amend the PFILE making the entries relevant for the standby database. I'm making a replica of the original server, so in my case I only had to amend the following parameters. (I just wrote the below parameters at the end of the file) *.db_unique_name='DB11G_STBY' *.fal_server='DB11G' *.log_archive_dest_2='SE *.log_arc hive_dest_2='SERVICE=db11g RVICE=db11g ASYNC DB_UNIQUE_NAME=DB11G'
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE)
*.log_archive_dest_2='SERVICE=db11g ASYNC VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G' Make a note or recheck the locations of oradata, controlfile, fast_recover_area etc. from the PFILE (initDB11G_stby.ora)
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
STANDBY Server
2nd host name orasys2 (Standby Host) Oracle Software only will be installed 3 Drives were assigned (Drives E, F and G) Drive E: is for Oracle Home, F: for "oradata" and G: for "fast_recovery_area /recovery_area".
Change Computer Name To change the Computer name to "orasys 2"
This step is found above.
Network configuratio configuration n
From "Start" -> click on "network" -> click on "network and sharing center" -> "manage network connections" -> right click on "local area connections" -> uncheck "internet protocol version 6" (We configure with Internet Protocol Version 4) Click on "internet protocol version 4 (TCP/IPv4)" and now click on the "properties" in this window and select "use the following IP Address" IP Address - 192.168.56.102 Subnet Mask - 255.255.255.0 Default Gateway - 192.168.56.1 In the preferred DNS Server - 192.168.56.1 Click "OK" and again "OK"
Creating directories on the Standby Server Creating directories on Host "orasys2" "orasys2" which is a Standby Server
We create the necessary Directories on the Standby.
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
In this way " 192.168.56.102 orasys2". I usually type the IP and press "TAB" Key once and write the Host Name. To keep things easier easier I typed " 192.168.56.101 orasys1 " in the next line which is the IP IP Address and Host Name of the 1st Host that we created already. Save the file.
Creating "oracle" User
Creating "oracle" User: This step is found above. above. Assigning Full Control to "oracle" to the Drives E, F and G
This step is found above. Environment Variables
This step is found above. Windows Firewall
This step is found above.
Create Service
We open cmd window with "run as administrator" Click "start" and in the " start search " box write "cmd". We'd see "cmd " now but do not open Right click on "cmd" and select "run as administrator" to open cmd window as Administrator. Click on "continue". Verify : On the top of the window we now see this cmd window is running as Administrator To see difference : Click "start" and in the " start search " box write "cmd" and just open it. Compare the two command windows now. We get errors when we create create an Oracle Oracle Instance opening opening a cmd window not as as Administrator
an
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
Copy Control File and PFILE on Standby Server
Open the Virtual Box Shared folder attached to this Host Copy the File "db11g_stby.ctl" to the location "F:\oradata\DB11G\" and rename it (right click on the file and click on "rename") "rename") to "control01.ctl" "control01.ctl" Copy the file "db11g_stby.crl" to the location "G:\fast_recovery_area "G:\fast_recovery_area or recovery_area\DB11G recovery_area\DB11G\\ " and rename (right click on the the file and and click on "rename") "rename") it to " control02.ct control02.ctll " Copy the file " intDB11G_stby.ora " and save in the location "%ORACLE_HOME%\database\"
Listener.ora
When using active duplicate, the standby server requires static listener configuration in a "listener.ora" file. In this case I used the following configuration. configuration. "C:\ notepad %ORACLE_HOME%\network\admin\l %ORACLE_HOME%\ network\admin\listener.ora" istener.ora" SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = DB11G.WORLD) (ORACLE_HOME = E:\app\oracle\product\11.2.4\dbhome) (SID_NAME = DB11G) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = orasys2)(PORT = 1521)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) )
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = orasys1)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = DB11G.WORLD) ) ) DB11G_STBY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = orasys2)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = DB11G.WORLD) ) )
Create Standby Redo Logs on Primary Server The DUPLICATE command automatically creates the standby redo logs on the standby. To make sure the primary database is configured for switchover, we must create the standby redo logs on the primary server. In my case it is
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
RMAN Duplicate Now we issue the following DUPLICATE command
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER SPFILE SET db_unique_name='DB11G_STBY' COMMENT 'Is standby' SET LOG_ARCHIVE_DEST_2='SERVICE=db11g ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G' SET FAL_SERVER='DB11G' COMMENT 'Is primary' NOFILENAMECHECK;
Start REDO APPLY Start the Redo Apply process on Standby Server using
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Test Log Transport
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
The world’s largest digital library
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
References The below articles helped me out to play Oracle on Windows Server: http://www.oracle-base.com/articles/11g/data-guard-setup-11gr2.php Oradim Utility on Windows: http://www.dba-oracle.com/tips_oradim_utility.htm And also the below links were helpful too: http://nadeemmohammed.wordpress.com/2012/05/24/how-to-create-oracle-11g-database-manuallyon-windows/ http://neeraj-dba.blogspot.in/2011/10/active-standby-database-in-oracle-11g.html