Happy New Year 2010

It's time to begin with New Year. I Hope Everyone Enjoyed Your Holidays.

Wish You and Families have Healthy, Happy, Prosperous and Wonderful.

How to register ASM diskgroup in Grid Infrastructure

It's a cool today, Someone are working, Someone are on vacation.
By the way. Have good holidays ;)

I read/tested Relocating Grid Infrastructure. After I relocated my Grid Infrastructure. I'd like to register ASM diskgroup else.

$ ./crsstat -word dg
HA Resource Type Target State (Host)
----------- ------ ------ -----
ora.DISK00.dg ora.diskgroup.type ONLINE ONLINE on rhel5-test

Some disk groups... not registered in Grid. I had to register by manual, then I checked srvctl command-line, but no "add" diskgroup.

$ srvctl -h | grep diskgroup | grep dg_name
Usage: srvctl start diskgroup -g <dg_name> [-n "<node_list>"]
Usage: srvctl stop diskgroup -g <dg_name> [-n "<node_list>"] [-f]
Usage: srvctl status diskgroup -g <dg_name> [-n "<node_list>"] [-a]
Usage: srvctl enable diskgroup -g <dg_name> [-n "<node_list>"]
Usage: srvctl disable diskgroup -g <dg_name> [-n "<node_list>"]
Usage: srvctl remove diskgroup -g <dg_name> [-f]

Actually, ASM disk groups are registered in Grid by Automatic, After we mounted them ???

$ sqlplus / as sysasm

SYS> alter diskgroup DISK01 mount;

Diskgroup altered.

SYS> alter diskgroup DISK02 mount;

Diskgroup altered.

SYS> alter diskgroup DISK03 mount;

Diskgroup altered.

$ ./crsstat -word dg
HA Resource Type Target State (Host)
----------- ------ ------ -----
ora.DISK00.dg ora.diskgroup.type ONLINE ONLINE on rhel5-test
ora.DISK01.dg ora.diskgroup.type ONLINE ONLINE on rhel5-test
ora.DISK02.dg ora.diskgroup.type ONLINE ONLINE on rhel5-test
ora.DISK03.dg ora.diskgroup.type ONLINE ONLINE on rhel5-test

By the way, It's just fun today. Nothing special.

Reference:
#!/bin/bash
# crsstat v. 0.01
# By surachart
if [ $# -gt 4 ]
then
echo "Please Check arguments."
echo
echo "./crsstat [-word|--word] resource_word [-crshome|--crshome] CRS_HOME "
echo
echo " ./crsstat"
echo " ./crsstat -word orcl"
echo ' ./crsstat -word orcl -crshome $CRS_HOME'
exit 0
fi

while [ $# -gt 0 ]; do
case "$1" in
-h|--h|-help|--help)
echo "./crsstat [-word|--word] resource_word [-crshome|--crshome] CRS_HOME "
echo
echo " ./crsstat"
echo " ./crsstat -word orcl"
echo ' ./crsstat -word orcl -crshome $CRS_HOME'
exit 0
;;
-word|--word)
GREP_KEY=$2
shift
;;
-crshome|--crshome)
ORA_CRS_HOME=$2
shift
;;
esac
shift
done

if [ -z $ORA_CRS_HOME ]
then
ORA_CRS_HOME=$CRS_HOME
if [ ! -d $ORA_CRS_HOME ]
then
echo "Please Check CRS_HOME Environment ($ORA_CRS_HOME)"
exit 1
fi

elif [ ! -d $ORA_CRS_HOME ]
then
ORA_CRS_HOME=$CRS_HOME
if [ ! -d $ORA_CRS_HOME ]
then
echo "Please Check CRS_HOME Environment ($ORA_CRS_HOME)"
exit 1
fi
fi

CRS_STAT=$ORA_CRS_HOME/bin/crs_stat

if [ ! -x $CRS_STAT ]
then
echo "Please Check CRS_HOME Environment ($ORA_CRS_HOME)"
exit 2
fi

AWK=/usr/bin/awk

if [ ! -x $AWK ]
then
AWK=/bin/awk
fi

$AWK \
'BEGIN {printf "%-40s %-25s %-10s %-12s\n", "HA Resource", "Type", "Target", "State (Host)"; printf "%-40s %-25s %-10s %-12s\n", "-----------", "------", "------", "-----";}'
$CRS_STAT | $AWK \
'BEGIN { FS="="; state = 0; }
$1~/NAME/ && $2~/'$GREP_KEY'/ {appname = $2; state=1;}
state == 0 {next;}
$1~/TYPE/ && state == 1 {apptype = $2; state=2;}
$1~/TARGET/ && state == 2 {apptarget = $2; state=3;}
$1~/STATE/ && state == 3 {appstate = $2; state=4;}
state == 4 {printf "%-40s %-25s %-10s %-12s\n", appname,apptype, apptarget, appstate;state=0}'

DEFERRED_SEGMENT_CREATION

What is DEFERRED_SEGMENT_CREATION?
After I read A Closer Look at the New Edition By Tom Kyte on Oracle Magazine.

Historically, when we created any database object—a table, an index, anything that consumed storage—the database would create the appropriate segments and allocate at least one initial extent. This extent might be small—say, 64K—but it was allocated.

This(deferred_segment_creation) is (11gR2)initialization parameter help to save disk space and minimizes install time. (default=true)
If set to true, then segments for non-partitioned tables and their dependent objects (LOBs, indexes) will not be created until the first row is inserted into the table.

So, I did some test about it.

SYS> create user demo identified by demo;

User created.

SYS> grant create session, create table to demo;

Grant succeeded.

SYS> connect demo/demo
Connected.

DEMO> create table tb_demo (a number);

Table created.

DEMO> select segment_name,extent_id,bytes from user_extents order by segment_name;

no rows selected

DEMO> insert into tb_demo values(1);
insert into tb_demo values(1)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

When first row was inserted into the table, it needs segment (quota on tablespace).

DEMO> alter session set deferred_segment_creation=false;

Session altered.

DEMO> create table tb_demo2 (a number);
create table tb_demo2 (a number)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

After changed "deferred_segment_creation value"=false, A "create table" situation needs segment...

SYS> alter user demo quota 10M on users;

User altered.

DEMO> select segment_name,extent_id,bytes from user_extents order by segment_name;

no rows selected

DEMO> alter session set deferred_segment_creation=false;

Session altered.

DEMO> create table tb_demo2 (a number);

Table created.

DEMO> select segment_name,extent_id,bytes from user_extents order by segment_name;

SEGMENT_NAME EXTENT_ID BYTES
------------------------- ---------- ----------
TB_DEMO2 0 65536

1 row selected.

TB_DEMO2 table allocated segment, when created.

DEMO> select segment_name,extent_id,bytes from user_extents order by segment_name;

SEGMENT_NAME EXTENT_ID BYTES
------------------------- ---------- ----------
TB_DEMO2 0 65536

1 row selected.

DEMO> insert into tb_demo values(1);

1 row created.

DEMO> select segment_name,extent_id,bytes from user_extents order by segment_name;

SEGMENT_NAME EXTENT_ID BYTES
------------------------- ---------- ----------
TB_DEMO 0 65536
TB_DEMO2 0 65536

2 rows selected.

TB_DEMO table allocated segment, after first row was inserted.

A Deferred Segment Creation (DEFERRED_SEGMENT_CREATION=true) - we don't necessary quota on tablespace, before we create table.

Oracle Application Express 4.0 Early Adopter

I follow Joel Kallman' blog and He posted APEX 4.0 Early Adopter available. That's fantastic, Cuz I hope to see/test APEX 4.0. I filled out and requested a workspace to test.

Oracle Application Express 4.0 Early Adopter hosted on Amazon EC2. We can fill out the survey and request a workspace - http://tryapexnow.com.


Nice... But I felt Layout differ from old version -)

Oracle Cluster Health Monitor(IPD/OS)

Oracle Cluster Health Monitor(IPD/OS)
This tool (formerly known as Instantaneous Problem Detector for Clusters) is designed to detect and analyze operating system (OS) and cluster resource related degradation and failures in order to bring more explanatory power to many issues that occur in clusters where Oracle Clusterware and Oracle RAC are running such as node eviction... (README)

After read README, that's easy to install and test.
But I have only one node (Oracle RAC 11gR2). But I'd like to test it(just learn it), so
On Cluster:
- Create "crfuser" user and unzip crfpack-linux.zip file.

# useradd -d /opt/crfuser -s /bin/sh -g oinstall crfuser
# su - crfuser
$ unzip /tmp/crfpack-linux.zip

- Make "crfuser" user remote all nodes (but, i have only one node.)

$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/opt/crfuser/.ssh/id_rsa):
Created directory '/opt/crfuser/.ssh'.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /opt/crfuser/.ssh/id_rsa.
Your public key has been saved in /opt/crfuser/.ssh/id_rsa.pub.
The key fingerprint is:
28:dc:bd:69:d2:23:8f:44:0d:65:05:83:e3:10:50:6a crfuser@RHEL5-TEST

$ cat .ssh/id_rsa.pub >> .ssh/authorized_keys

$ ssh rhel5-test hostname
RHEL5-TEST

- Install and then enable tool all nodes

$ cd install
$ ./crfinst.pl -i rhel5-test -b /oracle/oracrfdb -m rhel5-test

Performing checks on nodes: "rhel5-test" ...

Generating cluster wide configuration file...

Creating a bundle for remote nodes...

Installing on nodes "rhel5-test" ...

Configuration complete on nodes "rhel5-test" ...

Please run "/opt/crfuser/install/crfinst.pl -f, optionally specifying BDB location with -b as root on each node to complete the install process.

# /opt/crfuser/install/crfinst.pl -f

Installation completed successfully at /usr/lib/oracrf...

# /etc/init.d/init.crfd enable

# /etc/init.d/init.crfd status

OSysmond running with PID=17346.
OLoggerd running with PID=17455.

oproxyd running with PID=17462.

Check:

# ps -aef | grep oracrf
root 17345 17236 0 16:12 ? 00:00:00 /bin/sh /usr/lib/oracrf/bin/crfcheck
root 17346 17236 1 16:12 ? 00:00:00 /usr/lib/oracrf/bin/osysmond
root 17455 1 0 16:13 ? 00:00:00 /usr/lib/oracrf/bin/ologgerd -M -d /oracle/oracrfdb
crfuser 17462 1 0 16:13 ? 00:00:00 /usr/lib/oracrf/bin/oproxyd

# ls /oracle/oracrfdb/
crfalert.bdb crfconn.bdb crfhosts.bdb crfts.bdb __db.002 __db.004 __db.006 rhel5-test.ldb crfclust.bdb crfcpu.bdb crfloclts.bdb __db.001 __db.003 __db.005 log.0000000001

The OS Tool consists of three daemons: ologgerd, oproxyd and osysmond. There is one ologgerd master daemon on only one node in the installed set of nodes and there is one osysmond on every node. If there is more than 1 node in the installed set of nodes, another node is chosen to house the standby for the master ologgerd. If master daemon suffers a death (because daemon is not able come up after a fixed number of retries or node where master was running is down), standby takes over as master and selects a new standby. Master manages the OS metric database in Berkeley DB and interacts with the standby to manage a replica of the master OS metrics database.

osysmond is the monitoring and OS metric collection daemon that sends the data to ologgerd. ologgerd receives the information from all the nodes and persists in a Berkeley DB based database.

oproxyd is the proxy daemon which handles connections on the public interface. If the tool is configured with private node names, only orpoxyd is listening on the public interface for external clients (like oclumon and crfgui). This serves as a security measure against attacks on ologgerd master daemon. It runs on all the nodes and is highly avaliable.

Test:

$ /usr/lib/oracrf/bin/oclumon -h

For help from command line : oclumon -h
For help in interactive mode : -h
Currently supported verbs are :
showtrail, showobjects, dumpnodeview, manage, version, debug, quit and help

$ /usr/lib/oracrf/bin/oclumon dumpnodeview

dumpnodeview: Node name not given. Querying for the local host

----------------------------------------
Node: rhel5-test Clock: '12-17-09 09.20.28 UTC' SerialNo:434
----------------------------------------

SYSTEM:
#cpus: 2 cpu: 2.84 cpuq: 2 physmemfree: 18236 mcache: 596736 swapfree: 2458264 ior: 0 iow: 121 ios: 7 netr: 27.21 netw: 26.87 procs: 252 rtprocs: 13 #fds: 4059 #sysfdlimit: 6815744 #disks: 1 #nics: 2 nicErrors: 0

TOP CONSUMERS:
topcpu: 'osysmond(17346) 1.89' topprivmem: 'ocssd.bin(8077) 220036' topshm: 'ora_smon_orcl_1(20233) 68896' topfd: 'ocssd.bin(8077) 90' topthread: 'crsd.bin(2905) 58'

$ /usr/lib/oracrf/bin/oclumon dumpnodeview -v -n rhel5-test -last "00:00:10"

On Client:
- Install

# ./crfinst.pl -g

Installation completed sucessfully at /usr/lib/oracrf...

Test:

# /usr/lib/oracrf/bin/crfgui -m rhel5-test
Cluster Health Analyzer V1.10
Look for Loggerd via node rhel5-test
...Connected to Loggerd on rhel5-test
Note: Node rhel5-test is now up
Cluster 'MyCluster', 1 nodes. Ext time=2009-12-17 09:22:33
Making Window: IPD Cluster Monitor V1.10 on test01, Logger V1.03.20090322, Cluster "MyCluster" (View 0), Refresh rate: 1 sec

that's just a example and funny...

LOG_ARCHIVE_DEST_n on 11gR2

On 11gR2, The LOG_ARCHIVE_DEST_n initialization parameter defines up to 31 (where n = 1, 2, 3, ... 31).

SQL> show parameter log_archive_dest_31

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_31 string

When created database by using dbca. I saw LOG_ARCHIVE_DEST_1 through LOG_ARCHIVE_DEST_9 be able to fill up. About LOG_ARCHIVE_DEST_10 (If DB_RECOVERY_FILE_DEST is specified and no LOG_ARCHIVE_DEST_n is specified, then LOG_ARCHIVE_DEST_10 is implicitly set to the recovery area. So can not see LOG_ARCHIVE_DEST_10), What about LOG_ARCHIVE_DEST_11 through LOG_ARCHIVE_DEST_31?

Then I checked from All Initialization Parameters page... I found LOG_ARCHIVE_DEST_n initialization parameter (where n = 1 - 10) be on Archive Category and LOG_ARCHIVE_DEST_n initialization parameter (where n = 11 - 31) be on Miscellaneous Category.

So. read more:
- If configured, each LOG_ARCHIVE_DEST_1 through LOG_ARCHIVE_DEST_10 destination must contain either a LOCATION or SERVICE attribute to specify a local disk directory or a remotely accessed database, respectively. Each LOG_ARCHIVE_DEST_11 through LOG_ARCHIVE_DEST_31 destination must contain a SERVICE attribute.
- LOG_ARCHIVE_DEST_11 through LOG_ARCHIVE_DEST_31 cannot be specified as an ALTERNATE redo transport location.
- LOG_ARCHIVE_DEST_11 through LOG_ARCHIVE_DEST_31 can only be used when the COMPATIBLE initialization parameter is set to 11.2.0.0 or later.

SQL> alter system set log_archive_dest_6='location=+disk02'

System altered.

SQL> alter system set log_archive_dest_11='location=+disk02'
alter system set log_archive_dest_11='location=+disk01'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16025: parameter LOG_ARCHIVE_DEST_11 contains repeated or conflicting attributes

Middleware Management with Oracle Enterprise Manager Grid Control 10g R5 Book

Oracle Enterprise Manager offers:

- Complete, Open, Integrated Management of Oracle Technologies

- Deep, Optimized, Best of Breed Solutions for Database, Middleware, Packaged Applications, Physical and Virtual Infrastructure

- Business Centric, Top Down Application Management

- Complete Lifecycle Management

- Scalable Grid Management

Enterprise Manager 10g Grid Control (10.2.0.1) and higher version currently provide monitoring for Forms-based applications.

I mention a book titled "Middleware Management with Oracle Enterprise Manager Grid Control 10g R5" written by Debu Panda(blog), Arvind Maheshwari ,they wrote an excellent Middleware Management with Oracle Enterprise Manager Grid Control 10g R5 Guide. This book is helpful to manage middleware and application configures on Enterprise Manager Grid.

What we will learn from this book:

- Gain a deep understanding of the challenges in managing middleware platforms and applications

- Understand the architecture of Enterprise Manager, and how best to set it up

- Discover middleware platforms (WebLogic, OC4J, BPEL, OSB, Coherence, Oracle Forms and Reports, etc. and non-Oracle middleware) from Oracle Enterprise Manager

- Monitor event notifications and diagnostics for middleware platforms and Java EE/Composite applications

- Create and export application service levels

- Manage middleware and application configurations

- Provision your middleware platform

- Build plug-ins for new target types

Reference:

- Data Sheets

- Technical Information

- Oracle Enterprise Manager Downloads

- Debu Panda 's blog

Oracle 10g/11g Data and Database Management Utilities Book

Oracle 10g/11g Data and Database Management Utilities Book explains utility to optimize the efficiency, management, and performance of your daily database tasks.

"Oracle 10g/11g Data and Database Management Utilities" written by Hector R. Madrid (Blog).

He wrote about Data Pump/SQL*Loader/External Tables/Recovery Manager Advanced Techniques/Session Management/... (Table of Contents)

What we will learn from this book:
- Improve performance and manageability using the advanced features of direct export/import utilities among different databases
- Optimize your maintenance windows related to data management tasks such as importing data from one database to another using Data Pump and SQL*Loader
- Perform more than just the ETL processes by taking advantage of the external tables feature
- Use Oracle Scheduler to specify maintenance windows, assign priorities, configure job classes and many more features, and take decisions based on the task outcome
- Get acquainted with all the possibilities the Oracle Universal Installer tool offers to make the installation task more efficient
- Execute effective database creations: not just default creations, but comprehensive database creations
- Configure and manage an ASM environment using DBCA
- Improve performance and reduce the impact of recovery manager database backups in production environments
- Increase the security in an Oracle environment, protect the backups, and manage certificates using Oracle Wallet Manager
- Perform installations in batch environments and manage your software updates related to Critical Patch Updates (CPU) or individual patches using OPatch

After I read, this book is very helpful good ideas/examples to use Oracle Database utilities.
Example: EXPDP with FLASHBACK_SCN, SQL*Loader with Parallel Load and ...

Reference:
- Oracle Database Utilities (10g, 11g)
- Oracle Documents (10g, 11g)
- Hector R. Madrid's Blog

APPEND_VALUES hint and Table NOLOGGING/LOGGING

The APPEND_VALUES(11gR2) hint uses direct-path INSERT with the VALUES clause.

SQL> create table tb (a number, b varchar2(100));

Table created.

SQL> insert /*+ APPEND_VALUES */ into tb values (1, 'ABCDEFG');

1 row created.

SQL> insert /*+ APPEND_VALUES */ into tb values (1, 'ABCDEFG');
insert /*+ APPEND_VALUES */ into tb values (1, 'ABCDEFG')
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

Upon Table NOLOGGING, We use APPEND hint to use direct-path INSERT with the subquery syntax of the INSERT statement, that will generate a minimal number of redo log.
Then, use APPEND_VALUES hint on Table NOLOGGING.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 161
Next log sequence to archive 163
Current log sequence 163

SQL> create table tb_nologging (a number, b varchar2(100)) nologging;

Table created.

SQL> select table_name, logging from user_tables where table_name = 'TB_NOLOGGING';

TABLE_NAME LOG
------------------------------ ---
TB_NOLOGGING NO

start INSERT with the VALUES clause.
session 1:

SQL> @redo-new

REDO_SIZE
----------
2004

SQL> insert into tb_nologging values (1, 'ABCDEFG');

1 row created.

SQL> @redo-diff
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 2004) REDO_SIZE

REDO_SIZE
----------
1880

SQL> @redo-new

REDO_SIZE
----------
3884

SQL> commit;

Commit complete.

SQL> @redo-diff
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 3884) REDO_SIZE

REDO_SIZE
----------
140

SQL> @redo-new

REDO_SIZE
----------
4024

SQL> insert into tb_nologging values (2, 'GFEDCBA');

1 row created.

SQL> @redo-diff
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 4024) REDO_SIZE

REDO_SIZE
----------
472

SQL> @redo-new

REDO_SIZE
----------
4496

SQL> commit;

Commit complete.

SQL> @redo-diff
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 4496) REDO_SIZE

REDO_SIZE
----------
140

session 2:

SQL> @redo-new

REDO_SIZE
----------
836

SQL> insert /*+ APPEND_VALUES */ into tb_nologging values (1, 'ABCDEFG');

1 row created.

SQL> @redo-diff
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 836) REDO_SIZE

REDO_SIZE
----------
888

SQL> @redo-new

REDO_SIZE
----------
1724

SQL> commit;

Commit complete.

SQL> @redo-diff
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 1724) REDO_SIZE

REDO_SIZE
----------
1200

SQL> @redo-new

REDO_SIZE
----------
2924

SQL> insert /*+ APPEND_VALUES */ into tb_nologging values (2, 'GFEDCBA');

1 row created.

SQL> @redo-diff
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 2924) REDO_SIZE

REDO_SIZE
----------
452

SQL> @redo-new

REDO_SIZE
----------
3376

SQL> commit;

Commit complete.

SQL> @redo-diff
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 3376) REDO_SIZE

REDO_SIZE
----------
1184

we don't see the different redo log size between APPEND_VALUES hint and no hint, when insert on the same session again. but i see APPEND_VALUES hint generated redo log size more than no hint, after committed.(test another else...)

use APPEND_VALUES hint on Table LOGGING.

SQL> create table tb_logging (a number, b varchar2(100)) logging;

Table created.

SQL> @redo-new

REDO_SIZE
----------
1756

SQL> insert into tb_logging values (1, 'ABCDEFG');

1 row created.

SQL> @redo-diff
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 1756) REDO_SIZE

REDO_SIZE
----------
1880

SQL> @redo-new

REDO_SIZE
----------
3636

SQL> commit;

Commit complete.

SQL> @redo-diff
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 3636) REDO_SIZE

REDO_SIZE
----------
140

SQL> @redo-new

REDO_SIZE
----------
3776

SQL> insert /*+ APPEND_VALUES */ into tb_logging values (1, 'ABCDEFG');

1 row created.

SQL> @redo-diff
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 3776) REDO_SIZE

REDO_SIZE
----------
10508

SQL> @redo-new

REDO_SIZE
----------
14284

SQL> commit;

Commit complete.

SQL> @redo-diff;
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 14284) REDO_SIZE

REDO_SIZE
----------
1200

SQL> @redo-new

REDO_SIZE
----------
15484

SQL> insert into tb_logging values (1, 'ABCDEFG');

1 row created.

SQL> @redo-diff
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 15484) REDO_SIZE

REDO_SIZE
----------
592

SQL> @redo-new

REDO_SIZE
----------
16076

SQL> commit
2 ;

Commit complete.

SQL> @redo-diff
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 16076) REDO_SIZE

REDO_SIZE
----------
140

SQL> @redo-new

REDO_SIZE
----------
16216

SQL> insert /*+ APPEND_VALUES */ into tb_logging values (1, 'ABCDEFG');

1 row created.

SQL> @redo-diff
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 16216) REDO_SIZE

REDO_SIZE
----------
8508

SQL> @redo-new

REDO_SIZE
----------
24724

SQL> commit;

Commit complete.

SQL> @redo-diff
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 24724) REDO_SIZE

REDO_SIZE
----------
1200

On Table Logging... APPEND_VALUES hint generated many redo logs... (strange)
So, we tested with (PL/SQL) FORALL

SQL> create table tb1 nologging as select * from all_objects where 1=0;

Table created.

SQL> c/tb1/tb2
1* create table tb2 nologging as select * from all_objects where 1=0
SQL> /

Table created.

SQL> select table_name, logging from user_tables where table_name in ('TB1','TB2');

TABLE_NAME LOG
------------------------------ ---
TB1 NO
TB2 NO

PL/SQL Scripts: a1=no hint, a2=APPEND_VALUES hint

CREATE OR REPLACE PROCEDURE a1 (p_array_size IN PLS_INTEGER DEFAULT 100)
IS

TYPE ARRAY IS TABLE OF all_objects%ROWTYPE;
l_data ARRAY;

CURSOR c IS
SELECT *
FROM all_objects;

BEGIN
OPEN c;
LOOP
FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;

FORALL i IN 1..l_data.COUNT
INSERT INTO tb1 VALUES l_data(i);
commit;
EXIT WHEN c%NOTFOUND;
END LOOP;
close c;
END a1;
/

CREATE OR REPLACE PROCEDURE a2 (p_array_size IN PLS_INTEGER DEFAULT 100)
IS

TYPE ARRAY IS TABLE OF all_objects%ROWTYPE;
l_data ARRAY;

CURSOR c IS
SELECT *
FROM all_objects;

BEGIN
OPEN c;
LOOP
FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;

FORALL i IN 1..l_data.COUNT
INSERT /*+ APPEND_VALUES */ INTO tb2 VALUES l_data(i);
commit;
EXIT WHEN c%NOTFOUND;
END LOOP;
close c;
END a2;
/

start with Table NOLOGGING

SQL> @redo-new

REDO_SIZE
----------
3060

SQL> exec a1;

PL/SQL procedure successfully completed.

SQL> @redo-diff
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 3060) REDO_SIZE

REDO_SIZE
----------
6871852

SQL> @redo-new

REDO_SIZE
----------
6874912

SQL> exec a2(100);

PL/SQL procedure successfully completed.

SQL> @redo-diff
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 6874912) REDO_SIZE

REDO_SIZE
----------
956988

SQL> select count(*) from tb1;

COUNT(*)
----------
55796

SQL> select count(*) from tb2;

COUNT(*)
----------
55796

APPEND_VALUES hint on Table NOLOGGING generated redo log less than NO hint.
start with Table LOGGING

SQL> truncate table tb1;

Table truncated.

SQL> truncate table tb2;

Table truncated.

SQL> alter table tb1 logging;

Table altered.

SQL> alter table tb2 logging;

Table altered.

SQL> select table_name, logging from user_tables where table_name in ('TB1','TB2');

TABLE_NAME LOG
------------------------------ ---
TB1 YES
TB2 YES

SQL> @redo-new

REDO_SIZE
----------
89088

SQL> exec a1;

PL/SQL procedure successfully completed.

SQL> @redo-diff
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 89088) REDO_SIZE

REDO_SIZE
----------
6869772

SQL> @redo-new

REDO_SIZE
----------
6958860

SQL> exec a2;

PL/SQL procedure successfully completed.

SQL> @redo-diff
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 6958860) REDO_SIZE

REDO_SIZE
----------
10107816

APPEND_VALUES hint on Table LOGGING generated redo log more than NO hint.
By the way if use APPEND_VALUES hint (direct-path), we should use with FORALL.

Scripts:
$ cat redo-new.sql
column REDO_SIZE new_value REDO_SIZE
select value REDO_SIZE
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';

$ cat redo-diff.sql
select (value - &REDO_SIZE) REDO_SIZE
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';