oracle 学习随手笔记

oracle 初始化参数分类

 

推导参数

操作系统依赖参数

alter system set <parameter_name>=<value> scope=memory|spfile|both [sid=sid_name]

 create database oradb 

logfile 

group 1('/data/oradb/redo/redo01.log') size 100m reuse,

group 2('/data/oradb/redo/redo02.log') size 100m reuse,

group 3('/data/oradb/redo/redo03.log') size 100m reuse

maxlogfiles 50

maxloghistory 200

maxdatafiles 500

maxinstances 5

archivelog

character set utf8

national character set utf8

datafile '/data/oradb/data/datafile.dbf' size 1000m extent management local

sysaux datafile '/data/oradb/data/sysaux01.dbf' size 500m

undo tablespace undotbs1 datafile '/data/oradb/data/undo01.dfb' size 500m

default temporary tablespace temp tempfile '/data/oradb/data/temp.dbf' size 500m;

 

每日脚本:

run {

allocate channel oem_disk_backup device type disk;

recover copy of database with tag 'ORA$OEM_LEVEL_0';

backup incremental level 1 cumulative  copies=1 for recover of copy with tag 'ORA$OEM_LEVEL_0' database;

}

 

select name,value

from v$parameter where name in ('db_black_buffers','db_block_size');

 

select

(select value from v$parameter where name='db_block_buffers')

*

(select value from v$parameter where name='db_block_size')

/1024/1024 BUFFER_CACHE_MB

from dual;

 

sql>show parameter sga_max_size

 

sql>alter system set sga_max_size=120m scope=spfile;

 

sql>select id,name,block_size,current_size,target_size from v$buffer_pool;

 

sql>show parameter db_cache_size;

 

sql>show parameter log_buffer;

 

sql>select * from v$sgastat;

 

sql>select * from v$sgainfo;

 

#ipcs -sa

 

kernel.shmmax = 1073741824

 

sql>alter system set db_cache_size = 2g scope=memory

 

sql>alter system set large_pool_size=200m scope=memory

 

sql>alter system set java_pool_size=200m scope=memory

 

sql>select tname from tab where tname like '%ADVICE%'

 

sql>select id,name,block_size,size_for_estimate sfe,size_factor sf,

estd_physical_read_factor eprf,estd_physical_reads epr from v$db_cache_advice

 

sql>select statistics_name,session_status,

system_status,activation_level,session_settable from v$statistics_level;

 

sql>alter system set statistics_level={basic,typical,all}

 

获取shared_pool修改建议

 

select shared_pool_size_for_estimate spsfe,

shared_pool_size_factor spsf,

estd_lc_size,

estd_lc_memory_objects elmo,

estd_lc_time_saved elts,

estd_lc_time_saved_factor eltsf,

estd_lc_memory_object_hits elmoh

from v$shared_pool_advice;

 

sql>select component,current_size,min_size,last_oper_type,

last_oper_mode,to_char(last_oper_time,'yyyy-mm-dd hh24:mi:ss) lot

from v$sga_dynamic_components;

 

添加数据库文件到表空间

sql>alter tablespace "TEMP" add tempfile '/data/jzmt/data/jzmt/jzmt.dbf'

sql>size 100M autoextend on next 50M maxsize 2000M;

 

sql>alter tablespace "TEMP" add tempfile '/data/jzmt/data/jzmt/jzmt.dbf'

sql>size 100M autoextend on next 50M maxsize unlimited

 

sql>create smallfile tablespace "chechi" datafile '/data/jzmt/data/jzmt/chechi.dbf'

sql>size 100M autoextend on next 20M maxsize unlimited loggin extent management 

sql>local segment space management auto

 

sql>create smallfile tablespace "chechi" datafile '/data/jzmt/data/jzmt/chechi.dbf'

sql>size 100M autoextend on next 20M maxsize unlimited loggin extent management

sql>dictionary

 

修改系统默认表空间

 

 

sql>ALTER DATABASE DEFAULT TABLESPACE "CHECHI" 

 

sql>CREATE SMALLFILE TEMPORARY TABLESPACE "CHECHI" TEMPFILE '/data/jzmt/data/jzmt/chechi.dbf' 

sql>SIZE 100M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M

sql>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "CHECHI"

 

sql>CREATE SMALLFILE UNDO TABLESPACE "CHECHI" DATAFILE '/data/jzmt/data/jzmt/chechi.dbf'

sql>SIZE 100M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED RETENTION NOGUARANTEE

 

sql>CREATE SMALLFILE UNDO TABLESPACE "CHECHI" DATAFILE '/data/jzmt/data/jzmt/chechi.dbf' SIZE 100M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED , 

sql>'/data/jzmt/data/jzmt/chechi02' SIZE 100M RETENTION NOGUARANTEE

 

sql>ALTER TABLESPACE TEMP TABLESPACE GROUP TEMP01

 

sql>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP01

 

创建数据库作业

BEGIN

sys.dbms_scheduler.create_job( 

job_name => '"SYS"."TEST"',

job_type => 'PLSQL_BLOCK',

job_action => 'begin

   -- Insert PL/SQL code here

end;',

start_date => systimestamp at time zone '+8:00',

job_class => 'DEFAULT_JOB_CLASS',

auto_drop => FALSE,

enabled => TRUE);

END;

 

 

sql>CREATE USER "CHECHI" PROFILE "DEFAULT" IDENTIFIED BY "*******" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK

sql>GRANT ADVISOR TO "CHECHI"

sql>GRANT "CONNECT" TO "CHECHI"

sql>GRANT "DBA" TO "CHECHI"

 

sql>CREATE USER "CHECHI" PROFILE "DEFAULT" IDENTIFIED BY "*******" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" ACCOUNT LOCK

 

创建概要文件 对数据库用户使用的资源进行限制

CREATE PROFILE "TEST" LIMIT CPU_PER_SESSION DEFAULT

CPU_PER_CALL DEFAULT

CONNECT_TIME DEFAULT

IDLE_TIME DEFAULT

SESSIONS_PER_USER DEFAULT

LOGICAL_READS_PER_SESSION DEFAULT

LOGICAL_READS_PER_CALL DEFAULT

PRIVATE_SGA DEFAULT

COMPOSITE_LIMIT DEFAULT

PASSWORD_LIFE_TIME DEFAULT

PASSWORD_GRACE_TIME DEFAULT

PASSWORD_REUSE_MAX DEFAULT

PASSWORD_REUSE_TIME DEFAULT

PASSWORD_LOCK_TIME DEFAULT

FAILED_LOGIN_ATTEMPTS DEFAULT

PASSWORD_VERIFY_FUNCTION DEFAULT

 

oracle_log

$ORACLE_HOME/oradb2_sid/sysman/log

 

$ORACLE_BASE/admin/sid/bdump

 

 

CREATE SMALLFILE TABLESPACE "CHECHI" DATAFILE '/data/jzmt/data/jzmt/chechi01.dbf' SIZE 200M AUTOEXTEND ON NEXT 20M MAXSIZE 600M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO 

 

dbca -silent  -createDatabase -templateName General_Purpose.dbc -gdbName jzmt -sid jzmt  -sysPassword 057cb33ca455bac9169335f376a069692b  -systemPassword 05ef7919a4158005b4ce87a248518ba76b  -sysmanPassword 05ef7919a4158005b4ce87a248518ba76b  -dbsnmpPassword 05b6162ba4359d67e57df954988cc0c864  -emConfiguration NOEMAIL  -hostUserName oracle -hostUserPassword 05f7cb65a4f5a8ae347aa7912786e3caca    -datafileJarLocation /u01/app/oracle/product/10.2.0/db_1/assistants/dbca/templates  -datafileDestination /data/jzmt -responseFile NO_VALUE   -characterset AL32UTF8   -obfuscatedPasswords true  -sampleSchema false -oratabLocation /u01/app/oracle/product/10.2.0/db_1/install/oratab  -recoveryAreaDestination /data/jzmt/flash_recovery_area 

 

oracle 数据库恢复区域

 

SQL>show parameter db_recovery_file_dest_size;

 

SQL>alter system set db_recovery_file_dest_size=4g;

 

SQL>show parameter db_recovery_file_dest_size;

 

SQL> select * from v$recovery_file_dest;

 

SQL>alter system reset db_recovery_file_dest_size scope=spfile sid='*';

 

SQL>ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u01/app/oracle/flash_recovery_area';

 

SQL>ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET = 1440;

 

oracle远程连接客户端tnsnames.ora配置

REMOTEDB =
     (DESCRIPTION =
       (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = 60.18.148.197)(PORT = 1521))
       )
       (CONNECT_DATA =
         (SERVICE_NAME = jzmt)
       )
     ) 

服务器端配置

SID_LIST_LISTENER =
  (SID_LIST =
   (SID_DESC =
          (GLOBAL_DBNAME = jzmt)
            (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
              (SID_NAME = jzmt)
          )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.251)(PORT = 1521))
    )
  )

 

chcon -t texrel_shlib_t   /usr/local/oracle/product/10.2.0/lib/libnnz10.so

chcon -t texrel_shlib_t  /usr/local/oracle/product/10.2.0/lib/libclntsh.so.10.1