顯示具有 oracle 標籤的文章。 顯示所有文章
顯示具有 oracle 標籤的文章。 顯示所有文章

2020年2月2日 星期日

手工配置tnsnames.ora的空格问题




本文给出一个手工配置tnsnames.ora极易出错的一个案例:谨慎处理空格!

1.标准的tnsnames.ora内容
ora10g@secdb /home/oracle$ vi $ORACLE_HOME/network/admin/tnsnames.ora
……
ORA10G =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = secdb)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora10g)
    )
  )
……

标准的配置以精美的缩进展示了连接串的配置,很容易阅读。
建议以此为模板去配置其他的连接串。

2.其他形式的配置
1)无缩进形式
ORA10G =
(DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = secdb)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = ora10g)
 )
)

上面这种书写形式没有缩进,因此可阅读性就大大地降低了。不过这种形式是可行的。
这里需要提醒的是:连接串名需要顶头书写,数据库连接串定义中除首尾括号外不允许其他内容顶头书写!(后面会有错误例子)

2)一行搞定一切
ORA10G=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=secdb)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ora10g)))



这是最精简的一种配置,但是缺点非常显然:很难快速的理清语法关系。这种配置方法可集成在脚本中,达到快速编辑测试环境下的tnsnames.ora配置文件的目的。


ORA10G =
(DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = secdb)(PORT = 1521))
 (CONNECT_DATA =
(SERVER = DEDICATED)
 (SERVICE_NAME = ora10g)
 )
)

紅色部份需縮排,無縮排即無法使用


————————————————
版权声明:本文为CSDN博主「半支烟丶」的原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/q82272513/article/details/50856493


2017年4月22日 星期六

sqlplus color.sql



If you have seen a colored scripts like a fish from “Session Snapper v.4” by Tanel Poder or OraLatencyMap by Luca Canali, you may be also want to colorize your scripts.
I’ve created the script for this purposes with predefined substitution variables.
Just download colors.sql and use it like that:



in VI  插入 ESC 符號

Hi,
I need to send "^[" command to the buffer.
I tried to use insert within VIM and press 'ctrl' key and then '^' and '[' key.
but it didn't work.
CTRL + V +[
^[

在 Notepad 插入 ESC 符號
ALT + 27
出現  ESC

select open_mode
    , case  open_mode
when 'READ WRITE' then ' [42m OK [0m'
when 'READ ONLY' then  ' [42m NG!~~ [0m'
when 'MOUNTED' then    ' [41m NG!! [0m'
end  STATUS
from v$database;


@colors.sql;
prompt ::: &_C_RED ***  TEST PASSED  *** &_C_RESET :::
prompt ::: &_C_RED *** &_C_BLINK TEST PASSED &_C_BLINK_OFF *** &_C_RESET :::

 prompt :::  &_C_RED SQL> [0m  abcd


set sqlprompt '^[[31mSQL> ^[[0m'


set sqlprompt 'ESC[31mSQL> ESC[0m'
SQL> 

set sqlprompt 'ESC[32mSQL> ESC[0m'
SQL> 


## Production
set sqlprompt '^[[25;1;41mSQL> ^[[0m'
SQL> 


set sqlprompt '^[[37;1;44mSQL> ^[[0m'
SQL> 



def _C_RESET =^[[0m
-- _C_RESET can be simply =^[[m

def _C_BOLD =^[[1m
def _C_BOLD_OFF =^[[21m

def _C_UNDERLINE =^[[4m
def _C_UNDERLINE_OFF=^[[24m

def _C_BLINK =^[[5m
def _C_BLINK_OFF =^[[25m

def _C_REVERSE =^[[7m
def _C_REVERSE_OFF =^[[27m

def _C_HIDE =^[[8m
def _C_HIDE_OFF =^[[28m

def _C_BLACK =^[[30m
def _C_RED =^[[31m
def _C_GREEN =^[[32m
def _C_YELLOW =^[[33m
def _C_BLUE =^[[34m
def _C_MAGENTA =^[[35m
def _C_CYAN =^[[36m
def _C_WHITE =^[[37m
def _C_DEFAULT =^[[39m

def _CB_BLACK =^[[40m
def _CB_RED =^[[41m
def _CB_GREEN =^[[42m
def _CB_YELLOW =^[[43m
def _CB_BLUE =^[[44m
def _CB_MAGENTA =^[[45m
def _CB_CYAN =^[[46m
def _CB_WHITE =^[[47m
def _CB_DEFAULT =^[[49m

REM just addition variables for convenience:
def _ESC =^[
def _CLS =^[[2J

2017年4月15日 星期六

025_DG_5_Online

025_DG_5_Online




5.2 確認Archive log都傳送到Standby
SQL>
SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER
(PARTITION BY thread#)       AS LAST from  V$ARCHIVED_LOG;



5.3 確認archive log no gaps
SQL>
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM  V$ARCHIVE_GAP;

5.4 Stop Redo Apply
SQL>
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  CANCEL;

5.5 完成Apply所有redo data
SQL>
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;


5.6
確認Standby Database可切換成
SQL>
SELECT SWITCHOVER_STATUS FROM V$DATABASE;


看到 TO PRIMARY 代表正常


SWITCHOVER_STATUS
--------------------
TO PRIMARY


5.7
將standby切換為primary
SQL> ALTER DATABASE COMMIT TO
SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;




5.8 開啟資料庫

SQL> ALTER DATABASE OPEN;




024_DG_42_START_Prod

024_DG_42_START_Parmary

檢查開啟狀態

-- 請全貼
-- ===check===
select dbid,name from v$database;
select
log_mode , OPEN_MODE , DATABASE_ROLE
from v$database;

--DataGuard

select SWITCHOVER_STATUS , PROTECTION_MODE , PROTECTION_LEVEL
from
v$database;


Process
SELECT
PROCESS,STATUS,CLIENT_PROCESS,SEQUENCE#, BLOCK#,BLOCKS FROM
V$MANAGED_STANDBY;





Process
SQL>   -- 找 LNS
PROCESS,STATUS,CLIENT_PROCESS,SEQUENCE#, BLOCK#,BLOCKS FROM
V$MANAGED_STANDBY;


SQL>  -- 傳送狀態

SELECT DEST_NAME,STATUS,TYPE,RECOVERY_MODE,GAP_STATUS
FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2;


SQL>  --
log_archive_dest_2
alter system set
log_archive_dest_2='SERVICE=STANDBY compression=disable' scope=both;



SQL> -- # Deffer ENABLE   

alter system set log_archive_dest_state_2=defer ;
alter system set
log_archive_dest_state_2=enable ;


SQL>  --log switch

alter system switch logfile;



設定

1  lsnrctl 要先通


ORCL  =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)(HOST = 192.168.48.10 )(PORT = 1521)))
(CONNECT_DATA
=
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL  )))


STANDBY2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL
= TCP)(HOST = 192.168.48.20 )(PORT = 1521)))
(CONNECT_DATA =

(SERVER = DEDICATED)
(SERVICE_NAME = ORCL  )))




2  tnsping 測試看



[oracle]$
tnsping standby2
Used
TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION
= (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.48.20 )(PORT =
1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL  )))

OK (10 msec)

4.9 調整Primary Database參數  這個很重要   最重要的一動

 
SQL>   --  ## 11G 專用

alter system set log_archive_dest_2='SERVICE=STANDBY compression=disable'
scope=both;



SQL> -- 10G 專用

alter system set log_archive_dest_2=

'SERVICE=STANDBY LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=orcl';


SQL> show parameter
log_archive_dest_2  ;

SQL> -- #
Deffer ENABLE   
alter system set
log_archive_dest_state_2=defer ;
alter system set
log_archive_dest_state_2=enable ;

4.10.3 確認log傳送的狀態


SQL>  -- 11G 以上
SELECT
DEST_NAME,STATUS,TYPE,RECOVERY_MODE,GAP_STATUS FROM V$ARCHIVE_DEST_STATUS
WHERE DEST_ID=2;



SQL> -- 10G 專用

 SELECT DEST_NAME,STATUS,TYPE,RECOVERY_MODE FROM
V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2 ;


使用下列語法於Primary
Database確認Standby Database apply機制是否為real-time apply。

DEST_NAME

--------------------------------------------------------------------------------

STATUS TYPE RECOVERY_MODE GAP_STATUS
--------- --------------
----------------------- ------------------------
LOG_ARCHIVE_DEST_2

VALID PHYSICAL MANAGED REAL
TIME APPLY
NO GAP






4.10.2 Primary Database switch logfile  PROD

PROD




使用下列語法於Primary Database switch logfile
SQL>
alter system switch logfile;










SQL> SELECT
PROCESS,STATUS,CLIENT_PROCESS,SEQUENCE#, BLOCK#,BLOCKS FROM
V$MANAGED_STANDBY;


PROCESS STATUS CLIENT_P SEQUENCE# BLOCK#
BLOCKS
--------- ------------ -------- ---------- ---------- ----------

ARCH CLOSING ARCH 20 1 149
ARCH CLOSING ARCH 21 43008 1546
ARCH
CLOSING ARCH 17 1 248
ARCH CLOSING ARCH 19 1 448

LNS  WRITING
LNS 22
55382 1


ORACLE BASE 11G

https://oracle-base.com/articles/11g/data-guard-setup-11gr2

ORACLe 官網 11G
https://docs.oracle.com/cd/E11882_01/server.112/e41134/create_ps.htm#SBYDB00200

ORACLE 官網 10g
https://docs.oracle.com/cd/B19306_01/server.102/b14239/create_ps.htm#i68937

2017年4月11日 星期二

ORA-01722 during upgrade 10.2 to 11.2

轉自

ORA-01722 during upgrade 10.2 to 11.2
Yesterday I was trying to upgrade my 10.2 database to 11.2 version in new host and I got below error when I ran catupgrd.sql to upgrade it:-

Issue :-

SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')
                 *
ERROR at line 1:
ORA-01722: invalid number

Cause :-

I checked the Timezone value in registry$database table, I got below value

SQL> col PLATFORM_NAME for a30
SQL> set lin 400
SQL> select * from sys.registry$database;

PLATFORM_ID PLATFORM_NAME                  EDITION                        TZ_VERSION
-----------             ------------------------------                ------------------------------   ----------
         13               Linux x86 64-bit

TZ_Version coloumn was blank, so was getting ORA-01722: invalid number error

Solution :-

Update the Tz_version column of registry$database table with 11.2 version details :-

SQL> create table registry_backup as select * from registry$database;

SQL> INSERT into registry$database (platform_id, platform_name, edition, tz_version) VALUES ((select platform_id from v$database), (select platform_name from v$database),NULL,(select version from v$timezone_file));

1 row created.

SQL> select * from sys.registry$database;

PLATFORM_ID PLATFORM_NAME                  EDITION                        TZ_VERSION
-----------             ------------------------------           ------------------------------    ----------
         13              Linux x86 64-bit
         13             Linux x86 64-bit                                                                 14

SQL> delete from sys.registry$database where TZ_VERSION is NULL;

1 row deleted.

SQL> select * from registry_backup;

PLATFORM_ID PLATFORM_NAME                  EDITION                        TZ_VERSION
-----------             ------------------------------           ------------------------------    ----------
         13              Linux x86 64-bit

SQL> commit;

Commit complete.

SQL> select * from sys.registry$database;

PLATFORM_ID PLATFORM_NAME                  EDITION                        TZ_VERSION
-----------             ------------------------------           ------------------------------    ----------
         13              Linux x86 64-bit                                                                14

Timezone issue resolved with above solution.

Again I ran catupgrd.sql and now this time I got a new error :-

Issue :-

SELECT TO_NUMBER('DATA_VAULT_OPTION_ON') FROM v$option
                 *
ERROR at line 1:
ORA-01722: invalid number

Cause :-

We have to disable the data vault option before running the upgrade and enable it once donewith upgrade

Solution :-

==>chopt disable dv

Writing to /opt/oracle/product/database/11.2.0.4/install/disable_dv.log...
/usr/bin/make -f /opt/oracle/product/database/11.2.0.4/rdbms/lib/ins_rdbms.mk dv_off ORACLE_HOME=/opt/oracle/product/database/11.2.0.4
/usr/bin/make -f /opt/oracle/product/database/11.2.0.4/rdbms/lib/ins_rdbms.mk ioracle ORACLE_HOME=/opt/oracle/product/database/11.2.0.4

Now I ran the Catupgrd.sql and it ran without any issues and upgrade got successfully completed.

After the upgrade was done , I enable the data vault option by running below command :-

==>chopt enable dv


032_DG_2_OPENTEST

4. Activate the physical standby database.

4.1 active standby database


SQL>
ALTER DATABASE ACTIVATE STANDBY DATABASE;

Database altered.

SQL>


aletr log 會產生以下的訊息

AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.
ACTIVATE STANDBY: Complete - Database mounted as primary
Completed: ALTER DATABASE ACTIVATE STANDBY DATABASE

4.2 確認control file 狀態


SQL>
select CONTROLFILE_TYPE from v$database;

CONTROL
-------
CURRENT

4.3 open database


SQL>
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
ALTER DATABASE OPEN;


aletr log 會產生以下的訊息
Completed: ALTER DATABASE OPEN

4.4 確認database 狀態


SQL>
select status from v$thread ;

STATUS
------
OPEN

5 6 7 一連串的測試  自已體會



5. 手動 bind ip for DB VIP
6. 修改listener.ora  /etc/hosts 
7. 連線確認 DB 可正常讀取

該注意 
lisrctl IP   VIP是否正確
/etc/hosts   VIP對是否正確
sqlnet.ora   db的白名單是否正確

8. 還原 Physical cold standby database

8.1 重啟 database on mount mode


SQL>
shutdown immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
startup mount
ORACLE instance started.

Total System Global Area 776646656 bytes
Fixed Size 2257272 bytes
Variable Size 507514504 bytes
Database Buffers 260046848 bytes
Redo Buffers 6828032 bytes
Database mounted.
SQL>



8.2 flashback database to before open


SQL>
FLASHBACK DATABASE TO RESTORE POINT BEFORE_OPEN_STANDBY;

Flashback complete.

aletr log 會產生以下的訊息 FLASHBACK DATABASE TO RESTORE POINT BEFORE_OPEN_STANDBY
Flashback Restore Start
Flashback Restore Complete
Completed: FLASHBACK DATABASE TO RESTORE POINT BEFORE_OPEN_STANDBY


8.3 可以確認control file type 為 backup


SQL>
select controlfile_type from v$database ;

CONTROL
-------
BACKUP

8.4 轉換 database 為 physical standby


SQL>
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.

aletr log 會產生以下的訊息


Completed: ALTER DATABASE CONVERT TO PHYSICAL STANDBY

8.5 重啟 standby database on mount mode


SQL>
shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL>
startup mount
ORACLE instance started.

Total System Global Area 776646656 bytes
Fixed Size 2257272 bytes
Variable Size 507514504 bytes
Database Buffers 260046848 bytes
Redo Buffers 6828032 bytes
Database mounted.
SQL>

8.6 重啟 standby databas apply


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE;

Database altered.

aletr log 會產生以下的訊息


Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE

9. 恢復primary database 歸檔至standby database


SQL>
alter system set log_archive_dest_state_2=enable ;

System altered.

SQL>

aletr log 會產生以下的訊息
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************

10. Drop the restore point

10 .1 drop and off


SQL>
DROP RESTORE POINT BEFORE_OPEN_STANDBY;
alter database flashback off;

Restore point dropped.
Database altered.

aletr log 會產生以下的訊息


Drop guaranteed restore point BEFORE_OPEN_STANDBY
Guaranteed restore point BEFORE_OPEN_STANDBY dropped

Completed: alter database flashback off

10.2 再重啟一次並恢復連線


SQL>
shutdown immediate;
startup mount;


ORACLE instance started.

Total System Global Area 776646656 bytes
Fixed Size 2257272 bytes
Variable Size 507514504 bytes
Database Buffers 260046848 bytes
Redo Buffers 6828032 bytes
Database mounted.
SQL>
SQL>
alter database recover managed standby database using current logfile disconnect from session;

Database altered.

11 恢復 檢查


SQL>
select log_mode , OPEN_MODE , DATABASE_ROLE from v$database;
select SWITCHOVER_STATUS , PROTECTION_MODE , PROTECTION_LEVEL from v$database;


LOG_MODE OPEN_MODE DATABASE_ROLE
------------ -------------------- ----------------
ARCHIVELOG MOUNTED PHYSICAL STANDBY

SWITCHOVER_STATUS PROTECTION_MODE PROTECTION_LEVEL
-------------------- -------------------- --------------------
NOT ALLOWED MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

031_DG_1_OPENTEST


1. 啟動 flashback database功能

1.1 檢查 Cold Standby DB 是否開始 archive log mode  注意紅字


SQL>
archive log list

Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/archivelog
Oldest online log sequence 45
Next log sequence to archive 47
Current log sequence 47
SQL>



1.2 設定 flashback 相關參數


注意 資料夾要存在,不然要先建立
這邊要求的空間,不一定要有,反正只是測試一下而以,不用擔心

[oracle]$ mkdir -p /data/flash_recovery_area
[oracle]$ SS

SQL>
alter system set db_recovery_file_dest_size=100g;
alter system set db_recovery_file_dest='/data/flash_recovery_area';
alter system set db_flashback_retention_target=1440;



1.3 先暫停 Cold Standby apply,並確認 Standby database in mount mode


SQL>
alter database recover managed standby database cancel;
select open_mode from v$database;


OPEN_MODE
--------------------
MOUNTED

如果出現 READ ONLY  也沒關系

1.4 開啟 flashback database 功能


SQL>
alter database flashback on;

Database altered.

1.5 重啟 Cold Standby apply


SQL>
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;


Database altered.

Prod Alter log  會產生以下的字眼
***************************************************
LGWR: Setting 'active' archival for destination LOG
***************************************************

2. Active Cold Standby 前準備 on Standby database

2.1 停止 cold standby db apply


SQL>
alter database recover managed standby database cancel;

Database altered.

2.2 建立還原點


SQL>
create restore point before_open_standby guarantee flashback database;

Restore point created.

2.3 確認所建立的還原點 有Select 到即可


SQL>
select scn, storage_size, time, name from v$restore_point;

SCN STORAGE_SIZE TIME
---------- ------------ ---------------------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------
1481019 52428800 08-APR-17 08.23.39.000000000 PM
BEFORE_OPEN_STANDBY


SQL>

3. Active Cold Standby 前準備 on Primary database

3.1 switch log file


SQL>
alter system switch logfile;

System altered.

3.2 暫停 archive log 歸檔至standby database


SQL>
alter system set log_archive_dest_state_2=defer ;

System altered.

2017年4月9日 星期日

041_DG_Check

041_DG_Check

1 檢查 Process

1  STANDBY PROCESS 一定要是 APPLYING_LOG


SQL>
SQL>
SELECT PROCESS,STATUS,CLIENT_PROCESS,SEQUENCE#, BLOCK#,BLOCKS FROM V$MANAGED_STANDBY;

PROCESS STATUS CLIENT_P SEQUENCE# BLOCK# BLOCKS
--------- ------------ -------- ---------- ---------- ----------
ARCH CONNECTED ARCH 0 0 0
ARCH CONNECTED ARCH 0 0 0
ARCH CONNECTED ARCH 0 0 0
ARCH CLOSING ARCH 54 28672 1262
MRP0 APPLYING_LOG N/A 55 567 102400
RFS IDLE ARCH 0 0 0
RFS IDLE LGWR 55 567 1
RFS IDLE UNKNOWN 0 0 0

8 rows selected.

Database altered.

SQL>
不是的話
===============================================================================================
沒有MRP0的話
執行
# 啟動APPLY
SQL>
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

SQL> SELECT PROCESS,STATUS,CLIENT_PROCESS,SEQUENCE#, BLOCK#,BLOCKS FROM V$MANAGED_STANDBY;

PROCESS   STATUS       CLIENT_P  SEQUENCE#     BLOCK#  BLOCKS
--------- ------------ -------- ---------- ---------- ----------
ARCH  CONNECTED    ARCH 0    0       0
ARCH  CONNECTED    ARCH 0    0       0
ARCH  CONNECTED    ARCH 0    0       0
ARCH  CONNECTED    ARCH 0    0       0
MRP0  WAIT_FOR_LOG N/A 55    0       0

就是有排掉LOG 要補檔


2  PROD PROCESS  一定要是 WRITING


SQL>
SQL>
SELECT PROCESS,STATUS,CLIENT_PROCESS,SEQUENCE#, BLOCK#,BLOCKS FROM V$MANAGED_STANDBY;

PROCESS STATUS CLIENT_P SEQUENCE# BLOCK# BLOCKS
--------- ------------ -------- ---------- ---------- ----------
ARCH CLOSING ARCH 53 1 3036
ARCH CLOSING ARCH 54 28672 1262
ARCH CONNECTED ARCH 0 0 0
ARCH CLOSING ARCH 54 2 29932
LNS  WRITING LNS 55 3846 1

SQL>
不是的話
===============================================================================================
沒有LNS 的話
執行
#
SQL>
alter system set log_archive_dest_state_2=enable ;


2 檢查 傳送是否正常,是否新增

4.3 open database


SQL>
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received",
APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#)
"Difference"
FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE
(THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)
FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME )
IN (SELECT THREAD#,MAX(FIRST_TIME)
FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

aletr log 會產生以下的訊息
Completed: ALTER DATABASE OPEN

 SELECT DEST_NAME,STATUS,TYPE,RECOVERY_MODE,GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2;







2016年10月10日 星期一

rman restore newname






startup nomount ;
set DBID=1438520397;
run {
restore controlfile     from    '/rmanbackup/Control.ctl';
startup mount ;
restore database ;
recover database ;
};



startup nomount ;
set DBID=1438520397;
run {
restore controlfile    
 from    '/rmanbackup/Control.ctl';
set newname for datafile  5
    to '/oradata/data/userdata2.dbf' ;
set newname for datafile  6
    to '/oradata/data/user2datafile2.dbf' ;
set newname for datafile  7
    to '/oradata/data/user2datafile1.dbf' ;
startup mount ;
restore database ;
switch datafile all;
recover database ;
};

2016年5月3日 星期二

024_DG_4_START_Standby

檢查開啟狀態

-- 請全貼

-- ===check===

select dbid,name from v$database;

select
log_mode , OPEN_MODE , DATABASE_ROLE

from v$database;

--DataGuard

select SWITCHOVER_STATUS , PROTECTION_MODE , PROTECTION_LEVEL

from
v$database;








Process   要找 MRP0 

SELECT
PROCESS,STATUS,CLIENT_PROCESS,SEQUENCE#, BLOCK#,BLOCKS FROM
V$MANAGED_STANDBY;




LOG APPLY START

ALTER DATABASE RECOVER MANAGED STANDBY
DATABASE USING CURRENT LOGFILE DISCONNECT;
LOG APPLY STOP

ALTER DATABASE RECOVER MANAGED STANDBY
DATABASE CANCEL;

4.8 啟動listener on standby server

Standby

lsnrctl start



範列︰

cdTNS

vi listener.ora

===================================================================================

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = orcl)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

(SID_NAME = orcl)

)

)



LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.221.20)(PORT = 1521))

)

)



ADR_BASE_LISTENER = /u01/app/oracle

===================================================================================




開始apply



SQL> ALTER DATABASE RECOVER MANAGED STANDBY
DATABASE USING CURRENT LOGFILE DISCONNECT;


ALTER DATABASE RECOVER
MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT

*

ERROR at
line 1:

ORA-38500: USING CURRENT LOGFILE option not available without
stand


出現 ORA-38500  使用以下部驟



4.7.7 Create Standby logfile

先使用下列語法查詢,Primary有幾個online redo log,以及online redo log file的size。





SQL>

select group#,bytes from v$log;



GROUP# BYTES

---------- ----------

1 52428800

3 52428800

2
52428800



SQL>







使用下列語法新增
stnadby logfile,所需standby
logfile
數量為Primary
logfile


數
+1,size大小與Primary一致,

因上一步驟查詢
Primary logfile數為3,size大小為50M,故需新增standby
logfile
數量為4,size大小為50M



,紅字部份請依環境進行調整。


[oracle]$

LPATH=/oracle/oradata/redo
mkdir -p
$LPATH
for i in `seq 1 4`
do
echo " alter database add standby
logfile '$LPATH/redo${i}_std.log' size 50M; "
done


alter
database add standby logfile '/oracle/oradata/redo/redo1_std.log' size 50M;

alter database add standby logfile '/oracle/oradata/redo/redo2_std.log' size
50M;

alter database add standby logfile
'/oracle/oradata/redo/redo3_std.log' size 50M;

alter database add standby
logfile '/oracle/oradata/redo/redo4_std.log' size 50M;


SQL>

alter database add standby logfile
'/oracle/oradata/redo/redo1_std.log' size 50M;
alter database add standby
logfile '/oracle/oradata/redo/redo2_std.log' size 50M;
alter database add
standby logfile '/oracle/oradata/redo/redo3_std.log' size 50M;
alter
database add standby logfile '/oracle/oradata/redo/redo4_std.log' size 50M;


重新apply



SQL>
ALTER DATABASE RECOVER MANAGED STANDBY
DATABASE USING CURRENT LOGFILE DISCONNECT;




Database altered.










檢查process





SQL>

SELECT
PROCESS,STATUS,CLIENT_PROCESS,SEQUENCE#, BLOCK#,BLOCKS FROM
V$MANAGED_STANDBY;




PROCESS STATUS CLIENT_P SEQUENCE# BLOCK#
BLOCKS

--------- ------------ -------- ---------- ---------- ----------

ARCH CONNECTED ARCH 0 0 0

ARCH CONNECTED ARCH 0 0 0

MRP0
WAIT_FOR_GAP N/A 27 0 0



以上紅色有落差



SQL>


select * from v$recovery_log;




THREAD# SEQUENCE# TIME

---------- ---------- ---------

ARCHIVE_NAME

--------------------------------------------------------------------------------

1 27 14-APR-17

/oracle/u01/app/oracle/product/10.2.0/dbs/arch1_27_940991096.dbf


















SQL>

SELECT
PROCESS,STATUS,CLIENT_PROCESS,SEQUENCE#, BLOCK#,BLOCKS FROM
V$MANAGED_STANDBY;




PROCESS STATUS CLIENT_P SEQUENCE# BLOCK#
BLOCKS

--------- ------------ -------- ---------- ---------- ----------

ARCH CLOSING ARCH 28 6145 581

ARCH CLOSING ARCH 29 1 22

MRP0
APPLYING_LOG N/A 30 15727 102400

RFS IDLE
UNKNOWN 0 0 0

RFS IDLE LGWR 30 15729 1

以上紅色為正常



4.10.5 開啟檔案自動管理



SQL> alter system set standby_file_management = auto;



SQL> show parameter standby ;



NAME TYPE VALUE

------------------------------------ -----------
------------------------------

standby_archive_dest string           
?/dbs/arch

standby_file_management string         
AUTO








4.11 停止Standby database apply



SQL> 
ALTER DATABASE RECOVER MANAGED STANDBY
DATABASE CANCEL;

2016年5月2日 星期一

oracle 基本的 alias









alert log 產出位子

SQL> show parameter background

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_core_dump                 string      partial
background_dump_dest                 string      /u01/app/oracle/diag/rdbms/orcl/orcl/trace



====================================================


export  PS1="\e[4m$ORACLE_SID\[\e[0m\]::\[\e[41m\]\h:\W \u\\$ \[\e[0m\] "
alias SS='sqlplus / as sysdba'
alias cdTNS='cd $ORACLE_HOME/network/admin'
alias cdDBS='cd $ORACLE_HOME/dbs'
alias cdALT='cd  /u01/app/oracle/diag/rdbms/orcl/orcl/trace'
alias viALT='vi  /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log'
alias TAILALT='tail -f  /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log '










前面有SID, 後面紅底白字
Prod
 PS1="\e[4m$ORACLE_SID\[\e[0m\]::\[\e[41m\]\h:\W \\$ \[\e[0m\] "



ColdStandby
 PS1="\e[4m$ORACLE_SID\[\e[0m\]::\[\e[36;44m\]\h:\W \\$ \[\e[0m\] "















oracle dump log



Oracle

  SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

SQL>  show parameter background

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_core_dump                 string      partial
background_dump_dest                 string      /oracle/app/oracle/diag/rdbms/PROD/PROD/trace

SQL> !ls -ltr /oracle/app/oracle/diag/rdbms/PROD/PROD/trace/al*
-rw-r----- 1 oracle oinstall 78512424 2011-10-03 02:00 /oracle/app/oracle/diag/rdbms/PROD/PROD/trace/alert_PROD.log

SQL>

021_DG_1_ARCHIVELOG_MODE



  

Check Log Mode


SQL>

select dbid,name from v$database;
select log_mode ,
OPEN_MODE , DATABASE_ROLE
from v$database;
--DataGuard
select
SWITCHOVER_STATUS , PROTECTION_MODE , PROTECTION_LEVEL
from v$database;



 DBID NAME
---------- ---------
1438520397 ORCL

SQL> 2

LOG_MODE OPEN_MODE DATABASE_ROLE
------------ --------------------
----------------
NOARCHIVELOG MOUNTED PRIMARY

SQL> SQL> 2

SWITCHOVER_STATUS PROTECTION_MODE PROTECTION_LEVEL
--------------------
-------------------- --------------------

NOT ALLOWED MAXIMUM
PERFORMANCE UNPROTECTED

How to change an Oracle database to noarchivelog mode


================================================

SQL> alter
database mount;

Database altered.

SQL> alter database
noarchivelog;

Database altered.

SQL> alter database open;


Database altered.
================================================


How to change an Oracle database to noarchivelog mode

 ================================================


SQL> alter
database mount;

Database altered.

SQL> alter database
noarchivelog;

Database altered.

SQL> alter database open;


Database altered.
================================================



How to change an Oracle database to archivelog mode


SQL> SELECT LOG_MODE FROM V$DATABASE;


LOG_MODE
------------
NOARCHIVELOG

SQL>
select force_logging from v$database;


FOR
---
NO

1 開啟Archive log mode

1.設定archive log
destination

SQL> alter system set
log_archive_dest_1='LOCATION=
/oradata/archivelog'
scope=both;


System altered.

SQL>



2.重啟資料庫於mount

SQL> startup force mount ;

ORACLE instance started.

Total System Global Area 409194496 bytes

Fixed Size 2213856 bytes
Variable Size 272631840 bytes
Database
Buffers 130023424 bytes
Redo Buffers 4325376 bytes
Database mounted.

SQL>


3.開啟archive log mode

SQL> alter
database archivelog;


Database altered.

4.確認資料庫歸檔模式



SQL> archive log list
Database log
mode Archive Mode
Automatic archival Enabled
Archive destination
/oradata/archivelog
Oldest online log sequence 8
Next log sequence to
archive 10
Current log sequence 10
SQL> SELECT
LOG_MODE FROM V$DATABASE;


LOG_MODE
------------

ARCHIVELOG

logging


SQL>
select force_logging from v$database;

FOR
---
NO

SQL>
alter
database force logging;


Database altered.

SQL>
select
force_logging from v$database;


FOR
---
YES

SQL>


2016年4月30日 星期六

023_DG_3_restore


023_DG_3_restore

   

4.7 restore database on standby

4.7.1 startup nomount on standby

STAGE


[oracle@Standby rmanbackup]$
export ORACLE_SID=SID;
[oracle@Standby
rmanbackup]$ rman target / nocatalog

RMAN> startup nomount;

Oracle instance started

Total System
Global Area 6747725824 bytes

Fixed Size 2213976 bytes
Variable
Size 3623880616 bytes
Database Buffers 3087007744 bytes
Redo Buffers
34623488 bytes





4.7.2 設定DBID  STAGE

STAGE


RMAN> set dbid=2330223285;


executing command: SET DBID





4.7.3 restore standby controlfile


使用下列指令restore standby controlfile,紅字部份請依環境進行調整。




RMAN> restore standby controlfile from '/rmanbackup/standby.ctl';



alter
database mount; 
restore database;  recover database;

 

4.7.4 mount database

##  使用下列語法mount database。

RMAN> alter
database mount;




4.7.5 restore database

##  使用下列指令執行restore database。

RMAN>
restore database;





4.7.6 recover database



使用下列指令執行recover database,底下出現的Error Message表示需要sequence=24的archive
log,但因2.4備份Primary Database時,當時archive log只到sequence 23,故此Error Message為正常的。


RMAN> recover database;

Starting
recover at 29-APR-16
using channel ORA_DISK_1
using channel ORA_DISK_2

using channel ORA_DISK_3
using channel ORA_DISK_4

starting media
recovery

archived log for thread 1 with sequence 475102 is already on
disk as file
/oracle/u01/app/oracle/flash_recovery_area/ADPMOBILE/archivelog/2016_04_29/o1_mf_1_475102_cl6g66qf_.arc

archived log for thread 1 with sequence 475103 is already on disk as file
/oracle/u01/app/oracle/flash_recovery_area/ADPMOBILE/archivelog/2016_04_29/o1_mf_1_475103_cl6g67pw_.arc

channel ORA_DISK_1: starting archived log restore to default destination

channel ORA_DISK_1: restoring archived log
archived log thread=1
sequence=475098
channel ORA_DISK_1: reading from backup piece
/rmanbackup/PRMY_ifr491kb_1_1.bk
RMAN-00571:
===========================================================
RMAN-00569:
=============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571:
===========================================================
RMAN-03002:
failure of recover command at 04/29/2016 18:59:53
ORA-19870: error while
restoring backup piece /rmanbackup/PRMY_ifr491kb_1_1.bk
ORA-19809: limit
exceeded for recovery files
ORA-19804: cannot reclaim 202038272 bytes
disk space from 4070572032 limit

022_DG_2_Backup

022_DG_2_Backup

SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 29 15:43:31 2016


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> SELECT LOG_MODE FROM
V$DATABASE;


如果是出現  ARCHIVELOG 
沒事

LOG_MODE
------------
ARCHIVELOG


SQL>
select force_logging from v$database;


如果是出現  YES  沒事
FOR
---

YES


$> 






4.3.1 建立TNS-Alias on Primary

PROD


orcl:MADPDB:PROD-oracle$ cd
$ORACLE_HOME/network/admin

orcl:MADPDB:PROD-oracle$
 vi tnsnames.ora





4.3.2 建立所需目錄於Standby Server

STAGE

orcl:Standby:dbs oracle$
cd $ORACLE_HOME/dbs
orcl:Standby:dbs
oracle$ vi initorcl.ora






4.3.3 Copy SPFILE from Primary to Standby



orcl:PROD-oracle$ cd
$ORACLE_HOME/dbs

orcl:PROD-oracle$
vi initorcl.ora






4.4 備份Primary資料庫

PROD




orcl:MobileAD_DB1:archive_log oracle$ rman target /


Recovery Manager: Release 11.2.0.1.0 - Production on Fri Apr 29 17:17:23
2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All
rights reserved.

connected to target database: ADPMOBIL
(DBID=2330223285)

RMAN>

RMAN> backup
database format '/rmanbackup/PRMY_%U.bk'
       
plus archivelog format '/rmanbackup/PRMY_AR%U.bk';




4.4  如果有 完整備份的話   

不要用完整備份會出人命


也可以直接將後面的 archivelog 備份即可






RMAN>    backup archivelog all format '/rmanbackup/PRMY_AR%U.bk';




4.5 備份controlfile for standby


RMAN> backup current controlfile for standby format '/rmanbackup/standby.ctl';




4.6 傳送備份至Standby Server


使用scp指令將Primary Database的備份傳送到Standby Server,紅字部份請依環境調整。






2016年4月20日 星期三

OPatch command

[檢查 OPatch 版本的指令]
#   $ORACLE_HOME/OPatch/opatch lsinventory -all  -invPtrLoc `find $ORACLE_HOME -name "*.loc"`
# version
# $ORACLE_HOME/OPatch/opatch version


# 命令列出已安装的CPU/PSU  有找有修正的bug
[ORACLLE]$  $ORACLE_HOME/OPatch/opatch lsinventory  -bugs_fixed -invPtrLoc `find $ORACLE_HOME -name "*.loc"`



# 上patch前檢查
[ORACLE]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./ -invPtrLoc `find $ORACLE_HOME -name "*.loc"`
[ORACLE]$
# 上patch
[ORACLE]$  $ORACLE_HOME/OPatch/opatch apply -invPtrLoc `find $ORACLE_HOME -name "*.loc"`
[ORACLE]$
# 上patch 後檢查
[ORACLE]$   $ORACLE_HOME/OPatch/opatch lsinventory -invPtrLoc `find $ORACLE_HOME -name "*.loc"`
[ORACLE]$
# Rollback  斜體請自行調整
[ORACLE]$
[ORACLE]$ $ORACLE_HOME/OPatch/opatch rollback -id 12880299



=====================================

#上傳 p12880299_112020_Linux-x86-64 至 /tmp/ora
[ORACLLE]$  unzip p12880299_112020_Linux-x86-64.zip
[ORACLLE]$  cd 12880299

# 
[ORACLLE]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
...
OPatch cannot find a valid oraInst.loc file to locate Central Inventory.
OPatch failed with error code 104

此動作若失敗的話改使用下一個動作

[ORACLLE]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./ -invPtrLoc `find $ORACLE_HOME -name "*.loc"` 

# 上patch  apply

[ORACLLE]$  $ORACLE_HOME/OPatch/opatch apply -invPtrLoc `find $ORACLE_HOME -name "*.loc"` 
# 確認
[ORACLLE]$  $ORACLE_HOME/OPatch/opatch lsinventory -invPtrLoc `find $ORACLE_HOME -name "*.loc"`



11.2.0.1  BUG to  11.2.0.1.6

ORACLE
SQL > shutdown immediate ;

LSNRCTL
$>    lsnrctl sop

[ORACLE]$   unzip p12419378_11201_<platform>.zip
[ORACLE]$   $ORACLE_HOME/OPatch/opatch prereq prereq CheckConflictAgainstOHWithDetail -phBaseDir ./12419378



[ORACLE]$  cd 12419378
[ORACLE]$  $ORACLE_HOME/OPatch/opatch apply -invPtrLoc `find $ORACLE_HOME -name "*.loc"`


2016年4月11日 星期一

rman restore



RMAN> SET DBID 12345;

RMAN> STARTUP NOMOUNT;

RMAN> RESTORE CONTROLFILE FROM "/backup/rman/ctl_c-12345-20141003-03"; 

RMAN> RESTORE DATABASE;

RMAN> RECOVER DATABASE;

RMAN> ALTER DATABASE OPEN RESETLOGS;
 http://www.thegeekstuff.com/2014/11/oracle-rman-restore/


If you are new to RMAN, you should first understand how to backup oracle database using RMAN.

oracle v$database




 select dbid,name from v$database;
 select log_mode , OPEN_MODE , DATABASE_ROLE
   from v$database;
 --DataGuard
 select SWITCHOVER_STATUS  , PROTECTION_MODE , PROTECTION_LEVEL  
    from        v$database;

2016年4月9日 星期六

oracle listener sample

Listerner

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0)
      (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DB01)(PORT = 1521))
    )
  )
SECURE_REGISTER_LISTENER = (TCP,IPC)



2016年4月8日 星期五

php Oracle oci_connect Error



ORACLE
PHP


Call to undefined function oci_connect()

無法連線時檢查
php/ext
下面的OCI檔案喔



;extension=php_oci8.dll      ; Use with Oracle 10gR2 Instant Client
;extension=php_oci8_11g.dll  ; Use with Oracle 11gR2 Instant Client
extension=php_oci8_12c.dll

RHEL install EPEL

  https://www.linuxtechi.com/install-epel-repo-on-rhel-system/ EPEL dnf install -y https://dl.fedoraproject.org/pub/epel/epel-release-latest...