Pages

Friday, August 28, 2020

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.

Saturday, August 22, 2020

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

How to find table size?

How to find table size?

select segment_name,segment_type,bytes/1024/1024 MB from dba_segments where segment_type='TABLE' and segment_name='<yourtablename>';

Example:

select segment_name,segment_type,bytes/1024/1024 MB from dba_segments where segment_type='TABLE' and segment_name='QADER_T1';

SQL> select segment_name,segment_type,bytes/1024/1024 MB from dba_segments where segment_type='TABLE' and segment_name='QADER_T1';


SEGMENT_NAME                                                                                                                     SEGMENT_TYPE               MB

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

QADER_T1                                                                                                                         TABLE                    4776


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.


Thursday, August 20, 2020

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.





How to Modify AWR Snapshot Interval Setting

How to Modify AWR Snapshot Interval Setting


We can change the snap_interval and retention period for the automatic awr snapshot collection, using modify_snapshot_settings function.

The default settings for ‘interval’ and ‘retention’ are 60 minutes and 8 days .

DEFAULT SETTING:

select snap_interval, retention from dba_hist_wr_control;

 SNAP_INTERVAL                                                               RETENTION

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

+00000 01:00:00.0                                                           +00008 00:00:00.0

Modify the snapshot setting:( snap_interval 30 min and retention 30 days(60*24*30)

The values for both ‘interval’ and ‘retention’ are expressed in minutes.

SQL> execute dbms_workload_repository.modify_snapshot_settings(interval => 30,retention => 43200);

PL/SQL procedure successfully completed.

Verify the new setting:

SQL> select snap_interval, retention from dba_hist_wr_control;

 SNAP_INTERVAL                                                               RETENTION

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

+00000 00:30:00.0                                                           +00030 00:00:00.0

 


Oracle SQL Tunning Advisor

Oracle SQL Tunning Advisor

-The SQL Tuning Advisor takes one or more SQL statements as an input and invokes the Automatic Tuning Optimizer to perform SQL tuning on the statements.

-The output of the SQL Tuning Advisor is in the form of an recommendations, along with a rationale for each recommendation and its expected benefit.The recommendation relates to collection of statistics on objects, creation of new indexes, restructuring of the SQL statement, or creation of a SQL profile. You can choose to accept the recommendation to complete the tuning of the SQL statements.

-You can also run the SQL Tuning Advisor selectively on a single or a set of SQL statements that have been identified as problematic.

-We can find the problematic SQL_ID from v$session you would like to analyze. Usually the AWR has the top SQL_IDs column.

In order to access the SQL tuning advisor API, a user must be granted the ADVISOR privilege:

How To Run SQL Tuning Advisor For A Sql_id

Example: SQL_ID=4gk55ct4mnmh3

1. Create Tuning Task

DECLARE

  l_sql_tune_task_id  VARCHAR2(100);

BEGIN

  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

                          sql_id      => '4gk55ct4mnmh3',

                          scope       => DBMS_SQLTUNE.scope_comprehensive,

                          time_limit  => 500,

                          task_name   => '4gk55ct4mnmh3_tuning_task11',

                          description => 'Tuning task1 for statement 4gk55ct4mnmh3');

  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/

2. Execute Tuning task:

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '4gk55ct4mnmh3_tuning_task11');

3. Get the Tuning advisor report.

set long 65536

set longchunksize 65536

set linesize 100

select dbms_sqltune.report_tuning_task('4gk55ct4mnmh3_tuning_task11') from dual;

 4. Get list of tuning task present in database:

We can get the list of tuning tasks present in database from DBA_ADVISOR_LOG

SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME='4gk55ct4mnmh3_tuning_task11'; ----> task_name

5. Drop a tuning task:

execute dbms_sqltune.drop_tuning_task('4gk55ct4mnmh3_tuning_task11');

What if the sql_id is not present in the cursor, but present in AWR snap?

SQL_ID =4gk55ct4mnmh3

First we need to find the begin snap and end snap of the sql_id.

select a.instance_number inst_id, a.snap_id,a.plan_hash_value, to_char(begin_interval_time,'dd-mon-yy hh24:mi') btime, abs(extract(minute from (end_interval_time-begin_interval_time)) + extract(hour from (end_interval_time-begin_interval_time))*60 + extract(day from (end_interval_time-begin_interval_time))*24*60) minutes,executions_delta executions, round(ELAPSED_TIME_delta/1000000/greatest(executions_delta,1),4) "avg duration (sec)" from dba_hist_SQLSTAT a, dba_hist_snapshot b where sql_id='&sql_id' and a.snap_id=b.snap_id and a.instance_number=b.instance_number order by snap_id desc, a.instance_number;

 From here we can get the begin snap and end snap of the sql_id.

begin_snap -> 235

end_snap -> 240

1. Create the tuning task:

DECLARE

 l_sql_tune_task_id  VARCHAR2(100);

BEGIN

  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

                          begin_snap  => 235,

                          end_snap    => 240,

                          sql_id      => '4gk55ct4mnmh3',

                          scope       => DBMS_SQLTUNE.scope_comprehensive,

                          time_limit  => 60,

                          task_name   => '4gk55ct4mnmh3_AWR_tuning_task',

                          description => 'Tuning task for statement 4gk55ct4mnmh3  in AWR');

  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/

 2. Execute the tuning task: 

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '4gk55ct4mnmh3_AWR_tuning_task');

 3. Get the tuning task recommendation report

SET LONG 10000000;

SET PAGESIZE 100000000

SET LINESIZE 200

SELECT DBMS_SQLTUNE.report_tuning_task('4gk55ct4mnmh3_AWR_tuning_task') AS recommendations FROM dual;

SET PAGESIZE 24


Saturday, August 15, 2020

dbms_system.ksdwrt–Write messages to Oracle alert log

dbms_system.ksdwrt–Write messages to Oracle alert log


Oracle provides a procedure to insert messages to the alert log and/or trace files for testing/development purposes. This can be used to check the effectiveness of the monitoring tools/scripts used in the environment, to understand how well the monitoring tool captures the messages in the alert log.


Usage:


SQL> exec dbms_system.ksdwrt(1, 'This message goes to trace file in the udump location');

PL/SQL procedure successfully completed.

SQL> exec dbms_system.ksdwrt(2, 'This message goes to the alert log');

PL/SQL procedure successfully completed.

SQL> exec dbms_system.ksdwrt(3, 'This message goes to the alert log and trace file in the udump location');

PL/SQL procedure successfully completed.

SQL>

Friday, August 7, 2020

RMAN Restore/Recover a database UNTIL TIME

RMAN Restore/Recover a database UNTIL TIME


Below are the steps for Restoring/ Recovering an Oracle database until a specific time in the past:


1) Set the environment:

on Windows:

set ORACLE_SID=DB10

echo %ORACLE_SID%


on AIX/Linux:

export $ORACLE_SID=DB10

echo $ORACLE_SID


2) Connect to rman:

rman target / nocatalog


3) Start the database in "mount" state:

startup mount;


4) Run the following :

Restore:

restore database UNTIL TIME "to_date('08/07/2020 06:15:00 pm','mm/dd/yyyy hh:mi:ss am')";


Recover:

recover database UNTIL TIME "to_date('08/07/2020 06:15:00 pm','mm/dd/yyyy hh:mi:ss am')";


alter database open resetlogs;


OR


run

{

set UNTIL TIME "to_date('08/07/2020 06:15:00 pm','mm/dd/yyyy hh:mi:ss am')";

restore database;

recover database;

alter database open resetlogs;

}


Note: 

We cannot restore/recover the database in "open" state, below error you faced while doing it.

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 08/07/2020 18:50:14

ORA-19870: error while restoring backup piece C:\XE_INSTALL\BACKUP\FULL08V79FL2

ORA-19573: cannot obtain exclusive enqueue for datafile 2