Oracle OCP 082+083 终极

一、实例启停（必考）

startup nomount;   startup mount;   startup;
shutdown normal; transactional; immediate; abort;
alter system checkpoint; alter system switch logfile;

二、参数文件

create pfile from spfile; create spfile from pfile;
alter system set x scope=memory/spfile/both;
alter system reset x scope=spfile;

三、用户、权限、概要文件

create user c##u identified by p default tablespace users temporary tablespace temp;
alter user c##u quota 100M/unlimited on users;
grant connect,resource,create session,table to c##u;create profile p limit failed_login_attempts 3 password_life_time 30;
alter user scott profile p;

四、表空间 / 数据文件

create tablespace ts datafile '...' size 100M autoextend on;
create temporary tablespace temp tempfile '...';
create undo tablespace undo datafile '...';alter database datafile '...' resize 200M;
alter tablespace ts read only/read write/offline/online;
drop tablespace ts including contents and datafiles;

五、日志、控制文件、归档

alter database add logfile group 4 size 50M;
alter database add logfile member '...' to group 4;
alter database clear logfile group 4;alter database archivelog/noarchivelog; -- mount
alter system set log_archive_dest_1='location=...';alter database backup controlfile to '...';
alter database backup controlfile to trace;

六、表、索引、约束

alter table t add/drop/modify/rename column;
truncate table t; drop table t; flashback table t to before drop;
alter table t enable row movement;
create index i on t(c); alter index i rebuild;

七、闪回（082+083 核心）

select * from t as of timestamp sysdate-10/1440;
flashback table t to timestamp/s cn;
alter database flashback on; -- mount
flashback database to timestamp/scn; alter database open resetlogs;

八、数据泵 expdp/impdp

create directory d as '...'; grant read,write on directory d to system;
expdp ... schemas=... tables=... full=y
impdp ... remap_schema=... remap_tablespace=... table_exists_action=append/replace/truncate

九、RMAN（083 最高频）

configure controlfile autobackup on;
configure retention policy to redundancy 2;
backup database plus archivelog delete all input;
backup as compressed backupset database;
backup incremental level 0 database;crosscheck backup; delete expired; delete obsolete;restore database; recover database; alter database open resetlogs;

十、CDB/PDB（19c 必考）

show pdbs;
alter session set container=pdb1;
alter pluggable database all open/close immediate;
alter pluggable database all save state;
create pluggable database pdb1 admin user u identified by p;
drop pluggable database pdb1 including datafiles;

十一、ASM

create diskgroup data external redundancy disk '...';
alter diskgroup data add/drop disk ...;
asmcmd: ls, lsdg, lsdsk, cp

十二、Data Guard（DG）

alter database create standby controlfile as '...';
alter database recover managed standby database disconnect;
alter database commit to switchover to primary/standby;

十三、AWR/ASH/ADDM

exec dbms_workload_repository.create_snapshot;
@?/rdbms/admin/awrrpt.sql
@?/rdbms/admin/addmrpt.sql
@?/rdbms/admin/ashrpt.sql

十四、最核心视图（背名字即可）

v$instance v$database v$datafile v$log v$logfile v$controlfile
dba_tablespaces dba_data_files dba_users v$pdbs v$asm_diskgroup

---