NO_MERGE hint, example (2/2)

Part 1

Now, what I run is (I call this CQ - CA Query )

SELECT x.tablespace_name, sum(x.bytes) bytes_expired
FROM ( SELECT a.tablespace_name, bytes FROM dba_undo_extents a WHERE status = 'EXPIRED') x
GROUP BY x.tablespace_name;

SQL> set timing on 
SQL> set autot on exp stat
SQL> set lines 160


SELECT x.tablespace_name, sum(x.bytes) bytes_expired 
FROM ( SELECT a.tablespace_name, bytes FROM dba_undo_extents a WHERE status = 'EXPIRED') x 
GROUP BY x.tablespace_name;

TABLESPACE_NAME                BYTES_EXPIRED
------------------------------ -------------
UNDOTBS1                       1844445184 
UNDOTBS2                       4526112768 

 Elapsed: 00:03:52.20 

Execution Plan 
---------------------------------------------------------- 

Plan hash value: 2680378823 
-------------------------------------------------------------------------------- 
| Id   | Operation              | Name     | Rows | Bytes | Cost (%CPU)| Time  | 
-------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT        |          |  109 |  5886 | 77  (62)| 00:00:01 | 
|   1 |  HASH GROUP BY          |          |  109 |  5886 | 77  (62)| 00:00:01 | 
|   2 |   NESTED LOOPS          |          |  109 |  5886 | 77  (62)| 00:00:01 | 
|   3 |    VIEW                 | VW_GBC_5 |  109 |  5014 | 77  (62)| 00:00:01 | 
|   4 |     HASH GROUP BY       |          |  109 | 13625 | 77  (62)| 00:00:01 | 
|*  5 |      HASH JOIN          |          |  182 | 22750 | 76  (61)| 00:00:01 | 
|*  6 |       HASH JOIN         |          |  182 | 17108 | 48  (96)| 00:00:01 | 
|*  7 |        TABLE ACCESS FULL| UNDO$    |  141 |  2256 |  2   (0)| 00:00:01 | 
|*  8 |        FIXED TABLE FULL | X$KTFBUE | 1000 | 78000 | 45 (100)| 00:00:01 | 
|*  9 |       TABLE ACCESS FULL | TS$      |  154 |  4774 | 28   (0)| 00:00:01 | 
|* 10 |    INDEX UNIQUE SCAN    | I_FILE2  |    1 |     8 |  0   (0)| 00:00:01 | 
-------------------------------------------------------------------------------- 

 Predicate Information (identified by operation id): 
--------------------------------------------------- 
  5 - access("T"."TS#"="U"."TS#") 
  6 - access("E"."KTFBUESEGTSN"="U"."TS#" AND "E"."KTFBUESEGBNO"="U"."BLOCK#" 
             AND "E"."KTFBUESEGFNO"="U"."FILE#") 
  7 - filter(("U"."SPARE1"=1 OR "U"."SPARE1"=2) AND "U"."STATUS$"<>1) 
  8 - filter(DECODE("E"."KTFBUESTA",1,'ACTIVE',2,'EXPIRED',3,'UNEXPIRED','U NDEFINED')='EXPIRED') 
  9 - filter(BITAND("T"."FLAGS",16777216)=0) 
 10 - access("ITEM_1"="F"."TS#" AND "ITEM_2"="F"."RELFILE#") 

 Statistics 
---------------------------------------------------------- 
  84496 recursive calls 
  61604 db block gets 
 447085 consistent gets 
 140720 physical reads 
      0 redo size 
    704 bytes sent via SQL*Net to client 
    552 bytes received via SQL*Net from client 
      2 SQL*Net roundtrips to/from client 
     16 sorts (memory) 
      0 sorts (disk) 
      2 rows processed

If you compare "AQ" execution plan and "CQ" execution plan, you can see that they are the same. This mean that CBO made some manipulations during the choice of the best plan.

Pay attention. The behavior of the CBO is normal. The problem is that sometime the choice of the execution plan is not the best.

The manipulation that CBO used on "CQ" is to merge the external and internal select together. The effect is that "CQ" becomes "AQ".

So, what I do now, is to use the NO_MERGE hint, in order to force CBO to not make manipulation (statement DQ).


SELECT /*+ NO_MERGE(x) */ tablespace_name, sum(bytes) bytes_expired from 
( select a.tablespace_name, bytes FROM dba_undo_extents a WHERE status = 'EXPIRED') x 
group by tablespace_name;

TABLESPACE_NAME                BYTES_EXPIRED 
------------------------------ ------------- 
UNDOTBS1                       1400766464 
UNDOTBS2                       4706729984 

Elapsed: 00:00:00.08 


Execution Plan 
---------------------------------------------------------- 

Plan hash value: 1487528911 
--------------------------------------------------------------------------------------------- 
| Id | Operation                   | Name             | Rows| Bytes | Cost (%CPU)| Time     | 
--------------------------------------------------------------------------------------------- 
|  0 | SELECT STATEMENT            |                  |   1 |    30 |  1985  (99)| 00:00:01 | 
|  1 |  HASH GROUP BY              |                  |   1 |    30 |  1985  (99)| 00:00:01 | 
|  2 |   VIEW                      |                  |   1 |    30 |  1984  (99)| 00:00:01 | 
|  3 |    NESTED LOOPS             |                  |   1 |   133 |  1984  (99)| 00:00:01 | 
|  4 |     NESTED LOOPS            |                  | 182 | 22750 |  1984  (99)| 00:00:01 | 
|* 5 |      HASH JOIN              |                  | 141 |  6627 |    30   (0)| 00:00:01 | 
|* 6 |       TABLE ACCESS FULL     | UNDO$            | 141 |  2256 |     2   (0)| 00:00:01 | 
|* 7 |       TABLE ACCESS FULL     | TS$              | 154 |  4774 |    28   (0)| 00:00:01 | 
|* 8 |      FIXED TABLE FIXED INDEX| X$KTFBUE (ind:1) |   1 |    78 |    14 (100)| 00:00:01 | 
|* 9 |     INDEX UNIQUE SCAN       | I_FILE2          |   1 |     8 |     0   (0)| 00:00:01 | 
--------------------------------------------------------------------------------------------- 

 Predicate Information (identified by operation id): 
--------------------------------------------------- 
 5 - access("T"."TS#"="U"."TS#") 
 6 - filter(("U"."SPARE1"=1 OR "U"."SPARE1"=2) AND "U"."STATUS$"<>1) 
 7 - filter(BITAND("T"."FLAGS",16777216)=0) 
 8 - filter("E"."KTFBUESEGBNO"="U"."BLOCK#" 
            AND "E"."KTFBUESEGFNO"="U"."FILE#" AND "E"."KTFBUESEGTSN"="U"."TS#" 
            AND DECODE("E"."KTFBUESTA",1,'ACTIVE',2,'EXPIRED', 3,'UNEXPIRED','UNDEFINED')='EXPIRED') 
 9 - access("U"."TS#"="F"."TS#" AND "E"."KTFBUEFNO"="F"."RELFILE#") 

 Note ----- 
 - this is an adaptive plan 

 Statistics 
---------------------------------------------------------- 
 141 recursive calls 
   0 db block gets 
 876 consistent gets 
  25 physical reads 
   0 redo size 
 704 bytes sent via SQL*Net to client 
 552 bytes received via SQL*Net from client 
   2 SQL*Net roundtrips to/from client 
   0 sorts (memory) 
   0 sorts (disk) 
   2 rows processed


Now, if you compare the BQ execution plan with DQ execution plan you can see that there are just 2 more rows:

DQ execution plan, has

|  1 |  HASH GROUP BY
|  2 |   VIEW 

and this is exactly what I want:

1. Run the BQ, so it takes just few seconds  => rows 3-9 into DQ execution plan
2. Run the aggregation ong the BQ                  => rows  1-2 into  DQ execution plan

Note:
I have modified the output of explain plan because the blog visualization.

Part 1

NO_MERGE hint, example (1/2)

Part 2

This query (that I call AQ - A Query (A stay for "the first one") ) takes 3 minutes and 44 seconds.

SELECT a.tablespace_name, SUM(a.bytes) bytes_expired
FROM dba_undo_extents a
WHERE status = 'EXPIRED'
GROUP BY tablespace_name;

Here is the explain plan and statistics

SQL> set timing on 
SQL> set autot on exp stat 
SQL> set lines 160 

SQL> SELECT a.tablespace_name, SUM(a.bytes) bytes_expired 
   > FROM dba_undo_extents a WHERE status = 'EXPIRED' 
   > GROUP BY tablespace_name; 

TABLESPACE_NAME                BYTES_EXPIRED 
------------------------------ ------------- 
UNDOTBS1                       1677983744 
UNDOTBS2                       5107023872 

Elapsed: 00:03:44.07 

Execution Plan 
---------------------------------------------------------- 

 Plan hash value: 2680378823 
--------------------------------------------------------------------------------- 
| Id  | Operation                | Name     | Rows | Bytes | Cost (%CPU)| Time  | 
--------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT         |          |  109 |  5886 | 77  (62)| 00:00:01 | 
|   1 |  HASH GROUP BY           |          |  109 |  5886 | 77  (62)| 00:00:01 | 
|   2 |   NESTED LOOPS           |          |  109 |  5886 | 77  (62)| 00:00:01 | 
|   3 |    VIEW                  | VW_GBC_5 |  109 |  5014 | 77  (62)| 00:00:01 | 
|   4 |     HASH GROUP BY        |          |  109 | 13625 | 77  (62)| 00:00:01 | 
|*  5 |      HASH JOIN           |          |  182 | 22750 | 76  (61)| 00:00:01 | 
|*  6 |       HASH JOIN          |          |  182 | 17108 | 48  (96)| 00:00:01 | 
|*  7 |        TABLE ACCESS FULL | UNDO$    |  141 |  2256 |  2   (0)| 00:00:01 |
|*  8 |        FIXED TABLE FULL  | X$KTFBUE | 1000 | 78000 | 45 (100)| 00:00:01 | 
|*  9 |       TABLE ACCESS FULL  | TS$      |  154 |  4774 | 28   (0)| 00:00:01 | 
|* 10 |    INDEX UNIQUE SCAN     | I_FILE2  |    1 |     8 |  0   (0)| 00:00:01 | 
--------------------------------------------------------------------------------- 

 Predicate Information (identified by operation id): 
--------------------------------------------------- 
  5 - access("T"."TS#"="U"."TS#") 
  6 - access("E"."KTFBUESEGTSN"="U"."TS#" AND "E"."KTFBUESEGBNO"="U"."BLOCK#" 
             AND "E"."KTFBUESEGFNO"="U"."FILE#")
  7 - filter(("U"."SPARE1"=1 OR "U"."SPARE1"=2) AND "U"."STATUS$"<>1) 
  8 - filter(DECODE("E"."KTFBUESTA",1,'ACTIVE',2,'EXPIRED',3,'UNEXPIRED', 'UNDEFINED')='EXPIRED') 
  9 - filter(BITAND("T"."FLAGS",16777216)=0)
 10 - access("ITEM_1"="F"."TS#" AND "ITEM_2"="F"."RELFILE#") 

Statistics 
---------------------------------------------------------- 
  84080 recursive calls 
  61670 db block gets 
 438303 consistent gets 
 143544 physical reads 
      0 redo size 
    704 bytes sent via SQL*Net to client 
    552 bytes received via SQL*Net from client 
      2 SQL*Net roundtrips to/from client 
      1 sorts (memory) 
      0 sorts (disk) 
      2 rows processed


But if I run the statement without a aggregation (that I call BQ - B Query (B stay for "the second one") ), it is immediate

SQL> select a.tablespace_name, bytes 
   > FROM dba_undo_extents a WHERE status = 'EXPIRED'; 

TABLESPACE_NAME                 BYTES
 ------------------------------ ---------- 
UNDOTBS1                        65536 
UNDOTBS1                        1048576 
UNDOTBS1                        8388608 
[...] 

TABLESPACE_NAME BYTES 
------------------------------ ---------- 
UNDOTBS2                       65536 
UNDOTBS2                       1048576 
UNDOTBS2                       8388608 
UNDOTBS2                       16777216 
UNDOTBS2                       25165824 
UNDOTBS2                       33554432 
UNDOTBS2                       67108864 
[...] 

 1448 rows selected. 

 Elapsed: 00:00:00.19 

 Execution Plan 
---------------------------------------------------------- 

Plan hash value: 1222646468 
------------------------------------------------------------------------------------------- 
| Id | Operation                 | Name            | Rows | Bytes | Cost (%CPU)| Time     | 
------------------------------------------------------------------------------------------- 
|  0 | SELECT STATEMENT          |                  |   1 |   133 |   1984 (99)| 00:00:01 | 
|  1 |  NESTED LOOPS             |                  |   1 |   133 |   1984 (99)| 00:00:01 | 
|  2 |   NESTED LOOPS            |                  | 182 | 22750 |   1984 (99)| 00:00:01 | 
|* 3 |    HASH JOIN              |                  | 141 |  6627 |      30 (0)| 00:00:01 | 
|* 4 |     TABLE ACCESS FULL     | UNDO$            | 141 |  2256 |       2 (0)| 00:00:01 | 
|* 5 |     TABLE ACCESS FULL     | TS$              | 154 |  4774 |      28 (0)| 00:00:01 | 
|* 6 |    FIXED TABLE FIXED INDEX| X$KTFBUE (ind:1) |   1 |    78 |    14 (100)| 00:00:01 | 
|* 7 |   INDEX UNIQUE SCAN       | I_FILE2          |   1 |     8 |       0 (0)| 00:00:01 | 
------------------------------------------------------------------------------------------- 

 Predicate Information (identified by operation id): 
--------------------------------------------------- 
 3 - access("T"."TS#"="U"."TS#") 4 - filter(("U"."SPARE1"=1 OR "U"."SPARE1"=2) AND "U"."STATUS$"<>1) 
 5 - filter(BITAND("T"."FLAGS",16777216)=0) 
 6 - filter("E"."KTFBUESEGBNO"="U"."BLOCK#" AND "E"."KTFBUESEGFNO"="U"."FILE#" 
            AND "E"."KTFBUESEGTSN"="U"."TS#" 
            AND DECODE("E"."KTFBUESTA",1,'ACTIVE',2,'EXPIRED', 3,'UNEXPIRED','UNDEFINED')='EXPIRED') 
 7 - access("U"."TS#"="F"."TS#" AND "E"."KTFBUEFNO"="F"."RELFILE#") 

 Note 
----- 
 - this is an adaptive plan 

 Statistics 
---------------------------------------------------------- 
   164 recursive calls 
     0 db block gets 
   995 consistent gets 
    71 physical reads 
     0 redo size 
 27169 bytes sent via SQL*Net to client 
  1608 bytes received via SQL*Net from client 
    98 SQL*Net roundtrips to/from client 
     1 sorts (memory) 
     0 sorts (disk) 
  1448 rows processed


So the simple statement (BQ) take 1 seconds, while the aggregation (AQ) taks more than 3 minutes.

My idea is:

1. Run the BQ, so it takes just few seconds
2. Run the aggregation on the BQ

Note:
I have modified the output of explain plan because the blog visualization.

Part 2

Export/Import: on the fly. A new challenge.

This is the scenario.

I have a zipped export file on the source machine and I want to import it into a database on different host.

All is done on the fly.

In this picture:

• "src_hst", is the source machine
• "trg_db", is the target database
• "trg_hst", is the target machine
• "trg_usr", is the user, on the target database, to be imported
• "gunzip", is the unzip command
• "imp", is the import command
• "net", is the network between source host and target host 

Configuring the target and the source

First of all, you have to configure the source and target machine. To do this, please see "Configuring the target and the resource" of my previous post. 

Now you can run the import.

The taget

[oracle@trg_hst]$ mknod /tmp/trg_usr.pipe p
[oracle@trg_hst]$ nohup imp system/manager \
> fromuser=src_usr touser=trg_usr \
> file=/tmp/trg_usr.pipe \
> log=/tmp/dpdump/trg_usr.YYYYMMDD.log \
> 2>&1 > /tmp/dpdump/trg_usr.YYYYMMDD.hohup &

Please, note that I used YYYYMMDD. In this case I have to use the current date. For example: 20160924

The source

nohup gunzip -c src_hst.dmp.gz | ssh oracle@trg_hst 'cat > /tmp/trg_usr.pipe' &

Instance Needs Recovery: Introduction (3/3)

Part 1
Part 2

If an instance is dirty closed (SHUTDOWN ABORT, reboot of the server, kill -9, etc), you have two scenario:

1. Data blocks modified aren't yet written to disk into datafiles and the informations are only into the Redo Log File online 
2. Some data blocks into datafile contains data not yet committed 

• In the first case, changes need to re-applied to the database (roll-forward)
• In the second case, the changes have to disappear (roll-back) 

The V$DATABASE view, hase two columns that we are interested

• V$DATAFILE.CHECKPOINT_CHANGE#
• V$DATAFILE.LAST_CHANGE#

When an instance is opened, V$DATAFILE.CHECKPOINT_CHANGE# is the SCN at last checkpoint (1) while V$DATAFILE.LAST_CHANGE# is NULL. Oracle set to NULL V$DATAFILE.LAST_CHANGE# so it know that the database is working.

If the database is clean closed, Oracle update the V$DATAFILE.LAST_CHANGE# column to the last SCN. But if the close is dirty, Oracle can't to do this update and the V$DATAFILE.LAST_CHANGE# column remain NULL, so at the next startup Oracle knows that last close was not clean and the instance recovery is necessary.

So, when an issue force a shutdown abort (for example), the db is closed before V$DATAFILE.LAST_CHANGE# can be updated to V$DATAFILE.CHECKPOINT_CHANGE#.

In other words, when both SCN are the same (V$DATAFILE.LAST_CHANGE# and V$DATAFILE.CHECKPOINT_CHANGE#) we have no problem, but if V$DATAFILE.LAST_CHANGE# is NULL then the instance need recovery.

Question.

If  V$DATAFILE.LAST_CHANGE# and V$DATAFILE.CHECKPOINT_CHANGE# have the same value, are we out of any issue?
The answer is no. Not entirely. This is because Oracle do another check on the datafiles. He verify that V$DATAFILE_HEADER.CHECKPOINT_CHANGE# (2) and V$DATABASE.CHECKPOINT_CHANGE# (3) are the same. If not, than media recovery is needed.

(1) https://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_1100.htm#REFRN30050
(2) https://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_1102.htm#REFRN30052
(3) https://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_1097.htm#REFRN30047

Part 1
Part 2

Visualizing the alert.log

Usually an alert.log file has a date at first line followed by some several messages.

Mon Aug 08 09:29:49 2016 
Thread 1 advanced to log sequence 2348 (LGWR switch)
Current log# 7 seq# 2348 mem# 0: +DGCUSDB01/redolog/cusctip_01_07_01.dbf
Current log# 7 seq# 2348 mem# 1: +DGCUSDB02/redolog/cusctip_01_07_02.dbf

With this python script you can visualize an alert.log in a different way: each line is made of a date + message

Sat Aug 06 15:58:12 2016 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
Sat Aug 06 15:58:12 2016 With the Partitioning, Real Application Clusters, Oracle Label Security and Oracle Database Vault options.
Sat Aug 06 15:58:12 2016 ORACLE_HOME = /sbRdbms/app/oracle/product/11.2.0.4/dbh01Rac
Sat Aug 06 15:58:12 2016 System name: Linux
Sat Aug 06 15:58:12 2016 Node name: mdapora1d01
Sat Aug 06 15:58:12 2016 Release: 3.10.0-123.el7.x86_64
Sat Aug 06 15:58:12 2016 Version: #1 SMP Mon May 5 11:16:57 EDT 2014
Sat Aug 06 15:58:12 2016 Machine: x86_64
Sat Aug 06 15:58:12 2016 Using parameter settings in server-side pfile /sbRdbms/app/oracle/product/11.2.0.4/dbh01Rac/dbs/initCUSCTIp1.ora
Sat Aug 06 15:58:12 2016 System parameters with non-default values:
Sat Aug 06 15:58:12 2016 processes = 2500
Sat Aug 06 15:58:12 2016 sessions = 3840
Sat Aug 06 15:58:12 2016 spfile = "+DGCUSDB01/cusctip/pfile/spfilecusctip.ora"
Sat Aug 06 15:58:12 2016 sga_target = 8G
Sat Aug 06 15:58:12 2016 control_files = "+DGCUSDB01/cusctip/cntl/cusctip_control_01.ctl"
Sat Aug 06 15:58:12 2016 control_files = "+DGCUSDB02/cusctip/cntl/cusctip_control_02.ctl"
Sat Aug 06 15:58:12 2016 db_block_size = 8192
Sat Aug 06 15:58:12 2016 compatible = "11.2.0.4.0" 
Sat Aug 06 15:58:12 2016 log_archive_dest_1 = "LOCATION=/dbCUSCTIp/akCUSCTIp01/CUSCTIp/"
Sat Aug 06 15:58:12 2016 log_archive_format = "archive_DBID%d_CUSCTIp%t_%s_%r.dbf"
Sat Aug 06 15:58:12 2016 cluster_database = TRUE


or this

Mon Aug 08 09:29:49 2016 Thread 1 advanced to log sequence 2348 (LGWR switch)
Mon Aug 08 09:29:49 2016 Current log# 7 seq# 2348 mem# 0: +DGCUSDB01/redolog/cusctip_01_07_01.dbf
Mon Aug 08 09:29:49 2016 Current log# 7 seq# 2348 mem# 1: +DGCUSDB02/redolog/cusctip_01_07_02.dbf
Mon Aug 08 09:29:53 2016 Archived Log entry 5093 added for thread 1 sequence 2347 ID 0x4cb57b94 dest 1:
Mon Aug 08 09:31:25 2016 Thread 1 advanced to log sequence 2349 (LGWR switch)
Mon Aug 08 09:31:25 2016 Current log# 1 seq# 2349 mem# 0: +DGCUSDB01/redolog/cusctip_01_01_01.dbf
Mon Aug 08 09:31:25 2016 Current log# 1 seq# 2349 mem# 1: +DGCUSDB02/redolog/cusctip_01_01_02.dbf
Mon Aug 08 09:31:26 2016 Archived Log entry 5095 added for thread 1 sequence 2348 ID 0x4cb57b94 dest 1:

If you want to use my script then

• download the file
• copy the the script on your db machine
• change the first line with the correct python path
• run which python command, to find where is the interpreter
• if you use python3, comment (with # symbol) or delete this line
• from __future__ import print_function

 

Update 2016/Aug/31

I wrote the script so you can run it on a "tail" or "cat" command. For example, you can run

[oracle]# tail -21 alert_+ASM1.log |formatAlertLogX.py
Day Mon DD HH:MI:SS YYYY NOTE: assigning ARB0 to group 45/0xcd5a0bd8 (DGVSAPDB01) with 2 parallel I/Os
Thu Aug 25 09:26:58 2016 NOTE: header on disk 0 advanced to format #2 using fcn 0.525
Thu Aug 25 09:27:02 2016 NOTE: Attempting voting file refresh on diskgroup DGVSAPDB01
Thu Aug 25 09:27:02 2016 NOTE: Refresh completed on diskgroup DGVSAPDB01. No voting file found.
Thu Aug 25 09:28:00 2016 NOTE: GroupBlock outside rolling migration privileged region
Thu Aug 25 09:28:00 2016 NOTE: requesting all-instance membership refresh for group=45
Thu Aug 25 09:28:00 2016 NOTE: membership refresh pending for group 45/0xcd5a0bd8 (DGVSAPDB01)
Thu Aug 25 09:28:00 2016 GMON querying group 45 at 79717 for pid 22, osid 20432
Thu Aug 25 09:28:00 2016 SUCCESS: refreshed membership for 45/0xcd5a0bd8 (DGVSAPDB01)
Thu Aug 25 09:28:00 2016 NOTE: Attempting voting file refresh on diskgroup DGVSAPDB01
Thu Aug 25 09:28:00 2016 NOTE: Refresh completed on diskgroup DGVSAPDB01. No voting file found.
Thu Aug 25 09:28:33 2016 NOTE: stopping process ARB0
Thu Aug 25 09:28:33 2016 SUCCESS: rebalance completed for group 45/0xcd5a0bd8 (DGVSAPDB01)

Export/Import: on the fly

Here I show how to run export and import from a machine to another, just using pipes.

This is the configuration:

• Two unix machine
• source => HP-UX B.11.11
• target => Red Hat Enterprise Linux 7.1
•  Two database Oracle
• source => Oracle 9.2.0.6
• target => Oracle 11.2.0.4

This is what we want:

In this picture:

• "src_hst", is the source machine
• "src_db", is the source database
• "src_usr", is the user, on the source database, which we export
• "trc_db", is the target database
• "trc_hst", is the target machine
• "trg_user", is the user, on the target database, to be imported
• "exp", is the export command
• "imp", is the import command
• "net", is the network between src_db and trg_db

To do this job (exp/imp), I configure the target machine, first and the source machine at least.

Starting from the target, you start to create a tube from the end to the biginning. In this way, when you fill the tube (start the export), the data flow through it and when they arrived to the end, they find someone (the import) that empty the tube.

Configuring the target and the source

This is the step number 0. In order to avoid the password when you connect from source to the target, you need to exchange the ssh-key.

You have just to run the "ssh-keygen" command on the source, and press RETURN any time. 

[oracle@src_hst]$ ssh-keygen (see update)
Generating public/private rsa key pair.
Please be patient....   Key generation may take a few minutes
Enter file in which to save the key (/home/oracle/.ssh/id_rsa): Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/oracle/.ssh/id_rsa.
Your public key has been saved in /home/oracle/.ssh/id_rsa.pub.
The key fingerprint is:
bc:23:30:20:37:20:5b:9b:17:f5:5f:f9:10:cd:62:bd oracle@ht1-jim
The key's randomart image is:
+--[ RSA 2048]----+
|o . ...     .+   |
|.+ o . .    oo+  |
|o * .   .  .+. . |
| o +   . . . oE  |
|    o   S .   .  |
|     o   .       |
|      . o        |
|       . .       |
|                 |
+-----------------+


At this point you have two files in the /home/oracle/.ssh, direcotry

• id_rsa
• id_rsa.pub

You have to copy the content of the /home/oracle/.ssh/id_rsa.pub (following an example).....

[oracle@src_hst]$ cat /home/oracle/.ssh/id_rsa.pub
ssh-rsa AAAAB3NzaC1yc2EAAAABIwAAAQEArRfiCNq6nodHj5EKWeQ6ILOe/yICYVONaZa0SKF09msdZvfXYU+rN55fbubonwftyUjZbMRnMIoKTrsxi+UhFe8jIS2l/oVbOerHlCeyhXgCu+NrGM6Q56QPmrJgBMm7b1rb2Tsnh3jdTo3R00i7aCdaLvrVL3oK7IFS2jaVGGhJYTU1LXi8mhXQ3oQXkQ2bHkGGQ9Z68CD4cyYZ4St6lF7xLamwtvXwpC+M8m4s6zM/YiYTKib6RohLt7+kfXfzF3Boyx9AXOmTu93IS2GsRaiMcGVW/GH6T7qVvwPQej0vxpALOf2wdkRL5uSjVdGFUxwgXWDOtKlFDoWHEyYuuw== oracle@src_hst

.....in the /home/oracle/.ssh/authorized_keys file of the target host (following an example)

[oracle@trc_hst]$ cat /home/oracle/.ssh/authorized_keys
ssh-rsa AAAAB3NzaC1yc2EAAAABIwAAAQEArRfiCNq6nodHj5EKWeQ6ILOe/yICYVONaZa0SKF09msdZvfXYU+rN55fbubonwftyUjZbMRnMIoKTrsxi+UhFe8jIS2l/oVbOerHlCeyhXgCu+NrGM6Q56QPmrJgBMm7b1rb2Tsnh3jdTo3R00i7aCdaLvrVL3oK7IFS2jaVGGhJYTU1LXi8mhXQ3oQXkQ2bHkGGQ9Z68CD4cyYZ4St6lF7xLamwtvXwpC+M8m4s6zM/YiYTKib6RohLt7+kfXfzF3Boyx9AXOmTu93IS2GsRaiMcGVW/GH6T7qVvwPQej0vxpALOf2wdkRL5uSjVdGFUxwgXWDOtKlFDoWHEyYuuw== oracle@src_hst

Now you are ready to start the import/export job.

Configuring the target

[oracle@trc_hst]$ mkdir /home/oracle/IMPORT
[oracle@trc_hst]$ mknod /tmp/impTrcUsr.pipe p
[oracle@trc_hst]$ nohup imp trc_usr/trc_pwd \
                        file=/tmp/impTrcUsr.pipe \
                        parfile=/home/oracle/IMPORT/impTrcUsr.par \
                        > /home/oracle/IMPORT/impTrcUsr.nohup 2>&1 &

I used a parfile. Because I don't want to import the index, I used these options:

[oracle@trc_hst]$ cat /home/oracle/IMPORT/impTrcUsr.par
BUFFER=5000000
INDEXES=N
 

Configuring the source

[oracle@src_hst]$ mkdir /home/oracle/EXPORT
[oracle@src_hst]$ mknod /tmp/expTrcUsr.pipe p
[oracle@src_hst]$ nohup ssh oracle@src_hst "cat > /tmp/impSrcUsr.pipe" < /tmp/expTrcUsr.pipe &
[oracle@src_hst]$ time nohup exp src_usr/src_pwd \
                       file=/tmp/expTrcUsr.pipe \
                       parfile=/home/oracle/EXPORT/expSrcUsr.par \
                       > /home/oracle/EXPORT/expSrcUsr.nohup 2>&1 &

I used a parfile. Because I want to export only 2 tables, I used these options:

[oracle@src_hst]$ cat /home/oracle/EXPORT/expSrcUsr.par
DIRECT=Y
BUFFER=5000000
RECORDLENGTH=5000000
TABLES=(MYTAB01, MYTAB02)
 

Note on the import

I'm using a pipe instead a standard file. Except this, all is usual.

Note on the export

* I'm using a pipe instead a standard file
* I'm using a remote command "cat", in order to write the exported data into the remote pipe
* The source pipe is used as standard of input of ssh command. In this way all that is wrote in the source pipe is also wrote in the remote pipe
* I use the "time" command, in order to know how long the export/import process takes

Update

19/July/2017
Sometimes the ssh-keygen command want the type of  encryption. In this case you have to use the -t switch. For example

$ ssh-keygen -t rsa

Instance Needs Recovery: Introduction (2/3)

Part 1
Part 3

Closing a Redo Log file

There is always a CURRENT Redo Log file (one for each Instance of RAC).
When Oracle begin to write the CURRENT Redo Log file, it set the Low SCN (V$LOG.FIRST_CHANGE#) to the current SCN of the database and set the High SCN (V$LOG.NEXT_CHANGE#) to 281474976710655. Think this number as a infinite value.

Oracle do this because it don't know the final value of the High SCN,

When there is a "Log Switch (it appen wheh the CURRENT Redo Log is full or a DBA run "ALTER SYSTEM SWITCH LOGFILE statement) Oracle set the V$LOG.NEXT_CHANGE# column  to current SCN +1, and than begin to use the next Redo Log file.

The operation that set the V$LOG.NEXT_CHANGE# to a finite value, is called "closing the Redo Log file".

Archived Log

Some definition:

"A log switch is the event during which LGWR stops writing to one online redo log group and starts writing to another" (1). "The background process ARCn automates archiving operations when automatic archiving is enabled" (2)

"An archived redo log file is a copy of one of the filled members of a redo log group". "The process of turning online redo log files into archived redo log files is called archiving" (2)

"When running in ARCHIVELOG mode, the log writer process (LGWR) is not allowed to reuse and hence overwrite an online redo log group until it has been archived" (2)

When the log switch occur
(i) LGWR starts writing to another one
(ii) the ARCn process automates archive in backup the CURRENT redolog (3)

so, a user session not need to wait the arichive finish



(1) https://www.bnl.gov/phobos/Detectors/Computing/Orant/doc/database.804/a58397/ch5.htm
(2) https://docs.oracle.com/cd/B19306_01/server.102/b14231/archredo.htm
(3) http://www.datadisk.co.uk/html_docs/oracle/redo.htm
(4) Master Note: Troubleshooting Redo Logs and Archiving (Doc ID 1507157.1)

Part 1
Part 3

Instance Needs Recovery: Introduction (1/3)

Part 2
Part 3

In order to understand why the recovery of an instance is necessary, we need to know following background: 

1. Log Journal 
2. System Schange Number 

Log Journal 

Oracle save all changes in a logbook. This logbook are Redo Log Files (sometimes the logbooks are also called Transaction Logs). The Redo Log File works in Groups.

There are at least two Groups. Groups are made of Members. 

There are one or more member in each Group. All members in a Group are copies. 

Time to time, a Group is identified by a different number called Sequence. The Sequence is the chapter of a logbook.

There is always a CURRENT Group: it is the actual chapter where Oracle is writing. A Group can be ACTIVE. This mean that the information in that chapter are necessary to rebuild the last moments of life in case of unexpected death (abort).

System Change Number 

Oracle use numbers in order to measure it life. These numbers are integer and called SCN (System Change Number).

Each SCN is associated with time. When the changes are forever (commit), Oracle save this number in the logbook.  

The SCN is unique into database, but it has a different name based on the context it is used.

For example the V$LOG view, show 2 SCN: FIRST_CHANGE# and NEXT_CHANGE#. Their name are “Low SCN” and “High SCN”. 

Oracle release the SCN after a COMMIT. What I mean is that Oracle release the SCN when the changes are made final.

System Change Number is a too long name, so it called simply “Change”.

RAC 

In RAC, each insance have it Redo Log Group. One Group is called “Thread”. A Group is unique in the database. Each Thread have it CURRENT Group. 

The SCNs are unique in all database. Each instance always know the SCN of the other instances.

Update 2016/06/03

• I modified some phrases. I'm sorry, but I don't speak well english
• Also I wrote the wrong name of High SCN, so I changed the LAST_CHANGE# with NEXT_CHANGE#.

Part 2
Part 3

ASH. Like a movie (3/3)

In the previous post [1a] [1b], I shown a chart that you can see here

This picture show how the sampling (blue) and real time analysis (read) are intimately linked each other. In order to obtain that, I used the query [2] based on what I explain now.
 
The master document [3] was published by John Beresniewicz, Graham Wood, Uri Shaft. For it, exist also a video on You Tube [4].

The main formula is

DB Time ~ sample_nr * (rows in ASH)

where the "sample_nr" is the sample frequency. It's value, depend from row surce.

* V$ACTIVE_SESSION_HISTORY,     sample_nr => 1

* DBA_HIST_ACTIVE_SESS_HISTORY, sample_nr => _ash_disk_filter_ratio (default is 10)

In my query, there are three main components. They are in the "with" section: "awr", "ash" and "db_time".

• AWR Here 

I select the sample frequency of DBA_HIST_ACTIVE_SESS_HISTORY view.

select 
       ash_value.ksppstvl 
  from x$ksppi ash_name 
      ,x$ksppsv ash_value 
 where 
       ash_name.indx    = ash_value.indx 
   and ash_name.ksppinm = '_ash_disk_filter_ratio'; 

• ASH (Y-axis) 

The "rows in ASH" values, are the rows in DBA_HIST_ACTIVE_SESS_HISTORY.

select 
       instance_number
      ,snap_id, count(*)
      ,snap_id, 10*count(*) 
  from 
       dba_hist_active_ess_history
       dba_hist_active_sess_history  
group by 
       instance_number, snap_id; 

• DB_Time (Y-axis) 

The last piece of my query is the value of DB Time. For it I used the LAG analytic function in order to obtain the previous row:

select 
       snap_id 
      ,instance_number 
      ,(value - lag(value) over (partition by instance_number order by snap_id))/1000000 diff 
  from 
      dba_hist_sys_time_model 
 where 
      stat_name='DB time'; 


As last step I join this three query with DBA_HIST_SNAPSHOT, in order to get the "sample time" (X-axis). See [2] to get the full statement.

• Update

06/Oct/2017 - modified dba_hist_active_ess_history in dba_hist_active_sess_history

06/Oct/2017 - modified count(*) in 10*count(*)

• References

[1a] http://orasal.blogspot.it/2016/03/ash-like-movie-13.html
[1b] http://orasal.blogspot.it/2016/03/ash-come-un-film-13.html
[2] https://www.dropbox.com/s/gp4fh6soz0erwvq/ashvsdbtime.txt?dl=0
[3] http://www.slideshare.net/jberesni/ash-architecture-and-advanced-usage-rmoug2014-36611678
[4] https://www.youtube.com/watch?v=rxQkvXIY7X0

ASH. Like a movie (2/3)

Here the Italian version

ASH. Like a movie (1/3)
ASH. Like a movie (3/3)


In order to show how ASH is like the DB Time, I made a chart reporting both metrics in the same place.

However, some measures are "strange". I show this in this post.

The first anomaly is one in which there is a negative peak.

Snap_id.Sample Time	Sample	DB Time
92722.28-MAR-16 07.00.43.468 AM	5980	-7166541

This is due to restart of the instance, so I delete this point.

The second anomaly is of 3 peaks. These are a surge of Time DB.

Snap_id.Sample Time	Sample	DB Time
92614.26-MAR-16 12.00.15.371 AM	72870	78461
92626.26-MAR-16 06.00.46.878 AM	75420	100475
92677.27-MAR-16 08.30.58.334 AM	44570	124111

After I deleted this three points, the chart is like this...

...and you can see that the sampling approximates the real time.

The cause of the first point is the restart of the instance that drops all statistics. I'm investigating why sometimes exists (in this case there are 3 points) the difference between ASH and DB Time.

ASH. Like a movie (1/3) ASH. Like a movie (3/3)

ASH. Come un film (2/3)

Qui la versione Inglese

ASH. Come un film (1/3)
ASH. Come un film (3/3)

Nell'ottica di mostrare che ASH è come il DB Time, ho graficato l'andamento di entrambi in un solo posto.

Tuttavia le misure hanno alcuni punti "anomali". Mostro questi punti di seguito.

La prima anomalia è quella in cui è presente un picco negativo.

Snap_id.Sample Time	Sample	DB Time
92722.28-MAR-16 07.00.43.468 AM	5980	-7166541

Questo è dovuto al riavvio dell'istanza. Allora io cancello questo punto.

La sesconda anomalia è costituita da 3 picchi. Questi corrispondono ad un'impennata del DB Time.

Snap_id.Sample Time	Sample	DB Time
92614.26-MAR-16 12.00.15.371 AM	72870	78461
92626.26-MAR-16 06.00.46.878 AM	75420	100475
92677.27-MAR-16 08.30.58.334 AM	44570	124111

Una volta eliminati anche questi tre punti, il grafico risultante è quello mostrato di seguito...

...da cui si deduce come nonostante il campionamento, ASH approssima bene il DB Time.

Eccetto il primo punto dovuto al riavvio dell'istanza che azzera tutte le statistiche, sto ancora indagando il motivo per cui in alcuni casi, esiste questa differena tra ASH e DB Time.

ASH. Come un film (1/3) ASH. Come un film (3/3)