1. 查看控制文件位置
SQL> show parameter control_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string C:\ORACLE11G\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL01.CTL, C:\ORACLE11G\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL02.CTL, C:\ORACLE11G\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL03.CTL
2. 关闭数据库
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
3. 复制控制文件
SQL> host copy C:\ORACLE11G\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL01.CTL C:\ORACLE11G\APP\ADMINISTRA
TOR\ORADATA\ORCL\CONTROL04.CTL;
已复制 1 个文件。
4.多个实例下的 listener.ora 配置 ,否则找不到tns
# listener.ora Network Configuration File: C:\oracle11g\app\Administrator\product\11.1.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL2)
(SID_NAME = ORCL2)
(ORACLE_HOME = C:/oracle11g/app/Administrator/product/11.1.0/db_1)
)
(SID_DESC =
(GLOBAL_DBNAME = ORCL)
(SID_NAME = ORCL)
(ORACLE_HOME = C:/oracle11g/app/Administrator/product/11.1.0/db_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = xue-ghia3pp3ijv)(PORT = 1521))
)
)
配置好以后重起监听服务
5. 连接数据库
sqlplus /nolog
SQL> conn /@orcl as sysdba;
已连接到空闲例程。
6. 启动数据库
SQL> startup nomount;
ORACLE 例程已经启动。
Total System Global Area 431038464 bytes
Fixed Size 1333676 bytes
Variable Size 310380116 bytes
Database Buffers 113246208 bytes
Redo Buffers 6078464 bytes
7. 修改控制文件位置
SQL> alter system set control_files="C:\ORACLE11G\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL01.CTL",
"C:\ORACLE11G\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL02.CTL",
"C:\ORACLE11G\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL03.CTL",
"C:\ORACLE11G\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL04.CTL"
scope=spfile;
系统已更改。
8.SQL> startup force;
ORACLE 例程已经启动。
Total System Global Area 431038464 bytes
Fixed Size 1333676 bytes
Variable Size 310380116 bytes
Database Buffers 113246208 bytes
Redo Buffers 6078464 bytes
数据库装载完毕。
数据库已经打开。
9. 查看控制文件
SQL> show parameter control_files;
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
control_files string C:\ORACLE11G\APP\ADMINISTRATOR
\ORADATA\ORCL\CONTROL01.CTL, C
:\ORACLE11G\APP\ADMINISTRATOR\
ORADATA\ORCL\CONTROL02.CTL, C:
\ORACLE11G\APP\ADMINISTRATOR\O
RADATA\ORCL\CONTROL03.CTL, C:\
ORACLE11G\APP\ADMINISTRATOR\OR
ADATA\ORCL\CONTROL04.CTL
SQL> create pfile from spfile;
文件已创建。
______________配置归档模式__________________________________________________________________________________________
1.创建保存备份的目录
md C:\oracle11g\app\Administrator\oradata\orcl\archive1
2. 以dba身分 登陆
sqlplus system/123456@orcl as sysdba;
3. 设置归档日志 目标地址
查看数据库是否在归档模式
SQL> select log_mode from v$database;
LOG_MODE
------------------------
NOARCHIVELOG
SQL> show parameter 'log_archive_dest_1'
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
log_archive_dest_1 string
SQL> alter system set log_archive_dest_1='location=C:\oracle11g\app\Administrator\oradata\orcl\archive1' scope=spfile;
系统已更改。
show parameter log_archive_format;
--设置日志文件名称格式 重起数据库后生效
alter system set log_archive_format='arch_%d_%t_%r_%s.log' scope=spfile;
4.关闭数据库
shutdown immediate;
5. 启动数据库并加载
startup mount;
6.将数据库切换 为归档模式
alter database archivelog;
SQL> select log_mode from v$database;
LOG_MODE
------------------------
ARCHIVELOG
7.打开数据库
alter database open;
8. 切换日志,强迫归档
alter system switch logfile;
9. SQL> select archiver from v$instance;
ARCHIVER
--------------
STARTED
--查看生成的归档文件全路经
select name from v$archived_log;
C:\ORACLE11G\APP\ADMINISTRATOR\ORADATA\ORCL\ARCHIVE1\ARCH_4C763F27_1_755037098_30.LOG
SQL> show parameter control_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string C:\ORACLE11G\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL01.CTL, C:\ORACLE11G\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL02.CTL, C:\ORACLE11G\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL03.CTL
2. 关闭数据库
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
3. 复制控制文件
SQL> host copy C:\ORACLE11G\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL01.CTL C:\ORACLE11G\APP\ADMINISTRA
TOR\ORADATA\ORCL\CONTROL04.CTL;
已复制 1 个文件。
4.多个实例下的 listener.ora 配置 ,否则找不到tns
# listener.ora Network Configuration File: C:\oracle11g\app\Administrator\product\11.1.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL2)
(SID_NAME = ORCL2)
(ORACLE_HOME = C:/oracle11g/app/Administrator/product/11.1.0/db_1)
)
(SID_DESC =
(GLOBAL_DBNAME = ORCL)
(SID_NAME = ORCL)
(ORACLE_HOME = C:/oracle11g/app/Administrator/product/11.1.0/db_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = xue-ghia3pp3ijv)(PORT = 1521))
)
)
配置好以后重起监听服务
5. 连接数据库
sqlplus /nolog
SQL> conn /@orcl as sysdba;
已连接到空闲例程。
6. 启动数据库
SQL> startup nomount;
ORACLE 例程已经启动。
Total System Global Area 431038464 bytes
Fixed Size 1333676 bytes
Variable Size 310380116 bytes
Database Buffers 113246208 bytes
Redo Buffers 6078464 bytes
7. 修改控制文件位置
SQL> alter system set control_files="C:\ORACLE11G\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL01.CTL",
"C:\ORACLE11G\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL02.CTL",
"C:\ORACLE11G\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL03.CTL",
"C:\ORACLE11G\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL04.CTL"
scope=spfile;
系统已更改。
8.SQL> startup force;
ORACLE 例程已经启动。
Total System Global Area 431038464 bytes
Fixed Size 1333676 bytes
Variable Size 310380116 bytes
Database Buffers 113246208 bytes
Redo Buffers 6078464 bytes
数据库装载完毕。
数据库已经打开。
9. 查看控制文件
SQL> show parameter control_files;
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
control_files string C:\ORACLE11G\APP\ADMINISTRATOR
\ORADATA\ORCL\CONTROL01.CTL, C
:\ORACLE11G\APP\ADMINISTRATOR\
ORADATA\ORCL\CONTROL02.CTL, C:
\ORACLE11G\APP\ADMINISTRATOR\O
RADATA\ORCL\CONTROL03.CTL, C:\
ORACLE11G\APP\ADMINISTRATOR\OR
ADATA\ORCL\CONTROL04.CTL
SQL> create pfile from spfile;
文件已创建。
______________配置归档模式__________________________________________________________________________________________
1.创建保存备份的目录
md C:\oracle11g\app\Administrator\oradata\orcl\archive1
2. 以dba身分 登陆
sqlplus system/123456@orcl as sysdba;
3. 设置归档日志 目标地址
查看数据库是否在归档模式
SQL> select log_mode from v$database;
LOG_MODE
------------------------
NOARCHIVELOG
SQL> show parameter 'log_archive_dest_1'
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
log_archive_dest_1 string
SQL> alter system set log_archive_dest_1='location=C:\oracle11g\app\Administrator\oradata\orcl\archive1' scope=spfile;
系统已更改。
show parameter log_archive_format;
--设置日志文件名称格式 重起数据库后生效
alter system set log_archive_format='arch_%d_%t_%r_%s.log' scope=spfile;
4.关闭数据库
shutdown immediate;
5. 启动数据库并加载
startup mount;
6.将数据库切换 为归档模式
alter database archivelog;
SQL> select log_mode from v$database;
LOG_MODE
------------------------
ARCHIVELOG
7.打开数据库
alter database open;
8. 切换日志,强迫归档
alter system switch logfile;
9. SQL> select archiver from v$instance;
ARCHIVER
--------------
STARTED
--查看生成的归档文件全路经
select name from v$archived_log;
C:\ORACLE11G\APP\ADMINISTRATOR\ORADATA\ORCL\ARCHIVE1\ARCH_4C763F27_1_755037098_30.LOG