[09/08] admin 3일차 실습

orcl@SYS> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

[oracle@lnx04 ~]$ getconf LONG_BIT

32

[oracle@lnx04 ~]$ uname -ar

Linux lnx04.ocmkorea.com 2.6.18-238.el5 #1 SMP Tue Jan 4 15:24:05 EST 2011 i686 i686 i386 GNU/Linux

[oracle@lnx04 ~]$ cd //u01/app/oracle/product/11.2.0/db_1/rdbms/admin/

[oracle@lnx04 admin]$ ls cs*

  csminst.sql

캐릭터셋을 점검하는 스크립트

[oracle@lnx04 admin]$ csscan

Character Set Scanner v2.2 : Release 11.2.0.1.0 - Production on Tue Sep 8 12:14:45 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

[oracle@lnx04 admin]$ csscan -help

Character Set Scanner v2.2 : Release 11.2.0.1.0 - Production on Tue Sep 8 12:15:08 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

You can let Scanner prompt you for parameters by entering the CSSCAN

command followed by your username/password:

  Example: CSSCAN \"SYSTEM/MANAGER AS SYSDBA\"

Or, you can control how Scanner runs by entering the CSSCAN command

followed by various parameters. To specify parameters, you use keywords:

  Example:

    CSSCAN \"SYSTEM/MANAGER AS SYSDBA\" FULL=y TOCHAR=utf8 ARRAY=1024000 PROCESS=3

Keyword    Default Prompt Description

---------- ------- ------ -------------------------------------------------

USERID             yes    username/password

FULL       N       yes    scan entire database

USER               yes    owner of tables to be scanned

TABLE              yes    list of tables to scan

COLUMN             yes    list of columns to scan

EXCLUDE                   list of tables to exclude from scan

TOCHAR             yes    new database character set name

FROMCHAR                  current database character set name

TONCHAR                   new national character set name

FROMNCHAR                 current national character set name

ARRAY      1024000 yes    size of array fetch buffer

PROCESS    1       yes    number of concurrent scan process

MAXBLOCKS                 split table if block size exceed MAXBLOCKS

CAPTURE    N              capture convertible data

SUPPRESS                  maximum number of exceptions logged for each table

FEEDBACK                  report progress every N rows

BOUNDARIES                list of column size boundaries for summary report

LASTRPT    N              generate report of the last database scan

LOG        scan           base file name of report files

PARFILE                   parameter file name

PRESERVE   N              preserve existing scan results

LCSD       N       no     enable language and character set detection

LCSDDATA   LOSSY   no     define the scope of the detection

HELP       N              show help screen (this screen)

QUERY      N              select clause to scan subset of tables or columns

---------- ------- ------ -------------------------------------------------

Scanner terminated successfully.

orcl@SYS> 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

DST_UPGRADE_STATE

NONE

State of Day Light Saving Time Upgrade

DST_PRIMARY_TT_VERSION

11

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

AMERICAN

Language

NLS_TERRITORY

AMERICA

Territory

NLS_CURRENCY

$

Local currency

NLS_ISO_CURRENCY

AMERICA

ISO currency

NLS_NUMERIC_CHARACTERS

.,

Numeric characters

NLS_CHARACTERSET

AL32UTF8

Character set

NLS_CALENDAR

GREGORIAN

Calendar system

NLS_DATE_FORMAT

DD-MON-RR

Date format

NLS_DATE_LANGUAGE

AMERICAN

Date language

NLS_SORT

BINARY

Linguistic definition

NLS_TIME_FORMAT

HH.MI.SSXFF AM

Time format

NLS_TIMESTAMP_FORMAT

DD-MON-RR HH.MI.SSXFF AM

Time stamp format

NLS_TIME_TZ_FORMAT

HH.MI.SSXFF AM TZR

Time with timezone format

NLS_TIMESTAMP_TZ_FORMAT

DD-MON-RR HH.MI.SSXFF AM 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.1.0

RDBMS version for NLS parameters

GLOBAL_DB_NAME

ORCL

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 connect; REPLAY implies workload replay is in progress

NO_USERID_VERIFIER_SALT

820EA1118701F6539A50393BB68B7AD0

DBTIMEZONE

00:00

DB time zone

36 rows selected.

[oracle@lnx04 ~]$ dbca

[oracle@lnx04 ~]$ export ORACLE_SID=prod

[oracle@lnx04 ~]$ env | grep ORACLE

ORACLE_SID=prod

ORACLE_BASE=/u01/app/oracle

ORACLE_TERM=xterm

ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

[oracle@lnx04 ~]$ ss

SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 8 12:35:13 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@lnx04 ~]$ . oraenv

ORACLE_SID = [prod] ? prod

The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle

[oracle@lnx04 ~]$ echo $ORACLE_SID

prod

[oracle@lnx04 ~]$ . oraenv

ORACLE_SID = [prod] ? orcl

The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle

[oracle@lnx04 ~]$ echo $ORACLE_SID

orcl

[oracle@lnx04 ~]$ su - root

Password: 

[root@lnx04 ~]# xhost +

access control disabled, clients can connect from any host

[root@lnx04 ~]# su - oracle

한 창에서는 하나의 세션만 갖자 exit로 하자 

idle> startup mount exclusive restrict
ORACLE instance started.

Total System Global Area 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
Database mounted.
idle> drop database;