日志管理 (每个日志组里至少有2~3个组,每个组里的日志文件个数至少是1个或者是2~3个,太多会影响效率)
1).forcing log switches
sql> alter system switch logfile;
2).forcing checkpoints
global checkpoint
sql> alter system checkpoint;
3).adding online redo log groups
open,sys
sql> alter database add logfile group 4
('/opt/oracle/oradata/lh/redo041.log',
'/opt/oracle/oradata/lh/redo042.log')
size 50m;
alter database drop logfile group 1;**删除的不能是当前组和活跃的组
**alter database:进行的是物理上的操作,修改的是控制文件
4).adding online redo log members
open,sys,对当前组也可加
sql> alter database add logfile member
'/opt/oracle/oradata/lh/redo011.log' to group 1;
sql>alter database drop logfile member
'/opt/oracle/oradata/lh/redo011.log';**删除组里的成员
5).changes the name of the online redo logfile
shutdown immediate
os,oracle:
cd /opt/oracle/oradata/lh
mv redo01.log redo011.log
startup mount
mount,sys
sql> alter database rename file
'/opt/oracle/oradata/lh/redo01.log'
to '/opt/oracle/oradata/lh/redo011.log';
(alter database rename file
'/u01/app/oracle/oradata/orcl/redo01.log'
to '/u02/app/oracle/oradata/orcl/redo01.log';
)
sql>alter database open;
6).query log
group:select * from v$log 查看日志组的视图 **里面的byte表示日志组里的每个成员的大小一致
member:select * from v$logfile 查看日志组成员的视图
7).change logfile size,from 100M to 50M
select * from v$log;
看到group 3 is current
open,sys:
ALTER DATABASE DROP LOGFILE GROUP 1;
[
cd /opt/oracle/oradata/lh
rm redo01.log
]
ALTER DATABASE ADD LOGFILE GROUP 5
('/u01/app/oracle/oradata/hj/redo051.log') SIZE 50M;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE ADD LOGFILE GROUP 6
('/u01/app/oracle/oradata/hj/redo061.log') SIZE 50M;
now group 3 is current,then
alter system switch logfile;
ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE ADD LOGFILE GROUP 3
('/oracle/db/disk/log/redo033.log') SIZE 50M;
SQL> oradebug setmypid
sql> oradebug dump controlf 4
日志组和日志文件在控制文件的 LOG FILE RECORDS 部分
5 归档模式:archivelog(noarchivelog是非归档模式)
sqlplus sys/oracle as sysdba
archive log list
select log_mode from v$database;
shutdown immediate
startup mount
alter database archivelog; 切换到归档模式
(alter database noarchivelog;) 切换到非归档模式
alter database open;
show parameter db_recovery_file_dest
db_recovery_file_dest_size
alter system set db_recovery_file_dest_size=5G ;
alter system switch logfile;
alter system set log_archive_dest_1= 'location=/opt/oracle/arch';
alter system set log_archive_dest_2= 'location=/opt/oracle/arch3' ;
db_recovery_file_dest
db_recovery_file_dest_size
alter system set log_archive_dest_3= 'location=USE_DB_RECOVERY_FILE_DEST' ;**因为它可以保存到远程,所以这里用'location=...'
select * from v$archive_dest
alter system switch logfile;
select * from v$archive_dest **这个视图是显示的是归档目的地
select * from v$archived_log **这个视图记录了系统里所有的归档日志,还原数据的时候很有用,因为它把数据都备份到了这个视图里
select * from v$archive_processes **归档进程
alter system set log_archive_dest_1='';
alter system set log_archive_dest_2='';
archive log list
alter system set db_recovery_file_dest='/opt/oracle/flash_recovery_area' scope=spfile;
shutdown immediate
startup
select * from v$archive_dest **这个视图是显示的是归档目的地
select * from v$archive_gap; **查看没有归档的日志
6. shared server:共享服务(用在连接数多,并且连接时间短(短连接)的场合)
select * from v$session ;
select * from v$dispatcher;
select * from v$shared_server;
select * from v$circuit;
select * from v$shared_server_monitor
select * from v$queue
shared server: shared_servers>0
dedicated server: shared_servers=0
os oracle:
$ps -ef|grep orcl
ora_d000_orcl
ora_s000_orcl
oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
sys:
alter system set shared_servers=5 scope=both;
alter system set dispatchers = "(protocol=tcp)(dispatchers=5)(protocol=ipc)(dispatchers=1)" scope=both;
alter system set max_dispatchers=10 scope=both;
os oracle:
$ps -ef|grep orcl
ora_d000_orcl
ora_d001_orcl
ora_s000_orcl
ora_s001_orcl
ora_s002_orcl
oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
win cmd:sqlplus
new cmd:sqlplus
os oracle:
$ps -ef|grep orcl
ora_d000_orcl
ora_d001_orcl
ora_s000_orcl
ora_s001_orcl
ora_s002_orcl
oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
以上试验使用的tnsnames.ora:
10g =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.179.2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
client:tnsnames.ora
10g =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.179.2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
win cmd:sqlplus
os oracle:
$ps -ef|grep orcl
增加了:
oracleorcl (LOCAL=NO)
client:tnsnames.ora
10g =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.179.2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = shared)
(SERVICE_NAME = orcl)
)
)
win cmd:sqlplus
os oracle:
$ps -ef|grep orcl
没有增加进程
CIRCUITS
You should not specify a value for this
parameter unless you want to limit
the number of virtual circuits.
即使数据库工作在共享模式,
其本地sys连接也是专有的.
alter system set shared_servers=0 ;
alter system set dispatchers ='';
7. 非 listener的配置
server:
1.$ORACLE_HOME/network/admin/listener.ora 在原有的下面添加新的监听名字
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.192)(PORT = 1521))
)
这里监听名字改成非标准的,端口也可以改变
lsnrctl stop
listener.ora:
listener1,1522
lsnrctl
start listener1
2.
local tnsnames.ora: ***在这个$ORACLE_HOME/network/admin/tnsname.ora里面添加下面的内容
list=
(description=
(address=(protocol=tcp)(host=193.168.0.21)(port=1522))
阅读(458) | 评论(0) | 转发(0) |