oracle
記録すべき事項†
dba権限保有ユーザで情報を取得。ここではsysを使用(sqlplus sys@AERS as sysdba)
バージョン情報†
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
| | 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]情報†
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
| | SQL> col OPEN_MODE format a10
SQL> SELECT DBID,NAME,OPEN_MODE,DATABASE_ROLE,PLATFORM_NAME FROM V$DATABASE;
DBID NAME OPEN_MODE DATABASE_ROLE PLATFORM_NAME
2969170745 AERS READ WRITE PRIMARY Microsoft Windows x86 64-bit
SQL> col HOST_NAME format a10
SQL> set line 100
SQL> SELECT instance_name, host_name, status, archiver, database_status, instance_role, active_state FROM v$instance;
INSTANCE_NAME HOST_NAME STATUS ARCHIVE DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST
aers W2 OPEN STARTED ACTIVE PRIMARY_INSTANCE NORMAL
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 DESCRIPTION
DICT.BASE 2 dictionary base tables version #
DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace
DEFAULT_EDITION ORA$BASE Name of the database default edition
Flashback Timestamp TimeZone GMT Flashback timestamp created in GMT
TDE_MASTER_KEY_ID
DBTIMEZONE +09:00 DB time zone
DST_UPGRADE_STATE NONE State of Day Light Saving Time Upgrade
DST_PRIMARY_TT_VERSION 14 Version of primary timezone data file
DST_SECONDARY_TT_VERSION 0 Version of secondary timezone data file
DEFAULT_TBS_TYPE SMALLFILE Default tablespace type
NLS_LANGUAGE JAPANESE Language
NLS_TERRITORY JAPAN Territory
NLS_CURRENCY Y Local currency
NLS_ISO_CURRENCY JAPAN ISO currency
NLS_NUMERIC_CHARACTERS ., Numeric characters
NLS_CHARACTERSET JA16SJISTILDE Character set
NLS_CALENDAR GREGORIAN Calendar system
NLS_DATE_FORMAT RR-MM-DD Date format
NLS_DATE_LANGUAGE JAPANESE Date language
NLS_SORT BINARY Linguistic definition
NLS_TIME_FORMAT HH24:MI:SSXFF Time format
NLS_TIMESTAMP_FORMAT RR-MM-DD HH24:MI:SSXFF Time stamp format
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR Time with timezone format
NLS_TIMESTAMP_TZ_FORMAT RR-MM-DD HH24:MI:SSXFF TZR Timestamp with timezone format
NLS_DUAL_CURRENCY \ Dual currency symbol
NLS_COMP BINARY NLS comparison
NLS_LENGTH_SEMANTICS BYTE NLS length semantics
NLS_NCHAR_CONV_EXCP FALSE NLS conversion exception
NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set
NLS_RDBMS_VERSION 11.2.0.3.0 RDBMS version for NLS parameters
GLOBAL_DB_NAME AERS Global database name
EXPORT_VIEWS_VERSION 8 Export views revision #
WORKLOAD_CAPTURE_MODE CAPTURE implies workload capture is in progress
WORKLOAD_REPLAY_MODE PREPARE implies external replay clients can connec
t; REPLAY implies workload replay is in progress
NO_USERID_VERIFIER_SALT 46C70D68AA28156AC84CEBDF9237F99F
SQL>
|
バックアップ関係†
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
| | 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:\oracle\fast_recovery_area
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_TRACKING;
FILENAME STATUS BYTES
DISABLED
SQL>
|
RMANの設定状況
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
| | $ 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 DISK TO '%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
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 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO 'SBT_TAPE';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\ORACLE\PRODUCT\11.2.0\DBHOME_2\DATABASE\SNCFAERS.ORA'; # default
RMAN>
|
*バックアップ実行のスケジュールはどこを参照すればいいの?
バックアップ実行のログは V$RMAN_BACKUP_JOB_DETAILS
インストール済みパッケージ一覧†
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
| | 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>
|
データベースファイルら†
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
| | SQL> col NAME format a50
SQL> col IS_RECOVERY_DEST_FILE a24
SQL> SELECT * FROM v$controlfile;
STATUS NAME IS_RECOVERY_DEST_FILE BLOCK_SIZE FILE_SIZE_BLKS
D:\ORACLE\ORADATA\AERS\CONTROL01.CTL NO 16384 612
D:\ORACLE\FAST_RECOVERY_AREA\AERS\CONTROL02.CTL NO 16384 612
SQL>
SQL> col MEMBER format a35
SQL> SELECT l.group#, lf.type, lf.member, l.bytes, l.status LOG_STATUS, lf.status LOGFILE_STATUS
FROM v$log l, v$logfile lf
WHERE l.group# = lf.group#
ORDER BY 1,3;
GROUP# TYPE MEMBER BYTES LOG_STATUS LOGFILE
1 ONLINE D:\ORACLE\ORADATA\AERS\REDO01.LOG 104857600 INACTIVE
2 ONLINE D:\ORACLE\ORADATA\AERS\REDO02.LOG 104857600 INACTIVE
3 ONLINE D:\ORACLE\ORADATA\AERS\REDO03.LOG 104857600 CURRENT
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:\ORACLE\PRODUCT\11.2.0\DBHOM
E_2\DATABASE\SPFILEAERS.ORA
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\SYSTEM01.DBF
2 1 ONLINE READ WRITE D:\ORACLE\ORADATA\AERS\SYSAUX01.DBF
3 2 ONLINE READ WRITE D:\ORACLE\ORADATA\AERS\UNDOTBS01.DBF
4 4 ONLINE READ WRITE D:\ORACLE\ORADATA\AERS\USERS01.DBF
5 5 ONLINE READ WRITE D:\ORACLE\ORADATA\AERS\AERS.DBF
6 6 ONLINE READ WRITE D:\ORACLE\ORADATA\AERS\AERSIND.DBF
7 7 ONLINE READ WRITE D:\ORACLE\ORADATA\AERS\AERSTBL.DBF
SQL>
|
表領域とデータベースファイル†
![[PukiWiki]](image/picc.png "[PukiWiki]")
