Oracle 19c OCP 实验脚本

Oracle 19c OCP 超全实验脚本（全小写 + /u01 路径）

-- 0. 前置：sysdba 连接 & 基础信息
connect / as sysdba;
set pages 100 lines 200;
col name for a30;
col open_mode for a20;
col log_mode for a15;

select name, open_mode, log_mode, flashback_on, database_role from v$database;
select instance_name, status, version from v$instance;
show parameter db_name;
show parameter control_files;


-- 一、参数文件 & 实例管理
-- 生成 pfile/spfile
create pfile='/u01/app/oracle/pfile.ora' from spfile;
create spfile from pfile='/u01/app/oracle/pfile.ora';

-- 修改参数
alter system set sga_target=2g scope=both;
alter system set pga_aggregate_target=1g scope=both;
alter system set processes=800 scope=spfile;
alter system set sessions=1500 scope=spfile;
alter system set db_recovery_file_dest_size=10g scope=both;
alter system set db_recovery_file_dest='/u01/app/oracle/fra' scope=both;

-- 查看关键参数
show parameter sga;
show parameter pga;
show parameter processes;
show parameter undo_tablespace;

-- 启动关闭
startup nomount;
alter database mount;
alter database open;

shutdown immediate;
startup;


-- 二、cdb / pdb 完整生命周期
-- 查看容器
show pdbs;
col name for a20;
select con_id, name, open_mode, restricted from v$pdbs;
select con_id, name from v$containers;

-- 创建 pdb
create pluggable database ocp_pdb1
admin user pdb_admin identified by oracle
file_name_convert=('pdbseed','ocp_pdb1');

-- 打开 pdb
alter pluggable database ocp_pdb1 open;
alter pluggable database ocp_pdb1 save state;

-- 切换容器
alter session set container=ocp_pdb1;
show con_name;

-- 切回 cdb
alter session set container=cdb$root;

-- 关闭 pdb
alter pluggable database ocp_pdb1 close immediate;

-- 克隆 pdb
create pluggable database ocp_pdb2 from ocp_pdb1
file_name_convert=('ocp_pdb1','ocp_pdb2');

-- 删除 pdb
drop pluggable database ocp_pdb2 including datafiles;


-- 三、表空间 / 数据文件 / 临时表空间
-- 永久表空间
create tablespace ocp_tbs
datafile '/u01/app/oracle/oradata/orcl/ocp_tbs01.dbf'
size 100m autoextend on next 10m maxsize 10g;

-- 扩展数据文件
alter database datafile '/u01/app/oracle/oradata/orcl/ocp_tbs01.dbf' resize 200m;
alter tablespace ocp_tbs add datafile
'/u01/app/oracle/oradata/orcl/ocp_tbs02.dbf' size 100m autoextend on;

-- 临时表空间
create temporary tablespace ocp_temp
tempfile '/u01/app/oracle/oradata/orcl/ocp_temp01.dbf'
size 50m autoextend on next 5m maxsize 2g;

-- 临时表空间组
create temporary tablespace ocp_temp2
tempfile '/u01/app/oracle/oradata/orcl/ocp_temp02.dbf' size 50m;

alter tablespace ocp_temp tablespace group temp_group;
alter tablespace ocp_temp2 tablespace group temp_group;

-- 只读 / 脱机
alter tablespace ocp_tbs read only;
alter tablespace ocp_tbs read write;
alter tablespace ocp_tbs offline;
alter tablespace ocp_tbs online;

-- 删除表空间
drop tablespace ocp_tbs including contents and datafiles;


-- 四、undo 表空间管理
-- 创建 undo
create undo tablespace ocp_undo
datafile '/u01/app/oracle/oradata/orcl/ocp_undo01.dbf'
size 200m autoextend on next 10m maxsize 5g;

-- 切换 undo
alter system set undo_tablespace=ocp_undo scope=both;

-- 查看 undo
select tablespace_name, contents, status from dba_tablespaces where contents='undo';
show parameter undo_retention;
alter system set undo_retention=900 scope=both;


-- 五、用户 / 角色 / 权限 / 概要文件
-- 创建用户
create user ocp_user
identified by oracle
default tablespace users
temporary tablespace temp
quota unlimited on users;

-- 系统权限
grant create session, create table, create view, create synonym, create sequence to ocp_user;
grant create procedure, create trigger, create type to ocp_user;
grant select any dictionary to ocp_user;

-- 对象权限
grant select, insert, update, delete on hr.employees to ocp_user;
grant all on hr.departments to ocp_user;

-- 角色
create role ocp_role;
grant create session, create table to ocp_role;
grant ocp_role to ocp_user;

-- 概要文件
create profile ocp_profile limit
password_life_time 90
failed_login_attempts 5
password_lock_time 1/24
password_reuse_time 30
password_reuse_max 3;

alter user ocp_user profile ocp_profile;

-- 锁定/解锁/密码
alter user ocp_user account lock;
alter user ocp_user account unlock;
alter user ocp_user identified by oracle123;

-- 回收权限
revoke create table from ocp_user;
revoke select on hr.employees from ocp_user;


-- 六、重做日志 + 归档模式
-- 查看日志
select group#, thread#, sequence#, bytes/1024/1024 mb, status from v$log;
select group#, member from v$logfile;

-- 添加日志组
alter database add logfile group 4
('/u01/app/oracle/oradata/orcl/redo04a.log') size 200m;

-- 添加多路复用成员
alter database add logfile member
('/u01/app/oracle/oradata/orcl/redo04b.log') to group 4;

-- 切换日志
alter system switch logfile;
alter system checkpoint;
alter system archive log current;

-- 开启归档（mount 模式）
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;

archive log list;


-- 七、控制文件管理
-- 查看控制文件
show parameter control_files;

-- 多路复用控制文件（关闭后复制）
-- shutdown immediate;
-- cp /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/oradata/orcl/control02.ctl
-- startup;


-- 八、闪回数据库 & 闪回查询
shutdown immediate;
startup mount;
alter database flashback on;
alter database open;

select flashback_on from v$database;

create table ocp_test (id number);
insert into ocp_test values (1);
commit;

delete from ocp_test;
commit;

select * from ocp_test as of timestamp sysdate - 5/1440;
flashback table ocp_test to timestamp sysdate - 5/1440;


-- 九、rman 备份恢复
-- linux 执行：rman target /
run {
backup database plus archivelog;
backup current controlfile;
backup spfile;
}

validate database;
restore database;
recover database;


-- 十、数据泵
create directory dump_dir as '/u01/app/oracle';
grant read, write on directory dump_dir to ocp_user;

-- expdp ocp_user/oracle directory=dump_dir dumpfile=ocp.dmp logfile=ocp.log
-- impdp ocp_user/oracle directory=dump_dir dumpfile=ocp.dmp logfile=imp.log


-- 十一、awr 性能
exec dbms_workload_repository.create_snapshot();
select snap_id, begin_interval_time from dba_hist_snapshot order by snap_id;
-- @?/rdbms/admin/awrrpt


-- 十二、asm
select name, state, type from v$asm_diskgroup;
select path, state, mount_status from v$asm_disk;
alter diskgroup data mount;
alter diskgroup data dismount;


-- 十三、dataguard
alter system switch logfile;
select process, status, thread#, sequence# from v$managed_standby;
alter database recover managed standby database using current logfile disconnect;
alter database commit to switchover to physical standby;


-- 十四、故障处理
alter database clear unarchived logfile group 1;

alter database datafile '/u01/app/oracle/oradata/orcl/ocp_tbs01.dbf' offline drop;
recover datafile '/u01/app/oracle/oradata/orcl/ocp_tbs01.dbf';
alter database datafile '/u01/app/oracle/oradata/orcl/ocp_tbs01.dbf' online;

select owner, object_name, object_type from dba_objects where status='invalid';
exec utl_recomp.recomp_serial();


-- 十五、性能
select event, count(*) from v$session_wait group by event order by 2 desc;
select sql_id, elapsed_time, sql_text from v$sql order by elapsed_time desc;

select session_id, locked_mode, object_name
from v$locked_object l, dba_objects o
where l.object_id=o.object_id;
-- 看锁
select l.session_id sid,s.serial#,o.object_name
from v$locked_object l,dba_objects o,v$session s
where l.object_id=o.object_id and l.session_id=s.sid;

-- 看阻塞
select sid,serial#,blocking_session,event from v$session where blocking_session is not null;

-- kill
alter system kill session 'sid,serial#';
alter system kill session 'sid,serial#' immediate;

全部统一：

• 路径：/u01/app/oracle/oradata/orcl/
• 命令：全小写
• 密码：oracle
• 可直接复制到 sqlplus 执行