8月1日冷备份回复记录
备份脚本
PASSWD=sys/manager; export PASSWD
BACKUP_DIR=/home6/backup; export BACKUP_DIR
ARCH_DIR=/home2/arch; export ARCH_DIR
#
# Creating database file list in the file file_name.dat
#
echo
echo
echo
echo
echo
echo "get Creating database file list in the file file_name.dat ..."
sqlplus -s $PASSWD >file_name.dat<<!
set heading off;
set pagesize 0
set linesize 2000
set feedback off;
REM
REM Listing datafiles
REM
select name from V\$DATAFILE;
REM
REM Listing contolfiles
REM
select name from V\$CONTROLFILE;
REM
REM Listing online redo log files
REM
select member from V\$LOGFILE;
exit;
!
#
# Shutting down the database
#
echo
echo
echo
echo
echo
echo "Shutting down the database ..."
dbshut
lsnrctl stop tcp_listener
#
#
# Backing up the initialization parameter file
#
echo
echo
echo
echo
echo
echo "Backing up the initialization parameter file ... "
cp -f $ORACLE_HOME/dbs/init$ORACLE_SID.ora $BACKUP_DIR/datafiles
cp -f $ORACLE_HOME/dbs/config$ORACLE_SID.ora $BACKUP_DIR/datafiles
echo "Backing up the initialization parameter file ... OK!!! "
# Backing up the files
# to the file BKPFILE as defined above
#
echo
echo
echo
echo
echo
echo "Backing up the datafiles ..."
for i in `cat file_name.dat`
do
echo " Backing up the datafile $i ... "
cp -f $i $BACKUP_DIR/datafiles
echo " Backing up the datafile $i ... OK!!! "
done
echo
echo
echo
echo
echo
echo "Starting the database ... "
dbstart
lsnrctl start tcp_listener
echo "gzip the datafiles ..."
delete -f $BACKUP_DIR/datafiles/*.gz
chmod 777 $BACKUP_DIR/datafiles/*.*
gzip -f $BACKUP_DIR/datafiles/*.*
echo "gzip the datafiles ... finished"
1.拷贝 冷备份文件
清单
BSYS_DISK1.dbf STORE_DATA_002.dbf log4ora7.dbf
configora7.ora log5ora7.dbf
LARGE_TRANS.dbf ctrl1ora7.ctl rbsora7.dbf
NEW.dbf ctrl2ora7.ctl store_data_003.dbf
STOCKINDEX_002.dbf ctrl3ora7.ctl storedisk03.dbf
STOCK_DATA.dbf gunzip storedisk_004.dbf
STOCK_INDEX.dbf gzip systora7.dbf
STOCK_INDEX_002.dbf initora7.ora tempora7.dbf
STOREDISK.dbf log01ora7.dbf toolora7.dbf
STOREDISK_002.dbf log02ora7.dbf usrora7.dbf
STOREINDEX.dbf log03ora7.dbf
oracle 用户登录
门店的脚本file_name.dat 是在原来的备份的时候有的
dbshut
#删除 FILE_NAME.DAT 的中文
for i in `cat file_name.dat`
do
echo " RM $i ... "
rm -f $i
echo " 删除数据库文件 $i ... OK!!! "
done
rm -f /home/oracle/dbs/initora7.ora
rm -f /home/oracle/dbs/configora7.ora
echo "覆盖原来的数据库文件..."
for i in `cat file_name.dat`
do
echo "解压缩 $i ... "
cd /home6/backup/datafiles/
gunzip ${i##*/}.gz
cp -f /home6/backup/datafiles/${i##*/} $i
echo " 覆盖数据库文件${i##*/}... OK!!! "
done
cp -f /home6/backup/datafiles/initora7.ora /home/oracle/dbs/initora7.ora
cp -f /home6/backup/datafiles/configora7.ora /home/oracle/dbs/configora7.ora
dbstart
结束后
ORA-07331: smsnsg: unable to allocate the variable portion of the SGA.
Additional information: 4
---上网查一查 发现 内核参数没有调账
命令行 / 参数 / 值 值 说明
# ../bin/idtune SHMMNI 200
# ../bin/idtune SEMMSL 150
# ../bin/idtune SCORLIM 0x7FFFFFFF
# ../bin/idtune HCORLIM 0x7FFFFFFF
# ../bin/idtune HFNOLIM 2048
# ../bin/idtune SFNOLIM 1024
# ../bin/idtune ARG_MAX 1048576
# ../bin/idtune NPBUF 100
# ../bin/idtune STRTHRESH 0x500000
有关内存的参数设置
# ../bin/idtune SHMMAX 536870912 物理内存的1/2 共享内存段最大尺寸
# ../bin/idtune SEMMNI 1024 系统共享内存段标识最大数目,默认autotune
# ../bin/idtune SHMSEG 15 每个进程所能使用最大共享内存段数目
# ../bin/idtune NPROC 1600
# ../bin/idtune MAXUP 1000
# ../bin/idtune SEMMNS 200
# ../bin/idtune SDATLIM 0x7FFFFFFF
# ../bin/idtune HDATLIM 0x7FFFFFFF
# ../bin/idtune SVMMLIM 0x7FFFFFFF
# ../bin/idtune HVMMLIM 0x7FFFFFFF
# ../bin/idtune SFSZLIM 0x7FFFFFFF
# ../bin/idtune HFSZLIM 0x7FFFFFFF
ORACLE需要的系统核心参数
# ../bin/idtune MSGMAX 8192 消息最大尺寸
# ../bin/idtune MSGMNB 81920 消息队列尺寸
# ../bin/idtune MSGMNI 2048 系统并存的最大消息队列数目,默认autotune
# ../bin/idtune MSGSSZ 16384
# ../bin/idtune MSGTQL 4096
/etc/conf/bin/idbuild
重启 出现这个问题
ORA-01157: cannot identify data file 20 - file not found
ORA-01110: data file 20: '/home/dbfiles/large_trans_002.dbf'
sqldba lmode=yes
connect internal
startup nomount;
alter database mount
alter database datafile '/home/dbfiles/large_trans_002.dbf' offline drop;
alter database open;
SQLDBA> alter database open;
ORA-01545: rollback segment 'LARGE_TRANS' specified not available
这个时候 直接去/home/oracle/dbs/initora7.ora 里面删除就可以了
重启数据库 解决问题了
rm -f $ORACLE_HOME/rdbms/log/*
su $ORACLE_USER -c "$ORACLE_HOME/bin/dbstart"
su $ORACLE_USER -c "$ORACLE_HOME/bin/lsnrctl start tcp_listener"
删除不用的表空间
ALTER TABLESPACE "NEW" OFFLINE NORMAL;
drop tablespace "NEW" INCLUDING CONTENTS AND DATAFILES;
在数据库中删除用户使用以下语句,可释放表空间.
SQL> drop user ops$edpman cascade;
User dropped.
遇到数据库回滚段满后
回滚段表空间占100%
重新启动数据库
重启动后，
alter rollback segment LARGE_TRANS shrink;
alter rollback segment LARGE_TRANS offline;
drop rollback segment LARGE_TRANS;
然后重新create
CREATE PUBLIC ROLLBACK SEGMENT "LARGE_TRANS"
TABLESPACE "LARGE_TRANS"
STORAGE ( INITIAL 1024K NEXT 128K);
ALTER ROLLBACK SEGMENT "LARGE_TRANS" ONLINE;
数据库环境：oracle server 7.2.3
系统环境：UnixWare test05 5 7.1.3 i386 x86at SCO UNIX_SVR5 25
删除表空间
ALTER TABLESPACE LARGE_TRANS OFFLINE NORMAL;
drop tablespace LARGE_TRANS;
测试发现
UNIX下的物理文件不会自动删除哦
ORA7>rm LARGE_TRANS.dbf
ORA7>rm NEW.dbf
重新创建表空间
CREATE TABLESPACE "NEW" DATAFILE '/home/oracle/dbs/NEW.dbf' SIZE 5M;
CREATE TABLESPACE "LARGE_TRANS" DATAFILE '/home/oracle/dbs/LARGE_TRANS.dbf' SIZE 5M;
1、开启归档
a. 关闭数据库shutdown immediate
b. startup mount
c. alter database archivelog
d. alter database opne
2、禁止归档
a. 关闭数据库shutdown immediate
b. startup mount
c. alter database noarchivelog
d. alter database open
归档信息可以通过如下语句查看
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination E:\oracle\ora92\database\archive
Oldest online log sequence 131
Next log sequence to archive 133
Current log sequence 133