How many different type of wait event parameters in Oracle 12c (12.1.0.2)

SQL> select parameter1,count(*) from v$event_name group by parameter1 order by 2 desc;

PARAMETER1                                                         COUNT(*)
---------------------------------------------------------------- ----------
name|mode                                                               463
                                                                        456
type|mode                                                               200
address                                                                  50
file#                                                                    40
sleeptime/senderid                                                       40
cellhash#                                                                22
location                                                                 19
count                                                                    18
                                                                         17
driver id                                                                16
group                                                                    16
group#                                                                   14
Session ID                                                               13
2=>GoldenGate 1=>XStream 0=>Streams                                      12
session_id                                                                8
where                                                                     8
idn                                                                       7
sleep time                                                                7
function_id                                                               7
waittime                                                                  6
blkno                                                                     5
p1                                                                        5
filectx                                                                   5
handle address                                                            4
le                                                                        4
log#                                                                      4
file number                                                               4
block#                                                                    4
wait count                                                                4
retry count                                                               4
type                                                                      3
op                                                                        3
nalive                                                                    3
duration                                                                  3
object                                                                    3
files                                                                     3
cluinc                                                                    3
channel context                                                           3
msg ptr                                                                   3
clientid                                                                  3
requests                                                                  3
segment#                                                                  3
circuit#                                                                  2
msgop                                                                     2
buffer#                                                                   2
tsn                                                                       2
maximum attempts                                                          2
lmd/lms id                                                                2
object_id                                                                 2
Caller ID                                                                 2
dba                                                                       2
seghdr                                                                    2
waited                                                                    2
iowp id                                                                   2
pname                                                                     2
group_name1                                                               2
fileno                                                                    2
cache id                                                                  2
pid                                                                       2
wait for scn's hi 4 bytes                                                 2
channel handle                                                            2
Owner ID                                                                  2
copy latch #                                                              2
type|mode|where                                                           1
pool                                                                      1
undo seg#|slot#                                                           1
lock                                                                      1
hashid                                                                    1
action                                                                    1
File number                                                               1
Type                                                                      1
who am I                                                                  1
alive slaves                                                              1
send count                                                                1
index                                                                     1
event                                                                     1
table                                                                     1
undo segment#                                                             1
rootdba                                                                   1
end-point#                                                                1
kxfxsp debug wait: stalling for slave 0                                   1
component                                                                 1
ktelc_wait1s                                                              1
Slave ID                                                                  1
sleeptime                                                                 1
outstanding #aio                                                          1
wrap                                                                      1
blocks                                                                    1
operation count                                                           1
force SCN                                                                 1
succ                                                                      1
dismount force                                                            1
msg                                                                       1
by thread#                                                                1
dump                                                                      1
kxfxse debug wait: stalling for slave 0                                   1
delegation count                                                          1
timeout                                                                   1
FileOperation                                                             1
tape operation                                                            1
filename_hash                                                             1
object address                                                            1
netp id                                                                   1
dbwr#                                                                     1
xidusn                                                                    1
stage                                                                     1
function                                                                  1
process#                                                                  1
slave id                                                                  1
group_id                                                                  1
connection broker                                                         1
session#                                                                  1
defer segment index                                                       1
indicator                                                                 1
latch addr                                                                1
lwn_id                                                                    1
Instruction id                                                            1
branch#                                                                   1
session_num                                                               1
nservers                                                                  1
slowcellhash#                                                             1
kdcph_mai                                                                 1
from_process                                                              1
rolling mig                                                               1
domain id                                                                 1
caller instance number                                                    1
id                                                                        1
ts#                                                                       1
thread#                                                                   1
process number                                                            1
scans                                                                     1
tries                                                                     1
Broker Component                                                          1
test #                                                                    1
max operations                                                            1
component id                                                              1
retries                                                                   1
1=>MASTER 2=>SLAVE                                                        1
process_pid                                                               1
PURPOSE                                                                   1
pending_nd                                                                1
SCN                                                                       1
layer                                                                     1
clsrrestype                                                               1
serial                                                                    1
event #                                                                   1
limit                                                                     1
kdcphc_ack                                                                1
heap ds                                                                   1
wait event                                                                1
queue id                                                                  1
gopp id                                                                   1
error                                                                     1

154 rows selected.

SQL> select parameter2,count(*) from v$event_name group by parameter2 order by 2 desc;

PARAMETER2                                                         COUNT(*)
---------------------------------------------------------------- ----------
                                                                        602
id1                                                                     258
0                                                                        81
number                                                                   50
block#                                                                   44
passes                                                                   41
operation                                                                38
                                                                         24
QT_OBJ#                                                                  21
object #                                                                 20
Q_OBJ#                                                                   20
obj#                                                                     19
disk group                                                               15
intr                                                                     14
#bytes                                                                   14
disk group #                                                             12
log # / thread id #                                                      12
lock operation                                                           11
service ID                                                               11
tablespace #                                                             10
serial                                                                    8
value                                                                     8
diskhash#                                                                 7
0 or file #                                                               7
record type                                                               7
Internal                                                                  7
blocks                                                                    6
logid                                                                     6
file#                                                                     6
type                                                                      6
sleeptime                                                                 5
mode                                                                      5
kdlw lobid first half                                                     5
container group                                                           5
p2                                                                        5
resource id                                                               5
2                                                                         4
group ID / file ID                                                        4
where                                                                     4
wait flags                                                                4
opcode                                                                    4
first dba                                                                 4
persistent DG number                                                      4
usn<<16 | slot                                                            4
sqlid                                                                     3
TYPE                                                                      3
group and disk number                                                     3
count                                                                     3
failed                                                                    3
dbid                                                                      3
blkno                                                                     3
process#                                                                  3
reg id                                                                    3
waited                                                                    3
1                                                                         3
channel handle                                                            2
wait for scn's lo 4 bytes                                                 2
lms id                                                                    2
redo thread                                                               2
attempt count                                                             2
poll                                                                      2
lock address                                                              2
break?                                                                    2
0-MMON, 1-MMON Slave                                                      2
table space #                                                             2
message                                                                   2
groupid                                                                   2
Log #                                                                     2
disk group #:file #                                                       2
tablespace ID                                                             2
group_name2                                                               2
node#/parallelizer#                                                       2
checkpoint ID                                                             2
lock_mode                                                                 2
master object #                                                           2
#blks                                                                     2
Thread                                                                    2
bytes                                                                     2
workspace #                                                               2
interrupt                                                                 2
wait_count                                                                2
client id/group num                                                       2
sync scn                                                                  2
class id                                                                  2
size                                                                      1
fsb                                                                       1
num procs                                                                 1
low file obj add                                                          1
current aio limit                                                         1
property name                                                             1
op|restype                                                                1
pending_insts                                                             1
class*10+mode                                                             1
session ID                                                                1
undo segment #                                                            1
serial number                                                             1
view object #                                                             1
Session-id                                                                1
lock#                                                                     1
wait time                                                                 1
loop                                                                      1
slave_id                                                                  1
wait                                                                      1
file group id                                                             1
cluster incarnation number                                                1
scnwrp                                                                    1
amrv$ key                                                                 1
xidslt                                                                    1
requests                                                                  1
KZAM Fga Partition                                                        1
transaction entry #                                                       1
Wait Argument 1                                                           1
disk group number                                                         1
buffer length                                                             1
sleep time                                                                1
File Type or File number                                                  1
plan #                                                                    1
param                                                                     1
tx flags                                                                  1
retry_count                                                               1
l1bmb                                                                     1
hash value                                                                1
status                                                                    1
margin                                                                    1
consumer group id                                                         1
connection                                                                1
record length                                                             1
id                                                                        1
is_process                                                                1
nodeid                                                                    1
LTHREAD TYPE                                                              1
location                                                                  1
thread number                                                             1
base                                                                      1
scn                                                                       1
level                                                                     1
Schedule Id                                                               1
Flags                                                                     1
operation flags                                                           1
EnqMode                                                                   1
chunkNo                                                                   1
filename                                                                  1
dest|rcvr                                                                 1
pending_nd                                                                1
file #                                                                    1
KZAM Aud Partition                                                        1
element                                                                   1
Index Id                                                                  1
locn                                                                      1
gtrid hash value                                                          1
min_size                                                                  1
allocation mode                                                           1
process_sno                                                               1
rcvinc                                                                    1
chain#                                                                    1
wrap#                                                                     1
Slave process id                                                          1
KSBXIC Action                                                             1
error                                                                     1
instance                                                                  1
Op1                                                                       1
node#+sess#,cursor#+kv#                                                   1
file                                                                      1
map id                                                                    1
dginc                                                                     1
client id                                                                 1
fileno                                                                    1
channel handle count                                                      1
current size                                                              1
iocode                                                                    1
parno                                                                     1
waits                                                                     1
request                                                                   1
our thread#                                                               1
thread id #                                                               1
phase                                                                     1
pool #                                                                    1
Instance ID                                                               1
tsn#                                                                      1
segid                                                                     1
table obj#                                                                1
handle                                                                    1
edition obj#                                                              1
dg                                                                        1
pin address                                                               1
obj                                                                       1
block number                                                              1
startscn                                                                  1
client opcode                                                             1
inst id                                                                   1
tablespace # & objd                                                       1
Spillover audit file                                                      1
kjha_action                                                               1
lms#                                                                      1
group                                                                     1
task id                                                                   1

196 rows selected.

SQL> select parameter3,count(*) from v$event_name group by parameter3 order by 2 desc;

PARAMETER3                                                         COUNT(*)
---------------------------------------------------------------- ----------
                                                                        743
id2                                                                     258
0                                                                       150
tries                                                                    48
type                                                                     27
                                                                         25
timeout                                                                  25
block#                                                                   20
file #                                                                   20
shard:mode                                                               20
class#                                                                   17
sequence #                                                               13
queue type                                                               11
lock value                                                               11
operation parm                                                           10
blocks                                                                    9
dba                                                                       9
where                                                                     7
record id                                                                 7
thread                                                                    7
Internal                                                                  7
bktId<<16|instid                                                          6
p3                                                                        6
filetype                                                                  5
0/1                                                                       5
kdlw lobid sec half                                                       5
container id                                                              5
bytes                                                                     5
unused                                                                    4
block cnt                                                                 4
file ID                                                                   4
qref                                                                      4
workspace #                                                               4
non-DG number enqs                                                        4
sequence                                                                  4
disk #                                                                    4
id#                                                                       3
requests                                                                  3
wait                                                                      3
execid                                                                    3
2                                                                         3
AU number                                                                 3
notused                                                                   3
operation                                                                 3
instance                                                                  3
loop                                                                      3
size                                                                      2
block number                                                              2
slaveid                                                                   2
intr                                                                      2
100*mode+namespace                                                        2
location                                                                  2
zero                                                                      2
virtual extent number                                                     2
block                                                                     2
rdba                                                                      2
Bytes                                                                     2
file                                                                      2
event                                                                     2
bloom#                                                                    2
File number                                                               2
Sequence                                                                  2
process#                                                                  2
object #                                                                  1
set-id#                                                                   1
fail                                                                      1
ackscn                                                                    1
request identifier                                                        1
task id                                                                   1
ulevel                                                                    1
nbusy                                                                     1
Partition Id                                                              1
growth                                                                    1
target size                                                               1
100*mask+namespace                                                        1
op                                                                        1
wait time                                                                 1
allocated                                                                 1
1 or block                                                                1
count                                                                     1
objd#                                                                     1
group_name3                                                               1
1                                                                         1
not used                                                                  1
read/write                                                                1
version id                                                                1
mtype                                                                     1
log number                                                                1
childdba                                                                  1
insert/update                                                             1
Wait Argument 2                                                           1
extent                                                                    1
nothing                                                                   1
table/partition                                                           1
clatch                                                                    1
instanceid                                                                1
flag                                                                      1
only dml                                                                  1
relative file #                                                           1
hash value                                                                1
enqueue                                                                   1
serial #                                                                  1
slave ID                                                                  1
sequence # / apply #                                                      1
blockNo                                                                   1
instance|serial                                                           1
dtp                                                                       1
number of blocks                                                          1
mem_id                                                                    1
kv #                                                                      1
file number                                                               1
vol                                                                       1
wait time(millisec)                                                       1
pos                                                                       1
P3                                                                        1
id1|id2                                                                   1
startdba                                                                  1
Op2                                                                       1
Serial#                                                                   1
none                                                                      1
entry                                                                     1
bqual hash value                                                          1
diskno                                                                    1
high file obj add                                                         1
broadcast message                                                         1
scnbas                                                                    1
xidsqn                                                                    1
undo segment # / other                                                    1
waited                                                                    1
request                                                                   1
new aio limit                                                             1
key hash                                                                  1
buf_ptr                                                                   1
hash(logname)                                                             1
handle                                                                    1
generation                                                                1

136 rows selected.

Using DBCA to create CDB/PDB database in scripting manner

DBCA Script Generated by runInstaller:

/u01/app/oracle/product/12.1.0/dbhome_1/bin/dbca –silent -progress_only -createDatabase -templateName General_Purpose.dbc -createAsContainerDatabase true -pdbName pdb1 -numberOfPDBs 1 -sid cdborcl -gdbName cdborcl -emConfiguration DBEXPRESS -storageType FS -datafileDestination /u01/app/oracle/oradata -datafileJarLocation /u01/app/oracle/product/12.1.0/dbhome_1/assistants/dbca/templates -responseFile NO_VALUE -characterset WE8MSWIN1252 -obfuscatedPasswords false -sampleSchema true -automaticMemoryManagement true -totalMemory 1637 -maskPasswords false

Modified Version with “Silent” option:

/u01/app/oracle/product/12.1.0/dbhome_1/bin/dbca -silent -createDatabase -templateName General_Purpose.dbc -createAsContainerDatabase true -pdbName pdb1 -numberOfPDBs 1 -sid cdborcl -gdbName cdborcl -emConfiguration DBEXPRESS -storageType FS -datafileDestination /u01/app/oracle/oradata -datafileJarLocation /u01/app/oracle/product/12.1.0/dbhome_1/assistants/dbca/templates -responseFile NO_VALUE -characterset WE8MSWIN1252 -obfuscatedPasswords false -sampleSchema true -automaticMemoryManagement true -totalMemory 1637 -maskPasswords false

Output for Silent Option:

oracle@solaris:/u01/app/oracle/diag/rdbms/cdborcl/cdborcl/trace$
oracle@solaris:/u01/app/oracle/diag/rdbms/cdborcl/cdborcl/trace$ cd
oracle@solaris:~$ /u01/app/oracle/product/12.1.0/dbhome_1/bin/dbca -silent -createDatabase -templateName General_Purpose.dbc -createAsContainerDatabase true -pdbName pdb1 -numberOfPDBs 1 -sid cdborcl -gdbName cdborcl -emConfiguration DBEXPRESS -storageType FS -datafileDestination /u01/app/oracle/oradata -datafileJarLocation /u01/app/oracle/product/12.1.0/dbhome_1/assistants/dbca/templates -responseFile NO_VALUE -characterset WE8MSWIN1252 -obfuscatedPasswords false -sampleSchema true -automaticMemoryManagement true -totalMemory 1024 -maskPasswords false
Enter SYS user password:
password
Enter SYSTEM user password:
password
Enter PDBADMIN User Password:
password

Cleaning up failed steps
4% complete
Copying database files
5% complete
6% complete
12% complete
17% complete
22% complete
27% complete
30% complete
Creating and starting Oracle instance
32% complete
35% complete
36% complete
37% complete
41% complete
44% complete
45% complete
48% complete
Completing Database Creation
50% complete
53% complete
55% complete
63% complete
66% complete
74% complete
Creating Pluggable Databases
79% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/cdborcl/cdborcl0.log" for further details.
oracle@solaris:~$

Use Certificate to secure the endpoint used for AlwaysOn Availability Group if local account used for SQL Server

 

Commands in SSOSQL1	Commands in SSOSQL2
USE master GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = '!QAZXSW#$%e'; GO USE master; GO CREATE CERTIFICATE SSOSQL1_cert WITH SUBJECT = 'SSOSQL1 certificate', START_DATE = '20141031', EXPIRY_DATE = '20241031'; GO BACKUP CERTIFICATE SSOSQL1_cert TO FILE = 'C:\Backup\SSOSQL1_cert.cer'; GO ALTER ENDPOINT [Hadr_endpoint] for database_mirroring( AUTHENTICATION = CERTIFICATE SSOSQL1_cert ) GO	USE master GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = '!QAZXSW#$%e'; GO USE master; GO CREATE CERTIFICATE SSOSQL2_cert WITH SUBJECT = 'SSOSQL2 certificate', START_DATE = '20141031', EXPIRY_DATE = '20241031'; GO BACKUP CERTIFICATE SSOSQL2_cert TO FILE = 'C:\Backup\SSOSQL2_cert.cer'; GO ALTER ENDPOINT [Hadr_endpoint] for database_mirroring( AUTHENTICATION = CERTIFICATE SSOSQL2_cert ) GO
Copy SSOSQL1_cert.cer to Server SSOSQL2	Copy SSOSQL2_cert.cer to Server SSOSQL1
USE master; CREATE LOGIN [SSOSQL2_Login] WITH PASSWORD = '1Sample_Strong_Password!!#'; GO CREATE USER [SSOSQL2_User] FOR LOGIN [SSOSQL2_Login]; GO CREATE CERTIFICATE SSOSQL2_Cert AUTHORIZATION SSOSQL2_User FROM FILE = 'C:\backup\SSOSQL2_cert.cer' GO GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [SSOSQL2_Login]; GO	USE master; CREATE LOGIN [SSOSQL1_Login] WITH PASSWORD = '1Sample_Strong_Password!!#'; GO CREATE USER [SSOSQL1_User] FOR LOGIN [SSOSQL1_Login]; GO CREATE CERTIFICATE SSOSQL1_Cert AUTHORIZATION SSOSQL1_User FROM FILE = 'C:\backup\SSOSQL1_cert.cer' GO GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [SSOSQL1_Login]; GO

Possible error message in the errorlog if certification not configured correctly:

Date 11/13/2014 8:43:50 AM Log SQL Server (Current - 11/13/2014 8:26:00 AM) Source Logon Message Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 192.168.6.140]

Date 11/13/2014 8:43:53 AM Log SQL Server (Current - 11/13/2014 8:26:00 AM) Source Logon Message Database Mirroring login attempt failed with error: 'Connection handshake failed. The certificate used by the peer is invalid due to the following reason: Certificate not found. State 89.'. [CLIENT: 192.168.6.140]

 

Successful message:

Date 11/13/2014 8:49:47 AM Log SQL Server (Current - 11/13/2014 8:26:00 AM) Source spid25s Message A connection for availability group 'TestAG' from availability replica 'SSOSQL2' with id [20F90002-3F1A-440E-B8B1-BBDF23CAC3AC] to 'SSOSQL1' with id [DF75459C-A7C4-423A-92B8-DFE097ECAF06] has been successfully established. This is an informational message only. No user action is required.

Install SQL Server 2012 using Silent Mode

D:\setup.exe /ConfigurationFile=C:\ConfigurationFile.ini /IAcceptSQLServerLicenseTerms /SAPWD=password

;SQL Server 2012 Configuration File [OPTIONS] ; Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter. ACTION="Install" ; Detailed help for command line argument ENU has not been defined yet. ENU="True" ; Parameter that controls the user interface behavior. Valid values are Normal for the full UI,AutoAdvance for a simplied UI, and EnableUIOnServerCore for bypassing Server Core setup GUI block. ; The /UIMode setting cannot be used in conjunction with /Q or /QS. ; UIMODE="Normal" ; Setup will not display any user interface. ; QUIET="False" ; Setup will display progress only, without any user interaction. QUIETSIMPLE="True" ; Specify whether SQL Server Setup should discover and include product updates. The valid values are True and False or 1 and 0. By default SQL Server Setup will include updates that are found. UpdateEnabled="False" ; Specifies features to install, uninstall, or upgrade. The list of top-level features include SQL, AS, RS, IS, MDS, and Tools. The SQL feature will install the Database Engine, Replication, Full-Text, and Data Quality Services (DQS) server. The Tools feature will install Management Tools, Books online components, SQL Server Data Tools, and other shared components. FEATURES=SQLENGINE,BIDS,CONN,IS,BC,SDK,BOL,SSMS,ADV_SSMS,SNAC_SDK ; Specify the location where SQL Server Setup will obtain product updates. The valid values are "MU" to search Microsoft Update, a valid folder path, a relative path such as .\MyUpdates or a UNC share. By default SQL Server Setup will search Microsoft Update or a Windows Update service through the Window Server Update Services. UpdateSource="MU" ; Displays the command line parameters usage HELP="False" ; Specifies that the detailed Setup log should be piped to the console. INDICATEPROGRESS="False" ; Specifies that Setup should install into WOW64. This command line argument is not supported on an IA64 or a 32-bit system. X86="False" ; Specify the root installation directory for shared components.  This directory remains unchanged after shared components are already installed. INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server" ; Specify the root installation directory for the WOW64 shared components.  This directory remains unchanged after WOW64 shared components are already installed. INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server" ; Specify a default or named instance. MSSQLSERVER is the default instance for non-Express editions and SQLExpress for Express editions. This parameter is required when installing the SQL Server Database Engine (SQL), Analysis Services (AS), or Reporting Services (RS). INSTANCENAME="MSSQLSERVER" ; Specify the Instance ID for the SQL Server features you have specified. SQL Server directory structure, registry structure, and service names will incorporate the instance ID of the SQL Server instance. INSTANCEID="MSSQLSERVER" ; Specify that SQL Server feature usage data can be collected and sent to Microsoft. Specify 1 or True to enable and 0 or False to disable this feature. SQMREPORTING="False" ; Specify if errors can be reported to Microsoft to improve future SQL Server releases. Specify 1 or True to enable and 0 or False to disable this feature. ERRORREPORTING="False" ; Specify the installation directory. INSTANCEDIR="C:\Program Files\Microsoft SQL Server" ; Agent account name AGTSVCACCOUNT="NT AUTHORITY\SYSTEM" ; Auto-start service after installation.  AGTSVCSTARTUPTYPE="Automatic" ; Startup type for Integration Services. ISSVCSTARTUPTYPE="Automatic" ; Account for Integration Services: Domain\User or system account. ISSVCACCOUNT="NT AUTHORITY\SYSTEM" ; CM brick TCP communication port COMMFABRICPORT="0" ; How matrix will use private networks COMMFABRICNETWORKLEVEL="0" ; How inter brick communication will be protected COMMFABRICENCRYPTION="0" ; TCP port used by the CM brick MATRIXCMBRICKCOMMPORT="0" ; Startup type for the SQL Server service. SQLSVCSTARTUPTYPE="Automatic" ; Level to enable FILESTREAM feature at (0, 1, 2 or 3). FILESTREAMLEVEL="0" ; Set to "1" to enable RANU for SQL Server Express. ENABLERANU="False" ; Specifies a Windows collation or an SQL collation to use for the Database Engine. SQLCOLLATION="SQL_Latin1_General_CP1_CS_AS" ; Account for SQL Server service: Domain\User or system account. SQLSVCACCOUNT="NT AUTHORITY\SYSTEM" ; Windows account(s) to provision as SQL Server system administrators. SQLSYSADMINACCOUNTS="SSOSQL1\Administrator" ; The default is Windows Authentication. Use "SQL" for Mixed Mode Authentication. SECURITYMODE="SQL" ; Provision current user as a Database Engine system administrator for SQL Server 2012 Express. ADDCURRENTUSERASSQLADMIN="False" ; Specify 0 to disable or 1 to enable the TCP/IP protocol. TCPENABLED="1" ; Specify 0 to disable or 1 to enable the Named Pipes protocol. NPENABLED="0" ; Startup type for Browser Service. BROWSERSVCSTARTUPTYPE="Disabled"