内容

オープン・クローズ時のalertログの挙動確認。
PDBのオープン・クローズでは、大したログは出力されない。
インスタンスの起動、停止は今まで通りにログが大量に出力される。

※以下に表示しているalterログは、見やすくするため「Wed Aug dd hh:mi:ss yyyy」を削除している。

挙動確認

指定したPDBをオープン

▼コマンド (PDB1をオープン)

SQL> alter pluggable database pdb1 open;
プラガブル・データベースが変更されました。

SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           MOUNTED

▼alertログの出力
簡単なメッセージしか出てこない。
PDBの初期化パラメータ等をalertログで確認することはできないようだ。

alter pluggable database pdb1 open
Database Characterset for PDB1 is AL32UTF8
Opening pdb PDB1 (3) with no Resource Manager plan active
Pluggable database PDB1 opened read write
Completed: alter pluggable database pdb1 open

指定したPDBをクローズ

▼コマンド (PDB1をクローズ)

closeするとMOUNTEDになるようだ。

SQL> alter pluggable database pdb1 close immediate;
プラガブル・データベースが変更されました。

SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         4 PDB2                           MOUNTED

▼alertログの出力
OPEN時と同じく簡単なメッセージしか出てこない。

alter pluggable database pdb1 close immediate
ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local
Pluggable database PDB1 closed
Completed: alter pluggable database pdb1 close immediate

全PDBオープン

▼コマンド

SQL> alter pluggable database all open;
プラガブル・データベースが変更されました。

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO

▼alertログの出力

alter pluggable database all open
Database Characterset for PDB2 is AL32UTF8
Database Characterset for PDB1 is AL32UTF8
Opening pdb PDB2 (4) with no Resource Manager plan active
Opening pdb PDB1 (3) with no Resource Manager plan active
Pluggable database PDB2 opened read write
Pluggable database PDB1 opened read write
Completed: alter pluggable database all open

全PDBクローズ

▼コマンド

SQL> alter pluggable database all close immediate;
プラガブル・データベースが変更されました。

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         4 PDB2                           MOUNTED

▼alertログの出力

alter pluggable database all close immediate
ALTER SYSTEM: Flushing buffer cache inst=0 container=4 local
Pluggable database PDB2 closed
ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local
Pluggable database PDB1 closed
Completed: alter pluggable database all close immediate

インスタンスのOPEN

▼コマンド

$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on 水 8月 24 22:41:14 2016
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
アイドル・インスタンスに接続しました。

SQL> startup
ORACLEインスタンスが起動しました。

Total System Global Area 1560281088 bytes
Fixed Size                  2924784 bytes
Variable Size             939527952 bytes
Database Buffers          603979776 bytes
Redo Buffers               13848576 bytes
データベースがマウントされました。
データベースがオープンされました。
SQL>

▼alertログの出力
11gの頃より詳細な情報が出力されている。
パッチを当てていると「Dumping current patch information」とかに情報が出力されるのだろうか？
今度、確認しよう。

Starting ORACLE instance (normal) (OS id: 3236)
CLI notifier numLatches:3 maxDescs:519
**********************************************************************
Dump of system resources acquired for SHARED GLOBAL AREA (SGA)

 Per process system memlock (soft) limit = 128G
 Expected per process system memlock (soft) limit to lock
 SHARED GLOBAL AREA (SGA) into memory: 1488M
 Available system pagesizes:
  4K, 2048K
 Supported system pagesize(s):
  PAGESIZE  AVAILABLE_PAGES  EXPECTED_PAGES  ALLOCATED_PAGES  ERROR(s)
        4K       Configured          380931          380931        NONE
 Reason for not supporting certain system pagesizes:
  2048K - Dynamic allocate and free memory regions
**********************************************************************
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Initial number of CPU is 2
Number of processor cores in the system is 2
Number of processor sockets in the system is 1
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =51
LICENSE_MAX_USERS = 0
SYS auditing is enabled
NOTE: remote asm mode is local (mode 0x1; from cluster type)
NOTE: Using default ASM root directory ASM
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options.
ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1
System name:   	Linux
Node name:     	OracleLinux7
Release:       	3.10.0-327.el7.x86_64
Version:       	#1 SMP Fri Nov 20 00:18:34 PST 2015
Machine:       	x86_64
Using parameter settings in server-side spfile /u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfileTTT.ora
System parameters with non-default values:
  processes                = 300
  nls_language             = "JAPANESE"
  nls_territory            = "JAPAN"
  memory_target            = 1488M
  control_files            = "/u01/app/oracle/oradata/TTT/control01.ctl"
  control_files            = "/u01/app/oracle/fast_recovery_area/TTT/control02.ctl"
  db_block_size            = 8192
  compatible               = "12.1.0.2.0"
  log_archive_format       = "%t_%s_%r.dbf"
  db_recovery_file_dest    = "/u01/app/oracle/fast_recovery_area"
  db_recovery_file_dest_size= 4815M
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=TTTXDB)"
  audit_file_dest          = "/u01/app/oracle/admin/TTT/adump"
  audit_trail              = "DB"
  db_name                  = "TTT"
  open_cursors             = 300
  diagnostic_dest          = "/u01/app/oracle"
  enable_pluggable_database= TRUE
NOTE: remote asm mode is local (mode 0x1; from cluster type)
Starting background process PMON
PMON started with pid=2, OS id=3240
Starting background process PSP0
PSP0 started with pid=3, OS id=3242
Starting background process VKTM
VKTM started with pid=4, OS id=3244 at elevated (RT) priority
Starting background process GEN0
VKTM running at (1)millisec precision with DBRM quantum (100)ms
GEN0 started with pid=5, OS id=3248
Starting background process MMAN
MMAN started with pid=6, OS id=3250
Starting background process DIAG
DIAG started with pid=8, OS id=3254
Starting background process DBRM
DBRM started with pid=9, OS id=3256
Starting background process VKRM
VKRM started with pid=10, OS id=3258
Starting background process DIA0
DIA0 started with pid=11, OS id=3260
Starting background process DBW0
DBW0 started with pid=12, OS id=3262
Starting background process LGWR
LGWR started with pid=13, OS id=3264
Starting background process CKPT
CKPT started with pid=15, OS id=3268
Starting background process SMON
SMON started with pid=17, OS id=3272
Starting background process RECO
RECO started with pid=18, OS id=3274
Starting background process LREG
LREG started with pid=19, OS id=3276
Starting background process PXMN
PXMN started with pid=20, OS id=3278
Starting background process MMON
MMON started with pid=21, OS id=3280
Starting background process MMNL
MMNL started with pid=22, OS id=3282
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
ORACLE_BASE from environment = /u01/app/oracle
ALTER DATABASE   MOUNT
Using default pga_aggregate_limit of 2048 MB
Successful mount of redo thread 1, with mount id 1874341402
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
ALTER DATABASE OPEN
Ping without log force is disabled
.
Starting background process TMON
TMON started with pid=25, OS id=3298
LGWR: STARTING ARCH PROCESSES
Starting background process ARC0
ARC0 started with pid=26, OS id=3300
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Starting background process ARC1
ARC1 started with pid=27, OS id=3302
Starting background process ARC2
ARC2 started with pid=28, OS id=3304
Starting background process ARC3
ARC3 started with pid=29, OS id=3306
ARC1: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Archival started
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC2: Becoming the heartbeat ARCH
Thread 1 opened at log sequence 190
  Current log# 1 seq# 190 mem# 0: /u01/app/oracle/oradata/TTT/redo01.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
[3296] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:2645114 end:2645154 diff:40 ms (0.0 seconds)
Verifying minimum file header compatibility (11g) for tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Starting background process SMCO
SMCO started with pid=31, OS id=3310
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process AQPC
AQPC started with pid=34, OS id=3316
Database Characterset for PDB$SEED is AL32UTF8
Opening pdb PDB$SEED (2) with no Resource Manager plan active
Starting background process CJQ0
CJQ0 started with pid=35, OS id=3336
Completed: ALTER DATABASE OPEN
Shared IO Pool defaulting to 48MB. Trying to get it from Buffer Cache for process 3280.
===========================================================
Dumping current patch information
===========================================================
No patches have been applied
===========================================================
db_recovery_file_dest_size of 4815 MB is 25.68% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Setting Resource Manager plan SCHEDULER[0x4445]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager CDB plan DEFAULT_MAINTENANCE_PLAN via parameter

インスタンスのSHUTDOWN

PDBがCLOSE状態でインスタンスをSHUTDOWN

▼コマンド

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         4 PDB2                           MOUNTED
SQL> shutdown immediate
データベースがクローズされました。
データベースがディスマウントされました。
ORACLEインスタンスがシャットダウンされました。
SQL>

▼alertログの出力
PDBの情報はないように見える。

Shutting down instance (immediate)
Stopping background process SMCO
Shutting down instance: further logons disabled
Stopping background process CJQ0
Stopping background process MMNL
Stopping background process MMON
License high water mark = 9
Stopping Job queue slave processes, flags = 7
Job queue slave processes stopped
All dispatchers and shared servers shutdown
ALTER DATABASE CLOSE NORMAL
SMON: disabling tx recovery
Stopping Emon pool
Stopping Emon pool
SMON: disabling cache recovery
Shutting down archive processes
Archiving is disabled
ARCH shutting down
ARCH shutting down
ARC3: Archival stopped
ARC2: Archival stopped
ARCH shutting down
ARC0: Archival stopped
ARCH shutting down
ARC1: Archival stopped
Thread 1 closed at log sequence 190
Successful close of redo thread 1
Completed: ALTER DATABASE CLOSE NORMAL
ALTER DATABASE DISMOUNT
Shutting down archive processes
Archiving is disabled
Completed: ALTER DATABASE DISMOUNT
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Stopping background process VKTM
Instance shutdown complete

PDBがOPEN状態でインスタンスをSHUTDOWN

▼コマンド

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
SQL> shutdown immediate
データベースがクローズされました。
データベースがディスマウントされました。
ORACLEインスタンスがシャットダウンされました。
SQL>

▼alertログの出力
PDBの情報はないように見える。

Shutting down instance (immediate)
Stopping background process SMCO
Shutting down instance: further logons disabled
Stopping background process CJQ0
Stopping background process MMNL
Stopping background process MMON
License high water mark = 11
All dispatchers and shared servers shutdown
ALTER DATABASE CLOSE NORMAL
SMON: disabling tx recovery
Stopping Emon pool
Stopping Emon pool
SMON: disabling cache recovery
Shutting down archive processes
Archiving is disabled
ARCH shutting down
ARC0: Archival stopped
ARCH shutting down
ARC3: Archival stopped
ARCH shutting down
ARC2: Archival stopped
ARCH shutting down
ARC1: Archival stopped
Thread 1 closed at log sequence 190
Successful close of redo thread 1
Completed: ALTER DATABASE CLOSE NORMAL
ALTER DATABASE DISMOUNT
Shutting down archive processes
Archiving is disabled
Completed: ALTER DATABASE DISMOUNT
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Stopping background process VKTM
Instance shutdown complete