Thinking Out Loud

December 8, 2021

Find Statistics and SQL Using Index

Filed under: 11g,oracle,performance — mdinh @ 10:05 pm

Database is facing issues with enq: TX – allocate ITL entry.

Research to find Troubleshooting waits for ‘enq: TX – allocate ITL entry’ (Doc ID 1472175.1)

Index DINH.INFO_IX1 was rebuilt.

To find SQL using index DINH.INFO_IX1- https://github.com/jkstill/oracle-script-lib/blob/master/sql/find-index-sql.sql

After running find-index-sql.sql, get execution statistics and history for a SQL using Doc ID 1371778.1.

The document illustrates how to get execution statistics and history for a SQL using sql_id using Data Dictionary queries for both queries in memory and those in the AWR.

DEMO:

-- Find SQL where an index has been used

SQL> @find-index-sql.sql

old   2:    :b_index_owner := '&&v_index_owner';
new   2:    :b_index_owner := 'DINH';
old   3:    :b_index_name := '&&v_index_name';
new   3:    :b_index_name := 'INFO_IX1';

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00


SQL_ID        PLAN_HASH_VALUE
------------- ---------------
272kpc0q766tx      1690776192
7wd9njg9sy9mk      1635894828
gu9t5kgmy3pp6       698638001

Elapsed: 00:00:46.75

SQL>

======================================================================
--- sql_history.sql for SQL_ID: 272kpc0q766tx using index DINH.INFO_IX1
======================================================================

SQL> @sql_history.sql

--- From Memory

Enter value for sql_id: 272kpc0q766tx
old  16: where sql_id = '&sql_id'
new  16: where sql_id = '272kpc0q766tx'

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE FIRST_LOAD_TIME      LAST_LOAD_TIME       OUTLINE_CATEGORY     SQL_PROFILE                      EXECUTIONS
------------- ------------ --------------- -------------------- -------------------- -------------------- -------------------------------- ----------
272kpc0q766tx            0      1690776192 2018-10-23/11:20:35  2021-10-21/05:55:23                                                                29
272kpc0q766tx            1      1690776192 2018-10-23/11:20:35  2021-07-29/19:55:22                                                          29268833
272kpc0q766tx            3      1690776192 2018-10-23/11:20:35  2021-12-08/08:00:26                                                               441
272kpc0q766tx            4      1690776192 2018-10-23/11:20:35  2021-05-07/05:16:48                                                              3583
272kpc0q766tx            6      1690776192 2018-10-23/11:20:35  2021-12-01/21:18:11                                                            413747
272kpc0q766tx            8      1690776192 2018-10-23/11:20:35  2021-12-01/22:07:51                                                               164
272kpc0q766tx           37      1690776192 2018-10-23/11:20:35  2021-04-24/10:51:28                                                           3394967
272kpc0q766tx           38      1690776192 2018-10-23/11:20:35  2021-04-24/16:02:52                                                             43383
272kpc0q766tx           49      1690776192 2018-10-23/11:20:35  2021-07-21/18:27:05                                                            466811
272kpc0q766tx           51      1690776192 2018-10-23/11:20:35  2021-07-21/22:27:11                                                              5238
272kpc0q766tx           54      1690776192 2018-10-23/11:20:35  2021-08-07/22:48:03                                                           3286771
272kpc0q766tx           56      1690776192 2018-10-23/11:20:35  2021-08-08/05:01:11                                                              9835
272kpc0q766tx           67      1690776192 2018-10-23/11:20:35  2021-10-02/06:52:24                                                            126753
272kpc0q766tx           68      1690776192 2018-10-23/11:20:35  2021-10-02/10:41:18                                                               138

14 rows selected.

--- From AWR

Enter value for sql_id: 272kpc0q766tx
old  15: where sql_id = '&sql_id'
new  15: where sql_id = '272kpc0q766tx'

SQL_ID           SNAP_ID PLAN_HASH_VALUE SQL_PROFILE                      EXECUTIONS_TOTAL
------------- ---------- --------------- -------------------------------- ----------------
272kpc0q766tx     353952      1690776192                                          35878169
272kpc0q766tx     355246      1690776192                                          37443248

SQL>

======================================================================
--- sql_history.sql for SQL_ID: 7wd9njg9sy9mk using index DINH.INFO_IX1
======================================================================

SQL> @sql_history.sql

--- From Memory

Enter value for sql_id: 7wd9njg9sy9mk
old  16: where sql_id = '&sql_id'
new  16: where sql_id = '7wd9njg9sy9mk'

no rows selected

--- From AWR

Enter value for sql_id: 7wd9njg9sy9mk
old  15: where sql_id = '&sql_id'
new  15: where sql_id = '7wd9njg9sy9mk'

no rows selected

SQL>

======================================================================
--- sql_history.sql for SQL_ID: gu9t5kgmy3pp6 using index DINH.INFO_IX1
======================================================================

SQL> @sql_history.sql

--- From Memory

Enter value for sql_id: gu9t5kgmy3pp6
old  16: where sql_id = '&sql_id'
new  16: where sql_id = 'gu9t5kgmy3pp6'

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE FIRST_LOAD_TIME      LAST_LOAD_TIME       OUTLINE_CATEGORY     SQL_PROFILE                      EXECUTIONS
------------- ------------ --------------- -------------------- -------------------- -------------------- -------------------------------- ----------
gu9t5kgmy3pp6            0       698638001 2018-10-23/11:20:27  2021-12-08/08:05:10                                                               268
gu9t5kgmy3pp6            1       698638001 2018-10-23/11:20:27  2021-08-07/23:19:17                                                           5343273
gu9t5kgmy3pp6            5       698638001 2018-10-23/11:20:27  2021-12-01/18:42:33                                                            190004
gu9t5kgmy3pp6           23       698638001 2018-10-23/11:20:27  2021-04-24/14:27:12                                                           5512867
gu9t5kgmy3pp6           32       698638001 2018-10-23/11:20:27  2021-07-21/19:22:08                                                           2831328
gu9t5kgmy3pp6           39       698638001 2018-10-23/11:20:27  2021-10-02/04:16:50                                                            287105

6 rows selected.

--- From AWR

Enter value for sql_id: gu9t5kgmy3pp6
old  15: where sql_id = '&sql_id'
new  15: where sql_id = 'gu9t5kgmy3pp6'

no rows selected

SQL>

UPDATE for sql_id 7wd9njg9sy9mk:

sql_id 7wd9njg9sy9mk exist from gv$sql_plan and dba_hist_sql_plan (using find-index-sql.sql)

but not from v$sql and dba_hist_sqlstat (using Doc ID 1371778.1) which is strange.

Manually checking using DBMS_XPLAN.display_cursor and dbms_xplan.display_awr shows:

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'7wd9njg9sy9mk',format=>'ALLSTATS LAST +cost +bytes')); 

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
SQL_ID: 7wd9njg9sy9mk, child number: 0 cannot be found


Elapsed: 00:00:00.00

SQL>

SQL> SELECT * FROM TABLE(dbms_xplan.display_awr('7wd9njg9sy9mk'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
SQL_ID 7wd9njg9sy9mk
--------------------
An uncaught error happened in prepare_sql_statement : ORA-01403: no data found

Plan hash value: 1635894828

-----------------------------------------------------------------------------------------------
| Id  | Operation           | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT    |                         |       |       |   538 (100)|          |
|   1 |  DELETE             | *******_UPDATE_INFO     |       |       |            |          |
|   2 |   COUNT STOPKEY     |                         |       |       |            |          |
|   3 |    NESTED LOOPS     |                         |    50M|   958M|   538 (100)| 00:00:07 |
|   4 |     INDEX FULL SCAN | SYS_C00137313           |    50M|   620M|     4   (0)| 00:00:01 |
|   5 |     INDEX RANGE SCAN| **************_INFO_IX1 |     1 |     7 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


21 rows selected.

Elapsed: 00:00:00.03

SQL>

December 4, 2021

How To Resolve Tablespace Created With Windows Path In Linux

Filed under: 11g,linux,oracle — mdinh @ 3:20 pm

Now I understand the rationale for not providing DBA privileges to inexperience developers.

Currently, I am uncertain if the current environment is production or not.

Here is demo for 11.2.0.4 to demonstrate the issue and resolution.

Reference:
https://docs.oracle.com/cd/E18283_01/server.112/e17120/dfiles005.htm#i1006478

[oracle@ol7-112-dg1 trace]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Dec 4 14:46:37 2021

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /u01/oradata

----------------------------------------
--- Windows path used for datafile:
----------------------------------------

SQL> create tablespace TEST datafile 'D:\Oracle\oradata\E1Local\TEST.dbf' SIZE 16M;

Tablespace created.

SQL> set lines 200
SQL> col name for a80
SQL> select file#,name from v$datafile order by 1;

     FILE# NAME
---------- --------------------------------------------------------------------------------
         1 /u01/oradata/hawk/system01.dbf
         2 /u01/oradata/hawk/sysaux01.dbf
         3 /u01/oradata/hawk/undotbs01.dbf
         4 /u01/oradata/hawk/users01.dbf
         5 /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/D:OracleoradataE1LocalTEST.dbf

SQL> alter tablespace TEST offline normal;

Tablespace altered.

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TEST                           OFFLINE

6 rows selected.

SQL> host

----------------------------------------
--- Rename datafile from OS
----------------------------------------

[oracle@ol7-112-dg1 trace]$ mv -fv /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/D:OracleoradataE1LocalTEST.dbf /u01/oradata/test.dbf
‘/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/D:OracleoradataE1LocalTEST.dbf’ -> ‘/u01/oradata/test.dbf’

[oracle@ol7-112-dg1 trace]$ exit
exit

SQL> alter tablespace TEST rename datafile '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/D:OracleoradataE1LocalTEST.dbf' TO '/u01/oradata/test.dbf';

Tablespace altered.

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TEST                           OFFLINE

6 rows selected.

SQL> alter tablespace TEST online;

Tablespace altered.

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TEST                           ONLINE

6 rows selected.

SQL> drop tablespace TEST including contents and datafiles;

Tablespace dropped.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ol7-112-dg1 trace]$

----------------------------------------
--- Alert Log: NO ERRORS!
----------------------------------------

[oracle@ol7-112-dg1 trace]$ tail -25 alert_hawk.log
LNS: Standby redo logfile selected for thread 1 sequence 126 for destination LOG_ARCHIVE_DEST_2
Sat Dec 04 14:47:17 2021
create tablespace TEST datafile 'D:\Oracle\oradata\E1Local\TEST.dbf' SIZE 16M
Completed: create tablespace TEST datafile 'D:\Oracle\oradata\E1Local\TEST.dbf' SIZE 16M
Sat Dec 04 14:48:08 2021
alter tablespace TEST offline normal
Completed: alter tablespace TEST offline normal
Sat Dec 04 14:48:08 2021
Starting background process SMCO
Sat Dec 04 14:48:08 2021
SMCO started with pid=36, OS id=5159
Sat Dec 04 14:48:19 2021
Checker run found 2 new persistent data failures
Sat Dec 04 14:49:33 2021
alter tablespace TEST rename datafile '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/D:OracleoradataE1LocalTEST.dbf' TO '/u01/oradata/test.dbf'
Completed: alter tablespace TEST rename datafile '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/D:OracleoradataE1LocalTEST.dbf' TO '/u01/oradata/test.dbf'
Sat Dec 04 14:49:42 2021
Checker run found 1 new persistent data failures
Sat Dec 04 14:50:00 2021
alter tablespace TEST online
Completed: alter tablespace TEST online
Sat Dec 04 14:50:33 2021
drop tablespace TEST including contents and datafiles
Deleted file /u01/oradata/test.dbf
Completed: drop tablespace TEST including contents and datafiles
[oracle@ol7-112-dg1 trace]$

----------------------------------------
--- Resolution for 19c with ASM
----------------------------------------

SQL> alter session set container=PDB;
SQL> ALTER DATABASE MOVE DATAFILE '/u02/app/oracle/product/19.0.0.0/dbhome_1/dbs/D:OracleoradataE1LocalTEST.dbf' TO '+DATAC1';

November 6, 2021

Flashback DataGuard Primary Database

Filed under: 11g,Dataguard,dgmgrl,flashback — mdinh @ 1:26 am

Practicing flashback just in case it is needed during production deployment.

The disadvantage is commands will be different for each environment vs simple and consistent

recover managed standby database cancel and

recover managed standby database using current logfile disconnect

--------------------------------------------------
--- CHECK FLASHBACK ENABLED:
--------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

### PRIMARY:
SQL> select current_scn,name,open_mode,database_role,flashback_on from v$database;

CURRENT_SCN NAME                           OPEN_MODE            DATABASE_ROLE    FLASHBACK_ON
----------- ------------------------------ -------------------- ---------------- ------------------
    1056941 HAWK                           READ WRITE           PRIMARY          YES

SQL>

-- Enable flashback if required:
alter database flashback on;
select current_scn,name,open_mode,database_role,flashback_on from v$database;

### STANDBY:
SQL> select current_scn,name,open_mode,database_role,flashback_on from v$database;

CURRENT_SCN NAME                           OPEN_MODE            DATABASE_ROLE    FLASHBACK_ON
----------- ------------------------------ -------------------- ---------------- ------------------
    1037296 HAWK                           MOUNTED              PHYSICAL STANDBY YES

SQL>

-- Enable flashback if required:
recover managed standby database cancel; 
alter database flashback on;
select current_scn,name,open_mode,database_role,flashback_on from v$database;

--------------------------------------------------
--- CREATE GRP:
--------------------------------------------------

### PRIMARY:
SQL> create restore point RP_TEST guarantee flashback database;

Restore point created.

SQL>

--------------------------------------------------
--- CHECK GRP:
--------------------------------------------------

Depending on database version, GRP is replicated to standby.

### PRIMARY:
SQL> select scn, name, GUARANTEE_FLASHBACK_DATABASE from v$restore_point;

       SCN NAME                           GUA
---------- ------------------------------ ---
   1057112 RP_TEST                        YES

SQL>

### STANDBY:
SQL> select scn, name, GUARANTEE_FLASHBACK_DATABASE from v$restore_point;

no rows selected

SQL>

##################################################
### FLASHBACK TO RESTORE POINT:
##################################################

### Flashback PRIMARY:
SQL> select current_scn,name,open_mode,database_role,flashback_on from v$database;

CURRENT_SCN NAME                           OPEN_MODE            DATABASE_ROLE    FLASHBACK_ON
----------- ------------------------------ -------------------- ---------------- ------------------
    1057767 HAWK                           READ WRITE           PRIMARY          YES

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2253664 bytes
Variable Size             469765280 bytes
Database Buffers         1124073472 bytes
Redo Buffers                7319552 bytes
Database mounted.

SQL> select scn, name, GUARANTEE_FLASHBACK_DATABASE from v$restore_point;

       SCN NAME                           GUA
---------- ------------------------------ ---
   1057112 RP_TEST                        YES

SQL> flashback database to restore point RP_TEST;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL>

### Flashback STANDBY:
SQL> recover managed standby database cancel;
ORA-16136: Managed Standby Recovery not active

--- (USE PRIMARY v$restore_point.SCN)
SQL> flashback standby database to scn &scn;
Enter value for scn: 1057112
old   1: flashback standby database to scn &scn
new   1: flashback standby database to scn 1057112

Flashback complete.

SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL>

--------------------------------------------------
--- Check DG using dgmgrl:
--------------------------------------------------

DGMGRL> show configuration

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Databases:
    hawk      - Primary database
    hawk_stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database hawk

Database - hawk

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    hawk

Database Status:
SUCCESS

DGMGRL> show database hawk_stby

Database - hawk_stby

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 12 seconds ago)
  Apply Lag:       0 seconds (computed 12 seconds ago)
  Apply Rate:      0 Byte/s
  Real Time Query: OFF
  Instance(s):
    hawk

Database Status:
SUCCESS

DGMGRL>

Q.E.D.

May 14, 2021

Gather Your Session Info For Killing

Filed under: 11g,linux,oracle — mdinh @ 4:40 am

So there I was, running emremove.sql as part of pre-upgrade task; however, it was taking longer than expected.

Session was stuck at the output shown below and desperately CTRL-C did not work.

14:35:05 472  /
old  70:     IF (upper('&LOGGING') = 'VERBOSE')
new  70:     IF (upper('VERBOSE') = 'VERBOSE')

^C

^C^C

^C

I checked for blocking session and there were blocking locks from SYS which was really strange.

I made a gutsy call and kill SYS session from OS prompt based on timestamp.

~ $ ps -ef|grep sysdba
oracle    57147 231962  0 May12 pts/4    00:00:00 sqlplus   as sysdba
oracle   155919 139352  0 14:34 pts/1    00:00:00 sqlplus   as sysdba
oracle   244619 216760  0 15:25 pts/5    00:00:00 grep --color=auto sysdba

~ $ kill -9 155919

As it turns out, another DBA was logged in as sysdba causing havoc.

I was lucky to have killed the correct SYS session and will you be as lucky as I was?

Based on my near disaster, it would be better to create good practice of gathering your session info to be able to kill the correct session.

Here is current session info.

SQL> @my
SQL> select b.sid, b.serial#, a.spid processid, b.process clientpid from v$process a, v$session b
  2  where a.addr = b.paddr
  3  and b.audsid = userenv('sessionid')
  4  and b.sid=userenv('sid')
  5  ;

       SID    SERIAL# PROCESSID                CLIENTPID
---------- ---------- ------------------------ ------------------------
         5        101 16428                    16427

SQL>

[oracle@ol7-112-dg1 ~]$ ps -ef|grep 16427
oracle   16427  8573  0 03:44 pts/0    00:00:00 sqlplus   as sysdba
oracle   16428 16427  0 03:44 ?        00:00:00 oraclehawk (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   16461 11677  0 03:45 pts/1    00:00:00 grep --color=auto 16427
[oracle@ol7-112-dg1 ~]$

Kill OS process using sqlplus PROCESSID – don’t know session is killed until DML is performed.

[oracle@ol7-112-dg1 ~]$ kill -9 16428

SQL> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 16428
Session ID: 5 Serial number: 101


SQL>

Another test

--- Session Info
SQL> @my
SQL> select b.sid, b.serial#, a.spid processid, b.process clientpid from v$process a, v$session b
  2  where a.addr = b.paddr
  3  and b.audsid = userenv('sessionid')
  4  and b.sid=userenv('sid')
  5  ;

       SID    SERIAL# PROCESSID                CLIENTPID
---------- ---------- ------------------------ ------------------------
         5        103 16533                    16532

SQL>

--- From another session, check waits for above session
SQL> r
  1  select NVL(s.username,'(oracle)') AS username, s.sid, s.serial#,
  2  sw.event, sw.seconds_in_wait, sw.state
  3  from v$session_wait sw, v$session s
  4  where s.sid = sw.sid and s.sid=&sid
  5*
Enter value for sid: 5
old   4: where s.sid = sw.sid and s.sid=&sid
new   4: where s.sid = sw.sid and s.sid=5

USERNAME               SID    SERIAL# EVENT                          SECONDS_IN_WAIT STATE
--------------- ---------- ---------- ------------------------------ --------------- -------------------
SYS                      5        115 SQL*Net message from client                169 WAITING

SQL>

Kill OS process using sqlplus CLIENTPID – immediate feedback –

[oracle@ol7-112-dg1 ~]$ ps -ef|grep 16532
oracle   16532  8573  0 03:46 pts/0    00:00:00 sqlplus   as sysdba
oracle   16533 16532  0 03:46 ?        00:00:00 oraclehawk (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   16557 11677  0 03:47 pts/1    00:00:00 grep --color=auto 16532
[oracle@ol7-112-dg1 ~]$


[oracle@ol7-112-dg1 ~]$ kill -9 16532


SQL> Killed
[oracle@ol7-112-dg1 ~]$

Hopefully you will never have to kill your own session.

When you need kill your session, it’s better to have the correct information versus guessing.

March 4, 2021

Purge Database Audit Trail Table

Filed under: 11g — mdinh @ 5:12 am

Segment for AUD$/FGA_LOG$ tables reside in SYSTEM tablespace and will be moved AUDIT_TBS before configuring purge.

The requirement is to purge audits older than 7 years (366*7=2562) [l_days NUMBER := 2562]

DEMO:

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 3 18:22:35 2021

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

18:22:35 SYS@DB01 AS SYSDBA> select file_name from dba_data_files where tablespace_name='SYSAUX';

FILE_NAME
--------------------------------------------------------------------------------
/oradata/db01/datafile/sysaux_01.dbf

Elapsed: 00:00:00.01
18:22:44 SYS@DB01 AS SYSDBA> create tablespace AUDIT_TBS datafile '&location/audit_tbs_01.dbf' size 5g autoextend on maxsize 30g;
Enter value for location: /oradata/db01/datafile
old   1: create tablespace AUDIT_TBS datafile '&location/audit_tbs_01.dbf' size 5g autoextend on maxsize 30g
new   1: create tablespace AUDIT_TBS datafile '/oradata/db01/datafile/audit_tbs_01.dbf' size 5g autoextend on maxsize 30g

Tablespace created.

Elapsed: 00:00:24.68
18:24:29 SYS@DB01 AS SYSDBA> @ audit.sql
18:24:37 SYS@DB01 AS SYSDBA>
18:24:37 SYS@DB01 AS SYSDBA> select owner, segment_name, segment_type, tablespace_name, round(bytes/1024/1024,2) USED_MB
18:24:37   2  from dba_segments where segment_name IN ('AUD$','FGA_LOG$') order by 2
18:24:37   3  ;

OWNER                SEGMENT_NAME         SEGMENT_TYPE       TABLESPACE_NAME         USED_MB
-------------------- -------------------- ------------------ -------------------- ----------
SYS                  AUD$                 TABLE              SYSTEM                     2946
SYS                  FGA_LOG$             TABLE              SYSTEM                      .06

Elapsed: 00:00:00.09
18:24:37 SYS@DB01 AS SYSDBA> select MIN(TIMESTAMP), MAX(TIMESTAMP), TRUNC(systimestamp)-MIN(TIMESTAMP) diff_day, COUNT(*)
18:24:37   2  from dba_audit_trail
18:24:37   3  ;

MIN(TIMES MAX(TIMES   DIFF_DAY   COUNT(*)
--------- --------- ---------- ----------
23-SEP-14 03-MAR-21 2352.58642   20801590

Elapsed: 00:00:53.32
18:25:30 SYS@DB01 AS SYSDBA> select * from dba_audit_mgmt_config_params order by 1
18:25:30   2  ;

PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL
------------------------------ -------------------- --------------------
AUDIT FILE MAX AGE             5                    OS AUDIT TRAIL
AUDIT FILE MAX AGE             5                    XML AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                OS AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                XML AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000                STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000                FGA AUDIT TRAIL
DB AUDIT TABLESPACE            SYSAUX               FGA AUDIT TRAIL
DB AUDIT TABLESPACE            SYSAUX               STANDARD AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000                 XML AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000                 OS AUDIT TRAIL

10 rows selected.

Elapsed: 00:00:00.01
18:25:30 SYS@DB01 AS SYSDBA> select * from dba_audit_mgmt_last_arch_ts
18:25:30   2  ;

no rows selected

Elapsed: 00:00:00.00
18:25:30 SYS@DB01 AS SYSDBA> exec dbms_audit_mgmt.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD, AUDIT_TRAIL_LOCATION_VALUE => 'AUDIT_TBS')

PL/SQL procedure successfully completed.

Elapsed: 00:09:17.79
18:34:48 SYS@DB01 AS SYSDBA> ;
  1  select * from dba_audit_mgmt_last_arch_ts
  2*
18:34:48 SYS@DB01 AS SYSDBA> exec dbms_audit_mgmt.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_FGA_STD, AUDIT_TRAIL_LOCATION_VALUE => 'AUDIT_TBS')

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.83
18:34:56 SYS@DB01 AS SYSDBA> ;
  1  select * from dba_audit_mgmt_last_arch_ts
  2*
18:34:56 SYS@DB01 AS SYSDBA>
18:34:56 SYS@DB01 AS SYSDBA> begin
18:34:56   2    dbms_audit_mgmt.INIT_CLEANUP (
18:34:56   3      audit_trail_type         => dbms_audit_mgmt.audit_trail_all,
18:34:56   4      default_cleanup_interval => 24);
18:34:56   5  end;
18:34:56   6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.21
18:35:01 SYS@DB01 AS SYSDBA>
18:35:01 SYS@DB01 AS SYSDBA> begin
18:35:01   2  if
18:35:01   3    dbms_audit_mgmt.IS_CLEANUP_INITIALIZED(dbms_audit_mgmt.AUDIT_TRAIL_ALL) then
18:35:01   4    dbms_output.put_line('******* YES *******');
18:35:01   5  else
18:35:01   6    dbms_output.put_line('******* NO *******');
18:35:01   7  end if;
18:35:01   8  end;
18:35:01   9  /
******* YES *******

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
18:35:01 SYS@DB01 AS SYSDBA>
18:35:01 SYS@DB01 AS SYSDBA> select * from dba_audit_mgmt_config_params order by 1
18:35:01   2  ;

PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL
------------------------------ -------------------- --------------------
AUDIT FILE MAX AGE             5                    XML AUDIT TRAIL
AUDIT FILE MAX AGE             5                    OS AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                OS AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                XML AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000                STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000                FGA AUDIT TRAIL
DB AUDIT TABLESPACE            AUDIT_TBS            STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE            AUDIT_TBS            FGA AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL      24                   XML AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL      24                   OS AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL      24                   STANDARD AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL      24                   FGA AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000                 OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000                 XML AUDIT TRAIL

14 rows selected.

Elapsed: 00:00:00.01
18:35:01 SYS@DB01 AS SYSDBA>
18:35:01 SYS@DB01 AS SYSDBA> begin
18:35:01   2    dbms_audit_mgmt.CREATE_PURGE_JOB(
18:35:01   3      audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
18:35:01   4      audit_trail_purge_interval => 24,
18:35:01   5      audit_trail_purge_name     => 'PURGE_ALL_AUDIT_TRAILS',
18:35:01   6      use_last_arch_timestamp    => TRUE);
18:35:01   7  end;
18:35:01   8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.77
18:35:02 SYS@DB01 AS SYSDBA>
18:35:02 SYS@DB01 AS SYSDBA> begin
18:35:02   2    dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(
18:35:02   3      audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
18:35:02   4      last_archive_time => SYSTIMESTAMP-2562);
18:35:02   5  end;
18:35:02   6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.05
18:35:02 SYS@DB01 AS SYSDBA>
18:35:02 SYS@DB01 AS SYSDBA> begin
18:35:02   2    DBMS_SCHEDULER.create_job (
18:35:02   3      job_name        => 'AUDIT_LAST_ARCHIVE_TIME',
18:35:02   4      job_type        => 'PLSQL_BLOCK',
18:35:02   5      job_action      => 'DECLARE
18:35:02   6                            l_days NUMBER := 2562;
18:35:02   7                          BEGIN
18:35:02   8                            dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD, TRUNC(SYSTIMESTAMP)-l_days);
18:35:02   9                            dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(dbms_audit_mgmt.AUDIT_TRAIL_FGA_STD, TRUNC(SYSTIMESTAMP)-l_days);
18:35:02  10                            dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(dbms_audit_mgmt.AUDIT_TRAIL_OS,      TRUNC(SYSTIMESTAMP)-l_days);
18:35:02  11                            dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(dbms_audit_mgmt.AUDIT_TRAIL_XML,     TRUNC(SYSTIMESTAMP)-l_days);
18:35:02  12                          END;',
18:35:02  13      start_date      => SYSTIMESTAMP,
18:35:02  14      repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;',
18:35:02  15      end_date        => NULL,
18:35:02  16      enabled         => TRUE,
18:35:02  17      comments        => 'Automatically set audit last archive time.');
18:35:02  18  end;
18:35:02  19  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
18:35:02 SYS@DB01 AS SYSDBA>
18:35:02 SYS@DB01 AS SYSDBA> select * from dba_audit_mgmt_config_params order by 1
18:35:02   2  ;

PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL
------------------------------ -------------------- --------------------
AUDIT FILE MAX AGE             5                    XML AUDIT TRAIL
AUDIT FILE MAX AGE             5                    OS AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                OS AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                XML AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000                STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000                FGA AUDIT TRAIL
DB AUDIT TABLESPACE            AUDIT_TBS            STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE            AUDIT_TBS            FGA AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL      24                   XML AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL      24                   OS AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL      24                   STANDARD AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL      24                   FGA AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000                 OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000                 XML AUDIT TRAIL

14 rows selected.

Elapsed: 00:00:00.00
18:35:02 SYS@DB01 AS SYSDBA> select * from dba_audit_mgmt_last_arch_ts
18:35:02   2  ;

AUDIT_TRAIL          RAC_INSTANCE LAST_ARCHIVE_TS
-------------------- ------------ ---------------------------------------------
STANDARD AUDIT TRAIL            0 26-FEB-14 06.35.02.000000 PM +00:00

Elapsed: 00:00:00.00
18:35:02 SYS@DB01 AS SYSDBA> select job_action from dba_scheduler_jobs where job_name='PURGE_ALL_AUDIT_TRAILS'
18:35:02   2  ;

JOB_ACTION
------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(15, TRUE);  END;

Elapsed: 00:00:00.17
18:35:02 SYS@DB01 AS SYSDBA> select job_action from dba_scheduler_jobs where job_name='AUDIT_LAST_ARCHIVE_TIME'
18:35:02   2  ;

JOB_ACTION
------------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE
                          l_days NUMBER := 2562;
                        BEGIN
                          dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD, TRUNC(SYSTIMESTAMP)-l_days);
                          dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(dbms_audit_mgmt.AUDIT_TRAIL_FGA_STD, TRUNC(SYSTIMESTAMP)-l_days);
                          dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(dbms_audit_mgmt.AUDIT_TRAIL_OS,      TRUNC(SYSTIMESTAMP)-l_days);
                          dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(dbms_audit_mgmt.AUDIT_TRAIL_XML,     TRUNC(SYSTIMESTAMP)-l_days);
                        END;


Elapsed: 00:00:00.14
18:35:02 SYS@DB01 AS SYSDBA> commit;

Commit complete.

Elapsed: 00:00:00.00
18:35:02 SYS@DB01 AS SYSDBA>
--- audit.sql
col parameter_name for a30
col parameter_value for a20
col audit_trail for a20
col owner for a20
col segment_name for a20
col tablespace_name for a20
col last_archive_ts for a45
col job_action for a150
set lines 200 pages 100 serverout on echo on

select owner, segment_name, segment_type, tablespace_name, round(bytes/1024/1024,2) USED_MB
from dba_segments where segment_name IN ('AUD$','FGA_LOG$') order by 2
;
select MIN(TIMESTAMP), MAX(TIMESTAMP), TRUNC(systimestamp)-MIN(TIMESTAMP) diff_day, COUNT(*)
from dba_audit_trail
;
select * from dba_audit_mgmt_config_params order by 1
;
select * from dba_audit_mgmt_last_arch_ts
;
exec dbms_audit_mgmt.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD, AUDIT_TRAIL_LOCATION_VALUE => 'AUDIT_TBS')
;
exec dbms_audit_mgmt.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_FGA_STD, AUDIT_TRAIL_LOCATION_VALUE => 'AUDIT_TBS')
;

begin
  dbms_audit_mgmt.INIT_CLEANUP (
    audit_trail_type         => dbms_audit_mgmt.audit_trail_all,
    default_cleanup_interval => 24);
end;
/

begin
if
  dbms_audit_mgmt.IS_CLEANUP_INITIALIZED(dbms_audit_mgmt.AUDIT_TRAIL_ALL) then
  dbms_output.put_line('******* YES *******');
else
  dbms_output.put_line('******* NO *******');
end if;
end;
/

select * from dba_audit_mgmt_config_params order by 1
;

begin
  dbms_audit_mgmt.CREATE_PURGE_JOB(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
    audit_trail_purge_interval => 24,
    audit_trail_purge_name     => 'PURGE_ALL_AUDIT_TRAILS',
    use_last_arch_timestamp    => TRUE);
end;
/

begin
  dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(
    audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    last_archive_time => SYSTIMESTAMP-2562);
end;
/

begin
  DBMS_SCHEDULER.create_job (
    job_name        => 'AUDIT_LAST_ARCHIVE_TIME',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'DECLARE
                          l_days NUMBER := 2562;
                        BEGIN
                          dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD, TRUNC(SYSTIMESTAMP)-l_days);
                          dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(dbms_audit_mgmt.AUDIT_TRAIL_FGA_STD, TRUNC(SYSTIMESTAMP)-l_days);
                          dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(dbms_audit_mgmt.AUDIT_TRAIL_OS,      TRUNC(SYSTIMESTAMP)-l_days);
                          dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(dbms_audit_mgmt.AUDIT_TRAIL_XML,     TRUNC(SYSTIMESTAMP)-l_days);
                        END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Automatically set audit last archive time.');
end;
/

select * from dba_audit_mgmt_config_params order by 1
;
select * from dba_audit_mgmt_last_arch_ts
;
select job_action from dba_scheduler_jobs where job_name='PURGE_ALL_AUDIT_TRAILS'
;
select job_action from dba_scheduler_jobs where job_name='AUDIT_LAST_ARCHIVE_TIME'
;
commit;

May 14, 2020

RMAN Restore From Tape

Filed under: 11g,Recovery,RMAN — mdinh @ 3:56 am

In my previous post, Testing RMAN Restore Validate From Tape , now I will perform actual restore.

There is requirement to test backup to the new tape library before implementing in production by restoring the database from tape backup.

Database resides on file system. Instead of drop database, shutdown abort, delete data files and control files from disk.

Archived logs resides in FRA and was not deleted which resulted in complete recovery without having to use set until time.

set until time "to_date('2020-05-13 12:10:00','YYYY-MM-DD HH24:MI:SS')";

This is a condense output for restore.

$ grep -i dbid rman_archbackup_HAWKEYE_arch_Wed_202005131210.log
connected to target database: HAWKEYE (DBID=2937483440)

$ rman @ restore.rman

Recovery Manager: Release 11.2.0.4.0 - Production on Wed May 13 15:37:09 2020

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

RMAN> set echo on
2> connect target;
3> run {
4> allocate channel ch1 type 'SBT_TAPE' parms 'SBT_LIBRARY=/opt/libddboostora.so,SBT_PARMS=(CONFIG_FILE=/opt/idpa_ddbea.config)';
5> allocate channel ch2 type 'SBT_TAPE' parms 'SBT_LIBRARY=/opt/libddboostora.so,SBT_PARMS=(CONFIG_FILE=/opt/idpa_ddbea.config)';
6> allocate channel ch3 type 'SBT_TAPE' parms 'SBT_LIBRARY=/opt/libddboostora.so,SBT_PARMS=(CONFIG_FILE=/opt/idpa_ddbea.config)';
7> set dbid 2937483440;
8> restore controlfile from autobackup;
9> alter database mount;
10> restore database;
11> recover database;
12> alter database open resetlogs;
13> release channel ch1;
14> release channel ch2;
15> release channel ch3;
16> }
17> exit
echo set on

connected to target database: HAWKEYE (not mounted)

using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=209 device type=SBT_TAPE
channel ch1: Database Application Agent Oracle v19.4.0.0

allocated channel: ch2
channel ch2: SID=217 device type=SBT_TAPE
channel ch2: Database Application Agent Oracle v19.4.0.0

allocated channel: ch3
channel ch3: SID=225 device type=SBT_TAPE
channel ch3: Database Application Agent Oracle v19.4.0.0

executing command: SET DBID

Starting restore at 2020-05-13 15:37:11

channel ch1: looking for AUTOBACKUP on day: 20200513
channel ch1: AUTOBACKUP found: c-2937483440-20200513-01
channel ch2: looking for AUTOBACKUP on day: 20200513
channel ch2: skipped, AUTOBACKUP already found
channel ch3: looking for AUTOBACKUP on day: 20200513
channel ch3: skipped, AUTOBACKUP already found
channel ch1: restoring control file from AUTOBACKUP c-2937483440-20200513-01
channel ch1: control file restore from AUTOBACKUP complete
output file name=/oradata/HAWKEYE/controlfile/control_01.ctl
output file name=/orafra/HAWKEYE/HAWKEYE/controlfile/control_02.ctl

Finished restore at 2020-05-13 15:37:21

database mounted

Starting restore at 2020-05-13 15:37:26

Starting implicit crosscheck backup at 2020-05-13 15:37:26
Finished implicit crosscheck backup at 2020-05-13 15:37:27

Starting implicit crosscheck copy at 2020-05-13 15:37:27
Finished implicit crosscheck copy at 2020-05-13 15:37:27

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================

channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: reading from backup piece 8juvv2b1_1_1
channel ch1: piece handle=8juvv2b1_1_1 tag=LEVEL0
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:17:55
Finished restore at 2020-05-13 15:55:24

Starting recover at 2020-05-13 15:55:24

channel ch1: starting incremental datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: reading from backup piece a6v01jp8_1_1
channel ch1: piece handle=a6v01jp8_1_1 tag=LEVEL1
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:25

starting media recovery

channel ch1: starting archived log restore to default destination
channel ch1: restoring archived log
channel ch1: reading from backup piece a7v01k17_1_1

channel ch2: starting archived log restore to default destination
channel ch2: restoring archived log
channel ch2: reading from backup piece a9v023is_1_1

channel ch3: starting archived log restore to default destination
channel ch3: restoring archived log
channel ch3: reading from backup piece aav023it_1_1

channel ch1: piece handle=a7v01k17_1_1 tag=LEVEL1
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:01

channel ch1: starting archived log restore to default destination
channel ch1: restoring archived log
channel ch1: reading from backup piece abv023iu_1_1

channel ch2: piece handle=a9v023is_1_1 tag=ARCH
channel ch2: restored backup piece 1
channel ch2: restore complete, elapsed time: 00:00:02

channel ch2: starting archived log restore to default destination
channel ch2: restoring archived log
channel ch2: reading from backup piece acv023iv_1_1

channel ch3: piece handle=aav023it_1_1 tag=ARCH
channel ch3: restored backup piece 1
channel ch3: restore complete, elapsed time: 00:00:02

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

Finished recover at 2020-05-13 15:56:56

database opened

released channel: ch1

released channel: ch2

released channel: ch3

Recovery Manager complete.
$
$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Wed May 13 16:00:32 2020

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

connected to target database: HAWKEYE (DBID=2937483440)</pre>
RMAN> list incarnation of database;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 HAWKEYE 2937483440 PARENT 6278186746 2017-05-22 10:50:52
2 2 HAWKEYE 2937483440 PARENT 6278189261 2017-05-22 11:05:05
3 3 HAWKEYE 2937483440 CURRENT 6747789335 2020-05-13 15:56:56

RMAN> exit

Recovery Manager complete.
$

March 28, 2020

Silent Install 11.2.0.4 DB Software With GI 18c On OEL 7.7

Filed under: 11g,18c,Grid Infrastructure,OEL7,runInstaller — mdinh @ 8:45 pm

Just some note:

One good thing about GUI install is that it allows one to fix any issues and retry and not so much with silent install

================================================================================
Requirements for Installing Oracle 11.2.0.4 RDBMS on OL7 or RHEL7 64-bit (x86-64) (Doc ID 1962100.1)

PRVF-4037 : CRS is not installed on any of the nodes (Doc ID 1316815.1)

Installation of Oracle 11.2.0.4 Database Software on OL7 fails with 'Error in invoking target 'agent nmhs' of makefile ' &
"undefined reference to symbol 'B_DestroyKeyObject'" error (Doc ID 1965691.1)
================================================================================

================================================================================
### First install attempt without -ignorePrereq
================================================================================

$ ./runInstaller -ignorePrereq

Note that the above command does not perform any pre-requisite checks.
Hence, ensure that all the software requirements documented in the install guide are fulfilled before executing the installer using the above option.

================================================================================

[oracle@ol7-183-rac1 ~]$ ./install_db_software.sh

+ /u01/app/oracle/software/database/runInstaller -force -silent -waitforcompletion
-responseFile /u01/app/oracle/software/database/response/db_install.rsp
oracle.install.option=INSTALL_DB_SWONLY
ORACLE_HOSTNAME=ol7-183-rac1.localdomain
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
SELECTED_LANGUAGES=en ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1
ORACLE_BASE=/u01/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.EEOptionsSelection=false
oracle.install.db.DBA_GROUP=dba
oracle.install.db.OPER_GROUP=oper
oracle.install.db.CLUSTER_NODES=ol7-183-rac1,ol7-183-rac2
oracle.installer.autoupdates.option=SKIP_UPDATES
oracle.install.db.isRACOneInstall=false
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false
DECLINE_SECURITY_UPDATES=true

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 25005 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 17391 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2020-03-26_04-15-06PM. Please wait ...

[FATAL] [INS-13013] Target environment do not meet some mandatory requirements.
CAUSE: Some of the mandatory prerequisites are not met. See logs for details. /u01/app/oraInventory/logs/installActions2020-03-26_04-15-06PM.log
ACTION: Identify the list of failed prerequisite checks from the log: /u01/app/oraInventory/logs/installActions2020-03-26_04-15-06PM.log.
Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually.
[oracle@ol7-183-rac1 ~]$

================================================================================
### Review types of errors
================================================================================

[oracle@ol7-183-rac1 ~]$ grep -e '[[:upper:]]: ' /u01/app/oraInventory/logs/installActions2020-03-26_04-15-06PM.log |cut -d ":" -f1 |sort -u
ACTION
CAUSE
INFO
SEVERE
WARNING
[oracle@ol7-183-rac1 ~]$

================================================================================
### Review List of failed Tasks
================================================================================

[oracle@ol7-183-rac1 ~]$ grep -A100 "List of failed Tasks" /u01/app/oraInventory/logs/installActions2020-03-26_04-15-06PM.log
INFO: ------------------List of failed Tasks------------------
INFO: *********************************************
INFO: Package: pdksh-5.2.14: This is a prerequisite condition to test whether the package "pdksh-5.2.14" is available on the system.
INFO: Severity:IGNORABLE
INFO: OverallStatus:VERIFICATION_FAILED
INFO: *********************************************
INFO: CRS Integrity: This test checks the integrity of Oracle Clusterware stack across the cluster nodes.
INFO: Severity:CRITICAL
INFO: OverallStatus:OPERATION_FAILED
INFO: *********************************************
INFO: Cluster Manager Integrity: This test checks the integrity of cluster manager across the cluster nodes.
INFO: Severity:CRITICAL
INFO: OverallStatus:OPERATION_FAILED
INFO: *********************************************
INFO: Node Application Existence: This test checks the existence of Node Applications on the system.
INFO: Severity:CRITICAL
INFO: OverallStatus:OPERATION_FAILED
INFO: *********************************************
INFO: Clock Synchronization: This test checks the Oracle Cluster Time Synchronization Services across the cluster nodes.
INFO: Severity:CRITICAL
INFO: OverallStatus:OPERATION_FAILED
INFO: *********************************************
INFO: Database Clusterware Version Compatibility: This test ensures that the Database version is compatible with the CRS version.
INFO: Severity:CRITICAL
INFO: OverallStatus:OPERATION_FAILED
INFO: -----------------End of failed Tasks List----------------
INFO: Adding ExitStatus PREREQUISITES_NOT_MET to the exit status set
SEVERE: [FATAL] [INS-13013] Target environment do not meet some mandatory requirements.
CAUSE: Some of the mandatory prerequisites are not met. See logs for details. /u01/app/oraInventory/logs/installActions2020-03-26_04-15-06PM.log
ACTION: Identify the list of failed prerequisite checks from the log: /u01/app/oraInventory/logs/installActions2020-03-26_04-15-06PM.log. Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually.
INFO: Advice is ABORT
INFO: Adding ExitStatus INVALID_USER_INPUT to the exit status set
INFO: Completed validating state {performChecks}
INFO: Terminating all background operations
INFO: Terminated all background operations
INFO: Finding the most appropriate exit status for the current application
INFO: Exit Status is -3
INFO: Shutdown Oracle Database 11g Release 2 Installer
[oracle@ol7-183-rac1 ~]$

================================================================================
### Search for "Error Message"
================================================================================

[oracle@ol7-183-rac1 ~]$ grep -i 'error message' /u01/app/oraInventory/logs/installActions2020-03-26_04-15-06PM.log
INFO: Error Message:PRVF-7532 : Package "pdksh" is missing on node "ol7-183-rac2"
INFO: Error Message:PRVF-7532 : Package "pdksh" is missing on node "ol7-183-rac1"
INFO: Error Message:PRVF-4037 : CRS is not installed on any of the nodes
INFO: Error Message:PRVF-4037 : CRS is not installed on any of the nodes
INFO: Error Message:PRVF-4037 : CRS is not installed on any of the nodes
INFO: Error Message:PRVF-4037 : CRS is not installed on any of the nodes
INFO: Error Message:PRVF-4037 : CRS is not installed on any of the nodes
[oracle@ol7-183-rac1 ~]$

================================================================================
PRVF-4037 : CRS is not installed on any of the nodes (Doc ID 1316815.1)
The bug is fixed in 11.2.0.3, the workaround is to update GI home with CRS="true" flag.
================================================================================

================================================================================
### Check inventory for GI RAC install
================================================================================

[oracle@ol7-183-rac1 ContentsXML]$ cat inventory.xml
(?xml version="1.0" standalone="yes" ?)
(!-- Copyright (c) 1999, 2020, Oracle and/or its affiliates.
All rights reserved. --)
(!-- Do not modify the contents of this file by hand. --)
(INVENTORY)
(VERSION_INFO)
(SAVED_WITH)12.2.0.4.0(/SAVED_WITH)
(MINIMUM_VER)2.1.0.6.0(/MINIMUM_VER)
(/VERSION_INFO)
(HOME_LIST)
(HOME NAME="OraGI18Home1" LOC="/u01/app/18.0.0/grid" TYPE="O" IDX="1" CRS="true"/)
(/HOME_LIST)
(COMPOSITEHOME_LIST)
(/COMPOSITEHOME_LIST)
(/INVENTORY)

================================================================================
### UPDATE inventory for GI RAC install
================================================================================

[oracle@ol7-183-rac1 ContentsXML]$ . oraenv {{{ +ASM1
ORACLE_SID = [cdbrac1] ? The Oracle base remains unchanged with value /u01/app/oracle

[oracle@ol7-183-rac1 ContentsXML]$ export GRID_HOME=$ORACLE_HOME

[oracle@ol7-183-rac1 ContentsXML]$ $GRID_HOME/oui/bin/runInstaller -silent -ignoreSysPrereqs -updateNodeList ORACLE_HOME=$GRID_HOME "CLUSTER_NODES={ol7-183-rac1,ol7-183-rac2}" CRS=true
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 17391 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
'UpdateNodeList' was successful.

================================================================================
### VERIFY inventory for GI RAC install
================================================================================

[oracle@ol7-183-rac1 ContentsXML]$ cat inventory.xml
(?xml version="1.0" standalone="yes" ?)
(!-- Copyright (c) 1999, 2020, Oracle and/or its affiliates.
All rights reserved. --)
(!-- Do not modify the contents of this file by hand. --)
(INVENTORY)
(VERSION_INFO)
(SAVED_WITH)12.2.0.4.0(/SAVED_WITH)
(MINIMUM_VER)2.1.0.6.0(/MINIMUM_VER)
(/VERSION_INFO)
(HOME_LIST)
(HOME NAME="OraGI18Home1" LOC="/u01/app/18.0.0/grid" TYPE="O" IDX="1" CRS="true")
(NODE_LIST)
(NODE NAME="ol7-183-rac1"/)
(NODE NAME="ol7-183-rac2"/)
(/NODE_LIST)
(/HOME)
(/HOME_LIST)
(COMPOSITEHOME_LIST)
(/COMPOSITEHOME_LIST)
(/INVENTORY'
[oracle@ol7-183-rac1 ContentsXML]$

================================================================================
### Retry Install
================================================================================

[oracle@ol7-183-rac1 ~]$ cat install_db_software.sh
#!/bin/sh -x
/u01/app/oracle/software/database/runInstaller -force \
-silent -waitforcompletion -ignorePrereq \
-responseFile /u01/app/oracle/software/database/response/db_install.rsp \
oracle.install.option=INSTALL_DB_SWONLY \
ORACLE_HOSTNAME=ol7-183-rac1.localdomain \
UNIX_GROUP_NAME=oinstall \
INVENTORY_LOCATION=/u01/app/oraInventory \
SELECTED_LANGUAGES=en \
ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1 \
ORACLE_BASE=/u01/app/oracle \
oracle.install.db.InstallEdition=EE \
oracle.install.db.EEOptionsSelection=false \
oracle.install.db.DBA_GROUP=dba \
oracle.install.db.OPER_GROUP=oper \
oracle.install.db.CLUSTER_NODES=ol7-183-rac1,ol7-183-rac2 \
oracle.installer.autoupdates.option=SKIP_UPDATES \
oracle.install.db.isRACOneInstall=false \
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false \
DECLINE_SECURITY_UPDATES=true
[oracle@ol7-183-rac1 ~]$

[oracle@ol7-183-rac1 ~]$ ./install_db_software.sh
+ /u01/app/oracle/software/database/runInstaller -force -silent -waitforcompletion -ignorePrereq
-responseFile /u01/app/oracle/software/database/response/db_install.rsp
oracle.install.option=INSTALL_DB_SWONLY
ORACLE_HOSTNAME=ol7-183-rac1.localdomain
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
SELECTED_LANGUAGES=en
ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1
ORACLE_BASE=/u01/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.EEOptionsSelection=false
oracle.install.db.DBA_GROUP=dba
oracle.install.db.OPER_GROUP=oper
oracle.install.db.CLUSTER_NODES=ol7-183-rac1,ol7-183-rac2
oracle.installer.autoupdates.option=SKIP_UPDATES
oracle.install.db.isRACOneInstall=false
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false
DECLINE_SECURITY_UPDATES=true

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 24578 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 17391 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2020-03-26_05-17-28PM. Please wait ...

You can find the log of this install session at:
/u01/app/oraInventory/logs/installActions2020-03-26_05-17-28PM.log

The installation of Oracle Database 11g was successful.
Please check '/u01/app/oraInventory/logs/silentInstall2020-03-26_05-17-28PM.log' for more details.

As a root user, execute the following script(s):
1. /u01/app/oracle/product/11.2.0.4/dbhome_1/root.sh

Execute /u01/app/oracle/product/11.2.0.4/dbhome_1/root.sh on the following nodes:
[ol7-183-rac1, ol7-183-rac2]

Successfully Setup Software.
[oracle@ol7-183-rac1 ~]$

[root@ol7-183-rac1 ~]# /u01/app/oracle/product/11.2.0.4/dbhome_1/root.sh
Check /u01/app/oracle/product/11.2.0.4/dbhome_1/install/root_ol7-183-rac1.localdomain_2020-03-26_17-44-13.log for the output of root script
[root@ol7-183-rac1 ~]#

[root@ol7-183-rac2 ~]# /u01/app/oracle/product/11.2.0.4/dbhome_1/root.sh
Check /u01/app/oracle/product/11.2.0.4/dbhome_1/install/root_ol7-183-rac2.localdomain_2020-03-26_17-44-55.log for the output of root script
[root@ol7-183-rac2 ~]#

================================================================================
### FROM silentInstall*.log - Known Issues - (Doc ID 1965691.1)
================================================================================

[oracle@ol7-183-rac1 ~]$ cat /u01/app/oraInventory/logs/silentInstall2020-03-26_05-17-28PM.log
silentInstall2020-03-26_05-17-28PM.log
sNativeVolName:/u01/app/oracle/product/11.2.0.4/dbhome_1/
m_asNodeArray:ol7-183-rac1,ol7-183-rac2
m_sLocalNode:ol7-183-rac1
sNativeVolName:/tmp/
m_asNodeArray:ol7-183-rac1,ol7-183-rac2
m_sLocalNode:ol7-183-rac1
Error in invoking target 'agent nmhs' of makefile '/u01/app/oracle/product/11.2.0.4/dbhome_1/sysman/lib/ins_emagent.mk'. See '/u01/app/oraInventory/logs/installActions2020-03-26_05-17-28PM.log' for details.
sNativeVolName:/u01/app/oracle/
m_asNodeArray:ol7-183-rac1,ol7-183-rac2
m_sLocalNode:ol7-183-rac1
sNativeVolName:/u01/app/oraInventory/
m_asNodeArray:ol7-183-rac1,ol7-183-rac2
m_sLocalNode:ol7-183-rac1
The installation of Oracle Database 11g was successful.
[oracle@ol7-183-rac1 ~]$

================================================================================
### Check installActions*.log
================================================================================

[oracle@ol7-183-rac1 ~]$ grep -e '[[:upper:]]: ' /u01/app/oraInventory/logs/installActions2020-03-26_05-17-28PM.log |cut -d ":" -f1 |sort -u
INFO
WARNING
[oracle@ol7-183-rac1 ~]$

================================================================================
### Check inventory for DB RAC install
================================================================================

[oracle@ol7-183-rac1 ContentsXML]$ cat inventory.xml
{?xml version="1.0" standalone="yes" ?}
{!-- Copyright (c) 1999, 2013, Oracle and/or its affiliates.
All rights reserved. --}
{!-- Do not modify the contents of this file by hand. --}
{INVENTORY}
{VERSION_INFO}
{SAVED_WITH}11.2.0.4.0{/SAVED_WITH}
{MINIMUM_VER}2.1.0.6.0{/MINIMUM_VER}
{/VERSION_INFO}
{HOME_LIST}
{HOME NAME="OraGI18Home1" LOC="/u01/app/18.0.0/grid" TYPE="O" IDX="1" CRS="true"}
{NODE_LIST}
{NODE NAME="ol7-183-rac1"/}
{NODE NAME="ol7-183-rac2"/}
{/NODE_LIST}
{/HOME}
{HOME NAME="OraDb11g_home1" LOC="/u01/app/oracle/product/11.2.0.4/dbhome_1" TYPE="O" IDX="2"}
{NODE_LIST}
{NODE NAME="ol7-183-rac1"/}
{NODE NAME="ol7-183-rac2"/}
{/NODE_LIST}
{/HOME}
{/HOME_LIST}
{COMPOSITEHOME_LIST}
{/COMPOSITEHOME_LIST}
{/INVENTORY}
[oracle@ol7-183-rac1 ContentsXML]$

================================================================================
### cluvfy comp healthcheck
================================================================================

[oracle@ol7-183-rac1 cvu]$ . oraenv <<< +ASM1
ORACLE_SID = [cdbrac1] ? The Oracle base remains unchanged with value /u01/app/oracle

[oracle@ol7-183-rac1 ~]$ cluvfy comp software

Verification of Health Check was unsuccessful.
Checks did not pass for the following nodes:
ol7-183-rac2,ol7-183-rac1

Failures were encountered during execution of CVU verification request "Health Check".

Verifying Physical Memory ...FAILED
ol7-183-rac2: PRVF-7530 : Sufficient physical memory is not available on node
"ol7-183-rac2" [Required physical memory = 8GB (8388608.0KB)]

ol7-183-rac1: PRVF-7530 : Sufficient physical memory is not available on node
"ol7-183-rac1" [Required physical memory = 8GB (8388608.0KB)]

Verifying Ethernet Jumbo Frames ...FAILED
ol7-183-rac2: PRVE-0293 : Jumbo Frames are not configured for interconnects
"eth2" on node "ol7-183-rac2.localdomain". [Expected="eth2=9000";
Found="eth2=1500"]

ol7-183-rac1: PRVE-0293 : Jumbo Frames are not configured for interconnects
"eth2" on node "ol7-183-rac1.localdomain". [Expected="eth2=9000";
Found="eth2=1500"]

CVU operation performed:      Health Check
Date:                         Mar 26, 2020 6:07:08 PM
CVU home:                     /u01/app/18.0.0/grid/
User:                         oracle
[oracle@ol7-183-rac1 cvu]$

September 5, 2018

tnsping for DataGuard

Filed under: 11g,Dataguard — mdinh @ 10:56 pm

I am preparing Dataguard for switchover with 1 primary and 3 standbys and should be able to tnsping all the services from log_archive_config=DG_CONFIG=(HAWKA,HAWKB,HAWKC)

Not sure how valuable this may be for you as I wanted to perform all the tasks in one command and know where the error is at.

tnsping HAWKC is failing in the 2nd position.

[oracle@db-fs-1 ~]$ { tnsping HAWKA & tnsping HAWKC & tnsping HAWKB & echo ; } > /tmp/tnsping_`hostname -s`; ls -l /tmp/tnsping_`hostname -s`
[1] 18375
[2] 18376
[3] 18377
-rw-r--r-- 1 oracle oinstall 1208 Sep  6 00:45 /tmp/tnsping_db-fs-1
[1]   Done                    tnsping HAWKA
[2]-  Exit 1                  tnsping HAWKC
[3]+  Done                    tnsping HAWKB
[oracle@db-fs-1 ~]$

tnsping HAWKC is failing in the 3rd position.

[oracle@db-fs-1 ~]$ { tnsping HAWKA & tnsping HAWKB & tnsping HAWKC & echo $? ; } > /tmp/tnsping_`hostname -s`; ls -l /tmp/tnsping_`hostname -s`
[1] 18433
[2] 18434
[3] 18435
-rw-r--r-- 1 oracle oinstall 1210 Sep  6 00:46 /tmp/tnsping_db-fs-1
[1]   Done                    tnsping HAWKA
[2]-  Done                    tnsping HAWKB
[3]+  Exit 1                  tnsping HAWKC
[oracle@db-fs-1 ~]$

tnsping HAWKC is failing in the 3rd position.
There were 3 processes spawned and had to press enter to get final results.

[oracle@db-fs-1 ~]$ { tnsping HAWKA & tnsping HAWKB & tnsping HAWKC & echo $? ; } > /tmp/tnsping_`hostname -s`; ls -l /tmp/tnsping_`hostname -s`
[1] 18469
[2] 18470
[3] 18471
-rw-r--r-- 1 oracle oinstall 837 Sep  6 00:47 /tmp/tnsping_db-fs-1
[1]   Done                    tnsping HAWKA
[3]+  Exit 1                  tnsping HAWKC
[oracle@db-fs-1 ~]$
[2]+  Done                    tnsping HAWKB
[oracle@db-fs-1 ~]$

Remove tnsping HAWKC to demo working results.

[oracle@db-fs-1 ~]$ { tnsping HAWKA & tnsping HAWKB & echo ; } > /tmp/tnsping_`hostname -s`; ls -l /tmp/tnsping_`hostname -s`
[1] 18500
[2] 18501
-rw-r--r-- 1 oracle oinstall 955 Sep  6 00:47 /tmp/tnsping_db-fs-1
[1]-  Done                    tnsping HAWKA
[2]+  Done                    tnsping HAWKB
[oracle@db-fs-1 ~]$

UPDATE:
Another preference suggested is to use for loops.

for s in "HAWKA" "HAWKB" "HAWKC"
do
echo $s
tnsping $s >> /tmp/log
done

August 24, 2018

RMAN: Synchronize standby database using production archivelog backupset

Filed under: 11g,Dataguard,RMAN — mdinh @ 3:07 am

If you have not read RMAN: Synchronize standby database using production archivelog, then please do so.

# Primary archivelog is on local vs shared storage.
# Primary RMAN archivelog backupset resides on shared folder with Standby.
# Full backup is performed once per day and include archivelog with format arch_DB02_`date '+%Y%m%d'
# MANAGED REAL TIME APPLY is running.
PRI: /shared/prod/DB02/rman/
SBY: /shared/backup/arch/DB02a/

#!/bin/sh -e
# Michael Dinh: Aug 21, 2018
# RMAN sync standby using production archivelog backupset
#
. ~/working/dinh/dinh.env
. ~/working/dinh/DB02a.env
sysresv|tail -1
set -x
# List production archivelog backupset for current day
ls -l /shared/prod/DB02/rman/arch_DB02_`date '+%Y%m%d'`*
# Copy production archivelog backupset for current day to standby
cp -ufv /shared/prod/DB02/rman/arch_DB02_`date '+%Y%m%d'`* /shared/backup/arch/DB02a
rman msglog /tmp/rman_sync_standby.log > /dev/null << EOF
set echo on;
connect target;
show all;
# Catalog production archivelog backupset from standby
catalog start with '/shared/backup/arch/DB02a' noprompt;
# Restore production archivelog backupset to standby
restore archivelog from time 'trunc(sysdate)-1';
exit
EOF
sleep 15m
# Verify Media Recovery Log from alert log
tail -20 $ORACLE_BASE/diag/rdbms/$ORACLE_UNQNAME/$ORACLE_SID/trace/alert_$ORACLE_SID.log
exit
$ crontab -l
00 12 * * * /home/oracle/working/dinh/rman_sync_standby.sh > /tmp/rman_sync_standby.sh.out 2>&1

$ ll /tmp/rman*
-rw-r--r--. 1 oracle oinstall 7225 Aug 22 12:01 /tmp/rman_sync_standby.log
-rw-r--r--. 1 oracle oinstall 4318 Aug 22 12:16 /tmp/rman_sync_standby.sh.out

+ tail -20 /u01/app/oracle/diag/rdbms/DB02a/DB02a2/trace/alert_DB02a2.log
ALTER DATABASE RECOVER  managed standby database using current logfile nodelay disconnect  
ORA-1153 signalled during: ALTER DATABASE RECOVER  managed standby database using current logfile nodelay disconnect  ...
Tue Aug 21 15:41:27 2018
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
Tue Aug 21 15:54:30 2018
db_recovery_file_dest_size of 204800 MB is 21.54% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Wed Aug 22 12:01:21 2018
Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31636.1275.984830461
Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31637.1276.984830461
Wed Aug 22 12:01:46 2018
Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31638.1278.984830487
Wed Aug 22 12:01:58 2018
Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31639.1277.984830487
Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31640.1279.984830487
Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31641.1280.984830487
Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31642.1281.984830489
Media Recovery Waiting for thread 1 sequence 31643
+ exit

# Manual recovery: WAIT_FOR_LOG and BLOCK#=0 and never increment.
SQL> r
  1  select PID,inst_id inst,thread#,client_process,process,status,sequence#,block#,DELAY_MINS
  2  from gv$managed_standby
  3  where 1=1
  4  and status not in ('CLOSING','IDLE','CONNECTED')
  5  order by status desc, thread#, sequence#
  6*

                        CLIENT                                               DELAY
     PID  INST  THREAD# PROCESS    PROCESS  STATUS       SEQUENCE#   BLOCK#   MINS
-------- ----- -------- ---------- -------- ------------ --------- -------- ------
   94734     2        1 N/A        MRP0     WAIT_FOR_LOG     31643        0      0

SQL>
$ cat /tmp/rman_sync_standby.log 

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Aug 22 12:00:58 2018

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

RMAN> 
echo set on

RMAN> connect target;
connected to target database: DB02 (DBID=1816794213, not open)

RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name DB02A are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 7;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE DEVICE TYPE 'SBT_TAPE' BACKUP TYPE TO BACKUPSET PARALLELISM 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'SBT_LIBRARY=/u01/app/oracle/product/11.2.0/dbhome_1/lib/libddobk.so, ENV=(STORAGE_UNIT=dd-u99,BACKUP_HOST=dd860.ccx.carecentrix.com,ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/db/11.2.0.4/dbs/snapcf_DB02a2.f'; # default

RMAN> catalog start with '/shared/backup/arch/DB02a' noprompt;
searching for all files that match the pattern /shared/backup/arch/DB02a

List of Files Unknown to the Database
=====================================
File Name: /shared/backup/arch/DB02a/arch_DB02_20180822_9ttb6h01_1_1
File Name: /shared/backup/arch/DB02a/arch_DB02_20180822_9utb6h02_1_1
File Name: /shared/backup/arch/DB02a/arch_DB02_20180822_9vtb6h02_1_1
File Name: /shared/backup/arch/DB02a/arch_DB02_20180822_a9tb6j6q_1_1
File Name: /shared/backup/arch/DB02a/arch_DB02_20180822_aatb6j6q_1_1
File Name: /shared/backup/arch/DB02a/arch_DB02_20180822_abtb6j6q_1_1
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /shared/backup/arch/DB02a/arch_DB02_20180822_9ttb6h01_1_1
File Name: /shared/backup/arch/DB02a/arch_DB02_20180822_9utb6h02_1_1
File Name: /shared/backup/arch/DB02a/arch_DB02_20180822_9vtb6h02_1_1
File Name: /shared/backup/arch/DB02a/arch_DB02_20180822_a9tb6j6q_1_1
File Name: /shared/backup/arch/DB02a/arch_DB02_20180822_aatb6j6q_1_1
File Name: /shared/backup/arch/DB02a/arch_DB02_20180822_abtb6j6q_1_1

RMAN> restore archivelog from time 'trunc(sysdate)-1';
Starting restore at 22-AUG-2018 12:01:00
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=285 instance=DB02a2 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=3 instance=DB02a2 device type=DISK

archived log for thread 1 with sequence 31630 is already on disk as file +FRA/DB02a/archivelog/2018_08_21/thread_1_seq_31630.496.984755257
archived log for thread 1 with sequence 31631 is already on disk as file +FRA/DB02a/archivelog/2018_08_21/thread_1_seq_31631.497.984755273
archived log for thread 1 with sequence 31632 is already on disk as file +FRA/DB02a/archivelog/2018_08_21/thread_1_seq_31632.498.984755273
archived log for thread 1 with sequence 31633 is already on disk as file +FRA/DB02a/archivelog/2018_08_21/thread_1_seq_31633.499.984755275
archived log for thread 1 with sequence 31634 is already on disk as file +FRA/DB02a/archivelog/2018_08_21/thread_1_seq_31634.500.984755275
archived log for thread 1 with sequence 31635 is already on disk as file +FRA/DB02a/archivelog/2018_08_21/thread_1_seq_31635.501.984755275
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=31636
channel ORA_DISK_1: reading from backup piece /shared/backup/arch/DB02a/arch_DB02_20180822_9ttb6h01_1_1
channel ORA_DISK_2: starting archived log restore to default destination
channel ORA_DISK_2: restoring archived log
archived log thread=1 sequence=31637
channel ORA_DISK_2: reading from backup piece /shared/backup/arch/DB02a/arch_DB02_20180822_9utb6h02_1_1
channel ORA_DISK_1: piece handle=/shared/backup/arch/DB02a/arch_DB02_20180822_9ttb6h01_1_1 tag=TAG20180822T110121
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=31638
channel ORA_DISK_1: reading from backup piece /shared/backup/arch/DB02a/arch_DB02_20180822_9vtb6h02_1_1
channel ORA_DISK_2: piece handle=/shared/backup/arch/DB02a/arch_DB02_20180822_9utb6h02_1_1 tag=TAG20180822T110121
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:25
channel ORA_DISK_2: starting archived log restore to default destination
channel ORA_DISK_2: restoring archived log
archived log thread=1 sequence=31639
channel ORA_DISK_2: reading from backup piece /shared/backup/arch/DB02a/arch_DB02_20180822_a9tb6j6q_1_1
channel ORA_DISK_2: piece handle=/shared/backup/arch/DB02a/arch_DB02_20180822_a9tb6j6q_1_1 tag=TAG20180822T113906
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:01
channel ORA_DISK_2: starting archived log restore to default destination
channel ORA_DISK_2: restoring archived log
archived log thread=1 sequence=31640
channel ORA_DISK_2: restoring archived log
archived log thread=1 sequence=31641
channel ORA_DISK_2: reading from backup piece /shared/backup/arch/DB02a/arch_DB02_20180822_aatb6j6q_1_1
channel ORA_DISK_2: piece handle=/shared/backup/arch/DB02a/arch_DB02_20180822_aatb6j6q_1_1 tag=TAG20180822T113906
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:01
channel ORA_DISK_2: starting archived log restore to default destination
channel ORA_DISK_2: restoring archived log
archived log thread=1 sequence=31642
channel ORA_DISK_2: reading from backup piece /shared/backup/arch/DB02a/arch_DB02_20180822_abtb6j6q_1_1
channel ORA_DISK_2: piece handle=/shared/backup/arch/DB02a/arch_DB02_20180822_abtb6j6q_1_1 tag=TAG20180822T113906
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: piece handle=/shared/backup/arch/DB02a/arch_DB02_20180822_9vtb6h02_1_1 tag=TAG20180822T110121
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:17
Finished restore at 22-AUG-2018 12:01:44

RMAN> exit
$ cat /tmp/rman_sync_standby.sh.out 
ORACLE_SID = [oracle] ? The Oracle base has been set to /u01/app/oracle
Oracle Instance alive for sid "DB02a2"
CURRENT_INSTANCE=DB02a2
ORACLE_UNQNAME=DB02a
OTHER_INSTANCE=DB02a3,DB02a4
ORACLE_SID=DB02a2
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/db/11.2.0.4
NLS_DATE_FORMAT=DD-MON-YYYY HH24:MI:SS
Oracle Instance alive for sid "DB02a2"
++ date +%Y%m%d
+ ls -l /shared/prod/DB02/rman/arch_DB02_20180822_9ttb6h01_1_1 /shared/prod/DB02/rman/arch_DB02_20180822_9utb6h02_1_1 /shared/prod/DB02/rman/arch_DB02_20180822_9vtb6h02_1_1 /shared/prod/DB02/rman/arch_DB02_20180822_a9tb6j6q_1_1 /shared/prod/DB02/rman/arch_DB02_20180822_aatb6j6q_1_1 /shared/prod/DB02/rman/arch_DB02_20180822_abtb6j6q_1_1
-rw-r-----. 1 oracle dba 1900124160 Aug 22 11:02 /shared/prod/DB02/rman/arch_DB02_20180822_9ttb6h01_1_1
-rw-r-----. 1 oracle dba 1938098176 Aug 22 11:02 /shared/prod/DB02/rman/arch_DB02_20180822_9utb6h02_1_1
-rw-r-----. 1 oracle dba 1370842112 Aug 22 11:01 /shared/prod/DB02/rman/arch_DB02_20180822_9vtb6h02_1_1
-rw-r-----. 1 oracle dba   11870720 Aug 22 11:39 /shared/prod/DB02/rman/arch_DB02_20180822_a9tb6j6q_1_1
-rw-r-----. 1 oracle dba       3584 Aug 22 11:39 /shared/prod/DB02/rman/arch_DB02_20180822_aatb6j6q_1_1
-rw-r-----. 1 oracle dba       3072 Aug 22 11:39 /shared/prod/DB02/rman/arch_DB02_20180822_abtb6j6q_1_1
++ date +%Y%m%d
+ cp -ufv /shared/prod/DB02/rman/arch_DB02_20180822_9ttb6h01_1_1 /shared/prod/DB02/rman/arch_DB02_20180822_9utb6h02_1_1 /shared/prod/DB02/rman/arch_DB02_20180822_9vtb6h02_1_1 /shared/prod/DB02/rman/arch_DB02_20180822_a9tb6j6q_1_1 /shared/prod/DB02/rman/arch_DB02_20180822_aatb6j6q_1_1 /shared/prod/DB02/rman/arch_DB02_20180822_abtb6j6q_1_1 /shared/backup/arch/DB02a
\u2018/shared/prod/DB02/rman/arch_DB02_20180822_9ttb6h01_1_1\u2019 -> \u2018/shared/backup/arch/DB02a/arch_DB02_20180822_9ttb6h01_1_1\u2019
\u2018/shared/prod/DB02/rman/arch_DB02_20180822_9utb6h02_1_1\u2019 -> \u2018/shared/backup/arch/DB02a/arch_DB02_20180822_9utb6h02_1_1\u2019
\u2018/shared/prod/DB02/rman/arch_DB02_20180822_9vtb6h02_1_1\u2019 -> \u2018/shared/backup/arch/DB02a/arch_DB02_20180822_9vtb6h02_1_1\u2019
\u2018/shared/prod/DB02/rman/arch_DB02_20180822_a9tb6j6q_1_1\u2019 -> \u2018/shared/backup/arch/DB02a/arch_DB02_20180822_a9tb6j6q_1_1\u2019
\u2018/shared/prod/DB02/rman/arch_DB02_20180822_aatb6j6q_1_1\u2019 -> \u2018/shared/backup/arch/DB02a/arch_DB02_20180822_aatb6j6q_1_1\u2019
\u2018/shared/prod/DB02/rman/arch_DB02_20180822_abtb6j6q_1_1\u2019 -> \u2018/shared/backup/arch/DB02a/arch_DB02_20180822_abtb6j6q_1_1\u2019
+ rman msglog /tmp/rman_sync_standby.log
+ sleep 15m
+ tail -20 /u01/app/oracle/diag/rdbms/DB02a/DB02a2/trace/alert_DB02a2.log
ALTER DATABASE RECOVER  managed standby database using current logfile nodelay disconnect  
ORA-1153 signalled during: ALTER DATABASE RECOVER  managed standby database using current logfile nodelay disconnect  ...
Tue Aug 21 15:41:27 2018
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
Tue Aug 21 15:54:30 2018
db_recovery_file_dest_size of 204800 MB is 21.54% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Wed Aug 22 12:01:21 2018
Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31636.1275.984830461
Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31637.1276.984830461
Wed Aug 22 12:01:46 2018
Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31638.1278.984830487
Wed Aug 22 12:01:58 2018
Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31639.1277.984830487
Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31640.1279.984830487
Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31641.1280.984830487
Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31642.1281.984830489
Media Recovery Waiting for thread 1 sequence 31643
+ exit

August 21, 2018

RMAN: Synchronize standby database using production archivelog

Filed under: 11g,Dataguard,RMAN — mdinh @ 11:39 pm

I know what you are thinking, “Why is this nut of a DBA writing shell script to synchronize standby with achivelog !”

It just happens the environment is very restrictive and NO changes can be made without any change control.

In one week, there’s a planned switchover to RAC standby and it would be nice to have standby duplicated and ready for switchover.

How is this going to work as standby will lag for days until switchover?

Have no fear, there’s a script for that.

# Primary archivelog resides on shared folder with Standby.
# MANAGED REAL TIME APPLY is running.
PRI: /shared/prod/DB01/arch/
SBY: /shared/backup/arch/DB01/

#!/bin/sh 
# rman_cat_arc.sh
# Michael Dinh Aug 21, 2018
#
# Don't forget to set environment here.
#
set -x
# list 5 most recent achivelog
ls -lrt /shared/prod/DB01/arch/|tail -5
# copy archivelog created in last 1 hour since cron runs script every 1 hour.
/bin/find /shared/prod/DB01/arch/ -type f -mmin -60 -exec cp -ufv {} /shared/backup/arch/DB01/ \;
# 
rman msglog /tmp/rman_cat_arc.log > /dev/null << EOF
set echo on;
connect target;
# delete achivelog older than 3 hours
delete force noprompt archivelog until time 'sysdate-3/24';
catalog start with '/shared/backup/arch/DB01/' noprompt;
EOF
# Review alert log
tail -20f $ORACLE_BASE/diag/rdbms/$ORACLE_UNQNAME/$ORACLE_SID/trace/alert_$ORACLE_SID.log
exit
 
# crontab
26 * * * * /home/oracle/rman_cat_arc.sh > /tmp/rman_cat_arc.sh.out 2>&1

# logs
$ ll /tmp/rman*
-rw-r--r--. 1 oracle oinstall 2664 Aug 21 11:26 /tmp/rman_cat_arc.log
-rw-r--r--. 1 oracle oinstall 1852 Aug 21 11:26 /tmp/rman_cat_arc.sh.out

# alert log 
Tue Aug 21 08:26:09 2018
Media Recovery Log /shared/backup/arch/DB01/DB01_1_717897269_86391.arc
Media Recovery Log /shared/backup/arch/DB01/DB01_1_717897269_86392.arc
Media Recovery Log /shared/backup/arch/DB01/DB01_1_717897269_86393.arc
Media Recovery Waiting for thread 1 sequence 86394
Tue Aug 21 09:26:06 2018
Media Recovery Log /shared/backup/arch/DB01/DB01_1_717897269_86394.arc
Media Recovery Log /shared/backup/arch/DB01/DB01_1_717897269_86395.arc
Media Recovery Log /shared/backup/arch/DB01/DB01_1_717897269_86396.arc
Media Recovery Log /shared/backup/arch/DB01/DB01_1_717897269_86397.arc
Media Recovery Waiting for thread 1 sequence 86398
Tue Aug 21 10:26:08 2018
Media Recovery Log /shared/backup/arch/DB01/DB01_1_717897269_86398.arc
Media Recovery Log /shared/backup/arch/DB01/DB01_1_717897269_86399.arc
Media Recovery Waiting for thread 1 sequence 86400
Tue Aug 21 11:26:06 2018
Media Recovery Log /shared/backup/arch/DB01/DB01_1_717897269_86400.arc
Media Recovery Log /shared/backup/arch/DB01/DB01_1_717897269_86401.arc
Media Recovery Waiting for thread 1 sequence 86402
Tue Aug 21 11:49:03 2018

select PID,inst_id inst,thread#,client_process,process,status,sequence#,block#,DELAY_MINS
from gv$managed_standby
where 1=1
and status not in ('CLOSING','IDLE','CONNECTED')
order by status desc, thread#, sequence#
;

# Manual recovery: WAIT_FOR_LOG and BLOCK#=0 and never increment.
*** gv$managed_standby ***
                        CLIENT                                               DELAY
     PID  INST  THREAD# PROCESS    PROCESS  STATUS       SEQUENCE#   BLOCK#   MINS
-------- ----- -------- ---------- -------- ------------ --------- -------- ------
  411795     4        1 N/A        MRP0     WAIT_FOR_LOG     86178        0      0
  
# MANAGED REAL TIME APPLY: APPLYING_LOG and BLOCK#>0 increments
*** gv$managed_standby ***
                        CLIENT                                               DELAY
     PID  INST  THREAD# PROCESS    PROCESS  STATUS       SEQUENCE#   BLOCK#   MINS
-------- ----- -------- ---------- -------- ------------ --------- -------- ------
  245652     4        1 N/A        MRP0     APPLYING_LOG     86410      472      0  
Next Page »

Blog at WordPress.com.