linux 通过拷贝所有oracle文件进行数据库恢复

DBA狗剩儿

已于 2022-08-23 15:12:18 修改

阅读量719

点赞数

分类专栏：运维日常文章标签： oracle 数据库 linux

于 2022-08-23 11:34:51 首次发布

本文链接：https://blog.csdn.net/weixin_41607523/article/details/126480611

版权

运维日常专栏收录该内容

83 篇文章 9 订阅

订阅专栏

文章目录

前言
一、情况收集
二、尝试登录并解决相关错误
总结

前言

今天利用早上一个小时时间帮客户恢复了一套11g数据库，因为原生产服务器坏了无法启动，利用每日文件备份恢复时候客户起不来库。接下来我们就看看怎么回事。
备份的是/home整个目录恢复前按部署Oracle 11G的步骤操作了创建Oracle用户修改修改/etc下的配置等但恢复后备份/home目录直接把原目录都覆盖了 Oracle用户的home文件也没了，遂客户有创建了ora用户。

提示：以下是本篇文章正文内容，下面案例可供参考

一、情况收集

[root@localhost ~]# df -h
文件系统                 容量  已用  可用 已用% 挂载点
devtmpfs                  32G     0   32G    0% /dev
tmpfs                     32G   12M   32G    1% /run
tmpfs                     32G     0   32G    0% /sys/fs/cgroup
/dev/mapper/centos-root  7.3T  361G  6.9T    5% /
tmpfs                    6.3G   64K  6.3G    1% /run/user/0
/dev/loop0               4.7T   62G  4.4T    2% /home
[root@localhost ~]# cd /home/
[root@localhost home]# id oracle
id: oracle: no such user
[root@localhost home]# 
[root@localhost home]# 
[root@localhost home]# id ora
uid=1002(ora) gid=1004(ora) 组=1004(ora)
[root@localhost home]# 
[root@localhost home]# 
[root@localhost home]# cd /home
[root@localhost home]# ls
guaranty  image  lost+found  ora  oracle  sfuser  system  u01  ztbsj  ztbyw
[root@localhost home]# 
[root@localhost home]# cd u01
[root@localhost u01]# ls
app  archlog
[root@localhost u01]# cd app/
[root@localhost app]# ls
oracle  oraInventory
[root@localhost app]# cd oracle
[root@localhost oracle]# 
[root@localhost oracle]# 
[root@localhost oracle]# ls
admin  cfgtoollogs  checkpoints  db  diag  flash_recovery_area  oradata
[root@localhost oracle]# cd oradata
[root@localhost oradata]# ls
imagedb
[root@localhost oradata]# 
[root@localhost oradata]# cd imagedb
[root@localhost imagedb]# 
[root@localhost imagedb]# ls
control01.ctl  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf
[root@localhost imagedb]# 
[root@localhost imagedb]# du -sh *
9.8M    control01.ctl
51M     redo01.log
51M     redo02.log
51M     redo03.log
1001M   sysaux01.dbf
711M    system01.dbf
13M     temp01.dbf
686M    undotbs01.dbf
522M    users01.dbf
[root@localhost imagedb]#

二、尝试登录并解决相关错误

1.权限不足

[ora@localhost oracle]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 23 09:56:43 2022

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

ERROR:
ORA-01031: insufficient privileges
#这里我为了节省时间，直接删除重新创建ora用户
[root@localhost imagedb]# useradd -g oiinstall -g dba -m ora
useradd：“oiinstall”组不存在
[root@localhost imagedb]# useradd -g oinstall -g dba -m ora
useradd：用户“ora”已存在
[root@localhost imagedb]# userdel -r ora
[root@localhost imagedb]# 
[root@localhost imagedb]# useradd -g oinstall -g dba -m ora
[root@localhost imagedb]# 
[root@localhost imagedb]# 
[root@localhost imagedb]# passwd ora
[root@localhost imagedb]#chown -R ora:oinstall /home

2.修改ora环境变量

[ora@localhost imagedb]# cat /home/ora/.bash_profile 
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin

export PATH
export ORACLE_BASE=/home/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/db
export ORACLE_SID=imagedb
export ORACLE_UNQNAME=imagedb
export ORACLE_TERM=xterm
export PATH=$ORACLE_HOME/bin:/usr/sbin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib 
export LANG=C
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
[ora@localhost imagedb]# 
[ora@localhost ~]$ source .bash_profile

3.修改内核参数

[root@localhost ~]# sh sysfile.sh 
[root@localhost ~]# 
[root@localhost ~]# sysctl -p
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 16394776
kernel.shmmax = 67153002495
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.ipv4.conf.em1.rp_filter = 1

4.启动数据库出现ORA-00845

[ora@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 23 10:14:05 2022

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> 
SQL> 
SQL> startup
ORA-00845: MEMORY_TARGET not supported on this system
SQL> 
SQL> 
SQL> !df -h
Filesystem               Size  Used Avail Use% Mounted on
devtmpfs                  32G     0   32G   0% /dev
tmpfs                     32G   12M   32G   1% /run
tmpfs                     32G     0   32G   0% /sys/fs/cgroup
/dev/mapper/centos-root  7.3T  361G  6.9T   5% /
tmpfs                    6.3G   64K  6.3G   1% /run/user/0
/dev/loop0               4.7T   62G  4.4T   2% /home

在oracle database 11g中新增的内存自动管理的参数MEMORY_TARGET，它能自动调整SGA和PGA，这个特性需要用到/dev/shm共享文件系统，而且要求/dev/shm必须大于MEMORY_TARGET，如果/dev/shm比MEMORY_TARGET小就会报错。
确定问题点，那么我们直接修改pfile文件不使用自动内存管理，最后通过pfile启动。

5.修改pfile并启动

SQL> create pfile='/home/pfile.txt' from spfile;

File created.

[ora@localhost shm]$ cat /home/pfile.txt 
imagedb.__db_cache_size=23353884672
imagedb.__java_pool_size=268435456
imagedb.__large_pool_size=268435456
imagedb.__oracle_base='/home/u01/app/oracle'#ORACLE_BASE set from environment
imagedb.__pga_aggregate_target=21743271936
imagedb.__sga_target=32480690176
imagedb.__shared_io_pool_size=0
imagedb.__shared_pool_size=8321499136
imagedb.__streams_pool_size=0
*.audit_file_dest='/home/u01/app/oracle/admin/imagedb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/home/u01/app/oracle/oradata/imagedb/control01.ctl','/home/u01/app/oracle/flash_recovery_area/imagedb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='imagedb'
*.db_recovery_file_dest='/home/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/home/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=imagedbXDB)'
#*.memory_target=54034169856
*.sga_max_size=38654705664
*.sga_target=38654705664
*.pga_aggregate_target=12884901888**
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

修改项：
#*.memory_target=54034169856
*.sga_max_size=38654705664
*.sga_target=38654705664
*.pga_aggregate_target=12884901888**

6.启动数据库

SQL> startup pfile='/home/pfile.txt';
ORACLE instance started.

Total System Global Area 3.8482E+10 bytes
Fixed Size                  2215704 bytes
Variable Size            8858370280 bytes
Database Buffers         2.9528E+10 bytes
Redo Buffers               93642752 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[ora@localhost shm]$ 
[ora@localhost shm]$ 
[ora@localhost shm]$ 
[ora@localhost shm]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 23-AUG-2022 10:25:29

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Starting /home/u01/app/oracle/db/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Log messages written to /home/u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                23-AUG-2022 10:25:29
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /home/u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
The listener supports no services
The command completed successfully
[ora@localhost shm]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 23 10:26:30 2022

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> 
SQL> 
SQL> create spfile from pfile='/home/pfile.txt';

File created.

SQL> !lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 23-AUG-2022 10:28:01

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                23-AUG-2022 10:25:29
Uptime                    0 days 0 hr. 2 min. 32 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /home/u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Services Summary...
Service "imagedb" has 1 instance(s).
  Instance "imagedb", status READY, has 1 handler(s) for this service...
Service "imagedbXDB" has 1 instance(s).
  Instance "imagedb", status READY, has 1 handler(s) for this service...
The command completed successfully

总结

至此数据库恢复完成了，以上处理过程以及思路供大家参考。

DBA狗剩儿

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
打赏
0
评论
linux 通过拷贝所有oracle文件进行数据库恢复

今天利用早上一个小时时间帮客户恢复了一套11g数据库，因为原生产服务器坏了无法启动，利用每日文件备份恢复时候客户起不来库。接下来我们就看看怎么回事。备份的是/home整个目录恢复前按部署Oracle 11G的步骤操作了创建Oracle用户修改修改/etc下的配置等但恢复后备份/home目录直接把原目录都覆盖了 Oracle用户的home文件也没了，遂客户有创建了ora用户。提示：以下是本篇文章正文内容，下面案例可供参考至此数据库恢复完成了，以上处理过程以及思路供大家参考。
复制链接

扫一扫