17.08.12
调度作业
$ ps -ef | grep cjq
SQL> show parameter job_queue_processes
后台预先设置的自动化管理作业：
自定义作业：
SQL> create table session_history(snap_time timestamp with local time zone, num_session number);
em中创建作业：
使用plsql块：
declare
session_count number;
begin
select count(*) into session_count from v$session;
insert into session_history values (systimestamp, session_count);
commit;
end;
全球化支持
$ vi .bash_profile
#export NLS_LANG=american_america.AL32UTF8
#export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
$ unset NLS_LANG
$ unset NLS_DATE_FORMAT
SQL> select sysdate from dual;
Database:
Show parameter nls
Client:
.bash_profile
Export NLS_DATE_FORMAT=’yyyy-mm-dd’
Session:
Alter session set nls_date_format=’dd-mon yyyy’
只影响这一个窗口，在别的客户端上不起作用
Sql:
Select to_char(sysdate,’yyyy,month,dd’)from dual;
优先级别最高，影响范围最小，只影响单个语句
（影响范围越小，优先级别最高）
备份恢复
配置可恢复性
控制文件
SQL> show parameter control_files
SQL> select * from v$controlfile;
修改路径：
$ cd $ORACLE_HOME/dbs
$ cp spfileorcl.ora spfileorcl.ora.bak 备份
SQL> alter system set control_files='/u01/app/oracle/oradata/orcl/control01.ctl', '/home/oracle/control02.ctl' scope=spfile; 修改系统参数，静态参数修改需要重启
SQL> shutdown immediate
$ mv /u01/app/oracle/fast_recovery_area/orcl/control02.ctl /home/oracle/control02.ctl
SQL> startup
SQL> show parameter control_files
SQL> select * from v$controlfile;
增加镜像：
SQL> alter system set control_files='/u01/app/oracle/oradata/orcl/control01.ctl', '/home/oracle/control02.ctl', '/home/oracle/control03.ctl' scope=spfile;
SQL> shutdown immediate
$ cp /home/oracle/control02.ctl /home/oracle/control03.ctl
SQL> startup
SQL> show parameter control_files
SQL> select * from v$controlfile;
日志文件：
增加成员和日志组：
SQL> select GROUP#, SEQUENCE#, STATUS, MEMBERS from v$log;
SQL> select GROUP#, MEMBER from v$logfile;
SQL> alter database add logfile member '/home/oracle/redo01b.log' to group 1;
SQL> alter database add logfile member '/home/oracle/redo02b.log' to group 2;
SQL> alter database add logfile member '/home/oracle/redo03b.log' to group 3;
SQL> alter database add logfile group 4 ('/u01/app/oracle/oradata/orcl/redo04.log', '/home/oracle/redo04b.log') size 50M;
FRA：
SQL> show parameter db_recovery
backupset: 10GB, archived log: 5GB
10+5, 10G
开启归档模式：
SQL> archive log list 检查当前设置
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list
SQL> show parameter log_archive_dest
SQL> select group#, sequence#, status, archived from v$log;
SQL> alter system switch logfile;
SQL> select group#, sequence#, status, archived from v$log;
SQL> select NAME, SEQUENCE#, STATUS from v$archived_log;
$ ls /u01/app/oracle/fast_recovery_area/ORCL
RMAN配置
$ rman target / 或rman target sys/password@orcl
RMAN> show all; 查看系统里备份方面所有的设置
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP On;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP clear;
RMAN> backup tablespace users;
RMAN> list backup;
SQL> desc v$backup_set 浏览器中也可以查看
自动通道管理：
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
RMAN> backup tablespace users, example;
$ mkdir /home/oracle/disk1 /home/oracle/disk2
RMAN> configure channel 1 device type disk to destination '/home/oracle/disk1';
RMAN> configure channel 2 device type disk to destination '/home/oracle/disk2';
RMAN> backup tablespace users, example;
恢复默认
RMAN> CONFIGURE DEVICE TYPE DISK clear;
RMAN> CONFIGURE CHANNEL 1 device type disk clear;
RMAN> CONFIGURE CHANNEL 2 device type disk clear;
手动通道管理：
RMAN> run {
allocate channel c1 device type disk to destination '/home/oracle/disk1';
allocate channel c2 device type disk to destination '/home/oracle/disk2';
backup tablespace users, example;
或
backup (tablespace users channel c1)(tablespace example channel c2);
}
backup section size 500M datafile 1;
指定备份格式：
RMAN> backup tablespace users;
RMAN> backup as backupset tablespace users;
RMAN> backup as compressed backupset tablespace users; 压缩备份集
RMAN> backup as copy tablespace users; 镜像复制
RMAN> list backup of tablespace users;
RMAN> list copy of tablespace users;
