Friday, November 14, 2014

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

 

image

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.

Thursday, November 13, 2014

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"

Monday, November 3, 2014

Possible Reasons for “TNS-01182: Listener rejected registration of service”

Symptom:

03-NOV-2014 20:26:02 * service_register_NSGR * 1182
TNS-01182: Listener rejected registration of service ""

Possible Reasons:

1. VALID_NODE_CHECKING_REGISTRATION_listenername=ON (By default All remote registrations will fail)

2. REGISTRATION_INVITED_NODES_listenername or REGISTRATION_EXCLUDED_NODES_listenername is in use

3. DYNAMIC_REGISTRATION_listenername is off

For example:

DYNAMIC_REGISTRATION_LISTENER=OFF

Tuesday, October 7, 2014

Optimized Shared Memory & MEMORY_MAX_TARGET in Solaris 11.2 + Oracle 12c

Starting with 12c, Oracle Database uses the Optimized Shared Memory (OSM) model of Oracle Solaris on Oracle Solaris 10 1/13 or later and Oracle Solaris 11 SRU 7.5 or later systems to implement Automatic Memory Management.

OSM allows dynamic resizing of System Global Area (SGA) without restarting the instance. It does not use the oradism utility and swap disk space. OSM is NUMA-optimized.

Total System Global Area 1258291200 bytes
Fixed Size                  3003176 bytes
Variable Size             905972952 bytes
Database Buffers          218103808 bytes
Redo Buffers               13770752 bytes
In-Memory Area            117440512 bytes

SQL> show parameter memory_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_target                        big integer 1200M
SQL> show parameter memory_max_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_max_target                    big integer 1200M


Dump of system resources acquired for SHARED GLOBAL AREA (SGA)
Available system pagesizes:
  4K, 2048K
Supported system pagesize(s):
  PAGESIZE  AVAILABLE_PAGES  EXPECTED_PAGES  ALLOCATED_PAGES  ERROR(s)
        4K       Configured               2               2        NONE
     2048K       Configured             601             601        NONE


oracle@solaris:~$ ipcs -dm
IPC status from <running system> as of Tuesday, October  7, 2014 08:39:35 PM SGT
T         ID      KEY        MODE        OWNER    GROUP      ALLOC
Shared Memory:
m         25   0x6a4bcd4  --rw-r-----   oracle oinstall           -
m         24   0x0        --rw-r-----   oracle oinstall   14680064
m         23   0x0        --rw-r-----   oracle oinstall   33554432
m         22   0x0        --rw-r-----   oracle oinstall 1123966976
m         21   0x0        --rw-r-----   oracle oinstall   83886080
m         20   0x0        --rw-r-----   oracle oinstall    4194304

If the column ALLOC shows an integer, it specifies that OSM is in use. If the column ALLOC shows a hyphen, it specifies that OSM is not in use.

oracle@solaris:~$ ipcs -im
IPC status from <running system> as of Tuesday, October  7, 2014 08:38:28 PM SGT
T         ID      KEY        MODE        OWNER    GROUP ISMATTCH
Shared Memory:
m         25   0x6a4bcd4  --rw-r-----   oracle oinstall       46
m         24   0x0        --rw-r-----   oracle oinstall       46
m         23   0x0        --rw-r-----   oracle oinstall       46
m         22   0x0        --rw-r-----   oracle oinstall       46
m         21   0x0        --rw-r-----   oracle oinstall       46
m         20   0x0        --rw-r-----   oracle oinstall       46

SQL> select (14680064+33554432+1123966976+83886080+4194304)/1024/1024 from dual;

(14680064+33554432+1123966976+83886080+4194304)/1024/1024
---------------------------------------------------------
                                               1201.89844

SQL> alter system set memory_max_target=1232M;
alter system set memory_max_target=1232M
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

                                          
SQL> alter system set memory_max_target=1232M scope=spfile;

System altered.

SQL> startup
ORACLE instance started.

Total System Global Area 1291845632 bytes
Fixed Size                  3003272 bytes
Variable Size             939527288 bytes
Database Buffers          218103808 bytes
Redo Buffers               13770752 bytes
In-Memory Area            117440512 bytes
Database mounted.

All SGA segments were allocated at startup
**********************************************************************
Dump of system resources acquired for SHARED GLOBAL AREA (SGA)
Available system pagesizes:
  4K, 2048K
Supported system pagesize(s):
  PAGESIZE  AVAILABLE_PAGES  EXPECTED_PAGES  ALLOCATED_PAGES  ERROR(s)
        4K       Configured               2               2        NONE
     2048K       Configured             617             617        NONE
**********************************************************************

oracle@solaris:~$ ipcs -dm
IPC status from <running system> as of Tuesday, October  7, 2014 08:53:02 PM SGT
T         ID      KEY        MODE        OWNER    GROUP      ALLOC
Shared Memory:
m  268435459   0x6a4bcd4  --rw-r-----   oracle oinstall           -
m  268435458   0x0        --rw-r-----   oracle oinstall   14680064
m  268435457   0x0        --rw-r-----   oracle oinstall   33554432
m  268435456   0x0        --rw-r-----   oracle oinstall 1157496832
m  251658303   0x0        --rw-r-----   oracle oinstall   83886080
m  251658302   0x0        --rw-r-----   oracle oinstall    4194304

SQL> select (14680064+33554432+1157496832+83886080+4194304)/1024/1024 from dual;

(14680064+33554432+1157496832+83886080+4194304)/1024/1024
---------------------------------------------------------
                                                 1233.875

                                                
SQL> alter system set memory_target=1232M;

System altered.

Monday, October 6, 2014

What are these values added in spfile after issued “create spfile from memory”

*.__data_transfer_cache_size=0
orclcdb.__data_transfer_cache_size=0
*.__db_cache_size=208M
orclcdb.__db_cache_size=285212672
*.__java_pool_size=16M
orclcdb.__java_pool_size=16777216
*.__large_pool_size=144M
orclcdb.__large_pool_size=33554432
*.__oracle_base='/u01/app/oracle'# ORACLE_BASE set from environment
*.__pga_aggregate_target=480M
orclcdb.__pga_aggregate_target=503316480
*.__sga_target=720M
orclcdb.__sga_target=754974720
*.__shared_io_pool_size=0
orclcdb.__shared_io_pool_size=16777216
*.__shared_pool_size=224M
orclcdb.__shared_pool_size=251658240
*.__streams_pool_size=0
orclcdb.__streams_pool_size=0
*._adaptive_window_consolidator_enabled=TRUE
*._aggregation_optimization_settings=0
*._always_anti_join='CHOOSE'
*._always_semi_join='CHOOSE'
*._and_pruning_enabled=TRUE
*._b_tree_bitmap_plans=TRUE
*._bloom_filter_enabled=TRUE
*._bloom_folding_enabled=TRUE
*._bloom_pruning_enabled=TRUE
*._bloom_serial_filter='ON'
*._complex_view_merging=TRUE
*._compression_compatibility='12.1.0.2.0'
*._connect_by_use_union_all='TRUE'
*._convert_set_to_join=FALSE
*._cost_equality_semi_join=TRUE
*._cpu_to_io=0
*._diag_adr_trace_dest='/u01/app/oracle/diag/rdbms/orclcdb/orclcdb/trace'
*._dimension_skip_null=TRUE
*._distinct_agg_optimization_gsets='CHOOSE'
*._eliminate_common_subexpr=TRUE
*._enable_type_dep_selectivity=TRUE
*._fast_full_scan_enabled=TRUE
*._first_k_rows_dynamic_proration=TRUE
*._gby_hash_aggregation_enabled=TRUE
*._gby_vector_aggregation_enabled=TRUE
*._generalized_pruning_enabled=TRUE
*._globalindex_pnum_filter_enabled=TRUE
*._gs_anti_semi_join_allowed=TRUE
*._improved_outerjoin_card=TRUE
*._improved_row_length_enabled=TRUE
*._index_join_enabled=TRUE
*._ksb_restart_policy_times='0','60','120','240'# internal update to set default
*._left_nested_loops_random=TRUE
*._local_communication_costing_enabled=TRUE
*._minimal_stats_aggregation=TRUE
*._mmv_query_rewrite_enabled=TRUE
*._new_initial_join_orders=TRUE
*._new_sort_cost_estimate=TRUE
*._nlj_batching_enabled=1
*._optim_adjust_for_part_skews=TRUE
*._optim_enhance_nnull_detection=TRUE
*._optim_new_default_join_sel=TRUE
*._optim_peek_user_binds=TRUE
*._optimizer_adaptive_cursor_sharing=TRUE
*._optimizer_adaptive_plans=TRUE
*._optimizer_aggr_groupby_elim=TRUE
*._optimizer_ansi_join_lateral_enhance=TRUE
*._optimizer_ansi_rearchitecture=TRUE
*._optimizer_batch_table_access_by_rowid=TRUE
*._optimizer_better_inlist_costing='ALL'
*._optimizer_cbqt_no_size_restriction=TRUE
*._optimizer_cluster_by_rowid=TRUE
*._optimizer_cluster_by_rowid_batched=TRUE
*._optimizer_cluster_by_rowid_control=129
*._optimizer_coalesce_subqueries=TRUE
*._optimizer_complex_pred_selectivity=TRUE
*._optimizer_compute_index_stats=TRUE
*._optimizer_connect_by_combine_sw=TRUE
*._optimizer_connect_by_cost_based=TRUE
*._optimizer_connect_by_elim_dups=TRUE
*._optimizer_correct_sq_selectivity=TRUE
*._optimizer_cost_based_transformation='LINEAR'
*._optimizer_cost_hjsmj_multimatch=TRUE
*._optimizer_cost_model='CHOOSE'
*._optimizer_cube_join_enabled=TRUE
*._optimizer_dim_subq_join_sel=TRUE
*._optimizer_distinct_agg_transform=TRUE
*._optimizer_distinct_elimination=TRUE
*._optimizer_distinct_placement=TRUE
*._optimizer_dsdir_usage_control=126
*._optimizer_eliminate_filtering_join=TRUE
*._optimizer_enable_density_improvements=TRUE
*._optimizer_enable_extended_stats=TRUE
*._optimizer_enable_table_lookup_by_nl=TRUE
*._optimizer_enhanced_filter_push=TRUE
*._optimizer_extend_jppd_view_types=TRUE
*._optimizer_extended_cursor_sharing='UDO'
*._optimizer_extended_cursor_sharing_rel='SIMPLE'
*._optimizer_extended_stats_usage_control=192
*._optimizer_false_filter_pred_pullup=TRUE
*._optimizer_fast_access_pred_analysis=TRUE
*._optimizer_fast_pred_transitivity=TRUE
*._optimizer_filter_pred_pullup=TRUE
*._optimizer_fkr_index_cost_bias=10
*._optimizer_full_outer_join_to_outer=TRUE
*._optimizer_gather_feedback=TRUE
*._optimizer_gather_stats_on_load=TRUE
*._optimizer_group_by_placement=TRUE
*._optimizer_hybrid_fpwj_enabled=TRUE
*._optimizer_improve_selectivity=TRUE
*._optimizer_inmemory_access_path=TRUE
*._optimizer_inmemory_autodop=TRUE
*._optimizer_inmemory_bloom_filter=TRUE
*._optimizer_inmemory_cluster_aware_dop=TRUE
*._optimizer_inmemory_gen_pushable_preds=TRUE
*._optimizer_inmemory_minmax_pruning=TRUE
*._optimizer_inmemory_table_expansion=TRUE
*._optimizer_interleave_jppd=TRUE
*._optimizer_join_elimination_enabled=TRUE
*._optimizer_join_factorization=TRUE
*._optimizer_join_order_control=3
*._optimizer_join_sel_sanity_check=TRUE
*._optimizer_max_permutations=2000
*._optimizer_mode_force=TRUE
*._optimizer_multi_level_push_pred=TRUE
*._optimizer_multi_table_outerjoin=TRUE
*._optimizer_native_full_outer_join='FORCE'
*._optimizer_new_join_card_computation=TRUE
*._optimizer_nlj_hj_adaptive_join=TRUE
*._optimizer_null_accepting_semijoin=TRUE
*._optimizer_null_aware_antijoin=TRUE
*._optimizer_or_expansion='DEPTH'
*._optimizer_order_by_elimination_enabled=TRUE
*._optimizer_outer_join_to_inner=TRUE
*._optimizer_outer_to_anti_enabled=TRUE
*._optimizer_partial_join_eval=TRUE
*._optimizer_proc_rate_level='BASIC'
*._optimizer_push_down_distinct=0
*._optimizer_push_pred_cost_based=TRUE
*._optimizer_reduce_groupby_key=TRUE
*._optimizer_rownum_bind_default=10
*._optimizer_rownum_pred_based_fkr=TRUE
*._optimizer_skip_scan_enabled=TRUE
*._optimizer_sortmerge_join_inequality=TRUE
*._optimizer_squ_bottomup=TRUE
*._optimizer_star_tran_in_with_clause=TRUE
*._optimizer_strans_adaptive_pruning=TRUE
*._optimizer_system_stats_usage=TRUE
*._optimizer_table_expansion=TRUE
*._optimizer_transitivity_retain=TRUE
*._optimizer_try_st_before_jppd=TRUE
*._optimizer_undo_cost_change='12.1.0.2'
*._optimizer_unnest_corr_set_subq=TRUE
*._optimizer_unnest_disjunctive_subq=TRUE
*._optimizer_unnest_scalar_sq=TRUE
*._optimizer_use_cbqt_star_transformation=TRUE
*._optimizer_use_feedback=TRUE
*._optimizer_use_gtt_session_stats=TRUE
*._optimizer_use_histograms=TRUE
*._optimizer_vector_transformation=TRUE
*._or_expand_nvl_predicate=TRUE
*._ordered_nested_loop=TRUE
*._parallel_broadcast_enabled=TRUE
*._partition_view_enabled=TRUE
*._pivot_implementation_method='CHOOSE'
*._pre_rewrite_push_pred=TRUE
*._pred_move_around=TRUE
*._push_join_predicate=TRUE
*._push_join_union_view=TRUE
*._push_join_union_view2=TRUE
*._px_adaptive_dist_method='CHOOSE'
*._px_concurrent=TRUE
*._px_cpu_autodop_enabled=TRUE
*._px_external_table_default_stats=TRUE
*._px_filter_parallelized=TRUE
*._px_filter_skew_handling=TRUE
*._px_groupby_pushdown='FORCE'
*._px_join_skew_handling=TRUE
*._px_minus_intersect=TRUE
*._px_object_sampling_enabled=TRUE
*._px_parallelize_expression=TRUE
*._px_partial_rollup_pushdown='ADAPTIVE'
*._px_partition_scan_enabled=TRUE
*._px_pwg_enabled=TRUE
*._px_replication_enabled=TRUE
*._px_scalable_invdist=TRUE
*._px_single_server_enabled=TRUE
*._px_ual_serial_input=TRUE
*._px_wif_dfo_declumping='CHOOSE'
*._px_wif_extend_distribution_keys=TRUE
*._query_rewrite_setopgrw_enable=TRUE
*._remove_aggr_subquery=TRUE
*._replace_virtual_columns=TRUE
*._right_outer_hash_enable=TRUE
*._selfjoin_mv_duplicates=TRUE
*._sql_model_unfold_forloops='RUN_TIME'
*._sqltune_category_parsed='DEFAULT'# parsed sqltune_category
*._subquery_pruning_enabled=TRUE
*._subquery_pruning_mv_enabled=FALSE
*._table_scan_cost_plus_one=TRUE
*._union_rewrite_for_gs='YES_GSET_MVS'
*._unnest_subquery=TRUE
*._use_column_stats_for_function=TRUE

*.audit_file_dest='/u01/app/oracle/admin/orclcdb/adump'
*.audit_trail='DB'
*.compatible='12.1.0.2.0'
*.connection_brokers='((TYPE=DEDICATED)(BROKERS=1))','((TYPE=EMON)(BROKERS=1))'# connection_brokers default value
*.control_files='/u01/app/oracle/oradata/ORCLCDB/controlfile/o1_mf_b2qcr7mn_.ctl','/u01/app/oracle/fast_recovery_area/ORCLCDB/controlfile/o1_mf_b2qcr8oj_.ctl'
*.core_dump_dest='/u01/app/oracle/diag/rdbms/orclcdb/orclcdb/cdump'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_domain=''
*.db_name='orclcdb'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4560M
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclcdbXDB)'
*.enable_pluggable_database=TRUE
*.inmemory_size=112M
*.local_listener='LISTENER_ORCLCDB'
*.log_buffer=12936K# log buffer update
*.memory_max_target=1200M
*.memory_target=1200M
*.open_cursors=300
*.optimizer_dynamic_sampling=2
*.optimizer_mode='ALL_ROWS'

*.plsql_warnings='DISABLE:ALL'# PL/SQL warnings at init.ora
*.processes=300
*.query_rewrite_enabled='TRUE'
*.remote_login_passwordfile='EXCLUSIVE'
*.result_cache_max_size=3M
*.skip_unusable_indexes=TRUE

*.undo_tablespace='UNDOTBS1'

Enable the In-Memory column store (IM column store) in database

SQL> select banner from v$version where banner like '%Database%';

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production


SQL> select * from v$sgainfo where name='In-Memory Area Size';

NAME                                  BYTES RES     CON_ID
-------------------------------- ---------- --- ----------
In-Memory Area Size                       0 No           0

SQL> show parameter inmemory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_clause_default              string
inmemory_force                       string      DEFAULT
inmemory_max_populate_servers        integer     0
inmemory_query                       string      ENABLE
inmemory_size                        big integer 0
inmemory_trickle_repopulate_servers_ integer     1
percent
optimizer_inmemory_aware             boolean     TRUE

SQL> conn / as sysdba
Connected.
SQL> alter system set inmemory_size=50M scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-64353: in-memory area size cannot be less than 100MB

oracle@solaris:/u01/app/oracle/product/12.1.0/dbhome_1/dbs$ grep inmemory initorclcdb.ora
*.inmemory_size=100M

SQL> create spfile from pfile;

File created.

SQL> startup
ORACLE instance started.

Total System Global Area 1258291200 bytes
Fixed Size                  3003176 bytes
Variable Size             905972952 bytes
Database Buffers          218103808 bytes
Redo Buffers               13770752 bytes
In-Memory Area            117440512 bytes
Database mounted.
Database opened.

SQL> show parameter inmemory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_clause_default              string
inmemory_force                       string      DEFAULT
inmemory_max_populate_servers        integer     1
inmemory_query                       string      ENABLE
inmemory_size                        big integer 112M
inmemory_trickle_repopulate_servers_ integer     1
percent
optimizer_inmemory_aware             boolean     TRUE

SQL> conn donghua/password@pdb1
Connected.
SQL> select * from v$sgainfo where name='In-Memory Area Size';

NAME                                  BYTES RES     CON_ID
-------------------------------- ---------- --- ----------
In-Memory Area Size               117440512 No           0


SQL> select * from v$inmemory_area;

POOL       ALLOC_BYTES USED_BYTES POPULATE_STATUS     CON_ID
---------- ----------- ---------- --------------- ----------
1MB POOL      82837504          0 DONE                     3
64KB POOL     16777216          0 DONE                     3

SQL> select sum(alloc_bytes)/1024/1024 from v$inmemory_area;

SUM(ALLOC_BYTES)/1024/1024
--------------------------
                        95

Use “pdb_save_or_discard_state” clause to instruct the database to save or discard the open mode of the PDB when the CDB restarts.

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           MOUNTED


SQL>  alter pluggable database pdb1 open;

Pluggable database altered.

SQL>  alter pluggable database pdb1 save state;

Pluggable database altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area 1061158912 bytes
Fixed Size                  3011736 bytes
Variable Size             708840296 bytes
Database Buffers          343932928 bytes
Redo Buffers                5373952 bytes
Database mounted.
Database opened.


SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE

SQL> col con_name for a10
SQL> col state for a20

SQL> select con_name,state from dba_pdb_saved_states;

CON_NAME   STATE
---------- --------------------
PDB1       OPEN

Description of pdb_save_or_discard_state.gif follows