cold back up the database and modify the nls

本文链接：https://blog.youkuaiyun.com/yfleng2002/article/details/7046470

本文详细介绍了数据库备份流程及管理系统配置优化步骤，包括查询文件信息、生成备份脚本、系统关闭与启动、修改ls_character参数等关键操作，旨在提高数据库备份效率与安全性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1. Backup the database full.

1.1. query the file information

--After execute the information, save it to a file to query it later.

set echo off
conn /as sysdba
set head on
set pages 100
col file_type format a8
col file_name format a50
select * from (
select 'SPFILE' as "file_type",value as "file_name",1 as "file_size(M)" from v$parameter where  name ='spfile'
UNION
select 'CONTROL' as "file_type",name as "file_name",round(BLOCK_SIZE*FILE_SIZE_BLKS/1024/1024,2) as "file_size(M)" from v$controlfile
UNION
select  'REDOLOG' as "file_type",v$logfile.member as "file_name",round(v$log.bytes/1024/1024,2) as "file_size(M)" from v$log,v$logfile where v$log.group#=v$logfile.group#
UNION
select 'TEMP' as "file_type",name as "file_name",round(bytes/1024/1024,2) as "file_size(M)" from v$tempfile
UNION
select 'DATAFILE' as "file_type",name as "file_name",round(bytes/1024/1024,2) as "file_size(M)" from v$datafile
);

select sum("file_size(M)") as "total_size(M)" from (
select 'SPFILE' as "file_type",value as "file_name",1 as "file_size(M)" from v$parameter where  name ='spfile'
UNION
select 'CONTROL' as "file_type",name as "file_name",round(BLOCK_SIZE*FILE_SIZE_BLKS/1024/1024,2) as "file_size(M)" from v$controlfile
UNION
select  'REDOLOG' as "file_type",v$logfile.member as "file_name",round(v$log.bytes/1024/1024,2) as "file_size(M)" from v$log,v$logfile where v$log.group#=v$logfile.group#
UNION
select 'TEMP' as "file_type",name as "file_name",round(bytes/1024/1024,2) as "file_size(M)" from v$tempfile
UNION
select 'DATAFILE' as "file_type",name as "file_name",round(bytes/1024/1024,2) as "file_size(M)" from v$datafile
);

1.2.generate the backup scripts in linux/unix.

--After execute the information, save it to a file to query it later.

conn /as sysdba
set head off
set feedback off
set pages 0
set echo off
select 'cp '||"file_name"||' &1' from (
select 'SPFILE' as "file_type",value as "file_name",1 as "file_size(M)" from v$parameter where  name ='spfile'
UNION
select 'CONTROL' as "file_type",name as "file_name",round(BLOCK_SIZE*FILE_SIZE_BLKS/1024/1024,2) as "file_size(M)" from v$controlfile
UNION
select  'REDOLOG' as "file_type",v$logfile.member as "file_name",round(v$log.bytes/1024/1024,2) as "file_size(M)" from v$log,v$logfile where v$log.group#=v$logfile.group#
UNION
select 'TEMP' as "file_type",name as "file_name",round(bytes/1024/1024,2) as "file_size(M)" from v$tempfile
UNION
select 'DATAFILE' as "file_type",name as "file_name",round(bytes/1024/1024,2) as "file_size(M)" from v$datafile
);

1.3. shutdown the database.

conn /as sysdba
shutdown immediate;

1.4.After completely shutdown the db, execute the OS scripts &1 from step 2.
After execute the copy process, nagative to the db_backup folder and check the backup is complete.

1.5.startup the database.

conn /as sysdba
startup open;

2. Modify the nls_character

conn / as sysdba;
select userenv('language') from dual;
shutdown immediate;
startup mount;
show parameter job_queue_processes;
show parameter  AQ_TM_PROCESSES;
alter system enable restricted session;
alter system set job_queue_processes =0;
alter system set aq_tm_processes = 0;
alter database open;
alter database character set INTERNAL_USE WE8MSWIN1252;
select userenv('language') from dual;
alter system set job_queue_processes =&1;
alter system set aq_tm_processes = &2;
alter system disable restricted session;
prompt complete.