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博客

OCP_02

最新文章