ITPub博客

首页 > 数据库 > Oracle > OCP_02

OCP_02

原创 Oracle 作者:Michael_DD 时间:2014-02-28 14:50:19 0 删除 编辑
1.
$ORACLE_HOME/dbs
pfile----init.ora --文本  --more/cat/tail --asm 
spfile---spfile.ora --二进制   --strings  --file system

2.查看spfile
show parameter spfile

3.修改
a. 修改比较少
alter system set parameter_name=value scope=memory/spfile/both sid=sid1/sid2/*
scope=spfile---重启库

eg:
alter system set open_cursors=1000;
alter system set processes=1000 scope=spfile; ---重启数据库

b.批量
create pfile='/tmp/aa.ora' from spfile;
vi /tmp/aa.ora
shutdown immediate;
startup pfile='/tmp/aa.ora'
create spfile from pfile='/tmp/aa.ora'
shutdown immediate;
startup

4.备份
create pfile='/tmp/aa.ora' from spfile;

5.参数是动态还是静态
1.uat test
2.v$parameter ---v$fixed_table
select *from v$parameter;
select *from v$fixed_table;


2.alert.log --->2G,建议做个备份另存


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

shutdown abort;
kill -9 smon

非常正常关库:smon---是实例恢复

正确的停启库(每次变更时写到变更步骤里):

a.停库---数据库变更、数据库服务器变更
1.停应用
2.lsnrctl stop
3.sqlplus / as sysdba 
alter system switch logfile;  ---3
alter system checkpoint;
shutdown immediate;


--一边关一半看alert.log
show parameter background_dump_dest
cd /u01/app/oracle/diag/rdbms/tk/tk/trace/


tail -f alert_tk.log        参数-f使tail不停地去读最新的内容,这样有实时监视的效果


b.启库(开启自动重启库)
手工:
lsnrctl start
--建议一步步启动
startup nomount;
alter database mount;
alter database open;

-----------------------------------------------------------------------------
3.初始化:
生产库正式上线:

a.数据库参数修改
b.版本发布---创建用户(业务用户,业务查询、监控、DBA维护监控用户)、赋予权限和表空间
c.profile---规则
d.部署备份(rman物理备份,expdp逻辑备份针对部分重要的业务表或某个用户下所有对象)
e.部署监控
f.维护公司相关的文档

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

》》》》》》》
1.alter system 动态、静态
2.spfile--pfile改
3.停启库,用正确的方法

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

listener
1.netca
$

2.
PL/SQL developer connec
client---->server
client:
IP/db_name/port/user_name/password
ping 192.168.1.199
server:
# lsnrctl status/start/stop
sql> alter system register  (数据库还启动着,这时stop listener,然后启动,就需要手动注册)


check:
tnsping tk  (ip/port/service_name) 
ping 192.168.1.199
lsnrctl status --->check port/service_name(一定成功注册)

3.动态、静态

动态(ready)---不需要listener.ora,PMON(service_name,instance_name),1521,非1521(local_listener,1522)
alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.199)(PORT = 1522))'

静态(UNKNOWN)--需要配置listener.ora
SID_LIST_LISTENER=
  (SID_LIST=
   (SID_DESC=
   (SID_NAME=tk)
   (ORACLE_HOME=/u01/app/oracle/product/11.2.0.3)
   )
)


可以通过看日志知道哪些机器连到DB了

lsnrctl status

/u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml


ORACLE_HOME/network/admin/listener.ora --tnsnames.ora



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

database link(db--db)


SQL> select username , account_status from dba_users where username='HR';


USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
HR                             EXPIRED & LOCKED


SQL> alter user hr account unlock;


User altered.


SQL> select username , account_status from dba_users where username='HR';


USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
HR                             EXPIRED


SQL> alter user hr identified by hr;


User altered.


SQL> select username , account_status from dba_users where username='HR';


USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
HR                             OPEN



1.
192.168.1.242 --->hr unlock
create table
create table hr.testabc as select * from hr.jobs;


2.
192.168.1.199--->hr unlock
-- Create database link 
create public database link LINK_HR
connect to HR
using '192.168.1.242/tk';


select * from hr.testabc@link_hr
create synonym hr.testabc for hr.testabc@link_hr
select * from hr.testabc

select 'X' from dual@link_hr;

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

ps -ef | grep -v grep | grep LOCAL=NO | awk '{print $2}' | xargs kill -9

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

tablespace

--tablespace
--view
select * from v$tablespace
--查看example下面放了哪些表和索引?
select * from dba_data_files where tablespace_name='EXAMPLE'
select * from dba_extents where file_id=5
select * from dba_segments where tablespace_name='EXAMPLE'


alter user dd default tablespace dd;   修改默认表空间

--业务系统上线,建用户和表空间 --研发
--create tablespace(table , index)
create tablespace erp_data datafile '/u01/app/oracle/oradata/tk/erp_data_01.dbf' size 10M  autoextend on;
create tablespace erp_index datafile '/u01/app/oracle/oradata/tk/erp_index_01.dbf' size 10M autoextend on;


--add user
create user erp identified by erp 
default tablespace erp_data
temporary tablespace temp;


--add privilege
grant create session,connect to erp;
GRANT CREATE JOB TO ERP;
GRANT CREATE PROCEDURE TO ERP;
GRANT CREATE SYNONYM TO ERP;
GRANT CREATE TABLE TO ERP;
GRANT CREATE ANY INDEX TO ERP;
GRANT CREATE TYPE TO ERP;
GRANT CREATE VIEW TO ERP;
GRANT CREATE SEQUENCE TO ERP;
GRANT CREATE MATERIALIZED VIEW TO ERP;



--offline
alter tablespace xxx offline/online/read only/read write;
--move tablespace
alter table xxx move tablespace xxx    (把指定表移动到指定的表空间下)
create table xxx (column_name .....) tablespace xxx; (在指定表空间下建表)



--drop tablespace
 drop tablespace erp_data INCLUDING CONTENTS and datafiles;

--add
alter tablespace example  add datafile '/u01/app/oracle/oradata/tk/example02.dbf' size 10m;
alter database datafile '/u01/app/oracle/oradata/tk/example02.dbf' resize 20m;

--迁移数据文件 (迁移一个tablespace)
root#
mkdir -p /u02/app/oracle/oradata/tk
chown -R oracle:oinstall /u02    (R表示在所有子目录及目录中的文件中递归执行。)

sql>alter tablespace example offline;
cp /u01/app/oracle/oradata/tk/example02.dbf /u02/app/oracle/oradata/tk/
sql>alter database rename file '/u01/app/oracle/oradata/tk/example02.dbf' to '/u02/app/oracle/oradata/tk/example02.dbf';
sql>alter tablespace example online;


-------------------------------------------------------
user
--add user
create user erp identified by erp 
default tablespace erp_data
temporary tablespace temp;

--add privilege
grant create session,connect to erp;
GRANT CREATE JOB TO ERP;
GRANT CREATE PROCEDURE TO ERP;
GRANT CREATE SYNONYM TO ERP;
GRANT CREATE TABLE TO ERP;
GRANT CREATE ANY INDEX TO ERP;
GRANT CREATE TYPE TO ERP;
GRANT CREATE VIEW TO ERP;
GRANT CREATE SEQUENCE TO ERP;
GRANT CREATE MATERIALIZED VIEW TO ERP;
grant unlimited tablespace to erp;

--profile --不是针对业务用户,只是针对一些个人账号,为了防止个人账号的而一些操作会影响数据库性能

--改密码
alter user xxx identified by xxx;

---lock/unlock
alter user xxx account unlock/lock;

--dorp user
drop user xxx cascade;

--kill session

select s.sid, s.serial#, pid, spid
  from v$session s, v$process p
 where s.paddr = p.addr
   and s.sid = 39
kill -9 spid

alter system kill session '27,87';

--role
create role aabb not identified ;

grant create session,connect to aabb;
GRANT CREATE JOB TO aabb;
GRANT CREATE PROCEDURE TO aabb;
GRANT CREATE SYNONYM TO aabb;
GRANT CREATE TABLE TO aabb;
GRANT CREATE ANY INDEX TO aabb;
GRANT CREATE TYPE TO aabb;
GRANT CREATE VIEW TO aabb;
GRANT CREATE SEQUENCE TO aabb;
GRANT CREATE MATERIALIZED VIEW TO aabb;
grant unlimited tablespace to aabb;

grant aabb to hr;
 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29500582/viewspace-1097108/,如需转载,请注明出处,否则将追究法律责任。

上一篇: OCP_01
下一篇: OCP_03
请登录后发表评论 登录
全部评论

注册时间:2014-02-22

  • 博文量
    326
  • 访问量
    2460613