Oracle 安装完后的初始化操作
解锁账户(可选)
解锁 scott 用户
1
2
3
4
5alter user scott account unlock;
alter user scott identified by tiger;
select username,account_status from dba_users;
防火墙开放 1521 端口(可选)
firewalld 配置开放 1521 端口
1
2firewall-cmd --zone=public --add-port=1521/tcp --permanent
firewall-cmd --reload
修改 processes 和 sessions 数
查看当前的 processes 和 sessions
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22SQL> show parameter processes;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 0
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 1000
log_archive_max_processes integer 4
processes integer 150
SQL> show parameter sessions;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size integer 0
java_soft_sessionspace_limit integer 0
license_max_sessions integer 0
license_sessions_warning integer 0
sessions integer 248
shared_server_sessions integer查询数据库当前进程的连接数
1
2
3
4
5SQL> select count(*) from v$process;
COUNT(*)
----------
32查看数据库当前会话的连接数
1
2
3
4
5SQL> select count(*) from v$session;
COUNT(*)
----------
29查看数据库的并发连接数：
1
select count(*) from v$session where status='ACTIVE';
查看当前数据库建立的会话情况：
1
select sid,serial#,username,program,machine,status from v$session;
修改 processes 以及 sessions
1
2alter system set processes=1024 scope=spfile;
alter system set sessions=2048 scope=spfile;
修改数据库时间格式
查看 nls_date_format 配置
1
SQL> show parameter nls_date_format;
修改 nls_date_format 配置
1
2
3
4
5# 修改全局的配置
SQL> alter system set nls_date_format='yyyy-mm-dd hh24:mi:ss' scope=spfile;
# 修改会话级别的
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';验证修改
1
SQL> select sysdate from dual;
备份相关设置
修改运行模式，以及开启闪回数据库
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20-- 关闭数据库
shutdown immediate;
-- 挂载数据库
startup mount;
-- 修改数据库的运行模式为归档日志模式
alter database archivelog;
-- 修改恢复分区大小
alter system set db_recovery_file_dest_size=50g;
-- 查看恢复分区设置
show parameter db_recovery;
-- 开启闪回数据库
alter database flashback on;
-- 设置闪回数据库可以闪回的时间范围(2天)
alter system set DB_FLASHBACK_RETENTION_TARGET = 2880;
-- 检查闪回状态以及运行模式
SELECT flashback_on, log_mode FROM v$database;
-- 检查闪回数据库可以闪回的时间范围
SELECT name, value FROM gv$parameter WHERE name LIKE '%flashback%';
-- 打开数据库
alter database open;创建 expdp/impdp 备份目录
1
create directory dump_dir as '/backup/oracle/dump_dir';
服务器上创建备份目录
1
2mkdir -p /backup/oracle/dump_dir
chown -R oracle:oinstall /backup/oracle/dump_dir修改 RMAN 全局配置
1
2
3
4% rman target /
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN 备份配置
创建完全备份
创建备份所需要的目录
1
2
3mkdir -p /backup/oracle/rman/{logs,scripts,backupfiles}
mkdir -p /backup/oracle/rman/backupfiles/{lev0,lev1}
chown -R oracle:oinstall /backup/oracle/rman/创建 RMAN level 0 备份命令文件
backup_level0.rcv1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29run {
# Hot database level 0 whole backup
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/oracle/rman/backupfiles/lev0/%F.bak';
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
backup as compressed backupset
incremental level 0 tag 'backup_level0'
skip inaccessible
format '/backup/oracle/rman/backupfiles/lev0/bak_%d_%T_%s_%p_db_level0'
database
plus archivelog
format '/backup/oracle/rman/backupfiles/lev0/arclogback_%s_%p_%t_%d'
delete input;
crosscheck backup;
delete noprompt expired backup;
report obsolete;
delete noprompt obsolete;
release CHANNEL t1;
release CHANNEL t2;
release CHANNEL t3;
}创建 RMAN level 0 级备份的 shell 脚本
oracle_level0_backup.sh1
2
3
4
5
6
7
8
export ORACLE_SID=ahwms
export ORACLE_HOME=/u01/app/oracle/product/11.2.0
export PATH=$ORACLE_HOME/bin:$PATH
export TIMESTAMP=`date +'%Y_%m_%d_%H_%M'`
rman target / nocatalog cmdfile=/backup/oracle/rman/scripts/backup_level0.rcv log=/backup/oracle/rman/logs/log_lev0_${TIMESTAMP}.log创建定时任务，每周日凌晨3点以 oracle 用户执行
oracle_level0_backup.sh脚本1
0 3 * * 0 oracle /backup/oracle/rman/oracle_level0_backup.sh
创建增量差异备份
创建 RMAN level 1 备份命令文件
backup_level1.rcv1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28run {
# Hot database level 1 whole backup
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/oracle/rman/backupfiles/lev1/%F.bak';
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
backup as compressed backupset
incremental level 1 tag 'subsdb_level1'
skip inaccessible
format '/backup/oracle/rman/backupfiles/lev1/bak_%d_%T_%s_%p_db_level1'
database plus archivelog
format '/backup/oracle/rman/backupfiles/lev1/arclogback_%s_%p_%t_%d'
delete input;
crosscheck backup;
delete noprompt expired backup;
report obsolete;
delete noprompt obsolete;
release channel t1;
release channel t2;
release channel t3;
}创建 RMAN level 1 级增量备份的 shell 脚本
oracle_level1_backup.sh1
2
3
4
5
6
7
8
export ORACLE_SID=ahwms
export ORACLE_HOME=/u01/app/oracle/product/11.2.0
export PATH=$ORACLE_HOME/bin:$PATH
export TIMESTAMP=`date +'%Y_%m_%d_%H_%M'`
rman target / nocatalog cmdfile=/backup/oracle/rman/scripts/backup_level1.rcv log=/backup/oracle/rman/logs/log_lev1_${TIMESTAMP}.log创建定时任务，周一到周六凌晨3点以 oracle 用户执行
oracle_level1_backup.sh脚本1
0 3 * * 1-6 oracle /backup/oracle/rman/oracle_level1_backup.sh