1. INTRODUCTION
Data Guard started with a very simple database function designed to keep a synchronized database copy with a lot of manual work.
It has now become a tool with automation, recovery and failover features.
Oracle Data Guard remains the only solution available today with the best RPO and RTO benefits for businesses.
This document describes the steps required to prepare and configure an Oracle 12c Data Guard on Windows server 2012, providing the necessary redundancy to our database.
This lab journey through Oracle 12c Data guard will be split in 3 blog posts. Each post will represent a major element of the deployment as follows:
1. Part I : Implementation
2. Part II : Administration (switchover and failover)
3. Part III : Explore Features (Fast Start failover)
4. Troubleshooting : Resolve Log gap Error: ORA-16724
What will be covered in Part I ?
In this post we will focus on the following steps:
2. Considerations
2.1 Data Guard Configuration Types
There are two criteria to differentiate the common configurations of the Data Guard databases: The first concerns the type of functionality implemented while the other concerns the redo synchronization mode between the nodes.
2.1.1 Types of standby databases
Fig1- Physical and logical Standby Databases
a) Physical standby database (Redo Apply)
Identical to the primary database at the block /disk level, the physical standby database is updated by MRP (Media Recovery Process).
It can be opened as read-only for queries only if the redo application is stopped beforehand.
b) Logical standby database (SQL Apply)
This standby database is updated by transforming the redo data received from the primary Database into SQL transactions then executing them.
This provides the advantage of being open for users to view or modify (as well as for reporting) at the same time as the changes are applied. One can even create elements specific to this standby database (indexes, tables, diagrams …).
Note: The only difference between the two Standby databases is the way to apply the redo at the reception of the redo from the primary Database.
2.1.2 Synchronization Mode of the transactions
a) Synchronous Redo Transport
Fig2- Synchronous Redo Log transport
Requires the primary database to wait for confirmation from the standby database that the shipped LGWR redo data have been received and written to disk (remote redo file) before applying the ‘commit’. This mode guarantees zero data loss if the primary database fails unexpectedly.
b) Asynchronous Redo Transport
Avoids any impact on the primary database performance by committing as early as the shipped redo data from the redo buffer or redo logs are written to the local Standby Redo Log (SRL) , without waiting for the commit feedback from the standby database. This ensures maximum performance, but does not guarantee that everything that is committed is copied at a given time.
It also allows to update the data in real time (either in Redo Apply or SQL Apply mode) .
2.1.3 New in Oracle 12c
§ Fast Sync : Improves the performance of a synchronous configuration because the standby database only needs to receive the redo change in memory to validate the commit on the primary side. This Avoids the wait for the redo writing on the primary redo log files (disk) . The round trips between primary and standby database are thus reduced and the zero data loss is ensured.
§ Far sync : Enables the zero data loss failover to a standby database even if it is thousands of miles away, without affecting the primary database or adding hardware complexity /deployment cost.
2.2 Data Guard Concepts Go to Top
a) Switchover
This action makes it possible to switch the roles between the primary database and the standby database and to change the direction of log shipping (redo).
b) Failover
This operation opens the access of a standby database to users in read / write mode, when the primary database becomes
unreachable (failure).
c) Data Guard Broker
his agent automates and centralizes the creation/maintenance and monitoring of Data Guard databases. It checks that everything is functional and corrects what’s needed if actions are to be performed for a return to normal (in theory).
» Background processes:
FAL (Fetch Archive Log) : Sends archived redo log files to standby destinations (gap resolution and synchronization)
LNS (log network server) : Is responsible of redo transport (synch / asynchronous)
MRP (Managed Recovery process) : Applies archived redo log files to the physical standby database.
RFS (Remote file server) : Receives redo data over the network from the Primary’s LGWR process and writes the redo data
to the standby redo log files.
LSP (Logical standby process) : Applies the archived redo log files to the logical standby database, using SQL interfaces
(convert redo data to SQL transactions).
Note: The LNS process is replaced in In 12c by NSSync for SYNC shipping and by TTnn for ASYNC shipping where the parallelisms is introduced.
2.3 Protection Modes Go to Top
3. PREPARATION Go to Top
3.1 Topology
Fig6- Topology of our lab test
3.2 Hardware and Software Prerequisites Go to Top
This solution has been deployed and tested according to the following hardware and software configuration:
Fig7– Details about the lab hosts configuration
- The Oracle 12c (grid, database) installation files are available on the following Oracle link: https://edelivery.oracle.com
The following operational requirements must be observed for any DATA GUARD installation:
+ All databases must be from the same Oracle Edition (Enterprise)
+ The Oracle Software version must be the same
+
Same type of operating system, but not necessarily the same version
+ Same hardware and OS architecture (32-bit to 32-bit, Sun to Sun, etc.)
+
User accounts must have sysdba privilege in each database
3.2.1 Storage Requirement
a) Installation prerequisites
Let’s look at the Installation requirement in terms of storage and RAM
Fig8– Storage and Memory requirement for the installation
b) ASM discs creation
- knowing the hosts are virtual machines (Vmware), the ideal storage distribution will be as follows
|
ASM disc Group | SCSI Virtual controller (VSCSI) | Discs | Discs size | Total Size |
| +FRA | 1:0 | 3x | 15GB |
45GB |
| +DATA | 2:0 | 3x |
10 GB |
30GB |
| +REDO | 3:0 | 2x |
1 GB |
2GB |
Vmware Note: In the case where the VDMK disks are formatted (NTFS) during the delivery of the virtual machine, it is necessary to proceed with the space layout reconfiguration through 2 tools provided for this purpose, Diskpart and asmtool.
Diskpart
Is a Windows command-line utility that allows the management of disks / partitions (creation, deletion, formatting)Asmtool
Is an Oracle command-line utility that (stamps) disks with persistent/meaningful names to facilitate using those disks with Oracle Automatic Storage Management.
Syntax : $ asmtool[option] [device] [label]
|
Command Description |
| -add Adds or changes stamps. |
| -addprefix Adds or changes stamps using a common prefix to generate stamps automatically. |
| -list Lists available disks. The stamp, windows device name, and disk size are shown. |
| -delete Removes existing stamps from disks. |
- Here is the storage distribution on the hosting machine:
Fig9- Storage devices of the provisioned VM
- We will split the storage according to the bellow layout (2 x discs of 15GB and 4 discs of 10GB)
Disk 0 : 1x 15Go + 2x 10Go + 1Go
Disk 1 : 2x 10Go + 1x 15Go + 1Go
PS C:UsersAdministrator> diskpart
DISKPART> list disk
Disk ### Status Size Free Dyn Gpt
——– ————- ——- ——- — —
Disk 0 Online 80 GB 0 B
Disk 1 Online 40 GB 1024 KB
Disk 2 Online 5120 MB 1024 KB
DISKPART> list volume
Volume ### Ltr Label Fs Type Size Status Info
———- — ———– —– ———- ——- ——— ——
Volume 0 D DVD-ROM 0 B No Media
Volume 1 System Rese NTFS Partition 350 MB Healthy System
Volume 2 C OS NTFS Partition 79 GB Healthy Boot
Volume 3 E DATA NTFS Partition 39 GB Healthy
Volume 4 F REDO NTFS Partition 5117 MB Healthy
- Create the partitions
DISKPART> select disk 0 DISKPART> create partition extended (1st) DISKPART> create partition logical size=15360 DISKPART> create partition logical size=10240 DISKPART> create partition logical (10gb) --- DISKPART> select disk 1 DISKPART> create partition extended (1st) DISKPART> create partition logical size=10240 DISKPART> create partition logical size=15360 DISKPART> create partition logical size=15360 --- DISKPART> select disk 2 DISKPART> create partition extended (1st) DISKPART> create partition logical size=1024 DISKPART> create partition logical size=1024
- Verify the created partitions
DISKPART> list volume Volume ### Ltr Label Fs Type Size Status Info ---------- --- ----------- ----- ---------- ------- --------- -------- Volume 1 System Rese NTFS Partition 350 MB Healthy System Volume 2 C OS NTFS Partition 44 GB Healthy Boot Volume 3 RAW Partition 15 GB Healthy Volume 5 RAW Partition 10 GB Healthy Volume 6 RAW Partition 10 GB Healthy Volume 7 RAW Partition 1024 MB Healthy Volume 8 RAW Partition 15 GB Healthy Volume 9 RAW Partition 15 GB Healthy Volume 10 RAW Partition 10 GB Healthy Volume 11 RAW Partition 1024 MB Healthy
• ASM disk stamping using asmtool
F:Grid12102gridasmtool> .asmtool -listNTFS DeviceHarddisk0Partition1 350M NTFS DeviceHarddisk0Partition2 45714M DeviceHarddisk0Partition3 15360M -- +FRA DeviceHarddisk0Partition4 10240M -- +DATA DeviceHarddisk0Partition5 10240M -- +DATA DeviceHarddisk0Partition6 1024M -- +REDO DeviceHarddisk1Partition1 15360M -- +FRA DeviceHarddisk1Partition2 15360M -- +FRA DeviceHarddisk1Partition3 10240M -- +DATA DeviceHarddisk1Partition4 1024M -- +REDO ------------ ASM Disks CreationF:..asmtool> .asmtool -add DeviceHarddisk1Partition1 FRA1 F:..asmtool> .asmtool -add DeviceHarddisk1Partition2 FRA2 F:..asmtool> .asmtool -add DeviceHarddisk0Partition3 FRA3 F:..asmtool> .asmtool -add DeviceHarddisk0Partition4 DATA1 F:..asmtool> .asmtool -add DeviceHarddisk0Partition5 DATA2 F:..asmtool> .asmtool -add DeviceHarddisk1Partition3 DATA3 F:..asmtool> .asmtool -add DeviceHarddisk0Partition6 REDO1 F:..asmtool> .asmtool -add DeviceHarddisk1Partition4 REDO2------------ASM disks VerificationPS F:Grid12102gridasmtool> .asmtool -list NTFS DeviceHarddisk0Partition1 350M NTFS DeviceHarddisk0Partition2 45714M ORCLDISKFRA3 DeviceHarddisk0Partition3 14336M ORCLDISKDATA1 DeviceHarddisk0Partition4 10240M ORCLDISKDATA2 DeviceHarddisk0Partition5 10240M ORCLDISKREDO1 DeviceHarddisk0Partition6 1024M ORCLDISKFRA1 DeviceHarddisk1Partition1 14336M ORCLDISKFRA2 DeviceHarddisk1Partition2 14336M ORCLDISKDATA3 DeviceHarddisk1Partition3 10240M ORCLDISKREDO2 DeviceHarddisk1Partition4 1024M4. INSTALLATION Go to Top
Now that all prerequisites are gathered, we can proceed with the installation.
4.1 Primary host
4.1.1 Installation
The following variables must be defined before any installation of Oracle products in the machine:
|
Variable Value |
| Oracle BASE C:APPORACLE |
| Oracle home 1 C:APPORACLEPRODUCT11.1.0grid |
| Oracle home 2 C:APPORACLEPRODUCT11.1.0DB_1 |
A) Grid Infrastructure
First, add the oracle user
PS F:Grid12102grid> net user oracle1 xxxxxxx /add
F:Grid12102grid> net localgroup Administrators oracle /add
After downloading and extracting zip files corresponding to the grid 12c software, two installation methods are possible:
– Via a script
– GUI Installation
» Script method
Run notepad.exe to create the following installation script (the parameters must be on a single line):
PS F:grid12201gridstage> type Ginstal.bat
.setup.exe -silent selected_languages=en
oracle.install.option=HA_CONFIG
ORACLE_BASE= C:APPORACLE
ORACLE_HOME= C:APPORACLEPRODUCT11.1.0grid_1
oracle.install.IsBuiltInAccount=true
oracle.install.OracleHomeUserName=”NT AUTHORITYSYSTEM”
oracle.install.asm.diskGroup.name=DATA
oracle.install.asm.diskGroup.redundancy=EXTERNAL
oracle.install.asm.diskGroup.disks= \.ORCLDISKDATA1,\.ORCLDISKDATA2,\.ORCLDISKDATA3 oracle.install.asm.SYSASMPassword=xxxxxxxx
oracle.install.asm.monitorPassword=xxxxxxx
» GUI method
-
Just run the setup.exe file and follow the instructions
F:Grid12102grid> setupe.exe
- Create the DATA disc group using 3 disc groups of 10GB each
- Create the Oracle Base and Grid Home directories
-
Select the Windows Built-in Account as Oracle database Home owner
-
At the end of the installation you can connect to the ASM instance to create the FRA and REDO group
PS F:Grid12102grid> $env:ORACLE_SID=+ASM
PS F:Grid12102grid> sqlplus “/as sysasm”
SQL> CREATE DISKGROUP FRA EXTERNAL REDUNDANCY DISK ‘\.ORCLDISKFRA1′,’\.ORCLDISKFRA2′,’\.ORCLDISKFRA3’ ;
SQL> CREATE DISKGROUP REDO EXTERNAL REDUNDANCY DISK ‘\.ORCLDISKREDO1′,’\.ORCLDISKREDO2’ ;
B) Database software
Same process for the database software, two installation methods are possible:
– Via a script
– GUI Installation
» Script method
Start notepad.exe to create the following installation script sample (adapt as needed; the parameters must be on a single line):
PS F:DB12201databasestage> type Ginstal.bat
.setup.exe -silent -debug -promptForPassword ^
oracle.install.option=INSTALL_DB_SWONLY ^
ORACLE_HOSTNAME=WIN-OQ40SUNBJQU ^
INVENTORY_LOCATION=”C:Program FilesOracleInventory” ^
SELECTED_LANGUAGES=en ^
ORACLE_HOME=C:APPORACLEPRODUCT11.1.0db_1^
ORACLE_BASE=E:apphomeowner4 ^
oracle.install.db.InstallEdition=EE ^
oracle.install.IsBuiltInAccount=false ^
oracle.install.OracleHomeUserName=Oracle ^
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false ^
DECLINE_SECURITY_UPDATES=true ^
oracle.installer.autoupdates.option=SKIP_UPDATES
» GUI method
- After unzipping the downloaded Oracle database installation files, just run the setup.exe and follow the instructions
F:BD12102grid> setup.exe
- Select install software only then select single instance database
-
Choose the Windows built-in Account as database Oracle Home owner
- Choose the Oracle Base and DB Software location [C:APPORACLE; C:APPORACLEproduct12.1.0.2db_1]
-
The rest of the flow is pretty self explanatory. Click install when you get to the last section
C) The database instance
-
Run the DBCA wizard and follow the instructions
C:APPORACLEproduct12.2.0.1db_1bin> DBCA.exe
- Choose the SID and GLOBAL DB Name
- Select the default listener for the database (choose the grid listener)
- Choose the file storage setting : data files and FRA ASM disc groups
- Initialization parameters setting
- Check and finalize the installation
-
Click close when the installation is finished
Note concerning ASM
As of Oracle 11.2 the ASM stripping attribute of the redo logs template is set to ‘COARSE’ by default. It is nevertheless recommended to change the value by replacing it with ‘Fine-Grained’ for a better performance in redo writes and a lower write latency on Highly active Databases.
To do this, perform the following :
SQL> ALTER DISKGROUP FRA ALTER TEMPLATE onlinelog ATTRIBUTES (FINE);
4.2 Standby host
4.2.1 Installation
The installation is identical to that of the primary node:
|
Variable Value |
| Oracle BASE C:APPORACLE |
| Oracle home 1 C:APPORACLEPRODUCT11.1.0grid |
| Oracle home 2 C:APPORACLEPRODUCT11.1.0DB_1 |
a) Grid Infrastructure
The installation is identical to that of the primary host.
b) Database software
The installation is identical to that of the primary host.
c) Database instance
IMPORTANT: The only difference here is that the database will not be created through DBCA, but via an RMAN backup of the remote primary database.
5. DATA GUARD CONFIGURATION Go to Top
5.1 Activities and Configuration Related to the Physical Standby Database
The following tasks will be performed to complete and validate the configuration
– Create the physical standby database
– Check that the data flow arrives at the secondary base (standby)
» Here are the existing database files on the primary database
- DATAFILES
FILE_NAME TABLESPACE_NAME FREE_SPACE
————————————————– —————- ———
+DATA/MTLDB/DATAFILE/sysaux.262.907771563 SYSAUX 50.1875
+DATA/MT LDB/DATAFILE/system.260.907771689 SYSTEM 10.3125
+DATA/MTLDB/DATAFILE/undotbs1.258.907771837 UNDOTBS 631.0625
+DATA/MTLDB/hetDATAFILE/users.266.907771835 USERS 3.625
-
LOGFILES
GROUP# MEMBER
———- ———————————————–
1 +REDO/MTLDB/ONLINELOG/group_1.262.907771999
1 +FRA/MTLDB/ONLINELOG/group_1.348.908384663
2 +REDO/MTLDB/ONLINELOG/group_2.263.907771999
2 +FRA/MTLDB/ONLINELOG/group_2.349.908384665
3 +FRA/MTLDB/ONLINELOG/group_3.350.908384665
3 +REDO/MTLDB/ONLINELOG/group_3.257.907772001
Note : If the log groups have only 1 member execute the following
SQL> ALTER DATABASE ADD LOGFILE MEMBER ‘+FRA’ TO GROUP 1;
SQL> ALTER DATABASE ADD LOGFILE MEMBER ‘+FRA’ TO GROUP 2;
SQL> ALTER DATABASE ADD LOGFILE MEMBER ‘+FRA’ TO GROUP 3;
-
CONTROLFILES
NAME
———————————————-
+FRA/MTLDB/CONTROLFILE/current.346.908382665
+REDO/MTLDB/CONTROLFILE/current.256.907771995
-
SPFILE
+DATA/MTLDB/PARAMETERFILE/spfile.257.907772105
5.2 Configuring Primary Database Initialization Parameters Go to Top
-
· Enable logging (archive log mode)
SQL> SHUTDOWN IMMEDIATE;
=> STARTUP MOUNT ;
=> ALTER DATABASE ARCHIVELOG;
=> ALTER database OPEN;
=> SELECT LOG_MODE FROM V$DATABASE;
Enable logging mode
SQL> ALTER DATABASE FORCE LOGGING;
SQL> SELECT FORCE_LOGGING FROM V$DATABASE;
- Verify DB_NAME and DB_UNIQUE_NAME values from primary database
SQL> SHOW PARAMETER DB_NAME
SQL> SHOW PARAMETER DB_UNIQUE_NAME —- MTLDB/MTLSTD
-
Configure the FAL server and parameter file_name_convert (primary to secondary)
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG=’DG_CONFIG=(mtldb,mtlstd)’ scope=both;
- Destinations
In our case we are using an ASM based configuration
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG=’DG_CONFIG=(mtldb,mtlstd)’ scope=both;
ALTER SYSTEM set LOG_ARCHIVE_DEST_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=mtldb’ scope=both;
ALTER SYSTEM set LOG_ARCHIVE_DEST_2=’SERVICE=boisbriand ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mtlstd’ scope=both;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT=’+DATA/mtldb/DATAFILE’,’+DATA/mtlstd/DATAFILE’ scope=spfile;
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT=’+FRA/mtldb/ONLINELOG’,’+FRA/mtlstd/ONLINELOG’, ‘+REDO/mtldb/ONLINELOG’,’+REDO/mtlstd/ONLINELOG’ scope=spfile;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=spfile;
ALTER SYSTEM SET FAL_SERVER=Boisbriand SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=SPFILE;
-
Set the Remote_Login_Password parameter
SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
- Confirm the entered values:
SQL> SELECT NAME, VALUE FROM V$SPPARAMETER WHERE UPPER(NAME) IN (‘REMOTE_LOGIN_PASSWORDFILE’,’STANDBY_FILE_MANAGEMENT’,’LOG_ARCHIVE_DEST_1′,’LOG_ARCHIVE_DEST_2′,’DB_FILE_NAME_CONVERT’,’LOG_FILE_NAME_CONVERT’,’FAL_SERVER’);
NAME VALUE
————————————————– ——————–
db_file_name_convert +DATA/mtldb
db_file_name_convert +DATA/mtlstd
log_file_name_convert +FRA/mtldb
log_file_name_convert +FRA/mtlstd
log_file_name_convert +REDO/mtldb
log_file_name_convert +REDO/mtlstd
log_archive_dest_1 LOCATION=+FRA VALID_
FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=mtldb log_archive_dest_2 SERVICE=mtlstd ASYNC VALID_FOR=
ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=mtlstd
fal_server MTLSTD
standby_file_management AUTO
remote_login_passwordfile EXCLUSIVE
SQL> SELECT DEST_NAME,DESTINATION,TARGET FROM V$ARCHIVE_DEST WHERE DEST_NAME IN (‘LOG_ARCHIVE_DEST_1′,’LOG_ARCHIVE_DEST_2’);
DEST_NAME DESTINATION TARGET
——————- —————————————- ——————–
LOG_ARCHIVE_DEST_1 +FRA PRIMARY
LOG_ARCHIVE_DEST_2 mtlstd STANDBY
5.3 Oracle NET Configuration
Listener.ora: Add the following for the two services (MTLDB et MTLSTD) respectively
– Montreal (primary) Listener configuration
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = montreal-01.evilcorp.com)
(PORT = 1521))))
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =(GLOBAL_DBNAME = mtldb.evilcorp.com)
(ORACLE_HOME =C:APPORACLEproduct12.1.0.2db_1)(SID_NAME =MTLDB)))
– BoisBriand (standby) Listener configuration
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = montreal-02.evilcorp.com)
(PORT = 1521))))
SID_LIST_LISTENER=
(SID_LIST =
(SID_DESC =(GLOBAL_DBNAME = MTLSTD.evilcorp.com) – pas avant la creation MTLSTD
(ORACLE_HOME = C:APPORACLEproduct12.1.0.2db_1)(SID_NAME = MTLSTD)))
Tnsnames.ora: Add the following content for both servers
Montreal =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = montreal-01.evilcorp.com)
(PORT = 1521)))
(CONNECT_DATA =(SID = mtldb)(SERVER = dedicated)))
Boisbriand =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = montreal-02.evilcorp.com)
(PORT = 1521)))
(CONNECT_DATA =(SID = mtlstd)(SERVER = dedicated)))
5.4 Backup the primary database via RMAN Go to Top
Rman target=/
RMAN>BACKUP DATABASE PLUS ARCHIVELOG;
5.5 Prepare the standby Database directories
Unified Auditing (12c) allows you to audit even if the database is closed or in Mount / read only mode. The content is written on
$ORACLE_BASE/audit/SID_NAME directory.
The following query checks the status of this option (FALSE=No directory) :
SQL>SELECT VALUE FROM V$OPTION WHERE PARAMETER=’UNIFIED AUDITING’;
-
Creation of the new database directories
The directories corresponding to the parameters : audit_file_dest,core_dump_dest, (background_dump_dest ,user_dump_dest <11.2g)
#mkdir -p C:apporacleadminmtlstdadump
#mkdir -p C:apporaclediagrdbmsmtlstdmtlstdcdump
#mkdir -p C:apporaclediagrdbmsmtlstdmtlstdtrace
#mkdir -p C:APPORACLEauditmtldb
-
ASM directories
ASMCMD> mkdir –p +DATA/MTLSTD/DATAFILE
ASMCMD> mkdir -p +REDO/MTLSTD/ONLINELOG
ASMCMD> mkdir -p +FRA/MTLSTD/ONLINELOG
ASMCMD> mkdir -p +FRA/MTLSTD/CONTROLFILE
ASMCMD> mkdir -p +REDO/MTLSTD/CONTROLFILE
ASMCMD> mkdir -p +DATA/MTLSTD/PARAMETERFILE
- Password file creation
On the standby server run the following command:
orapwd file=%ORACLE_HOME%databasePWDmtlstd.ora password=racattack ENTRIES=30 FORCE=Y IGNORECASE=Y
- Pfile creation
Notepad %ORACLE_HOME%databasetempini.ora «db_name=mtlstd”
- Start the Standby database in Nomount Mode
ORACLE_SID variable being MTLSTD we should first create the standby database service:
C:> Oradim -NEW -SID mtlstd -STARTMODE manual -PFILE “C:APPORACLEPRODUCT12.1.0.2db_1databasetempini.ora”
C:> set ORACLE_SID=MTLSTD
C:> sqlplus ‘/ as sysdba’
SQL> STARTUP PFILE=’C:APPORACLEPRODUCT12.1.0.2db_1databasetempini.ora‘ NOMOUNT;
5.6 Duplication of the primary database using RMAN
This method can be beneficial, fast and simple especially when ASM is configured:
-
From the primary server, run the following RMAN script
rman <<EOF
set echo on
connect target sys/racattack@montreal; # Primary
connect auxiliary sys/racattack@boisbriand; # Standby: used by primary
run {
# Create channels for parallelism
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate channel prmy5 type disk;
allocate auxiliary channel stby1 type disk;
duplicate target database for standby from active database
#DORECOVER
spfile
parameter_value_convert ‘mtldb’,’mtlstd’
set ‘db_unique_name’=’mtlstd’
set ‘Fal_server’=’Montreal’
set log_Archive_dest_2=’SERVICE=montreal ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mtldb’
nofilenamecheck
; }
Exit
EOF
Note : Additional care should be given to non-OMF files (requires an explicit new name set)
Possible Errors
a) RMAN does not recognize the spfiles hosted in ASM
RMAN-03009: failure of backup command on prmy2 channel at 04/08/2016
ORA-19505: failed to identify file “+DATA/MTLDB/spfilemtldb.ora”
ORA-15173: entry ‘spfilemtldb.ora’ does not exist in directory ‘MTLDB
- Solution: Create an alias for the file
ASMCMD> mkalias +DATA/MTLDB/PARAMETERFILE/spfile.257.907772105 +DATA/MTLDB/PARAMETERFILE/spfilemtldb.ora
b) Local listener not recognized
RMAN-04014: startup failed: ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name ‘LISTENER_MTLDB’
-
Solution: reset the LOCAL_LISTENER parameter on the primary database
SQL> ALTER SYSTEM SET LOCAL_LISTENER=” SCOPE=BOTH;
5.7 Standby redo logs Creation (Real-time apply)
This step is only necessary if you want to apply the maximum protection or maximum availability mode. Transactions will be sent directly from the redo buffer instead of the archives. The total number to be created = (Nbr primary redo groups +1) = 4
— group 4/5/6/7 ‘+FRA’ size 50m; ///
SQL> ALTER DATABASE ADD STANDBY LOGFILE size 50M; (4x)
SQL> ALTER DATABASE ADD STANDBY LOGFILE size 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE size 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE size 50M;
5.8 Standby database check after the RMAN duplication
Confirm that each database has the appropriate role
-
Primary database
SQL@MTLDB> SELECT NAME, OPEN_MODE, DATABASE_ROLE,DB_UNIQUE_NAME ,PROTECTION_MODE FROM V$DATABASE;
NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME PROTECTION_MODE
—– ———– ————– ————— ——————–
MTLDB READ WRITE PRIMARY MTLDB MAXIMUM PERFORMANCE
- Standby database
SQL@MTLSTD> SELECT NAME, OPEN_MODE, DATABASE_ROLE,DB_UNIQUE_NAME ,PROTECTION_MODE FROM V$DATABASE;
NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME PROTECTION_MODE
—– ———– ————– ————— ——————–
MTLDB READ WRITE PRIMARY MTLDB MAXIMUM PERFORMANCE
At this point you are ready to mess with Data guard Broker click the Part II. Go to Top
giddy up 