In this post, I will demonstrate a new feature introduced in 12c : In database archiving. It enables you to archive rows within a table by marking them as invisible. This is accomplshed  by means of a hidden column ORA_ARCHIVE_STATE. These invisible rows are not visible to the queries but if needed, can be viewed , by setting a session parameter ROW ARCHIVAL VISIBILITY.

Overview:

-- Create test user uilm, tablespace ilmtbs
-- Connect as user uilm
-- create and populate test table (5 rows) ilmtab with row archival clause
-- Note that the table has an additional column ORA_ARCHIVE_STATE automatically created   and has the default value of 0 (indicates that row is active)
-- Note that this column is not visible when we describe the table or simply issue select * from ...
-- We need to access data dictionary to view the column
-- Make two  rows in the table inactive by setting ORA_ARCHIVE_STATE column to a non zero value.
-- Check that inactive rows are not visible to query
-- Set the parameter ROW ARCHIVAL VISIBILITY  = all to see inactive rows also
-- Set the parameter ROW ARCHIVAL VISIBILITY  = active to hide inactive rows
-- Issue an insert into ... select * and check that only 3 visible rows are inserted
-- Set the parameter ROW ARCHIVAL VISIBILITY  = all to see inactive rows also
-- Issue an insert into ... select * and check that all the rows are inserted but ORA_ARCHIVE_STATE    is not propagated in inserted rows
-- Disable row archiving in the table and check that column ORA_ARCHIVE_STATE is automatically dropped
-- drop tablespace ilmtbs and user uilm

Implementation :

-- Create test user, tablespace and test table
SQL> conn sys/oracle@em12c:1523/pdb1 as sysdba
sho con_name

CON_NAME
------------------------------
PDB1

SQL> set sqlprompt PDB1>

PDB1>create tablespace ilmtbs datafile '/u02/app/oracle/oradata/cdb1/pdb1/ilmtbs01.dbf' size 1m;
grant connect, resource, dba  to uilm identified by oracle;
alter user uilm default tablespace ilmtbs;

conn uilm/oracle@em12c:1523/pdb1
sho con_name

CON_NAME
------------------------------
PDB1
-- create table with "row archival clause"
PDB1>drop table ilmtab purge;
create table ilmtab (id number, txt char(15)) row archival;
insert into ilmtab values (1, 'one');
insert into ilmtab values (2, 'two');
insert into ilmtab values (3, 'three');
insert into ilmtab values (4, 'four');
insert into ilmtab values (5, 'five');
commit;
-- Note that the table has an additional column ORA_ARCHIVE_STATE automatically created    and has the default value of 0 (indicates that row is active)
PDB1>col ora_archive_state for a20
select id, txt, ora_archive_state from ilmtab;

ID TXT             ORA_ARCHIVE_STATE
---------- --------------- --------------------
1 one             0
2 two             0
3 three           0
4 four            0
5 five            0
-- Note that this column is not visible when we describe the table or simply issue select * from ...
PDB1>desc ilmtab
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ID                                                 NUMBER
TXT                                                CHAR(15)

PDB1>select * from ilmtab;

ID TXT
---------- ---------------
1 one
2 two
3 three
4 four
5 five
-- Since the column is invisible, let me try and make it visible
-- Note that Since the column is maintained by oracle itself, user can't modify its attributes
PDB1>alter table ilmtab modify (ora_archive_state visible);
alter table ilmtab modify (ora_archive_state visible)
*
ERROR at line 1:
ORA-38398: DDL not allowed on the system ILM column
-- We need to access data dictionary to view the column
-- Note that this column is shown as hidden and has not been generated by user
PDB1>col hidden for a7
col USER_GENERATED for 20
col USER_GENERATED for a20

select TABLE_NAME, COLUMN_NAME, HIDDEN_COLUMN, USER_GENERATED
from user_tab_cols where table_name='ILMTAB';

TABLE_NAME  COLUMN_NAME          HID USER_GENERATED
----------- -------------------- --- --------------------
ILMTAB      ORA_ARCHIVE_STATE    YES NO
ILMTAB      ID                   NO  YES
ILMTAB      TXT                  NO  YES
-- We can make selected rows in the table inactive by setting ORA_ARCHIVE_STATE column to a non zero value.
This can be accomplished using update table... set ORA_ACRHIVE_STATE =
. <non-zero value>
. dbms_ilm.archivestatename(1)

-- Let's update row with id =1 with ORA_ARCHIVE_STATE=2
     and update row with id =2 with dbms_ilm.archivestatename(2)
PDB1>update ilmtab set ora_archive_state=2 where id=1;

update ilmtab set ora_archive_state= dbms_ilm.archivestatename(2) where id=2;
-- Let's check whether updates have been successful and hidden rows are not visible
PDB1>select id, txt, ORA_ARCHIVE_STATE from ilmtab;

ID TXT             ORA_ARCHIVE_STATE
---------- --------------- --------------------
3 three           0
4 four            0
5 five            0
-- The updated rows are not visible!!
-- Quite logical since we have made the rows active and by default only active rows are visible

-- To see inactive rows also, we need to set the parameter ROW ARCHIVAL VISIBILITY  = all at session level
-- Note that the column ORA_ARCHIVE_STATE has been set to 1 for id =2 although we had set it to 2 using
dbms_ilm.archivestatename(2)
PDB1>alter session set ROW ARCHIVAL VISIBILITY  = all;
select id, txt, ORA_ARCHIVE_STATE from ilmtab;

ID TXT             ORA_ARCHIVE_STATE
---------- --------------- --------------------
1 one             2
2 two             1
3 three           0
4 four            0
5 five            0
-- Note that the column ORA_ARCHIVE_STATE has been set to 1 for id =2 although we had set it to 2 using    dbms_ilm.archivestatename(2)

-- Let's find out why
-- Note that The function dbms_ilm.archivestatename(n) returns only two values    0 for n=0 and 1 for  n <> 0
PDB1>col state0 for a8
col state1 for a8
col state2 for a8
col state3 for a8

select dbms_ilm.archivestatename(0) state0 ,dbms_ilm.archivestatename(1) state1,
dbms_ilm.archivestatename(2) state2,dbms_ilm.archivestatename(3) state3  from dual;

STATE0   STATE1   STATE2   STATE3
-------- -------- -------- --------
0        1        1        1
-- In order to make the inactive rows (id=1,2) hidden again, we need to set the parameter ROW ARCHIVAL VISIBILITY  = Active
PDB1>alter session set row archival visibility = active;
select id, txt, ORA_ARCHIVE_STATE from ilmtab;

ID TXT             ORA_ARCHIVE_STATE
---------- --------------- --------------------
3 three           0
4 four            0
5 five            0
-- Let's issue an insert into ... select *
-- Note that only 3 new rows are visible
PDB1>insert into ilmtab select * from ilmtab;

select id, txt, ora_archive_state from ilmtab;

ID TXT             ORA_ARCHIVE_STATE
---------- --------------- --------------------
3 three           0
4 four            0
5 five            0
3 three           0
4 four            0
5 five            0

6 rows selected.
-- I want to check if hidden rows were also inserted
-- Let's check by making  hidden rows visible again
-- Note that only visible rows(id=3,4,5) were inserted
PDB1>alter session set row archival visibility=all;
select id, txt, ora_archive_state from ilmtab;

ID TXT             ORA_ARCHIVE_STATE
---------- --------------- --------------------
1 one             2
2 two             1
3 three           0
4 four            0
5 five            0
3 three           0
4 four            0
5 five            0

8 rows selected.
-- Let's set row archival visibility = all and then again insert rows from ilmtab
-- Note that all the 8 rows are inserted but ORA_ARCHIVE_STATE ha not been copied    ORA_ARCHIVE_STATE <> 0 in only 2 records (id = 1,2) even now.
PDB1>alter session set row archival visibility=all;
insert into ilmtab select * from ilmtab;
select id, txt, ora_archive_state from ilmtab order by id;

ID TXT             ORA_ARCHIVE_STATE
---------- --------------- --------------------
1 one             0
1 one             2
2 two             0
2 two             1
3 three           0
3 three           0
3 three           0
3 three           0
4 four            0
4 four            0
4 four            0
4 four            0
5 five            0
5 five            0
5 five            0
5 five            0

16 rows selected.
-- Disable row level archiving for the table
-- Note that as soon as row archiving is disabled, pseudo column ora_archive_state is dropped automatically
PDB1>alter table ilmtab no row archival;
select id, txt, ORA_ARCHIVE_STATE from ilmtab;

ERROR at line 1:
ORA-00904: "ORA_ARCHIVE_STATE": invalid identifier

PDB1>col hidden for a7
col USER_GENERATED for 20
col USER_GENERATED for a20

select TABLE_NAME, COLUMN_NAME, HIDDEN_COLUMN, USER_GENERATED
from user_tab_cols where table_name='ILMTAB';

TABLE_NAME  COLUMN_NAME          HID USER_GENERATED
----------- -------------------- --- --------------------
ILMTAB      ID                   NO  YES
ILMTAB      TXT                  NO  YES
Note : Had we created this table using sys, we could not have disabled row archiving .

-- cleanup --
PDB1>conn sys/oracle@em12c:1523/pdb1 as sysdba
drop tablespace ilmtbs including contents and datafiles;
drop user uilm cascade;
References:

http://docs.oracle.com/cd/E16655_01/server.121/e17613/part_lifecycle.htm#VLDBG14154

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

Oracle 12c Index

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

AIX is like an Unix environment awesome original, same to HP-Unix, and, if you have a plan to install Oracle RAC, you need to pay attention. I note some tips in this article to help.

1. Checking Operating System Packages

# lslpp -l bos.adt.base bos.adt.lib bos.adt.libm bos.perf.libperfstat 
bos.perf.perfstat bos.perf.proctools rsct.basic.rte rsct.compat.clients.rte 
xlC.aix61.rte

If not, install on AIX source by smity. It's easy, but remember, some packaged requires your IBM's account to download.

2. Verify UDP and TCP Kernel Parameters

# /usr/sbin/no -a | fgrep ephemeral

If you expect your workload to require a high number of ephemeral ports, then update
the UDP and TCP ephemeral port range to a broader range. For example:

# /usr/sbin/no -p -o tcp_ephemeral_low=9000 -o tcp_ephemeral_high=65500
# /usr/sbin/no -p -o udp_ephemeral_low=9000 -o udp_ephemeral_high=65500

3. Checking Resource Limits:
To ensure that these resource limits are honored, confirm that the line login
session required /usr/lib/security/pam_aix is set in /etc/pam.conf.For example:

dtsession auth required /usr/lib/security/pam_aix
dtlogin session required /usr/lib/security/pam_aix
ftp session required /usr/lib/security/pam_aix
imap session required /usr/lib/security/pam_aix
login session required /usr/lib/security/pam_aix
rexec session required /usr/lib/security/pam_aix
rlogin session required /usr/lib/security/pam_aix
rsh session required /usr/lib/security/pam_aix
snapp session required /usr/lib/security/pam_aix
su session required /usr/lib/security/pam_aix
swrole session required /usr/lib/security/pam_aix
telnet session required /usr/lib/security/pam_aix
xdm session required /usr/lib/security/pam_aix
OTHER session required /usr/lib/security/pam_prohibit
websm_rlogin session required /usr/lib/security/pam_aix
websm_su session required /usr/lib/security/pam_aix
wbem session required /usr/lib/security/pam_aix

4. Tuning AIX System Environment
Confirm the aio_maxreqs value using the procedure for your release:
AIX 6.1 and 7.1:

# ioo -o aio_maxreqs
aio_maxreqs = 65536

The aio is Asynchronous Input Output is an exciting parameter, I tried to control and modified it many times, but it's strongly to do from Oracle advices,

Oracle document refer: https://docs.oracle.com/database/121/AXDBI/app_manual.htm#AXDBI7880

5. Tuning Virtual Memory Manager

vmo -p -o minperm%=3
vmo -p -o maxperm%=90
vmo -p -o maxclient%=90
vmo -p -o lru_file_repage=0
vmo -p -o strict_maxclient=1
vmo -p -o strict_maxperm=0

Note: You must restart the system for these changes to take effect

6. Increase System block size allocation

# /usr/sbin/chdev -l sys0 -a ncargs='128'

7. Configure SSH LoginGraceTime Parameter
On AIX systems, the OpenSSH parameter LoginGraceTime by default is commented
out, and the default behavior of OpenSSH on AIX can sometimes result in timeout
errors. To avoid these errors, complete the following procedure:
7.1. Log in as root.
7.2. Using a text editor, open the OpenSSH configuration file /etc/ssh/sshd_config.
7.3. Locate the comment line #LoginGraceTime 2m.
7.4. Uncomment the line, and change the value to 0 (unlimited). For example:
LoginGraceTime 0
7.5. Save /etc/ssh/sshd_config.
7.6. Restart SSH.

8. Setting priviledge to Oracle ASM Luns
Same to Solaris, HP-Unix. Remember, when you've got failure of ASM configuration, you need to flush out the disk's slice/partition by OS command "dd". And the slice/partition/LUN allocated from storage to IBM, has got different first alphabet to other platform. The alphabet begins by "r", example:

7.1 ORC and Voting disk
# chown grid:asmadmin /dev/rhdisk5 -> OCR
# chmod 660 /dev/rhdisk5 
# chown grid:asmadmin /dev/rhdisk6 -> Voting Disk
# chmod 660 /dev/rhdisk6
7.2 Datafile, Archivelog and Backup 
# chown grid:asmadmin /dev/rhdisk2
# chmod 660 /dev/rhdisk2
# chown grid:asmadmin /dev/rhdisk3
# chmod 660 /dev/rhdisk3
# chown grid:asmadmin /dev/rhdisk4
# chmod 660 /dev/rhdisk4
# chown grid:asmadmin /dev/rhdisk9
# chmod 660 /dev/rhdisk9
# chown grid:asmadmin /dev/rhdisk10
# chmod 660 /dev/rhdisk10

9. Enable simultaneous access to a disk device from multiple nodes
To enable simultaneous access to a disk device from multiple nodes, you must set the appropriate Object Data Manager (ODM) attribute, depending on the type of reserve attribute used by your disks. The following section describes how to perform this task using hdisk logical names

8.1. determine the reserve setting your disks use, enter the following command,where n is the hdisk device number

# lsattr -E -l hdiskn | grep reserve_

The response is either a reserve_lock setting, or a reserve_policy setting. If the attribute is reserve_lock, then ensure that the setting is reserve_lock = no. If the attribute is reserve_policy, then ensure that the setting is reserve_policy = no_reserve.

8.2. If necessary, change the setting with the chdev command using the following syntax, where n is the hdisk device number:

chdev -l hdiskn -a [ reserve_lock=no | reserve_policy=no_reserve ]

# chdev -l hdisk5 -a reserve_lock=no
# chdev -l hdisk5 -a reserve_policy=no_reserve

8.3. Enter commands similar to the following on any node to clear the PVID from each disk device that you want to use:

# /usr/sbin/chdev -l hdiskn -a pv=clear

When you are installing Oracle Clusterware, you must enter the paths to the appropriate device files when prompted for the path of the OCR and Oracle Clusterware voting disk. For example: /dev/rhdisk10

9.Configure Shell Limits
9.1. Add the following lines to the /etc/security/limits file:

default:
fsize = -1
core = 2097151
cpu = -1
data = -1
rss = -1
stack = -1
nofiles = -1

9.2.Enter the following command to list the current setting for the maximum number of process allowed by the Oracle software user:

/usr/sbin/lsattr -E -l sys0 -a maxuproc

If necessary, change the maxuproc setting using the following command:

/usr/sbin/chdev -l sys0 -a maxuproc=16384

10. Configure User Process Parameters (Verify that the maximum number of processes allowed for each user is set to 2048 or greater)
Enter the following command:

# smit chgsys

11. Configure Network Tuning Parameters:
To check the current values of the network tuning parameters:

# no -a | more

If the system is running in compatibility mode, then follow these steps to change the parameter values:

Enter commands similar to the following to change the value of each parameter:

# no -o parameter_name=value

For example:

# no -o udp_recvspace=655360

Add entries similar to the following to the /etc/rc.net file for each parameter that you changed in the previous step:

if [ -f /usr/sbin/no ] ; then
/usr/sbin/no -o udp_sendspace=65536
/usr/sbin/no -o udp_recvspace=655360
/usr/sbin/no -o tcp_sendspace=65536
/usr/sbin/no -o tcp_recvspace=65536
/usr/sbin/no -o rfc1323=1
/usr/sbin/no -o sb_max=4194304
/usr/sbin/no -o ipqmaxlen=512
fi

For the ISNO parameter tcp_sendspace, use the following command to set it:

# ifconfig en0 tcp_sendspace 65536

12. Automatic SSH configuration
By default, OUI searches for SSH public keys in the directory /usr/local/etc/, and ssh-keygen binaries in /usr/local/bin. However, on AIX, SSH public keys typically are located in the path /etc/ssh, and ssh-keygen binaries are located in the path /usr/bin. To ensure that OUI can set up SSH, use the following command to create soft links:

# ln -s /etc/ssh /usr/local/etc
# ln -s /usr/bin /usr/local/bin

In rare cases, Oracle Clusterware installation may fail during the "AttachHome" operation when the remote node closes the SSH connection. To avoid this problem, set the following parameter in the SSH daemon configuration file /etc/ssh/sshd_config on all cluster nodes to set the timeout wait to unlimited:

LoginGraceTime 0

13. Shell Limit
Adding these line in /etc/security/limits

default:
fsize = -1
core = 2097151
cpu = -1
data = -1
rss = -1
stack = -1
nofiles = -1

14. Create groups and users

# mkgroup -'A' id='1000' adms='root' oinstall
# mkgroup -'A' id='1031' adms='root' dba
# mkgroup -'A' id='1032' adms='root' oper
# mkgroup -'A' id='1020' adms='root' asmadmin
# mkgroup -'A' id='1022' adms='root' asmoper
# mkgroup -'A' id='1021' adms='root' asmdba
# mkuser id='1100' pgrp='oinstall' groups='dba,asmadmin,asmoper,asmdba' home='/portalgrid/grid' grid
# mkuser id='1101' pgrp='oinstall' groups='dba,oper,asmdba' home='/portaloracle/oracle' oracle

# mkdir -p /portalapp/app/11.2.0/grid
# mkdir -p /portalapp/app/grid
# mkdir -p /portalapp/app/oracle

# chown grid:oinstall /portalapp/app/11.2.0/grid <- GRID_HOME
# chown grid:oinstall /portalapp/app/grid <- GRID_BASE (ORACLE_BASE for Grid user)
# chown -R grid:oinstall /portalapp
# chown oracle:oinstall /portalapp/app/oracle
# chmod -R 775 /portalapp/

15. Setting the profile
15.1. Grid Profile

export TEMP=/tmp
export TMP=/tmp
export TMPDIR=/tmp
umask 022

export ORACLE_HOSTNAME=portal1
export ORACLE_BASE=/portalapp/app/grid
export ORACLE_HOME=/portalapp/app/11.2.0/grid
export GRID_HOME=/portalapp/app/11.2.0/grid
export CRS_HOME=/portalapp/app/11.2.0/grid
export ORACLE_SID=+ASM1

export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib

16. Prevent Xserver does not display correct term

# startsrc -x

17. Create the following softlink needed for some Oracle utilites

# ln -s /usr/sbin/lsattr /etc/lsattr

To check existing capabilities, enter the following command as root; in this example,
the Grid installation user account is grid:

# /usr/bin/lsuser -a capabilities grid

To add capabilities, enter a command similar to the following:

# /usr/bin/chuser capabilities=CAP_NUMA_ATTACH,CAP_BYPASS_RAC_VMM,CAP_PROPAGATE grid

18. Remember to run the Installation fixup scripts

$ ./runcluvfy.sh stage -pre crsinst -n node -fixup -verbose

With Oracle Clusterware 11g release 2, Oracle Universal Installer (OUI) detects when the minimum requirements for an installation are not met, and creates shell scripts,
called fixup scripts, to finish incomplete system configuration steps. If OUI detects an incomplete task, then it generates fixup scripts (runfixup.sh). You can run the fixup script after you click the Fix and Check Again Button.

19. In the installation progressing, when root.sh at node 2, can the error such as "CRS appear in node 1, did not attemp to stop cluster, re-join cluster, by pass and continue installation.
- If Xterm did not occur, then do: $ export ORACLE_TERM=dtterm
- Manually export ORACLE_BASE, ORACLE_HOME when make an installation before running runInstaller.sh
- If /tmp is too small <500MB, then make a private directory point to other directory, change owner to grid, oracle user, example:

A. Grid

# cd /portallog
# mkdir /portallog/tmp
# chown -R grid:oinstall /portallog/tmp

B. Oracle

# cd /portal
# mkdir tmp
# chown -R oracle:dba /portal/tmp 

C. Export

# export TMP=/portallog/tmp
# export TEMPDIR=/portallog/tmp
# export TMPDIR=/portallog/tmp

Hope this help.
End.
TAT