Oracle OCP 1Z0‑083 最全必背命令（考试+实战全覆盖）

一、CDB / PDB 多租户（083 核心必考）

-- 查看容器
show con_id;
show con_name;
show pdbs;

select con_id, name, open_mode from v$pdbs;
select pdb_name, status from dba_pdbs;

-- 启动/关闭 PDB
alter pluggable database pdb1 open;
alter pluggable database pdb1 close immediate;
alter pluggable database all open;
alter pluggable database all close immediate;

-- 切换容器
alter session set container=pdb1;
alter session set container=CDB$ROOT;

-- 创建 PDB（从种子）
create pluggable database pdb1
admin user pdb_admin identified by password
file_name_convert=('pdbseed','pdb1');

-- 删除 PDB
drop pluggable database pdb1 including datafiles;

-- 保存PDB启动状态（开机自启）
alter pluggable database all save state;
alter pluggable database all discard state;

二、RMAN 完整命令（083 最难、分值最高）

1. 基础连接与配置

rman target /
rman target sys/password@orcl

-- 核心配置
configure controlfile autobackup on;
configure controlfile autobackup format for device type disk to '/backup/ctl_%F.bkp';
configure retention policy to redundancy 2;
configure retention policy to recovery window of 7 days;
configure device type disk parallelism 2;
configure compression algorithm 'basic' for device type disk;
configure default device type to disk;

show all;

2. 备份类

-- 全库+归档+删除已备份归档（最常用）
backup database plus archivelog delete all input;

-- 压缩全库
backup as compressed backupset database;

-- 归档备份
backup archivelog all;
backup archivelog all delete all input;
backup archivelog until time 'sysdate-1';

-- 表空间 / 数据文件
backup tablespace users;
backup datafile 1, 2, 3;

-- 控制文件 & spfile
backup current controlfile;
backup spfile;

-- 增量备份
backup incremental level 0 database;
backup incremental level 1 database;

3. 校验、删除过期、失效

crosscheck backup;
crosscheck archivelog all;

delete expired backup;
delete expired archivelog all;
delete obsolete;
delete backup;

4. 完全恢复（整个库）

shutdown immediate;
startup mount;

restore database;
recover database;
alter database open resetlogs;

5. 表空间恢复

sql 'alter tablespace users offline immediate';
restore tablespace users;
recover tablespace users;
sql 'alter tablespace users online';

6. 控制文件恢复

startup nomount;
restore controlfile from autobackup;
alter database mount;
recover database;
alter database open resetlogs;

7. spfile 恢复

startup nomount;
restore spfile from autobackup;
shutdown immediate;
startup;

三、闪回数据库（083 必考）

-- 开启闪回（必须 mount、归档、FRA）
alter database flashback on;
alter database flashback off;

select flashback_on from v$database;

-- 闪回操作
shutdown immediate;
startup mount;

flashback database to timestamp systimestamp - interval '1 hour';
flashback database to scn 123456;

alter database open resetlogs;

四、闪回数据归档（Flashback Archive）

-- 创建 FDA
create flashback archive fda1
tablespace users
retention 1 year;

-- 授权
grant flashback archive on fda1 to scott;

-- 表开启FDA
alter table emp flashback archive fda1;

-- 关闭
alter table emp no flashback archive;

-- 删除
drop flashback archive fda1;

五、ASM 核心命令（083 必考）

1. SQL 内管理 ASM

select group_number, name, state, type from v$asm_diskgroup;
select path, mount_status, header_status from v$asm_disk;

-- 创建磁盘组
create diskgroup DATA external redundancy
disk '/dev/oracleasm/disks/DISK1','/dev/oracleasm/disks/DISK2';

create diskgroup FRA normal redundancy
disk '/dev/oracleasm/disks/FRA1','/dev/oracleasm/disks/FRA2';

-- 增删磁盘
alter diskgroup DATA add disk '/dev/oracleasm/disks/DISK3';
alter diskgroup DATA drop disk DISK3;

-- 挂载/卸载
alter diskgroup all mount;
alter diskgroup all dismount;

2. asmcmd 常用

asmcmd
lsdg
lsdsk
lsct
ls
cp
mkdir
rm

六、Data Guard 常用（DG 083 高频）

-- 主库
alter database create standby controlfile as '/backup/stdby.ctl';
alter system archive log current;

-- 备库
alter database recover managed standby database disconnect from session;
alter database recover managed standby database cancel;

-- 角色切换
alter database commit to switchover to physical standby;
alter database commit to switchover to primary;

-- 开启实时应用
alter database recover managed standby database using current logfile disconnect;

七、SGA / PGA / 内存管理

-- AMM 自动内存管理
alter system set memory_target=4G scope=spfile;
alter system set memory_max_target=4G scope=spfile;

-- ASMM
alter system set sga_target=2G scope=both;
alter system set pga_aggregate_target=1G scope=both;

-- 组件
alter system set shared_pool_size=500M scope=both;
alter system set db_cache_size=1G scope=both;
alter system set log_buffer=128M scope=spfile;

八、AWR、ADDM、ASH（性能诊断）

-- 手工生成 AWR
exec dbms_workload_repository.create_snapshot;

-- 生成报告
@?/rdbms/admin/awrrpt.sql
@?/rdbms/admin/awrrpti.sql
@?/rdbms/admin/addmrpt.sql
@?/rdbms/admin/ashrpt.sql

-- 修改保留时间
exec dbms_workload_repository.modify_snapshot_settings(retention=>43200);

九、用户与资源管理（Profiles）

create profile prof1 limit
failed_login_attempts 3
password_life_time 30
password_lock_time 1
password_reuse_time 7
password_reuse_max 3;

alter user scott profile prof1;

drop profile prof1;
drop profile prof1 cascade;

十、加密、安全（083 少量）

-- 表空间加密
administer key management set encryption key identified by "password" with backup;

alter tablespace users encryption using 'AES256' encrypt;

十一、083 高频视图

select * from v$pdbs;
select * from v$flashback_database_log;
select * from v$recovery_file_dest;
select * from v$asm_diskgroup;
select * from v$asm_disk;
select * from v$dataguard_status;
select * from v$standby_log;
select * from dba_flashback_archive;
select * from dba_profiles;

---