Single Instance DB Upgrade From 12c to 19c

Single Instance DB Upgrade From 12c to 19c

-Oracle 12c database should be up and running and it should be applied with latest patches
-Download latest 19c software's from oracle website.
-Also download latest Oracle Patches for 19c
-Start Installation of 19c software on separate mount point and apply latest patches which was downloaded earlier.

At this stages two separate database software will be ready one is 12cR2 and other will be 19c on two different mount points

High Level Steps:

The Pre-upgrade Information Tool is available in the new release Oracle home, in path ORACLE_HOME/rdbms/admin/preupgrade.jar
mkdir -p /u02/preupgrade
Run the pre-upgrade tool from 12c home referring file of new 19c home

/u01/app/oracle/product/12c/db_1/jdk/bin/java -jar /u02/app/oracle/product/19c/db_1/rdbms/admin/preupgrade.jar DIR /u02/preupgrade

EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

purge dba_recyclebin;
purge recyclebin;

select name,open_mode,log_mode from v$database;
@/u02/preupgrade/preupgrade_fixups.sql

Performing the pre-upgrade actions

Run the preupgrade_fixups.sql

==> Need to resize datafile as per the recommendations

select file_name from dba_data_files;

alter database datafile '/u01/app/oradata/PROD/datafile/o1_mf_system_j87d12w1_.dbf' resize 1g; 

alter database datafile '/u01/app/oradata/PROD/datafile/o1_mf_sysaux_j87d2j6s_.dbf' resize 700m; alter database datafile '/u01/app/oradata/PROD/datafile/o1_mf_undotbs1_j87d3mbh_.dbf' resize 600m; 

select file_name from dba_temp_files;
alter database tempfile '/u01/app/oradata/PROD/datafile/o1_mf_temp_j87d525p_.tmp' resize 300m;

==>Create restore point before_upgrade_19c guarantee flashback database;

select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from v$restore_point;

sqlplus / as sysdba
shutdown immediate;
exit

Copy the spfile, password file and listener file from old ORACLE_HOME to new 19c ORACLE_HOME

cp orapwtest spfiletest.ora /u02/app/oracle/product/19c/db_1/dbs
ls -lrt /u02/app/oracle/product/19c/db_1/dbs
-rw-r--r--. 1 oracle oinstall 3079 May 14 2015 init.ora
-rw-r-----. 1 oracle oinstall 7680 Sep 23 21:12 orapwtest
-rw-r-----. 1 oracle oinstall 3584 Sep 23 21:12 spfiletest.ora

Stop listener running on 12c home and start it from 19c home. Start the database from 19c ORACLE_HOME and start the upgrade.

sqlplus / as sysdba
startup upgrade;
select name,open_mode,status from v$database, v$instance;

Run the DB Upgrade utility

$ORACLE_HOME/bin/dbupgrade -n 8

-Check output and wait for 30 min.
-Check the upgrade summary log,
-Startup database manually and

startup;
SELECT name, open_mode, status, version from v$database, v$instance;
@$ORACLE_HOME/rdbms/admin/utlusts.sql TEXT
Recompile the INVALID Objects using utlrp.sql @$ORACLE_HOME/rdbms/admin/utlrp.sql

Connect to sqlplus and run the postupgrade_fixups.sql script

sqlplus / as sysdba
@/u02/preupgrade/postupgrade_fixups.sql

Upgrade the timezone file version

sqlplus / as sysdba
SELECT version FROM v$timezone_file;

@$ORACLE_HOME/rdbms/admin/utltz_countstats.sql;
@$ORACLE_HOME/rdbms/admin/utltz_countstar.sql;
@$ORACLE_HOME/rdbms/admin/utltz_upg_check.sql;
@$ORACLE_HOME/rdbms/admin/utltz_upg_apply.sql;

SELECT version FROM v$timezone_file;
Gather statistics on fixed objects


Connect to sqlplus as sys user and execute
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS

SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

Now re-run the postupgrade_fixups.sql
select name from v$restore_point;
drop restore point BEFORE_UPGRADE_19C;


Update the compatible parameter:
alter system set compatible='19.0.0' scope=spfile;

shutdown immediate;
startup;
show parameter compatible;
select name, open_mode, version from v$database, v$instance;
select name,open_mode,database_role from V$database;
show parameter cluster

set lines 200 pages 500
col owner for a15
col OBJECT_NAME for a35
select owner,object_name,object_type,status from dba_objects where status='INVALID';

col COMP_NAME for a40
select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;

col ACTION_TIME for a30
col action for a30
col version for a10
col BUNDLE_SERIES for a30
col COMMENTS for a47
select ACTION_TIME,ACTION,VERSION,BUNDLE_SERIES,COMMENTS from dba_registry_history;

Database Upgrade Using AutoUpgrade Option (12c to 19c)

Database Upgrade Using AutoUpgrade Option (12c to 19c)

Database Upgrade Using Autoupgrade

How to check the block corruptions in database ?

How to check the block corruptions in database ?

RMAN Level:

RMAN> backup validate check logical datafile <<<file_number>>>;

RMAN> backup validate check logical database;

SQLPLUS Level:

sqlplus / as sysdba

select * from v$database_block_corruption;

OS Level:

The following example shows a sample use of the command-line interface to this mode of DBVERIFY.

% dbv FILE=t_db1.dbf feedback=100 >replace the file

dbv FILE=/u05/oradb/TEST/db/apps_st/data/eqddasm.dbf FEEDBACK=100

Manual Switchover Activity in Oracle Database 12c

Manual Switchover In Oracle Database (12c)

Switchover Steps

Primary Database:

tnsping QPROD

tnsping QPRODR

echo $ORACLE_SID

sqlplus / as sysdba

SQL> select name,status,database_role,open_mode from v$instance,v$database;

SQL> select switchover_status from v$database;

SQL> alter database commit to switchover to physical standby with session shutdown;

SQL> shutdown immediate;

SQL> startup mount;

SQL> select name,instance_name,status,database_role,open_mode from v$instance,v$database;

SQL> recover managed standby database disconnect from session;

SQL> !ps -ef | grep mrp

SQL> !lsnrctl status

Standby Database:

tnsping QPROD

tnsping QPRODR

echo $ORACLE_SID

sqlplus / as sysdba

SQL> select name,instance_name,status,database_role,open_mode from v$instance,v$database;

SQL> alter database commit to switchover to primary;

SQL> select name,instance_name,status,database_role,open_mode from v$instance,v$database;

SQL> alter database open;

SQL> select name,instance_name,status,database_role,open_mode from v$instance,v$database;

SQL> !lsnrctl status

Switchback Steps:

Follow same steps in reverse order.

Actual Steps:

PRIMARY:

Check the database status:

SQL> select name,status,database_role,open_mode from v$instance,v$database;

NAME      STATUS       DATABASE_ROLE    OPEN_MODE

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

QPROD      OPEN         PRIMARY          READ WRITE

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)

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

         1          23326

SQL>

Check the sync status:

SQL> @sync_p.sql

   THREAD# MAX(SEQUENCE#)

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

         1          23307

Check the switchover status:

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

TO STANDBY

Issue the command:

SQL> alter database commit to switchover to physical standby with session shutdown;

Database altered.

SQL> shut immediate;

ORA-01012: not logged on

SQL> exit

Make sure the background processes of primary is down.

ps -ef|grep pmon

Startup the database mount state:

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 3.2212E+10 bytes

Fixed Size                  5292336 bytes

Variable Size            4294975184 bytes

Database Buffers         2.7850E+10 bytes

Redo Buffers               61808640 bytes

SQL> alter database mount;

Database altered.

SQL> select name,instance_name,status,database_role,open_mode from v$instance,v$database;

NAME      INSTANCE_NAME    STATUS       DATABASE_ROLE    OPEN_MODE

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

QPROD        QPROD        MOUNTED      PHYSICAL STANDBY MOUNTED

SQL> recover managed standby database disconnect from session;

Media recovery complete.

SQL> !ps -ef|grep mrp

oraprod  5060  4931   0 22:22:09 pts/1       0:00 /usr/bin/bash -c ps -ef|grep mrp

oraprod  5062  5060   0 22:22:09 pts/1       0:00 grep mrp

oraprod  5025     1   0 22:21:54 ?           0:03 ora_mrp0_QPROD

Standby:

Check the database status:

SQL> select name,instance_name,status,database_role,open_mode from v$instance,v$database;

NAME      INSTANCE_NAME    STATUS       DATABASE_ROLE    OPEN_MODE

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

QPROD     QPRODR           MOUNTED      PHYSICAL STANDBY MOUNTED

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)

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

         1          23326

Check the sync status:

SQL> @sync_s.sql

    Thread Last Sequence Received Last Sequence Applied Difference

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

         1                  23307                 23307          0

Issue the command:

SQL> alter database commit to switchover to primary;

Database altered.

Open the database:

SQL> alter database open;

Database altered.

Check the database status:

SQL> select name,instance_name,status,database_role,open_mode from v$instance,v$database;

NAME      INSTANCE_NAME    STATUS       DATABASE_ROLE    OPEN_MODE

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

QPROD     QPRODR           OPEN         PRIMARY          READ WRITE

SQL> alter system switch logfile;

System altered.

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.

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

Copyright (c) 2014, Oracle.  All rights reserved.

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

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.

Different Ways To Check The Tablespaces Size

Different Ways To Check The Tablespace Size

 

Script 1.

set colsep |
set linesize 100 pages 100 trimspool on numwidth 14
col name format a25
col owner format a15
col "Used (GB)" format a15
col "Free (GB)" format a15
col "(Used) %" format a15
col "Size (M)" format a15

SELECT d.status "Status", d.tablespace_name "Name", TO_CHAR(NVL(a.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Size (GB)", TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024 /1024,'99999999.99') "Used (GB)", TO_CHAR(NVL(f.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Free (GB)", TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "(Used) %" FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a, (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY') UNION ALL SELECT d.status "Status", d.tablespace_name "Name", TO_CHAR(NVL(a.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Size (GB)", TO_CHAR(NVL(t.bytes,0)/1024/1024 /1024,'99999999.99') "Used (GB)", TO_CHAR(NVL((a.bytes -NVL(t.bytes, 0)) / 1024 / 1024 /1024, 0),'99,999,990.90') "Free (GB)",TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "(Used) %" FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a, (select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY';

 

Script 2.

 

set lines 135
column TABLESPACE_NAME for a20
column FILE_NAME for a93
set lines 220 pages 220

 SELECT df.tablespace_name, df.size_mb, f.free_mb, df.max_size_mb, f.free_mb + (df.max_size_mb - df.size_mb) AS max_free_mb, df.file_name FROM   (SELECT file_id, file_name, tablespace_name, TRUNC(bytes/1024/1024) AS size_mb, TRUNC(GREATEST(bytes,maxbytes)/1024/1024) AS max_size_mb FROM   dba_data_files) df, (SELECT TRUNC(SUM(bytes)/1024/1024) AS free_mb, file_id FROM dba_free_space GROUP BY file_id) f WHERE  df.file_id = f.file_id (+) ORDER BY df.tablespace_name, df.file_name;

                                 

Script 3.                                 

                                 

select fs.tablespace_name "Tablespace", (df.totalspace - fs.freespace) "Used MB", fs.freespace "Free MB", df.totalspace "Total MB", round(100 * (fs.freespace / df.totalspace)) "Pct. Free" from (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace from dba_data_files group by tablespace_name ) df, (select tablespace_name, round(sum(bytes) / 1048576) FreeSpace from dba_free_space group by tablespace_name ) fs where df.tablespace_name = fs.tablespace_name order by 1;

  

Script 4.  

 

set pages 80
set lin 120
set echo off
set feed off
column PCT format 999.99
column tbspce format A30
col container for a30

select substr(f.tablespace_name,1,30) tbspce, round(f.tsbytes/(1024*1024),0) "ALLOCATED(MB)", round(nvl(s.segbytes,0)/(1024*1024),0) "USED(MB)", round((nvl(s.segbytes,0)/f.tsbytes)*100,2) PCT, lower(vc.name) as container from (select con_id,tablespace_name,sum(bytes) tsbytes from cdb_data_files group by con_id,tablespace_name) f, (select con_id,tablespace_name,sum(bytes) segbytes from cdb_segments group by con_id,tablespace_name) s, v$containers vc where f.con_id=s.con_id(+) and f.tablespace_name=s.tablespace_name(+) and f.con_id=vc.con_id order by container, tbspce;

 

Script 5.

 

set pages 999
set lines 400

SELECT df.tablespace_name tablespace_name, max(df.autoextensible) auto_ext, round(df.maxbytes / (1024 * 1024), 2) max_ts_size,round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,round(df.bytes / (1024 * 1024), 2) curr_ts_size,round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size, nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free FROM dba_free_space fs, (select tablespace_name, sum(bytes) bytes, sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes, max(autoextensible) autoextensible from dba_data_files group by tablespace_name) df WHERE fs.tablespace_name (+) = df.tablespace_name GROUP BY df.tablespace_name, df.bytes, df.maxbytes UNION ALL SELECT df.tablespace_name tablespace_name, max(df.autoextensible) auto_ext, round(df.maxbytes / (1024 * 1024), 2) max_ts_size, round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used, round(df.bytes / (1024 * 1024), 2) curr_ts_size, round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size, round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used, round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size, nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free FROM (select tablespace_name, bytes_used bytes from V$temp_space_header group by tablespace_name, bytes_free, bytes_used) fs, (select tablespace_name, sum(bytes) bytes, sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes, max(autoextensible) autoextensible from dba_temp_files group by tablespace_name) df WHERE fs.tablespace_name (+) = df.tablespace_name GROUP BY df.tablespace_name, df.bytes, df.maxbytes ORDER BY 4 DESC;

Administering the DDL Log Files in 12c

Administering the DDL Log Files in 12c

The DDL log is created only if the ENABLE_DDL_LOGGING initialization parameter is set to TRUE. When this parameter is set to FALSE, DDL statements are not included in any log. A subset of executed DDL statements is written to the DDL log.

How to administer the DDL Log?

--> Enable the capture of certain DDL statements to a DDL log file by setting ENABLE_DDL_LOGGING to TRUE.

--> DDL log contains one log record for each DDL statement.

--> Two DDL logs containing the same information:

--> XML DDL log: named log.xml

--> Text DDL: named ddl_<sid>.log

When ENABLE_DDL_LOGGING is set to true, the following DDL statements are written to the log:

ALTER/CREATE/DROP/TRUNCATE CLUSTER

ALTER/CREATE/DROP FUNCTION

ALTER/CREATE/DROP INDEX

ALTER/CREATE/DROP OUTLINE

ALTER/CREATE/DROP PACKAGE

ALTER/CREATE/DROP PACKAGE BODY

ALTER/CREATE/DROP PROCEDURE

ALTER/CREATE/DROP PROFILE

ALTER/CREATE/DROP SEQUENCE

CREATE/DROP SYNONYM

ALTER/CREATE/DROP/RENAME/TRUNCATE TABLE

ALTER/CREATE/DROP TRIGGER

ALTER/CREATE/DROP TYPE

ALTER/CREATE/DROP TYPE BODY

DROP USER

ALTER/CREATE/DROP VIEW

Example

$ more ddl_orcl.log

Thu Nov 15 08:35:47 2012

diag_adl:drop user app_user

Locate the DDL Log File

$ pwd

/u01/app/oracle/diag/rdbms/orcl/orcl/log

$ ls

ddl ddl_orcl.log debug test

$ cd ddl

$ ls

log.xml

Notes: 

- Setting the ENABLE_DDL_LOGGING parameter to TRUE requires licensing the Database Lifecycle Management Pack.

- This parameter is dynamic and you can turn it on/off on the go.

- alter system set ENABLE_DDL_LOGGING=true/false;

Creating a Services In Oracle RAC

Creating a Services In Oracle RAC

To check the configuration:

srvctl config database -d RUMPROD

To create a new service:

srvctl add service -d RUMPROD -s QSHO -r RUMPROD1,RUMPROD2

To start a service on one node:

srvctl start service -d RUMPROD -s QRUM -n test02 -o open

To start a service on all nodes:

srvctl start service -d RUMPROD -s QSHO -o open

To stop service in one node/ instance:

srvctl stop service -d RUMPROD -s QSHO -n test01

To stop service in all nodes/ instances:

srvctl stop service -d RUMPROD -s QSHO

To remove a service:

Oracle Best practices is to frirst stop the service, then drop it.

srvctl stop service -d RUMPROD -s QSHO

srvctl remove service -d RUMPROD -s QSHO

To check status of database and list services:

srvctl status database -d db_unique_name -v

Or using View :

SQL> select * from gv$active_services;

ASH (Active Session History) Analysis

How To Generate ASH (Active Session History) Report

To generate ASH report:

@$ORACLE_HOME/rdbms/admin/ashrpt.sql

The report provides below areas:

1. Top User Events

2. Top Service/Module

3. Top SQL Command Types

4. Top Sessions

5. Top Blocking Sessions

6. Top DB Objects

7. Top Phases of Execution

8. Top PL/SQL Procedures

9. Top SQL With Top Row Sources

11. Complete list of SQL text

12. Activity Over Time

How To Generate Explain Plan In Oracle

How To Generate Explain Plan In Oracle

1.Generating explain plan for a sql query:

We will generate the explain plan for the query "select * from test.qader_t1;"

LOADING THE EXPLAIN PLAN TO PLAN_TABLE

SQL> explain plan for select * from test.qader_t1;

 DISPLAYING THE EXPLAIN PLAN

SQL> select * from table(dbms_xplan.display);

2. Explain plan for a sql_id from cursor

set lines 2000

set pagesize 2000

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));

3. Explain plan of a sql_id from AWR:

SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('&sql_id'));

Above will display the explain plan for all the plan_hash_value in AWR. If you wish to see the plan for a particular plan_hash_value.

SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('&sql_id',&plan_hash_value));

 

How to Stop and Start Oracle RAC Services on Single Node

How to Stop and Start Oracle RAC Services on Single Node

1) Disable the automatic startup of the Oracle Clusterware software:

crsctl disable has

2) Stop the Oracle clusterware stack:

crsctl stop has

3) Once clusterware Services has been stopped we will restart those Services:

crsctl enable has

4) Enable the automatic startup of the Oracle Clusterware software:

crsctl start has

Check the status:

ps -ef|grep d.bin|wc -l

ps -ef|grep pmon

5) Start the Oracle clusterware stack:

crsctl start crs

6)Check the status of Oracle Clusterware Resources:

crsctl stat res -t

Long Running Sessions in Oracle

Long Running Sessions in Oracle

SELECT SID, SERIAL#,OPNAME, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;

(or)

set lines 300

col TARGET for a40

col SQL_ID for a20

select SID,TARGET||OPNAME TARGET, TOTALWORK, SOFAR,TIME_REMAINING/60 Mins_Remaining,ELAPSED_SECONDS,SQL_ID from v$session_longops where TIME_REMAINING>0 order by TIME_REMAINING; 

Above output you can further check the sql_id, sql_text and the wait event for which query is waiting

TO find out sql_id for the above sid:

SQL> select sql_id from v$session where sid='&SID';

To find sql text for the above sql_id:

SQL> select sql_fulltext from V$sql where sql_id='1uksqt2vzxbz5';

To find wait event of the query for which it is waiting for:

SQL>select sql_id, state, last_call_et, event, program, osuser from v$session where sql_id='&sql_id';

Recover dropped tablespace using RMAN tablespace point in time recovery (TSPITR)

Recover dropped tablespace using RMAN tablespace point in time recovery (TSPITR)

Step 1: Create tablespace

SQL> conn / as sysdba

Connected.

SQL> select name from v$datafile;

NAME

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

/u01/app/oracle/oradata/QTEST/system01.dbf

/u01/app/oracle/oradata/QTEST/sysaux01.dbf

/u01/app/oracle/oradata/QTEST/undotbs01.dbf

/u01/app/oracle/oradata/QTEST/users01.dbf

SQL> create tablespace testtbs datafile '/u01/app/oracle/oradata/QTEST/testtbs01.dbf' size 100m;

Tablespace created.

SQL> create user testtbs identified by testtbs default tablespace testtbs;

User created.

SQL> grant dba to testtbs;

Grant succeeded.

SQL> conn testtbs/testtbs;

Connected.

SQL> create table test(empname varchar2(20),city varchar2(20));

Table created.

SQL> insert into test values('qader','bahrain');

1 row created.

SQL> insert into test values('kadar','manama');

1 row created.

SQL> commit;

Step 2: Backup database Plus archivelog

[oracle@node2 ~]$ rman target sys/Welcome1

Recovery Manager: Release 12.1.0.2.0 - Production on Sat Aug 29 01:18:39 2020

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: QTEST (DBID=2589001473)

RMAN> backup database plus archivelog;

Step 3: Note Current SCN

SQL> conn / as sysdba

Connected.

SQL> SQL> select current_scn from v$database;

CURRENT_SCN

-----------

    1744628

Step 4: Drop tablespace

SQL> drop tablespace testtbs including contents and datafiles;

Tablespace dropped.

Step 5: Create auxiliary & Recover tablespace using tablespace point in time recovery.

[oracle@node2 ~]$ rman target sys/Welcome1

Recovery Manager: Release 12.1.0.2.0 - Production on Sat Aug 29 01:29:27 2020

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: QTEST (DBID=2589001473)

RMAN> run

{

recover tablespace testtbs

until scn 1744628

auxiliary destination '/u01/app/oracle/fast_recovery_area/QTEST'; ------> backup pieces location

}

initialization parameters used for automatic instance:

db_name=QTEST

db_unique_name=hsbr_pitr_QTEST

compatible=12.1.0.2.0

db_block_size=8192

db_files=200

diagnostic_dest=/u01/app/oracle

_system_trig_enabled=FALSE

sga_target=1168M

processes=200

db_create_file_dest=/u01/app/oracle/fast_recovery_area/QTEST

log_archive_dest_1='location=/u01/app/oracle/fast_recovery_area/QTEST'

#No auxiliary parameter file used

starting up automatic instance QTEST

Oracle instance started

Total System Global Area    1224736768 bytes

Fixed Size                     2923824 bytes

Variable Size                335545040 bytes

Database Buffers             872415232 bytes

Redo Buffers                  13852672 bytes

Automatic instance created

List of tablespaces that have been dropped from the target database:

Tablespace TESTTBS

contents of Memory Script:

{

# set requested point in time

set until  scn 1744628;

sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';

}

executing Memory Script

executing command: SET until clause

Starting restore at 29-AUG-20

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=23 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/fast_recovery_area/QTEST/QTEST/controlfile/o1_mf_hnm1rzmd_.ctl

Finished restore at 29-AUG-20

contents of Memory Script:

{

# set requested point in time

 "/u01/app/oracle/oradata/QTEST/testtbs01.dbf";

# switch all tempfiles

switch clone tempfile all;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile  1, 4, 3, 5;

 switch clone datafile all;

}

executing Memory Script

renamed tempfile 1 to /u01/app/oracle/fast_recovery_area/QTEST/QTEST/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 29-AUG-20

archived log file name=/u01/app/oracle/fast_recovery_area/QTEST/archivelog/2020_08_29/o1_mf_1_37_hnm1rjny_.arc thread=1 sequence=37

media recovery complete, elapsed time: 00:00:00

Finished recover at 29-AUG-20

database opened

contents of Memory Script:

{

sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''

Performing export of metadata...

   EXPDP> Starting "SYS"."TSPITR_EXP_hsbr_sAEw":  

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

   EXPDP> Master table "SYS"."TSPITR_EXP_hsbr_sAEw" successfully loaded/unloaded

   EXPDP> ******************************************************************************

   EXPDP> Dump file set for SYS.TSPITR_EXP_hsbr_sAEw is:

   EXPDP>   /u01/app/oracle/fast_recovery_area/QTEST/tspitr_hsbr_12180.dmp

   EXPDP> ******************************************************************************

   EXPDP> Datafiles required for transportable tablespace TESTTBS:

   EXPDP>   /u01/app/oracle/oradata/QTEST/testtbs01.dbf

   EXPDP> Job "SYS"."TSPITR_EXP_hsbr_sAEw" successfully completed at Sat Aug 29 04:09:12 2020 elapsed 0 00:01:11

Export completed

contents of Memory Script:

# shutdown clone before import

shutdown clone abort

executing Memory Script

Oracle instance shut down

Performing import of metadata...

   IMPDP> Master table "SYS"."TSPITR_IMP_hsbr_rhej" successfully loaded/unloaded

   IMPDP> Starting "SYS"."TSPITR_IMP_hsbr_rhej":  

   IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE

   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

   IMPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER

   IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

   IMPDP> Job "SYS"."TSPITR_IMP_hsbr_rhej" successfully completed at Sat Aug 29 04:09:53 2020 elapsed 0 00:00:32

Import completed

contents of Memory Script:

auxiliary instance file /u01/app/oracle/fast_recovery_area/QTEST/QTEST/onlinelog/o1_mf_1_hnm1sp84_.log deleted

auxiliary instance file /u01/app/oracle/fast_recovery_area/QTEST/QTEST/datafile/o1_mf_sysaux_hnm1s60l_.dbf deleted

auxiliary instance file /u01/app/oracle/fast_recovery_area/QTEST/QTEST/datafile/o1_mf_undotbs1_hnm1s60s_.dbf deleted

auxiliary instance file /u01/app/oracle/fast_recovery_area/QTEST/QTEST/datafile/o1_mf_system_hnm1s60g_.dbf deleted

auxiliary instance file /u01/app/oracle/fast_recovery_area/QTEST/QTEST/controlfile/o1_mf_hnm1rzmd_.ctl deleted

auxiliary instance file tspitr_hsbr_12180.dmp deleted

Finished recover at 29-AUG-20

Data Guard Physical Standby Setup Using Active Duplicate

Data Guard Physical Standby Setup Using Active Duplicate

VM Machine Details:

node1.oracle.com (147.43.0.15)

node2.oracle.com (147.43.0.16)

Note:

1. Both VM machines should be ping each, Network adapter setting choose "Host-Only".

2. Configure or add both machine ip addresses in /etc/hosts file.

3. Check or test the connectivity using ping or ssh commands.

Database Details:

Database Name :- PRODUAT

Primary db_unique_name :- PRODUAT

standby db_unique_name :- PRODSIT

Steps:

1. Ensure that the database is in archivelog mode.

SQL> select log_mode from v$database;

LOG_MODE

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

ARCHIVELOG

2. Enable force logging.

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

-- Make sure at least one logfile is present.

ALTER SYSTEM SWITCH LOGFILE;

SQL> select force_logging from v$database;

FORCE_LOGGING

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

YES

3. Create standby redologs.

Minimally, the configuration should have one more standby redo log file group than the number of online redo log file groups on the primary database. However, the recommended number of standby redo log file groups is dependent on the number of threads on the primary database. Use the following equation to determine an appropriate number of standby redo log file groups:

(maximum number of logfiles for each thread + 1) * maximum number of threads

SQL> select bytes from v$standby_log;

no rows selected

SQL> SELECT * FROM V$LOGFILE;

SQL> select group#,thread#,bytes from v$log;

    GROUP#    THREAD#      BYTES

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

         1          1   52428800

         2          1   52428800

         3          1   52428800

alter database add standby logfile '/u01/app/oracle/fast_recovery_area/PRODUAT/stbyredo03.log' size 50M;

alter database add standby logfile '/u01/app/oracle/fast_recovery_area/PRODUAT/stbyredo04.log' size 50M;

alter database add standby logfile '/u01/app/oracle/fast_recovery_area/PRODUAT/stbyredo05.log' size 50M;

SQL> select bytes from v$standby_log;

     BYTES

----------

  52428800

  52428800

  52428800

  52428800

Note: We no need to create standby redo log files on standby and Oracle take cares of it during RMAN duplicate. 

4. Modify the primary initialization parameter for dataguard on primary.

SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRODUAT,PRODSIT)';

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/fast_recovery_area/PRODUAT/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRODUAT';

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=PRODSIT LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRODSIT';

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;

System altered.

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

System altered.

SQL> alter system set FAL_SERVER=PRODSIT;

System altered.

SQL> alter system set FAL_CLIENT=PRODUAT;

System altered.

SQL> alter system set DB_FILE_NAME_CONVERT='PRODUAT','PRODSIT' scope=spfile;                System altered.

SQL> alter system set LOG_FILE_NAME_CONVERT='PRODUAT','PRODSIT' scope=spfile;

System altered.

5. Create the necessary directories on the standby server.

mkdir -p /u01/app/oracle/oradata/PRODSIT

chown -R oracle:oinstall /u01/app/oracle/oradata/PRODSIT

6. Configure Oracle net service/TNS names for standby system using NETCA or NETMGR 

Make sure tnsnames.ora file contains both entries in Primary and Standby servers

Primary:

[oracle@node1 admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/MTEST/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

PRODUAT =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = node1.oracle.com)(PORT = 1522))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = PRODUAT)

    )

  )

PRODSIT =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = node2.oracle.com)(PORT = 1522))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = PRODSIT)

    )

  )

Standby:

[oracle@node2 admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

PRODSIT =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = node2.oracle.com)(PORT = 1522))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = PRODSIT)

    )

  )

PRODUAT =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = node1.oracle.com)(PORT = 1522))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = PRODUAT)

    )

  )

7. Check with the SQL*Net configuration using the following commands on the Primary and Standby

tnsping MQMPROD

tnsping MQMDR

8. Create the standby database

-Copy the password file from the primary $ORACLE_HOME/dbs and rename it to the standby database name.

-Create a initialization parameter with only one parameter DB_NAME.

DB_NAME=PRODUAT

DB_UNIQUE_NAME=PRODSIT

compatible='12.1.0.2.0'

log_file_name_convert='PRODUAT','PRODSIT'

9. Create the necessary directories in the standby location to place database files and trace files ($ADR_HOME)

mkdir -p /u01/app/oracle/admin/MQMDR/adump

10. Set the environment variable ORACLE_SID to the standby service and start the standby-instance.

export ORACLE_SID=MQMDR

sqlplus "/ as sysdba"

SQL> startup nomount pfile=$ORACLE_HOME/dbs/initPRODSIT.ora

11. Verify if the connection 'AS SYSDBA' is working

sqlplus /nolog

SQL> connect sys/Welcome1@MQMDR AS SYSDBA

Connected.

SQL> connect sys/Welcome1@MQMPROD AS SYSDBA

Connected.

12. Connect to RMAN, specifying a full connect string for both the TARGET and AUXILIARY instances ( Issue on Standby )

Connect RMAN using target and auxiliary should connected as “not mounted” only.

[oracle@node2 ~]$ rman target sys/Welcome1@PRODUAT auxiliary sys/Welcome1@PRODSIT

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Aug 28 05:23:39 2020

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PRODUAT (DBID=1381890412)

connected to auxiliary database: PRODUAT (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck dorecover;

An explanation of the above RMAN command.

FOR STANDBY: This tells the DUPLICATE command is to be used for a standby, so it will not force a DBID change.

FROM ACTIVE DATABASE: The DUPLICATE will be created directly from the source datafile, without an additional backup step.

DORECOVER: The DUPLICATE will include the recovery step, bringing the standby up to the current point in time.

NOFILENAMECHECK: Destination file locations are not checked.

Once the command is complete, we can start the apply process.

Output:

Starting Duplicate Db at 28-AUG-20

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=23 device type=DISK

current log archived

contents of Memory Script:

{

   backup as copy reuse

   targetfile  '/u01/MTEST/dbs/orapwPRODUAT' auxiliary format 

 '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwPRODSIT'   ;

}

executing Memory Script

Starting backup at 28-AUG-20

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=55 device type=DISK

Finished backup at 28-AUG-20

contents of Memory Script:

{

   restore clone from service  'PRODUAT' standby controlfile;

}

executing Memory Script

Starting restore at 28-AUG-20

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service PRODUAT

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03

output file name=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/cntrlPRODSIT.dbf

Finished restore at 28-AUG-20

contents of Memory Script:

{

   sql clone 'alter database mount standby database';

}

executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:

{

   set newname for tempfile  1 to 

 "/u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_temp_hnkbdtkj_.tmp";

   switch clone tempfile all;

   set newname for datafile  1 to 

 "/u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_system_hnkb8mqx_.dbf";

   set newname for datafile  3 to 

 "/u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_sysaux_hnkb68cx_.dbf";

   set newname for datafile  4 to 

 "/u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_undotbs1_hnkbcn7x_.dbf";

   set newname for datafile  6 to 

 "/u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_users_hnkbcm4m_.dbf";

   restore

   from service  'PRODUAT'   clone database

   ; sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_temp_hnkbdtkj_.tmp in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 28-AUG-20

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service PRODUAT

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_system_hnkb8mqx_.dbf

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service PRODUAT

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_sysaux_hnkb68cx_.dbf

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service PRODUAT

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_undotbs1_hnkbcn7x_.dbf

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service PRODUAT

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_users_hnkbcm4m_.dbf

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 28-AUG-20

sql statement: alter system archive log current

current log archived

contents of Memory Script:

{

   restore clone force from service  'PRODUAT' 

           archivelog from scn  1618367;

   switch clone datafile all;

}

executing Memory Script

Starting restore at 28-AUG-20

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting archived log restore to default destination

channel ORA_AUX_DISK_1: using network backup set from service PRODUAT

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=14

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

channel ORA_AUX_DISK_1: starting archived log restore to default destination

channel ORA_AUX_DISK_1: using network backup set from service PRODUAT

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=15

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 28-AUG-20

datafile 1 switched to datafile copy

input datafile copy RECID=1 STAMP=1049648103 file name=/u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_system_hnkb8mqx_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=2 STAMP=1049648103 file name=/u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_sysaux_hnkb68cx_.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=3 STAMP=1049648103 file name=/u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_undotbs1_hnkbcn7x_.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=4 STAMP=1049648103 file name=/u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_users_hnkbcm4m_.dbf

contents of Memory Script:

{

   set until scn  1618687;

   recover

   standby

   clone database

    delete archivelog

   ;}

executing Memory Script

executing command: SET until clause

Starting recover at 28-AUG-20

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 14 is already on disk as file /u01/app/oracle/product/12.1.0/dbhome_1/dbs/arch1_14_1049631726.dbf

archived log for thread 1 with sequence 15 is already on disk as file /u01/app/oracle/product/12.1.0/dbhome_1/dbs/arch1_15_1049631726.dbf

archived log file name=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/arch1_14_1049631726.dbf thread=1 sequence=14

archived log file name=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/arch1_15_1049631726.dbf thread=1 sequence=15

media recovery complete, elapsed time: 00:00:00

Finished recover at 28-AUG-20

Finished Duplicate Db at 28-AUG-20

12. Start managed recovery

Connect to standby using SQL*Plus and start the MRP (Managed Recovery Process). Compare the primary last sequence and MRP (Managed Recovery Process) applying sequence.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select NAME,CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE from v$database;

NAME      CONTROL OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE

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

PRODUAT   STANDBY MOUNTED              PHYSICAL STANDBY MAXIMUM PERFORMANCE

SQL> host

[oracle@node2 ~]$ ps -ef|grep mrp

oracle   10077     1  0 17:17 ?        00:00:02 ora_mrp0_PRODSIT

oracle   10158 10129  0 17:23 pts/1    00:00:00 grep mrp

The Managed Recovery Process (MRP) applies information from the archived redo logs to the standby database. When performing managed recovery operations, log apply services automatically apply archived redo logs to maintain transactional synchronization with the primary database.

Alertlog:

Fri Aug 28 17:17:52 2020

alter database recover managed standby database disconnect from session

Fri Aug 28 17:17:52 2020

Attempt to start background Managed Standby Recovery process (PRODSIT)

Starting background process MRP0

Fri Aug 28 17:17:52 2020

MRP0 started with pid=21, OS id=10077 

Fri Aug 28 17:17:52 2020

MRP0: Background Managed Standby Recovery process started (PRODSIT)

Fri Aug 28 17:17:57 2020

Serial Media Recovery started

Managed Standby Recovery starting Real Time Apply

Fri Aug 28 17:17:57 2020

Waiting for all non-current ORLs to be archived...

Fri Aug 28 17:17:57 2020

All non-current ORLs have been archived.

Media Recovery Waiting for thread 1 sequence 16

Completed: alter database recover managed standby database disconnect from session

Note:

If you face any SYNC issue, try to set the standby related parameters.

Useful TFACTL Commands

Useful TFACTL Commands

1. Check tfactl status with version:

tfactl status

2. Check tfactl tool status:

tfactl toolstatus

3. Get config details:

tfactl print config

4. List of user having access to tfactl:

ORACLE DBA

DBA SCRIPTS

POSTGRES SCRIPTS

ANSIBLE

5.Adding or removing users from access list of tfactl:

tfactl access add -user rpdtro

tfactl access remove -user rdptro

6. change port number for tfactl:

tfactl set port=5001

 NOTE – make sure to restart the tfactl after port change.

7. Stop/ start tfactl:

tfactl stop

tfactl start

8. Enable /disable autostart of tfactl upon reboot:

tfactl disable

tfactl enable

 9. Find tfactl version with simple command:

tfactl version

AHF VERSION: 20.2.0

10. Collect diagnostic report 🙁 pass the time of incident in YYYY-MM-DD HH24:MI:SS)

tfactl diagcollect -all

11. Get notificationaddress email:

tfactl get notificationAddress

12. Change notification address email:

tfactl set notificationAddress=oracle:admin@dbaclass.com

13. Generate summary report :

tfactl summary

 -- Genearate complete summary overview in html

tfactl summary -html

 -- Generate patching summary:

 tfactl summary -patch -html

 -- Generate asm summary

tfactl summary -asm -html

13.view smtp details :

tfactl print smtp

14. Manage logs using tfactl managelogs:

tfactl  managelogs -show usage

 15. Purge old logs:

-- This is just a dry run:

tfactl managelogs -purge -older 5d -dryrun

 -- This will actually delete the logs older than 5 days

tfactl managelogs -purge -older 5d

 -- Delete only GI logs:

tfactl managelogs -purge -gi 5d

 tfactl run managelogs -purge -older 5d -gi

 -- Delete only database logs:

tfactl run managelogs -purge -older -5d -database

16. Get repository location and usage:

tfactl print repository

17. Get component details:

tfactl print components

18. Find diag collection details of tfactl:

tfactl print collections

19. Verify email/smtp configuration.

tfactl sendmail support@oracle.com

How To Generate AWR Report In Oracle

How To Generate AWR Report In Oracle

We can generate awr report for a particular time frame in the past using the script awrrpt.sql ( located under $ORACLE_HOME/rdbms/admin)

script – @$ORACLE_HOME/rdbms/admin/awrrpt.sql

For NON-SYSDBA USERS, BELOW GRANTS ARE REQUIRED TO GENERATE AWR REPORT:

SQL> grant connect,SELECT_CATALOG_ROLE to MQM;

SQL>  grant execute on dbms_workload_repository to MQM;

Note:

AWR report can be generating in RAC database using 2 scripts awrrpt.sql or awrrpti.sql

awrrpt.sql – > This will generate the one report for the database across all the nodes(i.e for all instances) for a partiular snapshot range.

awrrpti.sql – > This will genereate report for a particular instance, i.e for a 2 node RAC database , there will be two reports( one for each instance).

Following are the scripts that can be executed as sysdba in order to get the AWR, ASH and ADDM reports on Oracle RAC:

SQL script for getting AWR Report on RAC database:

SQL>@$ORACLE_HOME/rdbms/admin/awrgrpt.sql

SQL script for getting AWR Report for  single instance:

SQL>@$ORACLE_HOME/rdbms/admin/awrrpt.sql

SQL script for getting ASH Report on RAC database:

SQL>@$ORACLE_HOME/rdbms/admin/ashrpti.sql

SQL script for getting ASH Report for single Instance:

SQL>@$ORACLE_HOME/rdbms/admin/ashrpt.sql

SQL script for getting ADDM Report on RAC database:

SQL>@$ORACLE_HOME/rdbms/admin/addmrpti.sql

SQL script for getting ADDM Report for single instance:

SQL>@$ORACLE_HOME/rdbms/admin/addmrpt.sql

ORA-20200: The instance was shutdown between snapshots

ORA-20200: The instance was shutdown between snapshots

The AWR Report is only generated using snapshots from period that instance was Started. If any shutdown occurrs it break stats and AWR can’t generate a report comparing a period where stats belong a old Instance Startup.

This occurrs because Instance stats is not persistent accross reboots, (as the name says is a Instance), so all stats get reseted in every reboot.

When generating reports between hours is easy identify when instance was started, but when generating awr reports between many days this become a painfull task if instance was restarted multiples times during a desired period.

How to find the best Interval to Generate your AWR Reports?

set pagesize 1000

set linesize 1000

(or)

SET LINESIZE 200

SET PAGESIZE 200

UNDEF num_days

COL startup_time FOR a30

COL db_name FOR a10

COL snap_start FOR 9999999

COL snap_end FOR 9999999

COL start_interval FOR a25

COL end_interval FOR a25

COL range_interval FOR a40

COL qtd_snaps FOR 999

SELECT s.startup_time, di.instance_name, MIN(snap_id) snap_start, MAX(snap_id) snap_end, MIN(end_interval_time) start_interval, MAX(end_interval_time) end_interval, EXTRACT(DAY FROM(MAX(end_interval_time) ) - MIN(end_interval_time) ) || ' Days(s) ' || EXTRACT(HOUR FROM(MAX(end_interval_time) ) - MIN(end_interval_time) ) || ' Hour(s) ' || EXTRACT(MINUTE FROM(MAX(end_interval_time) ) - MIN(end_interval_time) ) || ' Minute(s) ' range_interval, MAX(snap_id) - MIN(snap_id) qtd_snaps FROM dba_hist_snapshot s, dba_hist_database_instance di WHERE di.dbid = s.dbid AND   di.instance_number = s.instance_number AND   end_interval_time > DECODE(&&num_days,0,TO_DATE('31-JAN-9999','DD-MON YYYY'),3.14,s.end_interval_time,TO_DATE(SYSDATE,'dd/mm/yyyy') - (&num_days - 1) ) GROUP BY s.startup_time, di.instance_name ORDER BY startup_time ASC;

STARTUP_TIME                   INSTANCE_NAME    SNAP_START SNAP_END START_INTERVAL            END_INTERVAL              RANGE_INTERVAL                           QTD_SNAPS

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

20-AUG-20 02.36.53.000 PM      MQMPROD                   1        4 20-AUG-20 03.30.09.329 PM 20-AUG-20 06.30.07.232 PM 0 Days(s) 2 Hour(s) 59 Minute(s)                 3

20-AUG-20 07.41.25.000 PM      MQMPROD                   5       14 20-AUG-20 07.52.26.815 PM 21-AUG-20 02.31.00.607 AM 0 Days(s) 6 Hour(s) 38 Minute(s)                 9

22-AUG-20 02.05.58.000 AM      MQMPROD                  15       36 22-AUG-20 02.16.38.541 AM 22-AUG-20 01.00.12.251 PM 0 Days(s) 10 Hour(s) 43 Minute(s)               21

In above output is easy identify what SNAP_ID to use without keep trying and getting ORA-20200 or by reading a huge list of snaps.

The above query is NOT valid to get SNAP_ID to generate AWR Global RAC Report.

How To Create AWR Snapshot Manually

How To Create AWR Snapshot Manually

Automatic Workload Repository (AWR) is a collection of database statistics owned by the SYS user. By default snapshot are generated once every 60min .

But In case we wish to generate awr snapshot manually, then we can run the below script.  This is usually useful, when we need to generate an awr report for a non-standard window with smaller interval.

For example if we want to generate a report for next 5 minutes. (7.10 – 7.15) . So we will generate a snapshot at 7.10 and another at 7.15. And AWR can be generated using this begin_snap_id and end_snap_id.

1. Current available snapshots in database:

SQL> set linesize 1000

SQL> set pagesize 1000

SQL> select snap_id,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from dba_hist_snapshot where BEGIN_INTERVAL_TIME > systimestamp -1 order by BEGIN_INTERVAL_TIME desc;

SNAP_ID BEGIN_INTERVAL_TIME                                                         END_INTERVAL_TIME

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

        11 21-AUG-20 12.30.11.310 AM                                                   21-AUG-20 01.20.40.714 AM

        10 20-AUG-20 11.30.26.603 PM                                                   21-AUG-20 12.30.11.310 AM

         9 20-AUG-20 10.30.05.138 PM                                                   20-AUG-20 11.30.26.603 PM

         8 20-AUG-20 09.30.48.535 PM                                                   20-AUG-20 10.30.05.138 PM

         7 20-AUG-20 08.30.35.698 PM                                                   20-AUG-20 09.30.48.535 PM

         6 20-AUG-20 07.52.26.815 PM                                                   20-AUG-20 08.30.35.698 PM

         5 20-AUG-20 07.41.25.000 PM                                                   20-AUG-20 07.52.26.815 PM

         4 20-AUG-20 05.30.50.857 PM                                                   20-AUG-20 06.30.07.232 PM

         3 20-AUG-20 04.30.31.670 PM                                                   20-AUG-20 05.30.50.857 PM

         2 20-AUG-20 03.30.09.329 PM                                                   20-AUG-20 04.30.31.670 PM

         1 20-AUG-20 02.36.53.000 PM                                                   20-AUG-20 03.30.09.329 PM

11 rows selected.

2.Generate a new snapshot:

 SQL> EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

PL/SQL procedure successfully completed.

3. Check the newly created snapshots 

SQL> select snap_id,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from dba_hist_snapshot where BEGIN_INTERVAL_TIME > systimestamp -1 order by BEGIN_INTERVAL_TIME desc;

   SNAP_ID BEGIN_INTERVAL_TIME                                                         END_INTERVAL_TIME

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

        12 21-AUG-20 01.20.40.714 AM                                                   21-AUG-20 01.30.42.900 AM ------> newly generated snapshot

        11 21-AUG-20 12.30.11.310 AM                                                   21-AUG-20 01.20.40.714 AM

        10 20-AUG-20 11.30.26.603 PM                                                   21-AUG-20 12.30.11.310 AM

         9 20-AUG-20 10.30.05.138 PM                                                   20-AUG-20 11.30.26.603 PM

         8 20-AUG-20 09.30.48.535 PM                                                   20-AUG-20 10.30.05.138 PM

         7 20-AUG-20 08.30.35.698 PM                                                   20-AUG-20 09.30.48.535 PM

         6 20-AUG-20 07.52.26.815 PM                                                   20-AUG-20 08.30.35.698 PM

         5 20-AUG-20 07.41.25.000 PM                                                   20-AUG-20 07.52.26.815 PM

         4 20-AUG-20 05.30.50.857 PM                                                   20-AUG-20 06.30.07.232 PM

         3 20-AUG-20 04.30.31.670 PM                                                   20-AUG-20 05.30.50.857 PM

         2 20-AUG-20 03.30.09.329 PM                                                   20-AUG-20 04.30.31.670 PM

         1 20-AUG-20 02.36.53.000 PM                                                   20-AUG-20 03.30.09.329 PM

12 rows selected.

SQL> !date

Fri Aug 21 01:31:07 IST 2020

In our example the snap 12 snap_id has been generated.