NIO使用小错误
2019-06-13 本文已影响0人
chimpansee
今天做一项测试，需要修改db_name，按照手册使用nio修改db_name。
修改前先创建pfile文件。
[oracle@primary dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jun 13 10:53:48 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create pfile from spfile;
File created.
SQL>
执行修改db_name：
[oracle@primary dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jun 13 10:12:23 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> startup force mount
ORACLE instance started.
Total System Global Area 709836800 bytes
Fixed Size 2231752 bytes
Variable Size 448791096 bytes
Database Buffers 251658240 bytes
Redo Buffers 7155712 bytes
Database mounted.
SQL> host nid target=sys/123456 dbname=orcl
DBNEWID: Release 11.2.0.3.0 - Production on Thu Jun 13 10:13:13 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to database PRIMARY (DBID=1848246663)
NID-00135: There are 1 active threads
Change of database name failed during validation - database is intact.
DBNEWID - Completed with validation errors.
SQL> exit
错误：
NID-00135: There are 1 active threads
原来是数据库关闭的不干净。
重来一遍：
[oracle@primary dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jun 13 10:13:52 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 709836800 bytes
Fixed Size 2231752 bytes
Variable Size 448791096 bytes
Database Buffers 251658240 bytes
Redo Buffers 7155712 bytes
Database mounted.
Database opened.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 709836800 bytes
Fixed Size 2231752 bytes
Variable Size 448791096 bytes
Database Buffers 251658240 bytes
Redo Buffers 7155712 bytes
Database mounted.
SQL> startup mount
ORACLE instance started.
Total System Global Area 709836800 bytes
Fixed Size 2231752 bytes
Variable Size 448791096 bytes
Database Buffers 251658240 bytes
Redo Buffers 7155712 bytes
SQL> host nid target=sys/123456 dbname=orcl;
DBNEWID: Release 11.2.0.3.0 - Production on Thu Jun 13 10:20:56 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to database PRIMARY (DBID=1848246663)
Connected to server version 11.2.0
Control Files in database:
/home/oracle/app/oracle/oradata/PRIMARY/controlfile/o1_mf_gj1cg77l_.ctl
/home/oracle/app/oracle/fast_recovery_area/PRIMARY/controlfile/o1_mf_gj1cg783_.ct l
Change database ID and database name PRIMARY to ORCL? (Y/[N]) => y
Proceeding with operation
Changing database ID from 1848246663 to 1538641546
Changing database name from PRIMARY to ORCL
Control File /home/oracle/app/oracle/oradata/PRIMARY/controlfile/o1_mf_gj1cg77l_. ctl - modified
Control File /home/oracle/app/oracle/fast_recovery_area/PRIMARY/controlfile/o1_mf _gj1cg783_.ctl - modified
Datafile /home/oracle/app/oracle/oradata/PRIMARY/datafile/o1_mf_system_gj1cg8bv_. db - dbid changed, wrote new name
Datafile /home/oracle/app/oracle/oradata/PRIMARY/datafile/o1_mf_sysaux_gj1cgf3k_. db - dbid changed, wrote new name
Datafile /home/oracle/app/oracle/oradata/PRIMARY/datafile/o1_mf_undotbs1_gj1cgjw1 _.db - dbid changed, wrote new name
Datafile /home/oracle/app/oracle/oradata/PRIMARY/datafile/o1_mf_users_gj1cgqy0_.d b - dbid changed, wrote new name
Datafile /home/oracle/app/oracle/oradata/PRIMARY/datafile/o1_mf_temp_gj1cgkrh_.tm - dbid changed, wrote new name
Control File /home/oracle/app/oracle/oradata/PRIMARY/controlfile/o1_mf_gj1cg77l_. ctl - dbid changed, wrote new name
Control File /home/oracle/app/oracle/fast_recovery_area/PRIMARY/controlfile/o1_mf _gj1cg783_.ctl - dbid changed, wrote new name
SQL> Instance shut down
Database name changed to ORCL.
Modify parameter file and generate a new password file before restarting.
Database ID for database ORCL changed to 1538641546.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.
nio修改了所有数据文件和控制文件中关于db_name的信息。以前所有的备份都不可用了。
还需要修改参数文件，密码文件。
先修改环境变量:
[oracle@primary dbs]$ export ORACLE_SID=orcl
[oracle@primary dbs]$ mv orapwprimary orapworcl
重新命名pfile文件。打开pfile文件，将全部的旧的db_name，替换成新db_name。
控制文件路径保持不动，其他都能修改。
[oracle@primary dbs]$ cat initorcl.ora
orcl.__db_cache_size=255852544
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__oracle_base='/home/oracle/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=285212672
orcl.__sga_target=427819008
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=146800640
orcl.__streams_pool_size=4194304
*.audit_file_dest='/home/oracle/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/home/oracle/app/oracle/oradata/PRIAMRY/controlfile/o1_mf_gj1cg77l_.ctl','/home/oracle/app/oracle/fast_recovery_area/PRIMARY/controlfile/o1_mf_gj1cg783_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/home/oracle/app/oracle/oradata'
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/home/oracle/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=5218762752
*.diagnostic_dest='/home/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=713031680
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
手工创建审计文件目录，确保存在。
[oracle@primary dbs]$ mkdir -p /home/oracle/app/oracle/admin/orcl/
重新启动数据库。
[oracle@primary dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jun 13 10:53:48 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile;
File created.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 709836800 bytes
Fixed Size 2231752 bytes
Variable Size 444596792 bytes
Database Buffers 255852544 bytes
Redo Buffers 7155712 bytes
Database mounted.
SQL> alter database open resetlogs;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string orcl
db_unique_name string orcl
global_names boolean FALSE
instance_name string orcl
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string orcl
支持修改db_name完毕，我们可以看到连带db_unique_name一并给与了修改。
