oracle/状況確認 をテンプレートにして作成
[
トップ
] [
新規
|
一覧
|
検索
|
最終更新
|
ヘルプ
|
ログイン
]
開始行:
[[oracle]]
***記録すべき事項 [#m0741060]
dba権限保有ユーザで情報を取得。ここではsysを使用(sqlplus ...
***バージョン情報 [#x4ee8343]
#code(sql,number,nooutline,noblock,nomenu){{
SQL> SELECT * FROM V$VERSION;
BANNER
-----------------------------------------------------------
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> col PLATFORM_NAME format a30 ...
SQL> SELECT * FROM DBA_REGISTRY_DATABASE;
PLATFORM_ID PLATFORM_NAME EDITION
----------- ------------------------------ --------------...
12 Microsoft Windows x86 64-bit
SQL> SELECT DBMS_UTILITY.PORT_STRING FROM DUAL;
PORT_STRING
-----------------------------------------------
IBMPC/WIN_NT64-9.1.0
SQL>
}}
***インスタンス[SID]情報 [#xfb65477]
#code(sql,number,nooutline,noblock,nomenu){{
SQL> col OPEN_MODE format a10
SQL> SELECT DBID,NAME,OPEN_MODE,DATABASE_ROLE,PLATFORM_NA...
DBID NAME OPEN_MODE DATABASE_ROLE PLATFORM...
---------- --------- ---------- ---------------- --------...
2969170745 AERS READ WRITE PRIMARY Microsof...
SQL> col HOST_NAME format a10
SQL> set line 100
SQL> SELECT instance_name, host_name, status, archiver, d...
INSTANCE_NAME HOST_NAME STATUS ARCHIVE DATABASE_STATU...
---------------- ---------- ------ ------- --------------...
aers W2 OPEN STARTED ACTIVE ...
SQL>
SQL> col PROPERTY_NAME FORMAT a30
SQL> col PROPERTY_VALUE FORMAT a35
SQL> col DESCRIPTION FORMAT a50
SQL> set line 150
SQL> SELECT * FROM DATABASE_PROPERTIES;
PROPERTY_NAME PROPERTY_VALUE ...
------------------------------ --------------------------...
DICT.BASE 2 ...
DEFAULT_TEMP_TABLESPACE TEMP ...
DEFAULT_PERMANENT_TABLESPACE USERS ...
DEFAULT_EDITION ORA$BASE ...
Flashback Timestamp TimeZone GMT ...
TDE_MASTER_KEY_ID
DBTIMEZONE +09:00 ...
DST_UPGRADE_STATE NONE ...
DST_PRIMARY_TT_VERSION 14 ...
DST_SECONDARY_TT_VERSION 0 ...
DEFAULT_TBS_TYPE SMALLFILE ...
NLS_LANGUAGE JAPANESE ...
NLS_TERRITORY JAPAN ...
NLS_CURRENCY Y ...
NLS_ISO_CURRENCY JAPAN ...
NLS_NUMERIC_CHARACTERS ., ...
NLS_CHARACTERSET JA16SJISTILDE ...
NLS_CALENDAR GREGORIAN ...
NLS_DATE_FORMAT RR-MM-DD ...
NLS_DATE_LANGUAGE JAPANESE ...
NLS_SORT BINARY ...
NLS_TIME_FORMAT HH24:MI:SSXFF ...
NLS_TIMESTAMP_FORMAT RR-MM-DD HH24:MI:SSXFF ...
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR ...
NLS_TIMESTAMP_TZ_FORMAT RR-MM-DD HH24:MI:SSXFF TZR...
NLS_DUAL_CURRENCY \ ...
NLS_COMP BINARY ...
NLS_LENGTH_SEMANTICS BYTE ...
NLS_NCHAR_CONV_EXCP FALSE ...
NLS_NCHAR_CHARACTERSET AL16UTF16 ...
NLS_RDBMS_VERSION 11.2.0.3.0 ...
GLOBAL_DB_NAME AERS ...
EXPORT_VIEWS_VERSION 8 ...
WORKLOAD_CAPTURE_MODE ...
WORKLOAD_REPLAY_MODE ...
...
NO_USERID_VERIFIER_SALT 46C70D68AA28156AC84CEBDF92...
SQL>
}}
***バックアップ関係 [#j804c44f]
#code(sql,number,nooutline,noblock,nomenu){{
SQL> SELECT log_mode, flashback_on FROM v$database; ---...
LOG_MODE FLASHBACK_ON
------------ ------------------
ARCHIVELOG NO
SQL> ARCHIVE LOG LIST ---...
データベース・ログ・モード アーカイブ・モード
自動アーカイブ 有効
アーカイブ先 USE_DB_RECOVERY_FILE_DEST
最も古いオンライン・ログ順序 181
アーカイブする次のログ順序 183
現行のログ順序 183
SQL>
SQL> show parameter db_recovery_file_dest ---...
NAME TYPE VALUE
------------------------------------ ----------- --------...
db_recovery_file_dest string d:\oracl...
db_recovery_file_dest_size big integer 7G
SQL>
SQL> col FILENAME format a10
SQL> col STATUS format a10
SQL> SELECT FILENAME, STATUS, BYTES FROM V$BLOCK_CHANGE_T...
FILENAME STATUS BYTES
---------- ---------- ----------
DISABLED
SQL>
}}
RMANの設定状況
#code(sql,number,nooutline,noblock,nomenu){{
$ rman TARGET sys@AERS
ターゲット・データベースのパスワード: ...
ターゲット・データベース: AERS (データベースID=2969170745...
RMAN>
RMAN> show all; ...
リカバリ・カタログのかわりにターゲット・データベース制御...
db_unique_name AERSのデータベースにおけるRMAN構成パラメー...
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE D...
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO B...
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO ...
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK T...
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'E:\bk\%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DE...
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES...
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\ORACLE\PRODUCT...
RMAN>
}}
&color(red){*};バックアップ実行のスケジュールはどこを参照...
バックアップ実行のログは V$RMAN_BACKUP_JOB_DETAILS
***インストール済みパッケージ一覧 [#wec716f5]
#code(sql,number,nooutline,noblock,nomenu){{
SQL> col COMP_NAME format a35
SQL> col VERSION format a15
SQL> col STATUS format a6
SQL> SELECT COMP_NAME,VERSION,STATUS FROM dba_registry;
COMP_NAME VERSION STATUS
----------------------------------- --------------- ------
Oracle Application Express 3.2.1.00.12 VALID
Oracle Enterprise Manager 11.2.0.3.0 VALID
Oracle Multimedia 11.2.0.3.0 VALID
Oracle XML Database 11.2.0.3.0 VALID
Oracle Text 11.2.0.3.0 VALID
Oracle Expression Filter 11.2.0.3.0 VALID
Oracle Rules Manager 11.2.0.3.0 VALID
Oracle Workspace Manager 11.2.0.3.0 VALID
Oracle Database Catalog Views 11.2.0.3.0 VALID
Oracle Database Packages and Types 11.2.0.3.0 VALID
JServer JAVA Virtual Machine 11.2.0.3.0 VALID
COMP_NAME VERSION STATUS
----------------------------------- --------------- ------
Oracle XDK 11.2.0.3.0 VALID
Oracle Database Java Packages 11.2.0.3.0 VALID
SQL>
}}
***データベースファイルら [#tfea43c9]
#code(sql,number,nooutline,noblock,nomenu){{
SQL> col NAME format a50
SQL> col IS_RECOVERY_DEST_FILE a24
SQL> SELECT * FROM v$controlfile; ----...
STATUS NAME ...
------- -------------------------------------------------...
D:\ORACLE\ORADATA\AERS\CONTROL01.CTL ...
D:\ORACLE\FAST_RECOVERY_AREA\AERS\CONTROL02.CTL ...
SQL>
SQL> col MEMBER format a35
SQL> SELECT l.group#, lf.type, lf.member, l.bytes, l.stat...
FROM v$log l, v$logfile lf
WHERE l.group# = lf.group#
ORDER BY 1,3; ----...
GROUP# TYPE MEMBER ...
---------- ------- ----------------------------------- --...
1 ONLINE D:\ORACLE\ORADATA\AERS\REDO01.LOG 1...
2 ONLINE D:\ORACLE\ORADATA\AERS\REDO02.LOG 1...
3 ONLINE D:\ORACLE\ORADATA\AERS\REDO03.LOG 1...
SQL>
SQL> SELECT VALUE FROM V$PARAMETER WHERE NAME = 'spfile' ...
VALUE
---------------------------------------------------------...
D:\ORACLE\PRODUCT\11.2.0\DBHOME_2\DATABASE\SPFILEAERS.ORA
SQL>
SQL> show parameter spfile ...
NAME TYPE VALUE
------------------------------------ ----------- --------...
spfile string D:\ORACL...
E_2\DATA...
SQL>
SQL> col FILE# format 9
SQL> col TS# format 9
SQL> col MEMBER format a35
SQL> select FILE#,TS#,STATUS,ENABLED,NAME from v$datafile...
FILE# TS# STATUS ENABLED NAME
----- --- ------- ---------- ----------------------------...
1 0 SYSTEM READ WRITE D:\ORACLE\ORADATA\AERS\SYSTE...
2 1 ONLINE READ WRITE D:\ORACLE\ORADATA\AERS\SYSAU...
3 2 ONLINE READ WRITE D:\ORACLE\ORADATA\AERS\UNDOT...
4 4 ONLINE READ WRITE D:\ORACLE\ORADATA\AERS\USERS...
5 5 ONLINE READ WRITE D:\ORACLE\ORADATA\AERS\AERS....
6 6 ONLINE READ WRITE D:\ORACLE\ORADATA\AERS\AERSI...
7 7 ONLINE READ WRITE D:\ORACLE\ORADATA\AERS\AERST...
SQL>
}}
***表領域とデータベースファイル [#a9d70abd]
終了行:
[[oracle]]
***記録すべき事項 [#m0741060]
dba権限保有ユーザで情報を取得。ここではsysを使用(sqlplus ...
***バージョン情報 [#x4ee8343]
#code(sql,number,nooutline,noblock,nomenu){{
SQL> SELECT * FROM V$VERSION;
BANNER
-----------------------------------------------------------
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> col PLATFORM_NAME format a30 ...
SQL> SELECT * FROM DBA_REGISTRY_DATABASE;
PLATFORM_ID PLATFORM_NAME EDITION
----------- ------------------------------ --------------...
12 Microsoft Windows x86 64-bit
SQL> SELECT DBMS_UTILITY.PORT_STRING FROM DUAL;
PORT_STRING
-----------------------------------------------
IBMPC/WIN_NT64-9.1.0
SQL>
}}
***インスタンス[SID]情報 [#xfb65477]
#code(sql,number,nooutline,noblock,nomenu){{
SQL> col OPEN_MODE format a10
SQL> SELECT DBID,NAME,OPEN_MODE,DATABASE_ROLE,PLATFORM_NA...
DBID NAME OPEN_MODE DATABASE_ROLE PLATFORM...
---------- --------- ---------- ---------------- --------...
2969170745 AERS READ WRITE PRIMARY Microsof...
SQL> col HOST_NAME format a10
SQL> set line 100
SQL> SELECT instance_name, host_name, status, archiver, d...
INSTANCE_NAME HOST_NAME STATUS ARCHIVE DATABASE_STATU...
---------------- ---------- ------ ------- --------------...
aers W2 OPEN STARTED ACTIVE ...
SQL>
SQL> col PROPERTY_NAME FORMAT a30
SQL> col PROPERTY_VALUE FORMAT a35
SQL> col DESCRIPTION FORMAT a50
SQL> set line 150
SQL> SELECT * FROM DATABASE_PROPERTIES;
PROPERTY_NAME PROPERTY_VALUE ...
------------------------------ --------------------------...
DICT.BASE 2 ...
DEFAULT_TEMP_TABLESPACE TEMP ...
DEFAULT_PERMANENT_TABLESPACE USERS ...
DEFAULT_EDITION ORA$BASE ...
Flashback Timestamp TimeZone GMT ...
TDE_MASTER_KEY_ID
DBTIMEZONE +09:00 ...
DST_UPGRADE_STATE NONE ...
DST_PRIMARY_TT_VERSION 14 ...
DST_SECONDARY_TT_VERSION 0 ...
DEFAULT_TBS_TYPE SMALLFILE ...
NLS_LANGUAGE JAPANESE ...
NLS_TERRITORY JAPAN ...
NLS_CURRENCY Y ...
NLS_ISO_CURRENCY JAPAN ...
NLS_NUMERIC_CHARACTERS ., ...
NLS_CHARACTERSET JA16SJISTILDE ...
NLS_CALENDAR GREGORIAN ...
NLS_DATE_FORMAT RR-MM-DD ...
NLS_DATE_LANGUAGE JAPANESE ...
NLS_SORT BINARY ...
NLS_TIME_FORMAT HH24:MI:SSXFF ...
NLS_TIMESTAMP_FORMAT RR-MM-DD HH24:MI:SSXFF ...
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR ...
NLS_TIMESTAMP_TZ_FORMAT RR-MM-DD HH24:MI:SSXFF TZR...
NLS_DUAL_CURRENCY \ ...
NLS_COMP BINARY ...
NLS_LENGTH_SEMANTICS BYTE ...
NLS_NCHAR_CONV_EXCP FALSE ...
NLS_NCHAR_CHARACTERSET AL16UTF16 ...
NLS_RDBMS_VERSION 11.2.0.3.0 ...
GLOBAL_DB_NAME AERS ...
EXPORT_VIEWS_VERSION 8 ...
WORKLOAD_CAPTURE_MODE ...
WORKLOAD_REPLAY_MODE ...
...
NO_USERID_VERIFIER_SALT 46C70D68AA28156AC84CEBDF92...
SQL>
}}
***バックアップ関係 [#j804c44f]
#code(sql,number,nooutline,noblock,nomenu){{
SQL> SELECT log_mode, flashback_on FROM v$database; ---...
LOG_MODE FLASHBACK_ON
------------ ------------------
ARCHIVELOG NO
SQL> ARCHIVE LOG LIST ---...
データベース・ログ・モード アーカイブ・モード
自動アーカイブ 有効
アーカイブ先 USE_DB_RECOVERY_FILE_DEST
最も古いオンライン・ログ順序 181
アーカイブする次のログ順序 183
現行のログ順序 183
SQL>
SQL> show parameter db_recovery_file_dest ---...
NAME TYPE VALUE
------------------------------------ ----------- --------...
db_recovery_file_dest string d:\oracl...
db_recovery_file_dest_size big integer 7G
SQL>
SQL> col FILENAME format a10
SQL> col STATUS format a10
SQL> SELECT FILENAME, STATUS, BYTES FROM V$BLOCK_CHANGE_T...
FILENAME STATUS BYTES
---------- ---------- ----------
DISABLED
SQL>
}}
RMANの設定状況
#code(sql,number,nooutline,noblock,nomenu){{
$ rman TARGET sys@AERS
ターゲット・データベースのパスワード: ...
ターゲット・データベース: AERS (データベースID=2969170745...
RMAN>
RMAN> show all; ...
リカバリ・カタログのかわりにターゲット・データベース制御...
db_unique_name AERSのデータベースにおけるRMAN構成パラメー...
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE D...
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO B...
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO ...
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK T...
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'E:\bk\%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DE...
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES...
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\ORACLE\PRODUCT...
RMAN>
}}
&color(red){*};バックアップ実行のスケジュールはどこを参照...
バックアップ実行のログは V$RMAN_BACKUP_JOB_DETAILS
***インストール済みパッケージ一覧 [#wec716f5]
#code(sql,number,nooutline,noblock,nomenu){{
SQL> col COMP_NAME format a35
SQL> col VERSION format a15
SQL> col STATUS format a6
SQL> SELECT COMP_NAME,VERSION,STATUS FROM dba_registry;
COMP_NAME VERSION STATUS
----------------------------------- --------------- ------
Oracle Application Express 3.2.1.00.12 VALID
Oracle Enterprise Manager 11.2.0.3.0 VALID
Oracle Multimedia 11.2.0.3.0 VALID
Oracle XML Database 11.2.0.3.0 VALID
Oracle Text 11.2.0.3.0 VALID
Oracle Expression Filter 11.2.0.3.0 VALID
Oracle Rules Manager 11.2.0.3.0 VALID
Oracle Workspace Manager 11.2.0.3.0 VALID
Oracle Database Catalog Views 11.2.0.3.0 VALID
Oracle Database Packages and Types 11.2.0.3.0 VALID
JServer JAVA Virtual Machine 11.2.0.3.0 VALID
COMP_NAME VERSION STATUS
----------------------------------- --------------- ------
Oracle XDK 11.2.0.3.0 VALID
Oracle Database Java Packages 11.2.0.3.0 VALID
SQL>
}}
***データベースファイルら [#tfea43c9]
#code(sql,number,nooutline,noblock,nomenu){{
SQL> col NAME format a50
SQL> col IS_RECOVERY_DEST_FILE a24
SQL> SELECT * FROM v$controlfile; ----...
STATUS NAME ...
------- -------------------------------------------------...
D:\ORACLE\ORADATA\AERS\CONTROL01.CTL ...
D:\ORACLE\FAST_RECOVERY_AREA\AERS\CONTROL02.CTL ...
SQL>
SQL> col MEMBER format a35
SQL> SELECT l.group#, lf.type, lf.member, l.bytes, l.stat...
FROM v$log l, v$logfile lf
WHERE l.group# = lf.group#
ORDER BY 1,3; ----...
GROUP# TYPE MEMBER ...
---------- ------- ----------------------------------- --...
1 ONLINE D:\ORACLE\ORADATA\AERS\REDO01.LOG 1...
2 ONLINE D:\ORACLE\ORADATA\AERS\REDO02.LOG 1...
3 ONLINE D:\ORACLE\ORADATA\AERS\REDO03.LOG 1...
SQL>
SQL> SELECT VALUE FROM V$PARAMETER WHERE NAME = 'spfile' ...
VALUE
---------------------------------------------------------...
D:\ORACLE\PRODUCT\11.2.0\DBHOME_2\DATABASE\SPFILEAERS.ORA
SQL>
SQL> show parameter spfile ...
NAME TYPE VALUE
------------------------------------ ----------- --------...
spfile string D:\ORACL...
E_2\DATA...
SQL>
SQL> col FILE# format 9
SQL> col TS# format 9
SQL> col MEMBER format a35
SQL> select FILE#,TS#,STATUS,ENABLED,NAME from v$datafile...
FILE# TS# STATUS ENABLED NAME
----- --- ------- ---------- ----------------------------...
1 0 SYSTEM READ WRITE D:\ORACLE\ORADATA\AERS\SYSTE...
2 1 ONLINE READ WRITE D:\ORACLE\ORADATA\AERS\SYSAU...
3 2 ONLINE READ WRITE D:\ORACLE\ORADATA\AERS\UNDOT...
4 4 ONLINE READ WRITE D:\ORACLE\ORADATA\AERS\USERS...
5 5 ONLINE READ WRITE D:\ORACLE\ORADATA\AERS\AERS....
6 6 ONLINE READ WRITE D:\ORACLE\ORADATA\AERS\AERSI...
7 7 ONLINE READ WRITE D:\ORACLE\ORADATA\AERS\AERST...
SQL>
}}
***表領域とデータベースファイル [#a9d70abd]
ページ名:
1
![[PukiWiki]](image/picc.png "[PukiWiki]")
