Learning Oracle: 2021

Single Instance is a simple and basic option of deploying oracle database. In single instance database there is only one instance of database running on one node. Single Instance is simple to install and maintain. This could be a good choice for small production and development environments.

Oracle RAC Database:

Oracle RAC database is a clustered database. It can have more than one instance running on one or more hosts. To make is database RAC , there is another software needs to be installed called Oracle Grid infrastructure which takes care of multinode sync, ASM, Oracle restart etc. Oracle GI is necessary as a base of Oracle RAC. In Oracle RAC, the load is balanced across multiple instance of databases running on different nodes. This is best for Production database. Failure of any instance is abstracted from user and forwarded to another instance from the database server pool. No of instances can be controlled using Policy Managed or Admin Managed.

Oracle RAC One Node:

Some time we need to have RAC databases but don't need many instances, why? Simple answers to it is leverage the high availability of RAC. The Oracle RAC One node is a RAC DB runs on one host. The advantage is it saves your resource, can to converted to full RAC any time, Cluster fail over i.e. the Database instance is moved to new instance in case of any failure on host known as Instance Relocation, easy to upgrade etc. This type of database is well suited for small production and dev/test environments.

Friday, April 30, 2021

Backup Based RMAN DUPLICATE Without Connecting To Target Database

Backup Based RMAN DUPLICATE Without Connecting To Target Database

If you are performing a backup based RMAN duplicate and using a recovery catalog as well, it is not required to connect to the source database as TARGET in RMAN.

This method is advantageous where network connections from the auxiliary host to the source database are restricted or prone to intermittent disruptions. In duplication without a TARGET connection, the source database is unaffected by the duplication.

Steps:

1.Prepare the shell script for backup and source the database:

vi rmanclone.sh

chmod 775 rmanclone.sh

ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
BACKUP_LOG_PATH=/home/oracle
export ORACLE_BASE
export ORACLE_HOME
export ORACLE_SID=GTP2PROD
export BACKUP_LOG_PATH
LOG_FILE=${BACKUP_LOG_PATH}/clone_db.log
$ORACLE_HOME/bin/rman msglog=${LOG_FILE} <<EOF

connect target /

run {
allocate channel t1 type disk;
allocate channel t2 type disk;
backup database format '/u01/rman_bkp/database_%d_%u_%s';
release channel t1;
release channel t2;
}
sql 'alter system archive log current';
run {
allocate channel a1 type disk;
allocate channel a2 type disk;
backup archivelog all format '/u01/rman_bkp/arch_%d_%u_%s';
release channel a1;
release channel a2;

}

run {
allocate channel c1 type disk;
backup current controlfile format '/u01/rman_bkp/control_%d_%u_%s';
release channel c1;
}

exit;

EOF

chmod 775 rmanclone.sh

nohup ./rmanclone.sh &

jobs -l

2.Verify the backups and transfer to destination side:

[oracle@host01 rman_bkp]$ ls -ltrh

total 1.2G

-rw-r----- 1 oracle oinstall 405M Apr 29 22:51 database_GTP2PROD_0ivtht5d_18
-rw-r----- 1 oracle oinstall 9.7M Apr 29 22:52 database_GTP2PROD_0jvtht8e_19
-rw-r----- 1 oracle oinstall 96K Apr 29 22:52 database_GTP2PROD_0kvtht8m_20
-rw-r----- 1 oracle oinstall 600M Apr 29 22:52 database_GTP2PROD_0hvtht5d_17
-rw-r----- 1 oracle oinstall 45M Apr 29 22:52 arch_GTP2PROD_0mvtht8v_22
-rw-r----- 1 oracle oinstall 52M Apr 29 22:52 arch_GTP2PROD_0lvtht8v_21
-rw-r----- 1 oracle oinstall 6.6M Apr 29 22:52 arch_GTP2PROD_0nvtht9f_23
-rw-r----- 1 oracle oinstall 9.7M Apr 29 22:52 control_GTP2PROD_0ovtht9i_24

scp -p * oracle@host02:/u01/oracle/rman_backups

3.Create the required directories in destination side:

mkdir -p /u01/app/oracle/admin/MQMARC/adump
mkdir -p /u01/app/oracle/oradata/MQMARC
mkdir -p /u01/app/oracle/fast_recovery_area/MQMARC
mkdir -p /u01/app/oracle/fast_recovery_area

4.Prepare pfile and nomount the database:

vi initMQMARC.ora';

MQMARC.__db_cache_size=2415919104
MQMARC.__java_pool_size=16777216
MQMARC.__large_pool_size=16777216
MQMARC.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
MQMARC.__pga_aggregate_target=1040187392
MQMARC.__sga_target=3103784960
MQMARC.__shared_io_pool_size=0
MQMARC.__shared_pool_size=603979776
MQMARC.__streams_pool_size=16777216
*.audit_file_dest='/u01/app/oracle/admin/MQMARC/adump'
*.audit_trail='none'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/MQMARC/control01.ctl','/u01/app/oracle/fast_recovery_area/MQMARC/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='MQMARC'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=MQMARCXDB)'
*.open_cursors=300
*.pga_aggregate_target=1032847360
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=3098542080
*.undo_tablespace='UNDOTBS1'
*.db_flashback_retention_target=4320
*.global_names=TRUE
#*.local_listener='MQMARC'
*.log_file_name_convert='/u01/app/oracle/oradata/GTP2PROD/','/u01/app/oracle/oradata/MQMARC/'
*.db_file_name_convert='/u01/app/oracle/oradata/GTP2PROD/','/u01/app/oracle/oradata/MQMARC/'

startup nomount pfile='$ORACLE_HOME/dbs/initMQMARC.ora';

5.Run the RMAN duplicate command:

vi clone_MQMARC_db.sh

ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
BACKUP_LOG_PATH=/home/oracle
export ORACLE_BASE
export ORACLE_HOME
export ORACLE_SID=MQMARC
export BACKUP_LOG_PATH
LOG_FILE=${BACKUP_LOG_PATH}/clone_mqmarc_db.log
/u01/app/oracle/product/11.2.0/dbhome_1/bin/rman msglog=${LOG_FILE} <<EOF
connect auxiliary /
run {
allocate auxiliary channel t1 type disk;
allocate auxiliary channel t2 type disk;
duplicate target database to MQMARC backup location '/u01/oracle/backup';
}
exit
EOF

chmod 775 clone_MQMARC_db.sh
nohup ./clone_MQMARC_db.sh &

6.Connect to database and check the status:

SQL> select name, open_mode, status from v$database,v$instance;
NAME OPEN_MODE STATUS
--------- -------------------- ------------
MQMARC READ WRITE OPEN

Note:

-RMAN will internally changed the SID during the duplicate command execution.
-It will open the database with resetlog option.
-Flashback is automatically on by duplicate command.

Tuesday, April 13, 2021

RAC Manual Patching Steps On 12c

RAC Manual Patching Steps On 12c

On NODE.1

1. Stop the database resources from DB home.

[oracle@test01 ~]$ ps -ef|grep pmon

oracle 15294 1 0 00:35 ? 00:00:00 asm_pmon_+ASM1

oracle 18171 1 0 00:39 ? 00:00:00 ora_pmon_RUMPROD1

oracle 19097 9655 0 00:40 pts/2 00:00:00 grep pmon

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

[oracle@test01 ~]$ ps -ef|grep pmon

oracle 15294 1 0 00:35 ? 00:00:00 asm_pmon_+ASM1

oracle 28749 9655 0 01:16 pts/2 00:00:00 grep pmon

2. On GI Home, as the root user or sudo execute below:

[root@test01 install]# ps -ef|grep pmon

oracle 15294 1 0 00:35 ? 00:00:00 asm_pmon_+ASM1

root 29836 6253 0 01:20 pts/1 00:00:00 grep pmon

[root@test01 install]# $ORACLE_HOME/crs/install/rootcrs.pl -prepatch

Using configuration parameter file: /u01/app/12.1.0/grid/crs/install/crsconfig_params

Oracle Clusterware active version on the cluster is [12.1.0.2.0]. The cluster upgrade state is [NORMAL]. The cluster active patch level is [0].

CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'test01'

CRS-2673: Attempting to stop 'ora.crsd' on 'test01'

CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'test01'

CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'test01'

CRS-2673: Attempting to stop 'ora.OCR.dg' on 'test01'

CRS-2677: Stop of 'ora.OCR.dg' on 'test01' succeeded

CRS-2673: Attempting to stop 'ora.asm' on 'test01'

CRS-2677: Stop of 'ora.asm' on 'test01' succeeded

CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'test01' succeeded

CRS-2673: Attempting to stop 'ora.test01.vip' on 'test01'

CRS-2677: Stop of 'ora.test01.vip' on 'test01' succeeded

CRS-2672: Attempting to start 'ora.test01.vip' on 'test02'

CRS-2676: Start of 'ora.test01.vip' on 'test02' succeeded

CRS-2673: Attempting to stop 'ora.ons' on 'test01'

CRS-2677: Stop of 'ora.ons' on 'test01' succeeded

CRS-2673: Attempting to stop 'ora.net1.network' on 'test01'

CRS-2677: Stop of 'ora.net1.network' on 'test01' succeeded

CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'test01' has completed

CRS-2677: Stop of 'ora.crsd' on 'test01' succeeded

CRS-2673: Attempting to stop 'ora.ctssd' on 'test01'

CRS-2673: Attempting to stop 'ora.evmd' on 'test01'

CRS-2673: Attempting to stop 'ora.storage' on 'test01'

CRS-2673: Attempting to stop 'ora.crf' on 'test01'

CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'test01'

CRS-2673: Attempting to stop 'ora.mdnsd' on 'test01'

CRS-2673: Attempting to stop 'ora.gpnpd' on 'test01'

CRS-2677: Stop of 'ora.storage' on 'test01' succeeded

CRS-2673: Attempting to stop 'ora.asm' on 'test01'

CRS-2677: Stop of 'ora.drivers.acfs' on 'test01' succeeded

CRS-2677: Stop of 'ora.ctssd' on 'test01' succeeded

CRS-2677: Stop of 'ora.evmd' on 'test01' succeeded

CRS-2677: Stop of 'ora.crf' on 'test01' succeeded

CRS-2677: Stop of 'ora.mdnsd' on 'test01' succeeded

CRS-2677: Stop of 'ora.gpnpd' on 'test01' succeeded

CRS-2677: Stop of 'ora.asm' on 'test01' succeeded

CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'test01'

CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'test01' succeeded

CRS-2673: Attempting to stop 'ora.cssd' on 'test01'

CRS-2677: Stop of 'ora.cssd' on 'test01' succeeded

CRS-2673: Attempting to stop 'ora.gipcd' on 'test01'

CRS-2677: Stop of 'ora.gipcd' on 'test01' succeeded

CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'test01' has completed

CRS-4133: Oracle High Availability Services has been stopped.

2021/04/14 01:22:01 CLSRSC-4012: Shutting down Oracle Trace File Analyzer (TFA) Collector.

2021/04/14 01:22:37 CLSRSC-4013: Successfully shut down Oracle Trace File Analyzer (TFA) Collector.

2021/04/14 01:22:37 CLSRSC-347: Successfully unlock /u01/app/12.1.0/grid

[root@test01 install]# ps -ef|grep pmon

root 3037 6253 0 01:27 pts/1 00:00:00 grep pmon

3. Check the patch Conflicts

4. Patch GI home [as GI home owner]

Goto patch directory and apply the patch.

opatch apply

5. Run the post script (as the root or sudo user)

[root@test01 install]# $ORACLE_HOME/rdbms/install/rootadd_rdbms.sh

[oracle@test01 ~]$ ps -ef|grep pmon

oracle 21867 9655 0 01:30 pts/2 00:00:00 grep pmon

[root@test01 install]# $ORACLE_HOME/crs/install/rootcrs.pl -postpatch

Using configuration parameter file: /u01/app/12.1.0/grid/crs/install/crsconfig_params

2021/04/14 01:30:33 CLSRSC-4015: Performing install or upgrade action for Oracle Trace File Analyzer (TFA) Collector.

2021/04/14 01:30:49 CLSRSC-4003: Successfully patched Oracle Trace File Analyzer (TFA) Collector.

CRS-4123: Starting Oracle High Availability Services-managed resources

CRS-2672: Attempting to start 'ora.mdnsd' on 'test01'

CRS-2672: Attempting to start 'ora.evmd' on 'test01'

CRS-2676: Start of 'ora.mdnsd' on 'test01' succeeded

CRS-2676: Start of 'ora.evmd' on 'test01' succeeded

CRS-2672: Attempting to start 'ora.gpnpd' on 'test01'

CRS-2676: Start of 'ora.gpnpd' on 'test01' succeeded

CRS-2672: Attempting to start 'ora.gipcd' on 'test01'

CRS-2676: Start of 'ora.gipcd' on 'test01' succeeded

CRS-2672: Attempting to start 'ora.cssdmonitor' on 'test01'

CRS-2676: Start of 'ora.cssdmonitor' on 'test01' succeeded

CRS-2672: Attempting to start 'ora.cssd' on 'test01'

CRS-2672: Attempting to start 'ora.diskmon' on 'test01'

CRS-2676: Start of 'ora.diskmon' on 'test01' succeeded

CRS-2676: Start of 'ora.cssd' on 'test01' succeeded

CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'test01'

CRS-2672: Attempting to start 'ora.ctssd' on 'test01'

CRS-2676: Start of 'ora.ctssd' on 'test01' succeeded

CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'test01' succeeded

CRS-2672: Attempting to start 'ora.asm' on 'test01'

CRS-2676: Start of 'ora.asm' on 'test01' succeeded

CRS-2672: Attempting to start 'ora.storage' on 'test01'

CRS-2676: Start of 'ora.storage' on 'test01' succeeded

CRS-2672: Attempting to start 'ora.crf' on 'test01'

CRS-5013: Agent "ORAROOTAGENT" failed to start process "/u01/app/12.1.0/grid/bin/osysmond" for action "start": details at "(:CLSN00008:)" in "/u01/app/oracle/diag/crs/test01/crs/trace/ohasd_orarootagent_root.trc"

CRS-2674: Start of 'ora.crf' on 'test01' failed

CRS-2679: Attempting to clean 'ora.crf' on 'test01'

CRS-2681: Clean of 'ora.crf' on 'test01' succeeded

CRS-2672: Attempting to start 'ora.crsd' on 'test01'

CRS-2676: Start of 'ora.crsd' on 'test01' succeeded

CRS-6017: Processing resource auto-start for servers: test01

CRS-2672: Attempting to start 'ora.net1.network' on 'test01'

CRS-2676: Start of 'ora.net1.network' on 'test01' succeeded

CRS-2672: Attempting to start 'ora.ons' on 'test01'

CRS-2673: Attempting to stop 'ora.test01.vip' on 'test02'

CRS-2677: Stop of 'ora.test01.vip' on 'test02' succeeded

CRS-2672: Attempting to start 'ora.test01.vip' on 'test01'

CRS-2676: Start of 'ora.test01.vip' on 'test01' succeeded

CRS-2672: Attempting to start 'ora.LISTENER.lsnr' on 'test01'

CRS-2676: Start of 'ora.ons' on 'test01' succeeded

CRS-2676: Start of 'ora.LISTENER.lsnr' on 'test01' succeeded

CRS-6016: Resource auto-start has completed for server test01

CRS-6024: Completed start of Oracle Cluster Ready Services-managed resources

CRS-4123: Oracle High Availability Services has been started.

Oracle Clusterware active version on the cluster is [12.1.0.2.0]. The cluster upgrade state is [NORMAL]. The cluster active patch level is [0].

PRCC-1010 : _mgmtdb was already enabled

PRCR-1002 : Resource ora.mgmtdb is already enabled

[oracle@test01 ~]$ ps -ef|grep pmon

oracle 1099 9655 0 01:36 pts/2 00:00:00 grep pmon

oracle 1502 1 0 01:32 ? 00:00:00 asm_pmon_+ASM1

6. Start the database resources from DB home.

[oracle@test01 ~]$ ps -ef|grep pmon

oracle 1099 9655 0 01:36 pts/2 00:00:00 grep pmon

oracle 1502 1 0 01:32 ? 00:00:00 asm_pmon_+ASM1

SQL> startup;

ORACLE instance started.

Total System Global Area 754974720 bytes

Fixed Size 2928968 bytes

Variable Size 385879736 bytes

Database Buffers 360710144 bytes

Redo Buffers 5455872 bytes

Database mounted.

Database opened.

[oracle@test01 ~]$ ps -ef|grep pmon

oracle 1502 1 0 01:32 ? 00:00:00 asm_pmon_+ASM1

oracle 16180 1 0 01:37 ? 00:00:00 ora_pmon_RUMPROD1

oracle 19651 19507 0 01:38 pts/2 00:00:00 grep pmon

Perform all the above steps in same manner on NODE.2

Note:

Always read the patch readme to understand the patching instructions, general procedure to apply the database prepatch and postpatch steps.

7. Run the postpatch datapatch apply (In rac only datapatch will be run only one node)

[oracle@test01 ~]$ cd $ORACLE_HOME/OPatch

[oracle@test01 OPatch]$ ./datapatch -verbose

SQL Patching tool version 12.2.0.0.0 on Wed Apr 14 01:41:32 2021

Connecting to database...OK

Determining current state...done

Current state of SQL patches:

Adding patches to installation queue and performing prereq checks...

Installation queue:

Nothing to roll back

Nothing to apply

SQL Patching tool complete on Wed Apr 14 01:43:17 2021

Monday, April 12, 2021

Switchover and Switchback Using Dataguard Broker For Oracle 11.2.0.3.0 Database

Switchover and Switchback Using Dataguard Broker For Oracle 11.2.0.3.0 Database

---Primary---

[oracle@host01 ~]$ dgmgrl

DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/Welcome1

Connected.

DGMGRL> show configuration

Configuration - gtp2prod

Protection Mode: MaxPerformance

Databases:

gtp2_live - Primary database

gtp2_stdy - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

To check the PRIMARY status:

DGMGRL> show database verbose gtp2_live

Database - gtp2_live

Role: PRIMARY

Intended State: TRANSPORT-ON

Instance(s):

GTP2PROD

Properties:

DGConnectIdentifier = 'gtp2_live'

ObserverConnectIdentifier = ''

LogXptMode = 'ASYNC'

DelayMins = '0'

Binding = 'optional'

MaxFailure = '0'

MaxConnections = '1'

ReopenSecs = '300'

NetTimeout = '30'

RedoCompression = 'DISABLE'

LogShipping = 'ON'

PreferredApplyInstance = ''

ApplyInstanceTimeout = '0'

ApplyParallel = 'AUTO'

StandbyFileManagement = 'AUTO'

ArchiveLagTarget = '0'

LogArchiveMaxProcesses = '30'

LogArchiveMinSucceedDest = '1'

DbFileNameConvert = 'GTP2_STDY, GTP2_LIVE'

LogFileNameConvert = 'GTP2_STDY, GTP2_LIVE'

FastStartFailoverTarget = ''

InconsistentProperties = '(monitor)'

InconsistentLogXptProps = '(monitor)'

SendQEntries = '(monitor)'

LogXptStatus = '(monitor)'

RecvQEntries = '(monitor)'

SidName = 'GTP2PROD'

StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.7)(PORT=1523))(CONNECT_DATA=(SERVICE_NAME=GTP2_LIVE_DGMGRL)(INSTANCE_NAME=GTP2PROD)(SERVER=DEDICATED)))'

StandbyArchiveLocation = '/u01/app/oracle/arch'

AlternateLocation = ''

LogArchiveTrace = '0'

LogArchiveFormat = 'arch%s%t%r.arc'

TopWaitEvents = '(monitor)'

Database Status:

SUCCESS

To check the STANDBY status:

DGMGRL> show database verbose gtp2_stdy

Database - gtp2_stdy

Role: PHYSICAL STANDBY

Intended State: APPLY-ON

Transport Lag: 0 seconds

Apply Lag: 0 seconds

Real Time Query: ON

Instance(s):

GTP2PROD

Properties:

DGConnectIdentifier = 'gtp2_stdy'

ObserverConnectIdentifier = ''

LogXptMode = 'ASYNC'

DelayMins = '0'

Binding = 'OPTIONAL'

MaxFailure = '0'

MaxConnections = '1'

ReopenSecs = '300'

NetTimeout = '30'

RedoCompression = 'DISABLE'

LogShipping = 'ON'

PreferredApplyInstance = ''

ApplyInstanceTimeout = '0'

ApplyParallel = 'AUTO'

StandbyFileManagement = 'AUTO'

ArchiveLagTarget = '0'

LogArchiveMaxProcesses = '30'

LogArchiveMinSucceedDest = '1'

DbFileNameConvert = 'GTP2_LIVE, GTP2_STDY'

LogFileNameConvert = 'GTP2_LIVE, GTP2_STDY'

FastStartFailoverTarget = ''

InconsistentProperties = '(monitor)'

InconsistentLogXptProps = '(monitor)'

SendQEntries = '(monitor)'

LogXptStatus = '(monitor)'

RecvQEntries = '(monitor)'

SidName = 'GTP2PROD'

StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.8)(PORT=1524))(CONNECT_DATA=(SERVICE_NAME=GTP2_STDY_DGMGRL)(INSTANCE_NAME=GTP2PROD)(SERVER=DEDICATED)))'

StandbyArchiveLocation = '/u01/app/oracle/arch'

AlternateLocation = ''

LogArchiveTrace = '0'

LogArchiveFormat = 'arch%s%t%r.arc'

TopWaitEvents = '(monitor)'

Database Status:

SUCCESS

Check the current status:

DGMGRL> show configuration

Configuration - gtp2prod

Protection Mode: MaxPerformance

Databases:

gtp2_live - Primary database

gtp2_stdy - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

Switchover

It is always advised to view the alert.log files for both PRIMARY and STANDBY databases.

Issue the below command:

DGMGRL> switchover to gtp2_stdy

Performing switchover NOW, please wait...

New primary database "gtp2_stdy" is opening...

Operation requires shutdown of instance "GTP2PROD" on database "gtp2_live"

Shutting down instance "GTP2PROD"...

ORACLE instance shut down.

Operation requires startup of instance "GTP2PROD" on database "gtp2_live"

Starting instance "GTP2PROD"...

ORACLE instance started.

Database mounted.

Database opened.

Switchover succeeded, new primary is "gtp2_stdy"

Check the current status:

DGMGRL> show configuration

Configuration - gtp2prod

Protection Mode: MaxPerformance

Databases:

gtp2_stdy - Primary database

gtp2_live - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

Switchback

To revert (switch back) to the previous situation, perform the same action. Remember now your primary is your previous STANDBY and standby is previous PRIMARY.

Issue the below command:

DGMGRL> swtichover to gtp2_live

Unrecognized command "swtichover", try "help"

DGMGRL> switchover to gtp2_live

Performing switchover NOW, please wait...

New primary database "gtp2_live" is opening...

Operation requires shutdown of instance "GTP2PROD" on database "gtp2_stdy"

Shutting down instance "GTP2PROD"...

ORACLE instance shut down.

Operation requires startup of instance "GTP2PROD" on database "gtp2_stdy"

Starting instance "GTP2PROD"...

ORACLE instance started.

Database mounted.

Database opened.

Switchover succeeded, new primary is "gtp2_live"

Check the current status:

DGMGRL> show configuration

Configuration - gtp2prod

Protection Mode: MaxPerformance

Databases:

gtp2_live - Primary database

gtp2_stdy - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

Wednesday, February 17, 2021

Check Whether Physical Standby Is In Sync With The Primary Or NOT

Check Whether Physical Standby Is In Sync With The Primary Or NOT

Method.1

set lines 555

select instance_name,name,open_mode,to_char(startup_time,'DD-MM-YY:HH24:MI:ss') startup_time from v$database,v$instance;

select database_role,db_unique_name instance,open_mode,protection_mode,protection_level,switchover_status from v$database;

+ From Primary:

SQL > select thread#, max(sequence#) "Last Primary Seq Generated" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;

SQL> SELECT thread#, dest_id, gvad.status, error, fail_sequence FROM gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id = gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id;

+ From Physical Standby:

SQL > select thread#, max(sequence#) "Last Standby Seq Received" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;

SQL > select thread#, max(sequence#) "Last Standby Seq Applied" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# and applied='YES' group by thread# order by 1;

SQL> select process,status,thread#,sequence# from v$managed_standby;

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD#;

select max(sequence#) from v$log_history;

select max(sequence#) from v$archived_log where applied='YES';

--Without realtime:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

--With realtime apply:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Method.2

From Primary Side:

select thread#, max(sequence#) "Last Primary Seq Generated" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;

select thread#, dest_id, gvad.status, error, fail_sequence FROM gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id = gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id;

select max(sequence#) from v$log_history;

v$archived_log

gv$archive_dest

v$database

gv$instance

From Physical Standby Side:

select thread#, max(sequence#) "Last Standby Seq Received" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;

select thread#, max(sequence#) "Last Standby Seq Applied" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# and applied='YES' group by thread# order by 1;

select max(sequence#) from v$log_history;

select process,status,thread#,sequence# from v$managed_standby;

v$archived_log

gv$archive_dest

v$database

v$managed_standby

Primary Side:

SQL> select thread#, max(sequence#) "Last Primary Seq Generated" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;

THREAD# Last Primary Seq Generated

---------- --------------------------

1 2915

SQL> SELECT thread#, dest_id, gvad.status, error, fail_sequence FROM gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id = gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id;

THREAD# DEST_ID STATUS

---------- ---------- ---------

ERROR FAIL_SEQUENCE

----------------------------------------------------------------- -------------

1 1 VALID

1 2 VALID 0

SQL>select max(sequence#) from v$log_history;

MAX(SEQUENCE#)

————–

2915

Standby Side:

SQL> select thread#, max(sequence#) "Last Standby Seq Received" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;

THREAD# Last Standby Seq Received

---------- -------------------------

1 2915

SQL> select thread#, max(sequence#) "Last Standby Seq Applied" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# and applied='YES' group by thread# order by 1;

THREAD# Last Standby Seq Applied

---------- ------------------------

1 2915

SQL>select max(sequence#) from v$log_history;

MAX(SEQUENCE#)

————–

2915

SQL> select process,status,thread#,sequence# from v$managed_standby;

PROCESS STATUS THREAD# SEQUENCE#

--------- ------------ ---------- ----------

DGRD ALLOCATED 0 0

ARCH CLOSING 1 2897

DGRD ALLOCATED 0 0

ARCH CLOSING 1 2914

ARCH CLOSING 1 2893

ARCH CLOSING 1 2915

MRP0 APPLYING_LOG 1 2916

RFS IDLE 1 0

RFS IDLE 1 2916

9 rows selected.

Friday, January 29, 2021

Difference Between ASMM & AMM

Difference Between ASMM & AMM

Automatic Shared Memory Management

Automatic Memory Management

About SQL Hints

About SQL Hints

* A hint is an instruction to the optimizer to follow an ‘application developer desired’ execution plan

* Make decisions for the optimizer because you have more in-depth knowledge about data distribution patterns

* In a test or development environments, hints are useful for testing the performance of a specific access path- test use of a specific index when there are several indexes defined on the same table

* Changes in the database or host environment can make hints obsolete and even have negative consequences

* Recommended to use tools like SQL Tuning Advisor, SQL plan management, and SQL Performance Analyzer, to address performance problems not solved by the optimizer before considering hints

How do we gather optimizer statistics?

How do we gather optimizer statistics?

* By default, Oracle Database uses automatic optimizer statistics collection

* Database automatically runs DBMS_STATS to collect optimizer statistics for all schema objects for which statistics are missing or stale

* You can also update and manage optimizer statistics by manually executing DBMS_STATS

* Also, when optimizer statistics are missing, stale, or insufficient, the database automatically gathers dynamic statistics during a parse (depends on level of dynamic sampling)

Automatic Statistics Gathering

Automatic Statistics Gathering

What is An Optimizer ?

What is An Optimizer ?

* Generates the optimal execution plan

* Determines the most efficient method for a SQL statement to access requested data

* The optimizer choose the plan with the lowest cost among all considered candidate plans

* The optimizer uses available statistics to calculate cost

* Cost computation accounts for factors of query execution like I/O , CPU, Network, Memory

Reasons Of Changing Execution Plans ?

Reasons Of Changing Execution Plans ?

* Stale or missing statistics

* Have gathered fresh statistics

* Optimizer engine has changed after a database upgrade

* Index has been added or dropped or Table structure has changed

* Database optimizer related init.ora parameters have been changed

* Parsed representation of the SQL statement is not in the Library Cache – statements are aged out

* Reparsing the same SQL statement now leads to generation of a new plan

Difference Between Execution plan & Explain plan ?

Difference Between Execution plan & Explain plan ?

Execution Plan?

* The execution plan for a SQL statement is a set of instructions.

* Tell the database how to access the data and join it together.

Explain Plan?

* An explain plan predicts how Oracle will process your query

* An execution plan describes the steps it actually took

Pages

Friday, July 16, 2021

Active Session History Details For a Particular Timeframe

Troubleshooting java.sql.SQLException: ORA-28000: The account is locked.

Saturday, June 26, 2021

RMAN Backup Based Duplication

How to start expdp without password

RMAN Active Duplicate

Thursday, June 24, 2021

How to check Standby Status

How to set EBS Version in Profile

Saturday, June 19, 2021

Cloning A Schema

RMAN Complete Backup Status Script

Startup & Shutdown Script

Health Check Script

Below Script To Check Alertlog Errors

Friday, June 18, 2021

Tablespace Monitoring Scripts

Monday, May 3, 2021

Friday, April 30, 2021

Tuesday, April 13, 2021

Monday, April 12, 2021

Wednesday, February 17, 2021

Friday, January 29, 2021