sql> alter database backup controlfile to trace;
This will put the create database syntax in the trace file directory.
The trace keyword tells oracle to generate a script containing a create controlfile command and store it in the trace directory identified in the user_dump_dest parameter of the init.ora file. It will look something like this:
Trong thư mục user_dump_dest file kinhdoanh_ora_11774.trc vừa dump ra
/u01/app/oracle/admin/kinhdoanh/udump
[oracle@c4is-billing-s kinhdoanh]$ls
adump bdump cdump dpdump pfile udump
- Tìm và thay thế tất cả kinhdoanh thành utility
- Sửa lại câu
CREATE CONTROLFILE REUSE DATABASE "KINHDOAN" NORESETLOGS ARCHIVELOG
thành
CREATE CONTROLFILE SET DATABASE "UTILITY" RESETLOGS NOARCHIVELOG
VD: File chuẩn db_create_controlfile.sql
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "UTILITY" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 1168
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/utility/redo01.log' SIZE 50M,
GROUP 2 '/u01/app/oracle/oradata/utility/redo02.log' SIZE 50M,
GROUP 3 '/u01/app/oracle/oradata/utility/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u02/oradata/utility/system01.dbf',
'/u02/oradata/utility/undotbs01.dbf',
'/u02/oradata/utility/sysaux01.dbf',
'/u02/oradata/utility/users01.dbf',
'/u02/oradata/utility/data01.dbf',
'/u02/oradata/utility/data02.dbf',
'/u02/oradata/utility/data03.dbf',
'/u02/oradata/utility/data04.dbf',
'/u02/oradata/utility/data05.dbf',
'/u02/oradata/utility/data06.dbf',
'/u02/oradata/utility/data07.dbf',
'/u02/oradata/utility/data08.dbf',
'/u02/oradata/utility/data09.dbf',
'/u02/oradata/utility/data10.dbf',
'/u02/oradata/utility/data11.dbf',
'/u02/oradata/utility/data12.dbf',
'/u02/oradata/utility/data13.dbf',
'/u02/oradata/utility/data14.dbf',
'/u02/oradata/utility/data15.dbf',
'/u02/oradata/utility/data_ivr_01.dbf',
'/u02/oradata/utility/data_ivr_02.dbf',
'/u02/oradata/utility/data_ivr_03.dbf',
'/u02/oradata/utility/data_ivr_04.dbf',
'/u02/oradata/utility/data16.dbf',
'/u02/oradata/utility/data17.dbf',
'/u02/oradata/utility/data18.dbf',
'/u02/oradata/utility/data19.dbf',
'/u02/oradata/utility/data20.dbf',
'/u02/oradata/utility/data21.dbf',
'/u02/oradata/utility/system02.dbf'
CHARACTER SET AL32UTF8
;
ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/utility/temp1_02.dbf'
SIZE 4096M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/utility/temp1_01.dbf'
SIZE 2048M REUSE AUTOEXTEND OFF;
STEP 2: Shutdown the old database
STEP 3: Copy all data files into the new directories on the new server. You may change the file names if you want, but you must edit the controlfile to reflect the new data files names on the new server.
cp /u03/oracle/oradata/kinhdoanh/* /u03/oracle/oradata/utility
cp /u02/oracle/oradata/kinhdoanh/* /u02/oracle/oradata/utility
cp /u01/app/oracle/oradata/kinhdoanh/* /u01/app/oracle/oradata/utility
STEP 4: Create the bdump, udump and cdump directories
cd $DBA/admin
cd kinhdoanh
mkdir cdump
mkdir dpdump
mkdir pfile
mkdir adump
mkdir udump
mkdir bdump
STEP 8: Copy-over the old init.ora file
rcp $ORACLE_BASE/admin/kinhdoanh/pfile/*.ora newhost:/u01/oracle/admin/utility/pfile
Step 5: Global replace kinhdoanh to utility in *.ora
STEP 6: Start the new database
sql> startup nomount;
sql> @db_create_controlfile.sql
sql> RECOVER DATABASE
sql> ALTER DATABASE OPEN;
Trong trường hợp shutdown copy data file( system02, undotbs01,sysaux01,users01 ) ở bước 3 xong, copy start database lên => copy tiếp các datafile còn lại sẽ bị mất đồng bộ
Cách xữ lý :
SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01152: file 5 was not restored from a sufficiently old backup
ORA-01110: data file 5: '/u02/oradata/utility/data01.dbf'
Copy hết cái archivelog mới qua server mới
SQL> recover database using backup controlfile;
ORA-00279: change 10911249216749 generated at 07/14/2012 10:10:01 needed for
thread 1
ORA-00289: suggestion : /u02/archivelog_10g/utility/1_6955_747570143.dbf
ORA-00280: change 10911249216749 for thread 1 is in sequence #6955
Specify log: {
AUTO
Chọn AUTO để tiến hành lấy lấy các dữ liệu thời điểm bị mất
Trong trường hợp thiếu archivelog
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 10911342402108 generated at 07/14/2012 14:24:11 needed for
thread 1
ORA-00289: suggestion : /u02/archivelog_10g/utility/1_6965_747570143.dbf
ORA-00280: change 10911342402108 for thread 1 is in sequence #6965
Specify log: {
CANCEL
Media recovery cancelled.
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
=====================================
bash-3.00$ id
uid=100(oracle) gid=100(oinstall)
bash-3.00$ . ~/profile_11g.sh
start
/tmp
/u01/app/oracle/product/11.2.0.3/db_1/dg4msql/driver/lib:/u01/app/oracle/product/11.2.0.3/db_1/lib
TT4-SMS-S
/u01/app/oracle/product/11.2.0.3/db_1
sms
bash-3.00$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Sat Nov 17 16:05:54 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup pfile='/tmp/initsms.ora'
ORACLE instance started.
Total System Global Area 2038276096 bytes
Fixed Size 2160776 bytes
Variable Size 1157629816 bytes
Database Buffers 855638016 bytes
Redo Buffers 22847488 bytes
Database mounted.
Database opened.
SQL> alter database backup controlfile to trace;
Database altered.
SQL> show parameter user_dump_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string /u01/app/oracle/diag/rdbms/sms
/sms/trace
bash-3.00$ cd /u01/app/oracle/diag/rdbms/sms/sms/trace
bash-3.00$ cp sms_ora_4466.trc /tmp/
bash-3.00$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Sat Nov 17 16:15:50 2012
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;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
bash-3.00$ cd /tmp/
bash-3.00$ vi sms_ora_4466.trc
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "SMS" RESETLOGS NOARCHIVELOG
MAXLOGFILES 40
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 13175
LOGFILE
GROUP 1 (
'/u01/app/oracle/oradata/sms/redo01_1.log',
'/u01/app/oracle/oradata/sms/redo01_2.log',
'/u01/app/oracle/oradata/sms/redo01_3.log',
'/u01/app/oracle/oradata/sms/redo01_4.log'
) SIZE 150M BLOCKSIZE 512,
GROUP 2 (
'/u04/logfile/redo02_1.log',
'/u04/logfile/redo02_2.log',
'/u04/logfile/redo02_3.log',
'/u04/logfile/redo02_4.log'
) SIZE 150M BLOCKSIZE 512,
GROUP 3 (
'/u03/logfile_sms/redo03_1.log',
'/u03/logfile_sms/redo03_2.log',
'/u03/logfile_sms/redo03_3.log',
'/u03/logfile_sms/redo03_4.log'
) SIZE 150M BLOCKSIZE 512,
GROUP 4 (
'/u02/logfile_sms/redo04_1.log',
'/u02/logfile_sms/redo04_2.log',
'/u02/logfile_sms/redo04_3.log',
'/u02/logfile_sms/redo04_4.log'
) SIZE 150M BLOCKSIZE 512,
GROUP 5 (
'/u03/logfile_sms/redo05_1.log',
'/u03/logfile_sms/redo05_2.log',
'/u03/logfile_sms/redo05_3.log',
'/u03/logfile_sms/redo05_4.log'
) SIZE 150M BLOCKSIZE 512,
GROUP 6 (
'/u02/logfile_sms/redo06_1.log',
'/u02/logfile_sms/redo06_2.log',
'/u02/logfile_sms/redo06_3.log',
'/u02/logfile_sms/redo06_4.log'
) SIZE 150M BLOCKSIZE 512,
GROUP 7 (
'/u04/logfile/redo07_1.log',
'/u04/logfile/redo07_2.log',
'/u04/logfile/redo07_3.log',
'/u04/logfile/redo07_4.log'
) SIZE 150M BLOCKSIZE 512,
GROUP 8 (
'/u01/app/oracle/oradata/sms/redo08_1.log',
'/u01/app/oracle/oradata/sms/redo08_2.log',
'/u01/app/oracle/oradata/sms/redo08_3.log',
'/u01/app/oracle/oradata/sms/redo08_4.log'
) SIZE 150M BLOCKSIZE 512,
GROUP 9 (
'/u02/logfile_sms/redo09_1.log',
'/u02/logfile_sms/redo09_2.log',
'/u02/logfile_sms/redo09_3.log',
'/u02/logfile_sms/redo09_4.log'
) SIZE 150M BLOCKSIZE 512,
GROUP 10 (
'/u02/logfile_sms/redo10_1.log',
'/u02/logfile_sms/redo10_2.log',
'/u02/logfile_sms/redo10_3.log',
'/u02/logfile_sms/redo10_4.log'
) SIZE 150M BLOCKSIZE 512,
GROUP 11 (
'/u03/logfile_sms/redo11_1.log',
'/u03/logfile_sms/redo11_2.log',
'/u03/logfile_sms/redo11_3.log',
'/u03/logfile_sms/redo11_4.log'
) SIZE 150M BLOCKSIZE 512,
GROUP 12 '/u02/logfile_sms/redo12_1.log' SIZE 150M BLOCKSIZE 512,
GROUP 13 '/u02/logfile_sms/redo13_1.log' SIZE 150M BLOCKSIZE 512,
GROUP 14 '/u02/logfile_sms/redo14_1.log' SIZE 150M BLOCKSIZE 512,
GROUP 15 (
'/u02/logfile_sms/redo15_1.log',
'/u02/logfile_sms/redo15_2.log',
'/u02/logfile_sms/redo15_3.log'
) SIZE 150M BLOCKSIZE 512,
GROUP 16 (
'/u02/logfile_sms/redo16_1.log',
'/u02/logfile_sms/redo16_2.log',
'/u02/logfile_sms/redo16_3.log'
) SIZE 150M BLOCKSIZE 512,
GROUP 17 (
'/u02/logfile_sms/redo17_1.log',
'/u02/logfile_sms/redo17_2.log',
'/u02/logfile_sms/redo17_3.log'
) SIZE 150M BLOCKSIZE 512,
GROUP 18 (
'/u02/logfile_sms/redo18_1.log',
'/u02/logfile_sms/redo18_2.log',
'/u02/logfile_sms/redo18_3.log'
) SIZE 150M BLOCKSIZE 512,
GROUP 19 (
'/u02/logfile_sms/redo19_1.log',
'/u02/logfile_sms/redo19_2.log',
'/u02/logfile_sms/redo19_3.log'
) SIZE 150M BLOCKSIZE 512,
GROUP 20 (
'/u02/logfile_sms/redo20_1.log',
'/u02/logfile_sms/redo20_2.log',
'/u02/logfile_sms/redo20_3.log'
) SIZE 150M BLOCKSIZE 512,
GROUP 21 (
'/u02/logfile_sms/redo21_1.log',
'/u02/logfile_sms/redo21_2.log',
'/u02/logfile_sms/redo21_3.log'
) SIZE 150M BLOCKSIZE 512,
GROUP 22 (
'/u02/logfile_sms/redo22_1.log',
'/u02/logfile_sms/redo22_2.log',
'/u02/logfile_sms/redo22_3.log'
) SIZE 150M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u02/oradata/sms/system01.dbf',
'/u02/oradata/sms/sysaux01.dbf',
'/u02/oradata/sms/undotbs01.dbf',
'/u02/oradata/sms/users01.dbf',
'/u02/oradata/sms/data01.dbf',
'/u02/oradata/sms/data02.dbf',
'/u02/oradata/sms/data03.dbf',
'/u02/oradata/sms/data04.dbf',
'/u02/oradata/sms/data05.dbf',
'/u02/oradata/sms/data06.dbf',
'/u02/oradata/sms/data07.dbf',
'/u02/oradata/sms/data08.dbf',
'/u03/oradata/sms/data09.dbf',
'/u03/oradata/sms/data10.dbf',
'/u03/oradata/sms/data11.dbf',
'/u03/oradata/sms/data12.dbf',
'/u03/oradata/sms/data13.dbf',
'/u03/oradata/sms/data14.dbf',
'/u03/oradata/sms/data15.dbf',
'/u03/oradata/sms/data16.dbf',
'/u03/oradata/sms/data17.dbf',
'/u03/oradata/sms/data18.dbf',
'/u03/oradata/sms/data19.dbf',
'/u03/oradata/sms/data20.dbf',
'/u03/oradata/sms/data21.dbf',
'/u03/oradata/sms/data22.dbf',
'/u03/oradata/sms/data23.dbf',
'/u03/oradata/sms/data24.dbf',
'/u03/oradata/sms/data25.dbf',
'/u03/oradata/sms/data26.dbf',
'/u03/oradata/sms/data27.dbf',
'/u03/oradata/sms/data28.dbf',
'/u03/oradata/sms/data29.dbf',
'/u03/oradata/sms/data30.dbf'
CHARACTER SET AL32UTF8
;
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/sms/temp01.dbf'
SIZE 1040187392 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
======================
cd /u01/app/oracle/product/11.2.0.3/db_1/dbs
bash-3.00$ scp initsms.ora oracle@10.151.120.244:/u02/app/oracle/product/11.2.0.3.0/db_1/dbs/
bash-3.2$ chmod 644 initsms.ora
audit_file_dest="/u02/app/oracle/admin/sms/adump"
audit_trail=DB
compatible=11.2.0.0.0
control_files=/u02/app/oracle/oradata/sms/control01.ctl, /u02/app/oracle/oradata/sms/control02.ctl
db_block_size=8192
db_domain=""
db_name="sms"
diagnostic_dest=/u02/app/oracle
log_archive_dest_1='LOCATION=/u02/archivelog'
log_archive_format=%t_%s_%r.dbf
memory_target=2046820352 => chú ý chỉnh ram cho vừa phải để tránh lỗi SQL> startup nomount; Segmentation Fault
open_cursors=300
processes=300
remote_login_passwordfile=EXCLUSIVE
sessions=480
undo_tablespace=UNDOTBS1
event=""
Vào thư mục oracle home
bash-3.2$ pwd
/u02/app/oracle/admin
bash-3.2$ mkdir sms
bash-3.2$ ls -ltr
total 7
drwxr-x--- 8 oracle oinstall 8 Apr 7 2012 kinhdoanh
drwxr-xr-x 8 oracle oinstall 8 Apr 7 2012 bcclml
drwxr-xr-x 2 oracle oinstall 2 Nov 17 18:31 sms
bash-3.2$ cd sms/
mkdir cdump
mkdir dpdump
mkdir pfile
mkdir adump
mkdir udump
mkdir bdump
bash-3.2$ cd /u02/app/oracle/oradata/
bash-3.2$ ls
bcclml kinhdoanh
bash-3.2$ mkdir sms
bash-3.2# su - oracle
Oracle Corporation SunOS 5.10 Generic Patch January 2005
bcclml
$ id
uid=100(oracle) gid=100(oinstall)
$ bash
bash-3.2$ echo $ORACLE_SID
bcclml
Chý ý cần đổi tên ORACLE_SI
bash-3.2$ export ORACLE_SID=sms
bash-3.2$ echo $ORACLE_SID
sms
bash-3.2$ cd /tmp/
bash-3.2$
bash-3.2$ echo $ORACLE_SID
sms
bash-3.2$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Sat Nov 17 18:36:45 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL>
bash-3.2$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Sat Nov 17 18:43:50 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 2472673280 bytes
Fixed Size 2161824 bytes
Variable Size 1392509792 bytes
Database Buffers 1056964608 bytes
Redo Buffers 21037056 bytes
SQL> @/tmp/sms_ora_4466.trc
ORA-01081: cannot start already-running ORACLE - shut it down first
Control file created.
Database altered.
Tablespace altered.
SQL> create spfile from pfile;
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 7451451392 bytes
Fixed Size 2173776 bytes
Variable Size 3841987760 bytes
Database Buffers 3590324224 bytes
Redo Buffers 16965632 bytes
Database mounted.
Database opened.
echo $ORACLE_HOME
SQL> var OHM varchar2(100);
SQL> EXEC dbms_system.get_env('ORACLE_HOME', :OHM) ;
PL/SQL procedure successfully completed.
SQL> PRINT OHM
OHM
$ORACLE_HOME/bin>sqlplus sys/
eg.
D:\10205\BIN>sqlplus sys/oracle1@testdb as sysdba
SQL> show parameter remote
NAME TYPE VALUE
------------------------------------ ----------- --------------
remote_login_passwordfile string EXCLUSIVE
NAME TYPE VALUE
------------------------------------ ----------- --------------
remote_login_passwordfile string EXCLUSIVE
If this parameter is not set to 'EXCLUSIVE', the following command can be run:-
alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile;
Unix:
$ orapwd file=$ORACLE_HOME/dbs/orapw<$ORACLE_SID> password=sys entries=5
$ orapwd file=$ORACLE_HOME/dbs/orapw<$ORACLE_SID> password=sys entries=5
select * from v$pwfile_users;
USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
$ORACLE_HOME/bin>sqlplus sys/ as sysdba
No comments:
Post a Comment