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

    文章目录

    前言

    今天利用早上一个小时时间帮客户恢复了一套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
    • 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
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53

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

    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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20

    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 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24

    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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22

    在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**

    • 1
    • 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
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39

    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

    • 1
    • 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
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84

    总结

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

  • 相关阅读:
    uni-app华为审核被拒，驳回原因：您的应用在运行时，未见向用户告知权限申请的目的
    利用OpenCV实现图片中导线的识别
    [Spring Cloud] Eureka Server安装
    JSP笔记
    UNDO自动管理和手工管理、闪回操作
    Qt http get请求数据阻塞和非阻塞实现源码
    人工智能学习03——mnist手写数字实战
    【英语语法】定语从句
    Android抓包工具——Fiddler
    年搜索量超 7 亿次背后：这款 APP 用火山引擎 DataTester 完成“数据驱动”
  • 原文地址：https://blog.csdn.net/weixin_41607523/article/details/126480611