Oracle 11g Physical Standy Setup

 

Step1: take Binaries tar backup from Primary DB server

tar -cvf /path/11.2.0.4.tar 11.2.0.4

Step2: untar Binaries tar back on Standby DB server

tar -xvf /path/11.2.0.4.tar -C /path

###################### Binaries Cloning ###################

Step3: run clone command on Standby DB server

export ORACLE_HOME=<PATH>

cd $ORACLE_HOME/clone/bin

perl $ORACLE_HOME/clone/bin/clone.pl ORACLE_BASE='<PATH>' ORACLE_HOME='<PATH>' ORACLE_HOME_NAME='<ANY_NAME>'


###################### POST Binaries Cloning ###################

Step4: run root scripts

$ORACLE_BASE/oraInventory/orainstRoot.sh

$ORACLE_HOME/root.sh


###################### Pre-requisites ###################

Standby:

Step5: update hostnames in listener and tnsnames files

Step6: start the listener in standby Server

Primary:

Step7: check force logging enabled -- to make sys activities will logged in redo logs

SQL> select force_logging from v$database; - NO ( default )

to enable force logging - SQL> alter database force logging;

SQL> select force_logging from v$database; - Yes

Step8: Create standby redo logs -- it will replicate automatically during rman

SQL> col "redo file name" for a60

col status for a20

select i.group#,l.thread#,bytes/1048576 "Size_MB",l.sequence#,l.archived,l.status,f.member "redo file name" from v$log l join v$logfile f on l.group#=f.group# order by l.group#;

SQL> col "redo file name" for a60

col status for a20

select i.group#,l.thread#,bytes/1048576 "Size_MB",l.sequence#,l.archived,l.status,f.member "redo file name" from v$standby_log l join v$logfile f on l.group#=f.group# order by l.group#;

SQL> alter database add standby logfile thread <number> group <number> ('/path/redo1a.log','/path/redo1b.log') size <number>g; --> size should same as primary redo logs otherwise apply  always shows lag and we need to manually switch log file in primary to check the apply LAG status

Standby:

SQL>alter system set db_unique_name='<ORACLE_SID>_p' scope=spfile;

SQL>alter system set db_recovery_file_dest_size=<number>g;

SQL>alter system set db_recovery_file_dest='+FRA'; -- ASM or filesystem

SQL>alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles.all_roles)' scope=both sid='*';

SQL>ALTER SYSTEM SET DB_CREATE_FILE_DEST='+DATA'; -- ASM or filesystem

SQL> alter system set  db_create_online_log_dest_1='+RECO'; -- ASM or filesystem

SQL> alter system set log_file_name_convert='<OLD_PATH_FOLDER>','+RECO','<OLD_PATH_FOLDER>','+RECO' scope=spfile;

NOTE: incase standby redo logs are not converted then after restoring data try to rename standby redo logs manually.

--SQL> alter database rename file '<OLD_PATH_FILENAME>' TO '+RECO/FILENAME';

SQL>alter system set db_file_name_convert='<OLD_PATH_FOLDER>','+DATA','<OLD_PATH_FOLDER>','+DATA' scope=spfile;

SQL> alter system set control_files='+DATA','+RECO' scope=spfile;

Step8: use rman to restore data from standby Db server

prepare below content and save clone.rcv

connect target sys/password@primarydb

connect auxiliary sys/password@standbydb

run {

allocate channel c1 type disk;

allocate auxiliary channel d1 type disk;

set newname for database to '+DATA'; -- ASM 

duplicate target database for standby from active database nofilenamecheck dorecover;

}

run the rman command:

nohup $ORACLE_HOME/bin/rman @clone.rcv > stby.log 2>&1 &


Step9: Enable Archive shipping and apply

Primary:

SQL> alter system set fal_server='<standby_db_unique_name>'  scope=both sid='*';

SQL> alter system set fal_client='<primary_db_unique_name>'  scope=both sid='*';

SQL> alter system set log_archive_config='dg_config=(<primary_db_unique_name>,<standby_db_unique_name>)' scope=both sid='*';

Standby:

SQL> alter system set fal_server='<primary_db_unique_name>'  scope=both sid='*';

SQL> alter system set fal_client='<standby_db_unique_name>'  scope=both sid='*';

SQL> alter system set log_archive_config='dg_config=(<standby_db_unique_name>,<primary_db_unique_name>)' scope=both sid='*';

Primary:

SQL> alter system set log_archive_dest_2='service=<service_name> async noaffirm reopen=15 valid_for=(all_logfiles,primary_role)  db_unique_name=<standby_db_unique_name>' scope=both sid='*';

SQL> alter system set log_archive_dest_state_2='ENABLE' scope=both sid='*';

NOTE: check in primary alert log log_archive_dest_state_2 should be ACTIVE

Step10: Start the MRP process

Standby:

SQL> select process,thread#,sequence#,status from gv$managed_standby where process='MRP0';

SQL> recover managed standby database disconnect from session; -- start MRP

Step 11: once LAG recovered, 

Primary and Standby:

SQL> alter system set standby_file_management=auto scope=both;


Step11 ( OPTIONAL ):

incase standby redo logs are not on new disk location ( ASM )

Primary and Standby:

SQL> alter system set standby_file_management=MANUAL scope=both;

Standby

SQL> select process,thread#,sequence#,status from gv$managed_standby where process='MRP0';

SQL> alter database recover managed standby database cancel; -- stop MRP

SQL> alter database rename file '<OLD_LOCATION>' to '<NEW_LOCATION>';

SQL> recover managed standby database disconnect from session; -- start MRP

SQL> select process,thread#,sequence#,status from gv$managed_standby where process='MRP0';

Primary and Standby:

SQL> alter system set standby_file_management=auto scope=both;


####################### DGMGRL setup #######################

Step1: listener.ora  and tnsnames.ora files update

Standby:

 listener.ora

(SID_DESC =

                (GLOBAL_DBNAME = <standby_db_unique_name>_DGMGRL.<domain_name>)

                (SID_NAME = <primary_db_unique_name>)

                (ORACLE_HOME = <ORACLE_HOME>)

        )

tnsnames.ora

<standby_db_unique_name>_DGMGRL.<domain_name>=

    (DESCRIPTION =

        (ADDRESS = (PROTOCOL = TCP) (HOST = <hostname>) (PORT = <number>))

        (CONNECT_DATA = (SERVER = DEDICATED)

        (SID = <primary_db_unique_name>)

        (SERVICE_NAME = <standby_db_unique_name>_DGMGRL.<domain_name>)

)

)

primary:

(SID_DESC =

                (GLOBAL_DBNAME = <primary_db_unique_name>_DGMGRL.<domain_name>)

                (SID_NAME = <primary_db_unique_name>)

                (ORACLE_HOME = <ORACLE_HOME>)

        )

tnsnames.ora

<primary_db_unique_name>_DGMGRL.<domain_name>=

    (DESCRIPTION =

        (ADDRESS = (PROTOCOL = TCP) (HOST = <hostname>) (PORT = <number>))

        (CONNECT_DATA = (SERVER = DEDICATED)

        (SID = <primary_db_unique_name>)

        (SERVICE_NAME = <primary_db_unique_name>_DGMGRL.<domain_name>)

)

)

Step2: Add DGMGRL configurations

Primary and standby:

SQL> alter system set dg_broker_start=TRUE scope=both sid='*';

Primary:

dgmgrl sys/password@<primary_db_unique_name>_DGMGRL.<domain_name>

DGMGRL> create configuration '<DBNAME_dg>' as primary database is '<primary_db_unique_name>' connect identifier is '<primary_db_unique_name>_DGMGRL.<domain_name>';

DGMGRL> add database <standby_db_unique_name> as connect identifier is '<standby_db_unique_name>_DGMGRL.<domain_name>';

NOTE: update StaticConnectIdentifier ( e.g hostname ) for primary and standby properties.

DGMGRL> edit database <primary_db_unique_name> set property StaticConnectIdentifier=' (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = <hostname>) (PORT = <number>))    (CONNECT_DATA = (SERVICE_NAME = <primary_db_unique_name>_DGMGRL.<domain_name>)(INSTANCE_NAME=<primary_db_unique_name>)(SERVER = DEDICATED)))

DGMGRL> edit database <standby_db_unique_name> set property StaticConnectIdentifier=' (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = <hostname>) (PORT = <number>))    (CONNECT_DATA = (SERVICE_NAME = <standby_db_unique_name>_DGMGRL.<domain_name>)(INSTANCE_NAME=<standby_db_unique_name>)(SERVER = DEDICATED)))


NOTE: check any inconsistent parameters values

DGMGRL> show database <primary_db_unique_name> InconsistentProperties

DGMGRL> show database <standby_db_unique_name> InconsistentProperties


DGMGRL>  enable configuration

DGMGRL>  show configuration

DGMGRL>  show database verbose <standby_db_unique_name> - no lag should be appear








Comments

Post a Comment

Popular Posts

AWR Report Analysis

Image
AWR report analysis is important for  Oracle DBA on Daily issues or P1/P2 issues time. Below some of the main things we general look to figure out issue. 1. 2.  3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13.
Read more

Goldengate 19c Installation with issue resolution

Image
  Basic Installation of GoldenGate 19c in OEL6. Tested with below requirements: Step1: Vmware requirements:   For installing OEL6.8 and Oracle DB 12c  installation, please refer URL https://venuoracledba.blogspot.com/2020/08/oracle-12c-2-node-rac-setup-in-vmware.html     Step2: After OEL6.8 installation finished. We will use /opt for goldengate installation.   Step3: unzip GG software in folder. Here unzipped in gg_software folder.     Folder fbo_ggs_Linux_x64_shiphome will create. Step4:  Enable GUI by issuing x host + command in root user. and keep open this root terminal.   Step5:  Enable GUI by issuing x host + command in oracle user.   Move to software path   execute ./runInstaller and select database version. Selected 12c version for my environment. and click Next     provide GG home, oracle home and port number and click Next.     After verifying paths and information, Click Install   ...
Read more

Oracle 12c 2-node RAC setup in VMware Player 15 -- Part01

Image
Tried to install Oracle 12c 2-node RAC for testing purpose but due to low Hardware configuration, ended up with Single Node RAC. I got lot of issues during Installation and tried to resolve them with my best knowledge. Please find below installation steps and troubleshooting. Main issues started from Step90. you can go through quickly. Requirements Most of the times, bridge option in VMWARE will not connect with host machine. I tried below option to use internet connection from host machine. We need to cross verify vmnet8 is NAT or not. Installation Steps Step1 launch VMware and click on New Virtual Machine Step2   select OEL iso Image Step3 Provide New Virtual Machine Name Step4 Provide HDD size and select single virtual disk. Step5  Select host setting for monitor and Network as NAT Step6 New Virtual Machine is ready for launch. Step7 OEL Installation starts automatically. Press Enter Step8 Skip Disk Checking Step9 Click Next Step10   Select langu...
Read more

Execution_Explain Plan

Whenever we get request for application slowness or sql query then we need to identify sql_id and to analyze sql_id, we need to check SQL execution/explain plan. 1. EXECUTION_PLAN: select id,plan_hash_value,operation, options,object_name,cardinality,cost,bytes,cpu_cost,io_cost,time,partition_start,partition_stop, object_node,other_tag,qblock_name,other_xml from dba_hist_sql_plan where sql_id='<SQL_ID>'; or select * from table(dbms_xplan.display_awr('<SQL_ID>')); EXPLAIN_PLAN: explain plan for <SQL_QUERY>; select * from table(dbms_xplan.display);
Read more

Oracle to Postgres

Image
  Round 1: Created metadata for tables, sequences, packages Round 2: Created indexes Round 3: freeze column name to freezes to_date function updated GEOMETRY datatype , installed POSTGIS package to support this datatype Round 4: created triggers created Functions Migration: bash-4.2$ pwd /usr/edb/migrationtoolkit/etc bash-4.2$ cat toolkit.properties SRC_DB_URL=jdbc:oracle:thin:@localhost:1521:orcl SRC_DB_USER=hr SRC_DB_PASSWORD=hr TARGET_DB_URL=jdbc:edb://postoffline:5444/dev TARGET_DB_USER=enterprisedb TARGET_DB_PASSWORD=password bash-4.2$  -bash-4.2$ pwd /usr/edb/migrationtoolkit/bin -bash-4.2$ ls -lrt total 724 -rwxr-xr-x. 1 root root   2439 Dec 11 13:29 runJavaApplication.sh -rwxr-xr-x. 1 root root    544 Dec 11 13:33 runMTK.sh -rwxr-xr-x. 1 root root 729258 Dec 11 13:33 edb-migrationtoolkit.jar -bash-4.2$  ./runMTK.sh HR OUTPUT: Running EnterpriseDB Migration Toolkit (Build 55.7.0) ... Source database connectivity info... conn =jdbc:oracle:thin:@o...
Read more

Active Session History Analysis

Image
ASH  analysis is important for  Oracle DBA on Daily issues or P1/P2 issues time. Below some of the main events we general look to figure out issue. RAC / Cloud RDS: 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. Redo Generation: 14. 15. 16. 17. 18. 19.
Read more

General Queries

                                                                              Tablespace growth Size We can use view to check tablespace size greater than 80% RAC / NON-RAC / Cloud RDS: SELECT tbm.TABLESPACE_NAME,   round(tbm.USED_SPACE * tb.BLOCK_SIZE /(1024*1024*1024),2) as USED_SPACE_GB,   round(tbm.TABLESPACE_SIZE * tb.BLOCK_SIZE /(1024*1024*1024),2) as TBS_SIZE_GB,   round((tbm.TABLESPACE_SIZE - tbm.USED_SPACE) * tb.BLOCK_SIZE /(1024*1024*1024),2) as TBS_FREE_SIZE_GB,   round(tbm.USED_PERCENT) as PERCENT_USEDFROM dba_tablespace_usage_metrics tbm      join dba_tablespaces tb on tb.TABLESPACE_NAME = tbm.TABLESPACE_NAME w...
Read more

Fragmentation & Re-organisation

Total  Savings: set verify off column file_name format a50 word_wrapped column smallest format 999,990 heading "Smallest|Size|Poss." column currsize format 999,990 heading "Current|Size" column savings format 999,990 heading "Poss.|Savings" break on report compute sum of savings on report column value new_val blksize select value from v$parameter where name = 'db_block_size' / select file_name, ceil( (nvl(hwm,1)*&amp;&amp;blksize)/1024/1024 ) smallest, ceil( blocks*&amp;&amp;blksize/1024/1024) currsize, ceil( blocks*&amp;&amp;blksize/1024/1024) - ceil( (nvl(hwm,1)*&amp;&amp;blksize)/1024/1024 ) savings from dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b where a.file_id = b.file_id(+) order by savings desc / HWM query for all datafiles: col data_file_name for a50 col tablespace_name for a20 set lines 300 select tablespace_name, file_id, file_name DATA_FILE_NAME, Al...
Read more

Postgresql installation offline using NGINX

  [root@localhost yumdownloaddir]# sudo  yum -y install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm Loaded plugins: langpacks, ulninfo epel-release-latest-7.noarch.rpm                                                                                                                                                            |  15 kB  00:00:00      Examining /var/tmp/yum-root-dewJ5H/epel-release-latest-7.noarch.rpm: epel-release-7-14.noarch Marking /var/tmp/yum-root-dewJ5H/epel-release-latest-7.noarch.rpm to be installed Resolving Dependencies --...
Read more