Click on the link below for a tutorial on Oracle Multitenant Architecture - Change PDB Modes and Settings. This link points to a YouTube video.
SYS@XE>show pdbs
CON_ID,CON_NAME ,OPEN MODE ,RESTRICTED
----------,------------------------------,----------,----------
2,PDB$SEED ,READ ONLY ,NO
3,PDB1 ,MOUNTED ,
SYS@XE>create user c##dba identified by password ;
User created.
Elapsed: 00:00:00.24
SYS@XE>alter session set container=pdb1;
Session altered.
Elapsed: 00:00:00.00
SYS@XE>startup
Pluggable Database opened.
SYS@XE>select username from dba_users where username like 'C##DBA';
USERNAME
-------------------------
C##DBA
Elapsed: 00:00:00.08
SYS@XE>shutdown immediate;
Pluggable Database closed.
SYS@XE>alter pluggable database pdb1 open restricted instances=('XE');
Pluggable database altered.
Elapsed: 00:00:01.40
SYS@XE>show pdbs
CON_ID,CON_NAME ,OPEN MODE ,RESTRICTED
----------,------------------------------,----------,----------
3,PDB1 ,READ WRITE,YES
SYS@XE>alter pluggable database pdb1 close immediate ;
Pluggable database altered.
Elapsed: 00:00:00.43
SYS@XE>show pdbs
CON_ID,CON_NAME ,OPEN MODE ,RESTRICTED
----------,------------------------------,----------,----------
3,PDB1 ,MOUNTED ,
SYS@XE>alter pluggable database pdb1 open upgrade ;
Pluggable database altered.
Elapsed: 00:00:01.04
SYS@XE>show pdbs
CON_ID,CON_NAME ,OPEN MODE ,RESTRICTED
----------,------------------------------,----------,----------
3,PDB1 ,MIGRATE ,YES
SYS@XE>alter pluggable database pdb1 save state;
Pluggable database altered.
Elapsed: 00:00:00.03
SYS@XE>alter session set container=cdb$root;
Session altered.
Elapsed: 00:00:00.03
SYS@XE>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@XE>startup
ORACLE instance started.
Total System Global Area,1073738624,bytes
Fixed Size , 9036672,bytes
Variable Size , 536870912,bytes
Database Buffers , 524288000,bytes
Redo Buffers , 3543040,bytes
Database mounted.
Database opened.
SYS@XE>show pdbs
CON_ID,CON_NAME ,OPEN MODE ,RESTRICTED
----------,------------------------------,----------,----------
2,PDB$SEED ,READ ONLY ,NO
3,PDB1 ,MIGRATE ,YES
SYS@XE>
SYS@XE>alter pluggable database pdb1 discard state;
Pluggable database altered.
Elapsed: 00:00:00.26
SYS@XE>alter session set container=pdb1;
Session altered.
Elapsed: 00:00:00.05
SYS@XE>select tablespace_name, contents from dba_tablespaces;
TABLESPACE_NAME ,CONTENTS
--------------------,---------------------
SYSTEM ,PERMANENT
SYSAUX ,PERMANENT
UNDOTBS1 ,UNDO
TEMP ,TEMPORARY
USERS ,PERMANENT
BIGFILETBS ,PERMANENT
6 rows selected.
Elapsed: 00:00:00.13
SYS@XE>create temporary tablespace pdb1_temp;
Tablespace created.
Elapsed: 00:00:00.10
SYS@XE>ALTER PLUGGABLE DATABASE PDB1 DEFAULT TEMPORARY TABLESPACE pdb1_temp;
Pluggable database altered.
Elapsed: 00:00:00.05
SYS@XE>ALTER PLUGGABLE DATABASE PDB1 SET DEFAULT BIGFILE TABLESPACE;
Pluggable database altered.
Elapsed: 00:00:00.05
SYS@XE>ALTER PLUGGABLE DATABASE PDB1 STORAGE(MAXSIZE UNLIMITED);
Pluggable database altered.
Elapsed: 00:00:00.04
SYS@XE>ALTER PLUGGABLE DATABASE PDB1 STORAGE UNLIMITED;
Pluggable database altered.
Elapsed: 00:00:00.03
SYS@XE>ALTER PLUGGABLE DATABASE PDB1 ENABLE FORCE LOGGING;
ALTER PLUGGABLE DATABASE PDB1 ENABLE FORCE LOGGING
*
ERROR at line 1:
ORA-65154: specified logging attribute for the pluggable database is same as the current attribute value
Elapsed: 00:00:00.04
SYS@XE>
SYS@XE>ALTER PLUGGABLE DATABASE PDB1 DISABLE FORCE LOGGING;
Pluggable database altered.
Elapsed: 00:00:00.03
SYS@XE>ALTER PLUGGABLE DATABASE PDB1 ENABLE FORCE LOGGING;
Pluggable database altered.
Elapsed: 00:00:00.03
SYS@XE>ALTER PLUGGABLE DATABASE PDB1 RENAME GLOBAL_NAME TO trainingdb.domain.com;
Pluggable database altered.
Elapsed: 00:00:02.26
SYS@XE>SELECT NAME FROM V$SYSTEM_PARAMETER WHERE ISPDB_MODIFIABLE='TRUE' ORDER BY NAME;
NAME
------------------------------------------------------------
O7_DICTIONARY_ACCESSIBILITY
_column_tracking_level
_cursor_bind_capture_area_size
_dbop_enabled
_dml_monitoring_enabled
_olap_row_load_time_precision
_rowsource_execution_statistics
_rowsource_profiling_statistics
_sqlmon_threshold
_timemodel_collection
adg_account_info_tracking
approx_for_aggregation
approx_for_count_distinct
approx_for_percentile
aq_tm_processes
asm_diskstring
awr_pdb_autoflush_enabled
bitmap_merge_area_size
blank_trimming
cell_offload_compaction
cell_offload_decryption
cell_offload_parameters
cell_offload_plan_display
cell_offload_processing
cell_offloadgroup_name
client_result_cache_lag
client_result_cache_size
commit_logging
commit_point_strength
commit_wait
commit_write
common_user_prefix
containers_parallel_degree
cpu_count
create_bitmap_area_size
create_stored_outlines
cursor_bind_capture_destination
cursor_invalidation
cursor_sharing
db_block_checking
db_cache_size
db_create_file_dest
db_create_online_log_dest_1
db_create_online_log_dest_2
db_create_online_log_dest_3
db_create_online_log_dest_4
db_create_online_log_dest_5
db_domain
db_file_multiblock_read_count
db_files
db_index_compression_inheritance
db_performance_profile
db_securefile
db_unrecoverable_scn_tracking
ddl_lock_timeout
default_sharing
deferred_segment_creation
dst_upgrade_insert_conv
enable_automatic_maintenance_pdb
enable_ddl_logging
encrypt_new_tablespaces
fixed_date
forward_listener
global_names
heat_map
inmemory_automatic_level
inmemory_clause_default
inmemory_expressions_usage
inmemory_force
inmemory_optimized_arithmetic
inmemory_prefer_xmem_memcompress
inmemory_prefer_xmem_priority
inmemory_query
inmemory_size
inmemory_virtual_columns
inmemory_xmem_size
java_jit_enabled
job_queue_processes
ldap_directory_access
ldap_directory_sysauth
listener_networks
local_listener
log_archive_min_succeed_dest
long_module_action
max_datapump_jobs_per_pdb
max_dump_file_size
max_idle_time
max_iops
max_mbps
max_pdbs
max_string_size
multishard_query_data_consistency
multishard_query_partial_results
nls_calendar
nls_comp
nls_currency
nls_date_format
nls_date_language
nls_dual_currency
nls_iso_currency
nls_language
nls_length_semantics
nls_nchar_conv_excp
nls_numeric_characters
nls_sort
nls_territory
nls_time_format
nls_time_tz_format
nls_timestamp_format
nls_timestamp_tz_format
object_cache_max_size_percent
object_cache_optimal_size
olap_page_pool_size
open_cursors
open_links
optimizer_adaptive_plans
optimizer_adaptive_reporting_only
optimizer_adaptive_statistics
optimizer_capture_sql_plan_baselines
optimizer_dynamic_sampling
optimizer_features_enable
optimizer_ignore_hints
optimizer_ignore_parallel_hints
optimizer_index_caching
optimizer_index_cost_adj
optimizer_inmemory_aware
optimizer_mode
optimizer_secure_view_merging
optimizer_use_invisible_indexes
optimizer_use_pending_statistics
optimizer_use_sql_plan_baselines
parallel_degree_limit
parallel_degree_policy
parallel_force_local
parallel_instance_group
parallel_max_servers
parallel_min_degree
parallel_min_time_threshold
parallel_servers_target
pdb_file_name_convert
pdb_lockdown
pdb_os_credential
pdb_template
pga_aggregate_limit
pga_aggregate_target
plscope_settings
plsql_ccflags
plsql_code_type
plsql_debug
plsql_optimize_level
plsql_v2_compatibility
plsql_warnings
private_temp_table_prefix
query_rewrite_enabled
query_rewrite_integrity
recyclebin
remote_dependencies_mode
remote_listener
remote_recovery_file_dest
resource_limit
resource_manager_plan
result_cache_max_result
result_cache_max_size
result_cache_mode
result_cache_remote_expiration
resumable_timeout
rollback_segments
session_cached_cursors
sessions
sga_min_size
sga_target
shadow_core_dump
shared_pool_size
shared_servers
shrd_dupl_table_refresh_rate
skip_unusable_indexes
smtp_out_server
sort_area_retained_size
sort_area_size
spatial_vector_acceleration
sql92_security
sql_trace
sqltune_category
star_transformation_enabled
statistics_level
tde_configuration
temp_undo_enabled
timed_os_statistics
timed_statistics
undo_management
undo_retention
undo_tablespace
unified_audit_systemlog
workarea_size_policy
xml_db_events
195 rows selected.
Elapsed: 00:00:00.28
SYS@XE>
SYS@XE>alter system set sga_target=1G scope=memory;
System altered.
Elapsed: 00:00:00.04
SYS@XE>
SYS@XE>show pdbs
CON_ID,CON_NAME ,OPEN MODE ,RESTRICTED
----------,------------------------------,----------,----------
2,PDB$SEED ,READ ONLY ,NO
3,PDB1 ,MOUNTED ,
SYS@XE>create user c##dba identified by password ;
User created.
Elapsed: 00:00:00.24
SYS@XE>alter session set container=pdb1;
Session altered.
Elapsed: 00:00:00.00
SYS@XE>startup
Pluggable Database opened.
SYS@XE>select username from dba_users where username like 'C##DBA';
USERNAME
-------------------------
C##DBA
Elapsed: 00:00:00.08
SYS@XE>shutdown immediate;
Pluggable Database closed.
SYS@XE>alter pluggable database pdb1 open restricted instances=('XE');
Pluggable database altered.
Elapsed: 00:00:01.40
SYS@XE>show pdbs
CON_ID,CON_NAME ,OPEN MODE ,RESTRICTED
----------,------------------------------,----------,----------
3,PDB1 ,READ WRITE,YES
SYS@XE>alter pluggable database pdb1 close immediate ;
Pluggable database altered.
Elapsed: 00:00:00.43
SYS@XE>show pdbs
CON_ID,CON_NAME ,OPEN MODE ,RESTRICTED
----------,------------------------------,----------,----------
3,PDB1 ,MOUNTED ,
SYS@XE>alter pluggable database pdb1 open upgrade ;
Pluggable database altered.
Elapsed: 00:00:01.04
SYS@XE>show pdbs
CON_ID,CON_NAME ,OPEN MODE ,RESTRICTED
----------,------------------------------,----------,----------
3,PDB1 ,MIGRATE ,YES
SYS@XE>alter pluggable database pdb1 save state;
Pluggable database altered.
Elapsed: 00:00:00.03
SYS@XE>alter session set container=cdb$root;
Session altered.
Elapsed: 00:00:00.03
SYS@XE>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@XE>startup
ORACLE instance started.
Total System Global Area,1073738624,bytes
Fixed Size , 9036672,bytes
Variable Size , 536870912,bytes
Database Buffers , 524288000,bytes
Redo Buffers , 3543040,bytes
Database mounted.
Database opened.
SYS@XE>show pdbs
CON_ID,CON_NAME ,OPEN MODE ,RESTRICTED
----------,------------------------------,----------,----------
2,PDB$SEED ,READ ONLY ,NO
3,PDB1 ,MIGRATE ,YES
SYS@XE>
SYS@XE>alter pluggable database pdb1 discard state;
Pluggable database altered.
Elapsed: 00:00:00.26
SYS@XE>alter session set container=pdb1;
Session altered.
Elapsed: 00:00:00.05
SYS@XE>select tablespace_name, contents from dba_tablespaces;
TABLESPACE_NAME ,CONTENTS
--------------------,---------------------
SYSTEM ,PERMANENT
SYSAUX ,PERMANENT
UNDOTBS1 ,UNDO
TEMP ,TEMPORARY
USERS ,PERMANENT
BIGFILETBS ,PERMANENT
6 rows selected.
Elapsed: 00:00:00.13
SYS@XE>create temporary tablespace pdb1_temp;
Tablespace created.
Elapsed: 00:00:00.10
SYS@XE>ALTER PLUGGABLE DATABASE PDB1 DEFAULT TEMPORARY TABLESPACE pdb1_temp;
Pluggable database altered.
Elapsed: 00:00:00.05
SYS@XE>ALTER PLUGGABLE DATABASE PDB1 SET DEFAULT BIGFILE TABLESPACE;
Pluggable database altered.
Elapsed: 00:00:00.05
SYS@XE>ALTER PLUGGABLE DATABASE PDB1 STORAGE(MAXSIZE UNLIMITED);
Pluggable database altered.
Elapsed: 00:00:00.04
SYS@XE>ALTER PLUGGABLE DATABASE PDB1 STORAGE UNLIMITED;
Pluggable database altered.
Elapsed: 00:00:00.03
SYS@XE>ALTER PLUGGABLE DATABASE PDB1 ENABLE FORCE LOGGING;
ALTER PLUGGABLE DATABASE PDB1 ENABLE FORCE LOGGING
*
ERROR at line 1:
ORA-65154: specified logging attribute for the pluggable database is same as the current attribute value
Elapsed: 00:00:00.04
SYS@XE>
SYS@XE>ALTER PLUGGABLE DATABASE PDB1 DISABLE FORCE LOGGING;
Pluggable database altered.
Elapsed: 00:00:00.03
SYS@XE>ALTER PLUGGABLE DATABASE PDB1 ENABLE FORCE LOGGING;
Pluggable database altered.
Elapsed: 00:00:00.03
SYS@XE>ALTER PLUGGABLE DATABASE PDB1 RENAME GLOBAL_NAME TO trainingdb.domain.com;
Pluggable database altered.
Elapsed: 00:00:02.26
SYS@XE>SELECT NAME FROM V$SYSTEM_PARAMETER WHERE ISPDB_MODIFIABLE='TRUE' ORDER BY NAME;
NAME
------------------------------------------------------------
O7_DICTIONARY_ACCESSIBILITY
_column_tracking_level
_cursor_bind_capture_area_size
_dbop_enabled
_dml_monitoring_enabled
_olap_row_load_time_precision
_rowsource_execution_statistics
_rowsource_profiling_statistics
_sqlmon_threshold
_timemodel_collection
adg_account_info_tracking
approx_for_aggregation
approx_for_count_distinct
approx_for_percentile
aq_tm_processes
asm_diskstring
awr_pdb_autoflush_enabled
bitmap_merge_area_size
blank_trimming
cell_offload_compaction
cell_offload_decryption
cell_offload_parameters
cell_offload_plan_display
cell_offload_processing
cell_offloadgroup_name
client_result_cache_lag
client_result_cache_size
commit_logging
commit_point_strength
commit_wait
commit_write
common_user_prefix
containers_parallel_degree
cpu_count
create_bitmap_area_size
create_stored_outlines
cursor_bind_capture_destination
cursor_invalidation
cursor_sharing
db_block_checking
db_cache_size
db_create_file_dest
db_create_online_log_dest_1
db_create_online_log_dest_2
db_create_online_log_dest_3
db_create_online_log_dest_4
db_create_online_log_dest_5
db_domain
db_file_multiblock_read_count
db_files
db_index_compression_inheritance
db_performance_profile
db_securefile
db_unrecoverable_scn_tracking
ddl_lock_timeout
default_sharing
deferred_segment_creation
dst_upgrade_insert_conv
enable_automatic_maintenance_pdb
enable_ddl_logging
encrypt_new_tablespaces
fixed_date
forward_listener
global_names
heat_map
inmemory_automatic_level
inmemory_clause_default
inmemory_expressions_usage
inmemory_force
inmemory_optimized_arithmetic
inmemory_prefer_xmem_memcompress
inmemory_prefer_xmem_priority
inmemory_query
inmemory_size
inmemory_virtual_columns
inmemory_xmem_size
java_jit_enabled
job_queue_processes
ldap_directory_access
ldap_directory_sysauth
listener_networks
local_listener
log_archive_min_succeed_dest
long_module_action
max_datapump_jobs_per_pdb
max_dump_file_size
max_idle_time
max_iops
max_mbps
max_pdbs
max_string_size
multishard_query_data_consistency
multishard_query_partial_results
nls_calendar
nls_comp
nls_currency
nls_date_format
nls_date_language
nls_dual_currency
nls_iso_currency
nls_language
nls_length_semantics
nls_nchar_conv_excp
nls_numeric_characters
nls_sort
nls_territory
nls_time_format
nls_time_tz_format
nls_timestamp_format
nls_timestamp_tz_format
object_cache_max_size_percent
object_cache_optimal_size
olap_page_pool_size
open_cursors
open_links
optimizer_adaptive_plans
optimizer_adaptive_reporting_only
optimizer_adaptive_statistics
optimizer_capture_sql_plan_baselines
optimizer_dynamic_sampling
optimizer_features_enable
optimizer_ignore_hints
optimizer_ignore_parallel_hints
optimizer_index_caching
optimizer_index_cost_adj
optimizer_inmemory_aware
optimizer_mode
optimizer_secure_view_merging
optimizer_use_invisible_indexes
optimizer_use_pending_statistics
optimizer_use_sql_plan_baselines
parallel_degree_limit
parallel_degree_policy
parallel_force_local
parallel_instance_group
parallel_max_servers
parallel_min_degree
parallel_min_time_threshold
parallel_servers_target
pdb_file_name_convert
pdb_lockdown
pdb_os_credential
pdb_template
pga_aggregate_limit
pga_aggregate_target
plscope_settings
plsql_ccflags
plsql_code_type
plsql_debug
plsql_optimize_level
plsql_v2_compatibility
plsql_warnings
private_temp_table_prefix
query_rewrite_enabled
query_rewrite_integrity
recyclebin
remote_dependencies_mode
remote_listener
remote_recovery_file_dest
resource_limit
resource_manager_plan
result_cache_max_result
result_cache_max_size
result_cache_mode
result_cache_remote_expiration
resumable_timeout
rollback_segments
session_cached_cursors
sessions
sga_min_size
sga_target
shadow_core_dump
shared_pool_size
shared_servers
shrd_dupl_table_refresh_rate
skip_unusable_indexes
smtp_out_server
sort_area_retained_size
sort_area_size
spatial_vector_acceleration
sql92_security
sql_trace
sqltune_category
star_transformation_enabled
statistics_level
tde_configuration
temp_undo_enabled
timed_os_statistics
timed_statistics
undo_management
undo_retention
undo_tablespace
unified_audit_systemlog
workarea_size_policy
xml_db_events
195 rows selected.
Elapsed: 00:00:00.28
SYS@XE>
SYS@XE>alter system set sga_target=1G scope=memory;
System altered.
Elapsed: 00:00:00.04
SYS@XE>
No comments:
Post a Comment