Click on the link below for a tutorial on Oracle Multitenant Architecture - Startup, Shutdown, and Availability of CDBs and PDBs. This link points to a YouTube video.
Friday, December 20, 2019
Wednesday, December 11, 2019
Oracle 18c Demo Videos
Demo on Oracle Multitenant Architecture - Change PDB Modes and Settings
Oracle DBA Interview Questions
Change PDB Modes and Settings
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>
Monday, December 9, 2019
SP2-1546
Error
SP2-1546: Invalid option(s) for Pluggable database.Cause
STARTUP PLUGGABLE DATABASE command was executed from a PDB.Resolution
1. Execute the command from the root container.or
2. Run STARTUP with no parameters to start the current PDB.
Example:
Startup from CDB$ROOT
SYS@XE>startup pluggable database pdb1 ;
SP2-1546: Invalid option(s) for Pluggable database.
SYS@XE>alter session set container=cdb$root
2 ;
Session altered.
Elapsed: 00:00:00.07
SYS@XE>startup pluggable database pdb1 ;
Pluggable Database opened.
Startup using the STARTUP command from PDB
SYS@XE>show con_name
CON_NAME
------------------------------
PDB1
SYS@XE>startup pluggable database pdb1;
SP2-1546: Invalid option(s) for Pluggable database.
SYS@XE>startup
Pluggable Database opened.
SYS@XE>
Thursday, December 5, 2019
CDB and PDB Startup and Shutdown
Click on the link below for a tutorial on Oracle Multitenant Architecture - Startup, Shutdown, and Availability of CDBs and PDBs. This link points to a YouTube video.
Demo - Startup and Shutdown Using SQL*Plus
SYS@XE>
SYS@XE>shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SYS@XE>startup nomount
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
SYS@XE>show con_name
CON_NAME
------------------------------
CDB$ROOT
SYS@XE>show pdbs
SYS@XE>startup mount
ORA-01081: cannot start already-running ORACLE - shut it down first
SYS@XE>startup force
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>
SYS@XE>
SYS@XE>show con_name
CON_NAME
------------------------------
CDB$ROOT
SYS@XE>
SYS@XE>
SYS@XE>show pdbs
CON_ID,CON_NAME ,OPEN MODE ,RESTRICTED
----------,------------------------------,----------,----------
2,PDB$SEED ,READ ONLY ,NO
3,PDB1 ,MOUNTED ,
SYS@XE>startup pluggable database pdb1 open
Pluggable Database opened.
SYS@XE>
SYS@XE>show pdbs
CON_ID,CON_NAME ,OPEN MODE ,RESTRICTED
----------,------------------------------,----------,----------
2,PDB$SEED ,READ ONLY ,NO
3,PDB1 ,READ WRITE,NO
SYS@XE>
Oracle Multitenant Architecture - Startup, Shutdown, and Availability of CDBs and PDBs
Demo - Startup and Shutdown Using SQL*Plus
Demo - Startup and Shutdown Using RMAN
Examples
SQL*Plus
SYS@XE>
SYS@XE>shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SYS@XE>startup nomount
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
SYS@XE>show con_name
CON_NAME
------------------------------
CDB$ROOT
SYS@XE>show pdbs
SYS@XE>startup mount
ORA-01081: cannot start already-running ORACLE - shut it down first
SYS@XE>startup force
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>
SYS@XE>
SYS@XE>show con_name
CON_NAME
------------------------------
CDB$ROOT
SYS@XE>
SYS@XE>
SYS@XE>show pdbs
CON_ID,CON_NAME ,OPEN MODE ,RESTRICTED
----------,------------------------------,----------,----------
2,PDB$SEED ,READ ONLY ,NO
3,PDB1 ,MOUNTED ,
SYS@XE>startup pluggable database pdb1 open
Pluggable Database opened.
SYS@XE>
SYS@XE>show pdbs
CON_ID,CON_NAME ,OPEN MODE ,RESTRICTED
----------,------------------------------,----------,----------
2,PDB$SEED ,READ ONLY ,NO
3,PDB1 ,READ WRITE,NO
SYS@XE>
RMAN
RMAN> shutdown immediate
database closed
database dismounted
Oracle instance shut down
RMAN> startup nomount
connected to target database (not started)
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
RMAN> show con_name
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "identifier": expecting one of: "all, archivelog, auxiliary, auxname, backup, channel, compression, controlfile, datafile, db_unique_name, default, device, encryption, exclude, incremental, maxsetsize, retention, snapshot, "
RMAN-01008: the bad identifier was: con_name
RMAN-01007: at line 1 column 6 file: standard input
RMAN> startup mount
database is already started
database mounted
RMAN> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
xe MOUNTED
RMAN> select substr(name, 1,10) name, open_mode from v$pdbs;
NAME OPEN_MODE
---------------------------------------- ----------
PDB$SEED MOUNTED
PDB1 MOUNTED
RMAN> startup open
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "open": expecting one of: "dba, force, mount, newline, nomount, pfile, ;"
RMAN-01007: at line 1 column 9 file: standard input
RMAN> startup
database is already started
database opened
RMAN> select substr(name,1,10) name, open_mode from v$pdbs;
NAME OPEN_MODE
---------------------------------------- ----------
PDB$SEED READ ONLY
PDB1 MOUNTED
RMAN> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
xe OPEN
RMAN> startup pluggable database pdb1 open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "pluggable": expecting one of: "dba, force, mount, newline, nomount, pfile, ;"
RMAN-01007: at line 1 column 9 file: standard input
RMAN> alter pluggable database pdb1 open;
Statement processed
RMAN>
Tuesday, December 3, 2019
Multitenant - Automatic Workload Repository
Click on the link below for a tutorial on Oracle Database 18c Multitenant Architecture - Automatic Workload Repository (AWR). This link points to a YouTube video.
Demo AWR
Oracle Database 18c Multitenant Architecture - Automatic Workload Repository
Demo AWR
Example
CREATE_SNAPSHOT Function and Procedure
EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT('ALL');
show con_id
select con_id,
dbid,
snap_id,
instance_number,
begin_interval_time,
end_interval_time
from dba_hist_snapshot where con_id=3;
EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_snap_id => 1, end_snap_id => 3, baseline_name =>'pdb1_peakload_1');
select con_id, dbid, baseline_id, baseline_name, start_snap_id, end_snap_id from DBA_HIST_BASELINE where con_id=3;
@?/rdbms/admin/ashrpt.sql
Similarly, for AWR report, run the awrrpt.sql script.
@?/rdbms/admin/awrrpt.sql
Manage PDB Service Names
Click on the link below for a tutorial on Oracle Multitenant Architecture - Managing PDB Service Names. This link points to a YouTube video.
Watch a YouTube video on Oracle 18c Multitenant Architecture - Manage PDB Service Name
Examples
select name from all_services;
alter session set container=pdb1;
select name from v$active_services;
exec DBMS_SERVICE.CREATE_SERVICE('sales_svc','sales_svc');
exec DBMS_SERVICE.DELETE_SERVICE('sales_svc');
exec DBMS_SERVICE.CREATE_SERVICE('sales_svc','sales_svc');
select name from all_services;
select name from v$active_services;
exec DBMS_SERVICE.START_SERVICE('sales_svc');
select name from v$active_services;
Create a CDB and PDB
Click on the link below for a tutorial on Oracle Multitenant Architecture - Creating a CDB and PDB. This link points to a YouTube video.
CREATE PLUGGABLE DATABASE pdb1
ADMIN USER pdb1adm IDENTIFIED BY password
ROLES=(PDB_DBA)
STORAGE (MAXSIZE 2G)
DEFAULT TABLESPACE tbs_pdb1
DATAFILE 'C:\app\archana\product\18.0.0\oradata\XE\pdb1\datafile\pdb01.dbf' SIZE 250M AUTOEXTEND ON
PATH_PREFIX = 'C:\app\archana\product\18.0.0\oradata\XE\pdb1\'
CREATE_FILE_DEST='C:\app\archana\product\18.0.0\oradata\XE\pdb1\';
--drop pluggable database pdb1 including datafiles;
--alter pluggable database pdb1 close immediate;
/* Viewing information about each PDB */
COLUMN PDB_NAME FORMAT A15
SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID;
/* Viewing the Name and Open Mode of Each PDB */
COLUMN NAME FORMAT A15
COLUMN RESTRICTED FORMAT A10
COLUMN OPEN_TIME FORMAT A30
SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM V$PDBS;
/* Showing the Users in Multiple PDBs */
COLUMN PDB_NAME FORMAT A15
COLUMN USERNAME FORMAT A30
SELECT p.PDB_ID, p.PDB_NAME, u.USERNAME
FROM DBA_PDBS p, CDB_USERS u
WHERE p.PDB_ID > 2 AND
p.PDB_ID = u.CON_ID
ORDER BY p.PDB_ID;
/* Showing the Data Files for Each PDB in a CDB */
COLUMN PDB_ID FORMAT 999
COLUMN PDB_NAME FORMAT A8
COLUMN FILE_ID FORMAT 9999
COLUMN TABLESPACE_NAME FORMAT A10
COLUMN FILE_NAME FORMAT A45
SELECT p.PDB_ID, p.PDB_NAME, d.FILE_ID, d.TABLESPACE_NAME, d.FILE_NAME
FROM DBA_PDBS p, CDB_DATA_FILES d
WHERE p.CON_ID = d.CON_ID
ORDER BY p.PDB_ID;
/* Showing the Temp Files in a CDB */
COLUMN CON_ID FORMAT 999
COLUMN FILE_ID FORMAT 9999
COLUMN TABLESPACE_NAME FORMAT A15
COLUMN FILE_NAME FORMAT A45
SELECT CON_ID, FILE_ID, TABLESPACE_NAME, FILE_NAME
FROM CDB_TEMP_FILES
ORDER BY CON_ID;
Watch a YouTube video on Oracle 18c Multitenant Architecture - Create a CDB and PDB
Examples
CREATE PLUGGABLE DATABASE pdb1
ADMIN USER pdb1adm IDENTIFIED BY password
ROLES=(PDB_DBA)
STORAGE (MAXSIZE 2G)
DEFAULT TABLESPACE tbs_pdb1
DATAFILE 'C:\app\archana\product\18.0.0\oradata\XE\pdb1\datafile\pdb01.dbf' SIZE 250M AUTOEXTEND ON
PATH_PREFIX = 'C:\app\archana\product\18.0.0\oradata\XE\pdb1\'
CREATE_FILE_DEST='C:\app\archana\product\18.0.0\oradata\XE\pdb1\';
--drop pluggable database pdb1 including datafiles;
--alter pluggable database pdb1 close immediate;
/* Viewing information about each PDB */
COLUMN PDB_NAME FORMAT A15
SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID;
/* Viewing the Name and Open Mode of Each PDB */
COLUMN NAME FORMAT A15
COLUMN RESTRICTED FORMAT A10
COLUMN OPEN_TIME FORMAT A30
SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM V$PDBS;
/* Showing the Users in Multiple PDBs */
COLUMN PDB_NAME FORMAT A15
COLUMN USERNAME FORMAT A30
SELECT p.PDB_ID, p.PDB_NAME, u.USERNAME
FROM DBA_PDBS p, CDB_USERS u
WHERE p.PDB_ID > 2 AND
p.PDB_ID = u.CON_ID
ORDER BY p.PDB_ID;
/* Showing the Data Files for Each PDB in a CDB */
COLUMN PDB_ID FORMAT 999
COLUMN PDB_NAME FORMAT A8
COLUMN FILE_ID FORMAT 9999
COLUMN TABLESPACE_NAME FORMAT A10
COLUMN FILE_NAME FORMAT A45
SELECT p.PDB_ID, p.PDB_NAME, d.FILE_ID, d.TABLESPACE_NAME, d.FILE_NAME
FROM DBA_PDBS p, CDB_DATA_FILES d
WHERE p.CON_ID = d.CON_ID
ORDER BY p.PDB_ID;
/* Showing the Temp Files in a CDB */
COLUMN CON_ID FORMAT 999
COLUMN FILE_ID FORMAT 9999
COLUMN TABLESPACE_NAME FORMAT A15
COLUMN FILE_NAME FORMAT A45
SELECT CON_ID, FILE_ID, TABLESPACE_NAME, FILE_NAME
FROM CDB_TEMP_FILES
ORDER BY CON_ID;
Monday, December 2, 2019
ORA-20200
Error
ERROR at line 1:ORA-20200: Database/Instance 2136624849/1 does not exist in
AWR_PDB_DATABASE_INSTANCE
ORA-06512: at line 27
Cause
An awrrpt.sql was run and no snapshots exist in the AWR for the database instance specified.
Resolution
Create a snapshot using EM Cloud Control or DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT and re-run the awrrpt.sqlAlso ensure that the AWR parameters are set.
SYS@XE>show parameter awr
NAME ,TYPE ,VALUE
------------------------------,----------,--------------------------
awr_pdb_autoflush_enabled ,boolean ,TRUE
awr_pdb_max_parallel_slaves ,integer ,10
awr_snapshot_time_offset ,integer ,1000000
EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
------------------------------,----------,--------------------------
awr_pdb_autoflush_enabled ,boolean ,TRUE
awr_pdb_max_parallel_slaves ,integer ,10
awr_snapshot_time_offset ,integer ,1000000
EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
@?/rdbms/admin/awrrpt.sql
..
..
..
ORA-65025
Error
ORA-65025: Pluggable database PDB1 is not closed on all instances
Cause
An attempt was made to drop pluggable database when the database was still open.Resolution
Close the pluggable database and drop the databasealter pluggable database pdb1 close immediate;
drop pluggable database pdb1 including datafiles;
ORA-65040
Error
ORA-65040: operation not allowed from within a pluggable database
Cause
User attempted to create a database from a non-root container.
Resolution
Switch the current container to either CDB root or application root and re-run the command.
alter session set container=cdb$root;
create pluggable database
..
..
..
Subscribe to:
Posts (Atom)