p2srunonp.sh
#IF P RAC then
#sh p2s.sh EMCC EMCCSTBY passwd 1,
#IF P RAC and not mount RAC
#sh p2s.sh EMCC EMCCSTBY passwd 0
#IF P on ADG then
#sh p2s.sh EMCCSTBY EMCC passwd,
PRI_STAT=`sqlplus -s sys/$3@$1 as sysdba <<EOF
set feedback off
set pages 0
select database_role from v\\$database;
EOF`
echo $PRI_STAT
if [[ $PRI_STAT != 'PRIMARY' ]]
then echo 'PRIMARY DB INSTANCE IS NOT '$1 ',PLEASE CHECK AGAIN'
exit
fi
PRI_DB=$1
#echo $PRI_DB
STD_STAT=`sqlplus -s sys/$3@$2 as sysdba <<EOF
set feedback off
set pages 0
select database_role from v\\$database;
EOF`
if [[ $STD_STAT != 'PHYSICAL STANDBY' ]]
then echo 'STANDBY DB INSTANCE IS NOT '$2 ',PLEASE CHECK AGAIN'
exit
fi
STD_DB=$2
#export ORACLE_SID=$STD_DB
sqlplus -s sys/$3@$PRI_DB as sysdba <<EOF
break on db_name
set pages 50
set linesize 100
prompt
prompt Primary Instance
prompt ~~~~~~~~~~~~~~~~
select d.dbid dbid, d.name db_name, i.instance_name inst_name, d.database_role ,SWITCHOVER_STATUS from v\$database d,v\$instance i;
EOF
#export ORACLE_SID=$STD_DB
sqlplus -s sys/$3@$STD_DB as sysdba <<EOF
break on db_name
set pages 50
set linesize 100
prompt
prompt Standby Instance
prompt ~~~~~~~~~~~~~~~~
select d.dbid dbid, d.name db_name, i.instance_name inst_name, d.database_role ,SWITCHOVER_STATUS from v\$database d,v\$instance i;
EOF
#export ORACLE_SID=$PRI_DB
sqlplus sys/$3@$PRI_DB as sysdba <<EOF
select d.dbid dbid, d.name db_name, i.instance_name inst_name, d.database_role ,SWITCHOVER_STATUS from v\$database d,v\$instance i;
prompt Alter database commit to switchover to physical standby with session shutdown;
Alter database commit to switchover to physical standby with session shutdown;
prompt Primary db now switched to physical standby and down
EOF
#Donot start because TAF without service need primary stopped
#sqlplus sys/$3@$PRI_DB as sysdba <<EOF
#prompt startup mount
#startup mount
#prompt recover managed standby database disconnect from session;
#recover managed standby database disconnect from session;
#EOF
#export ORACLE_SID=$STD_DB
sqlplus sys/$3@$STD_DB as sysdba <<EOF
select d.dbid dbid, d.name db_name, i.instance_name inst_name, d.database_role ,SWITCHOVER_STATUS from v\$database d,v\$instance i;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
select d.dbid dbid, d.name db_name, i.instance_name inst_name, d.database_role ,SWITCHOVER_STATUS from v\$database d,v\$instance i;
prompt alter database commit to switchover to primary WITH SESSION SHUTDOWN;;
alter database commit to switchover to primary WITH SESSION SHUTDOWN;
select d.dbid dbid, d.name db_name, i.instance_name inst_name, d.database_role ,SWITCHOVER_STATUS from v\$database d,v\$instance i;
prompt alter database open;
alter database open;
select d.dbid dbid, d.name db_name, i.instance_name inst_name, d.database_role ,SWITCHOVER_STATUS from v\$database d,v\$instance i;
EOF
export ORACLE_SID=$1$4
sqlplus / as sysdba <<EOF
SHUTDOWN ABORT
STARTUP MOUNT
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE OPEN;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
select d.dbid dbid, d.name db_name, i.instance_name inst_name, d.database_role ,SWITCHOVER_STATUS from v\$database d,v\$instance i;
EOF
sqlplus sys/$3@$STD_DB as sysdba <<EOF
select d.dbid dbid, d.name db_name, i.instance_name inst_name, d.database_role ,SWITCHOVER_STATUS from v\$database d,v\$instance i;
EOF
p2sstarts.sh
export ORACLE_SID=$1$2
sqlplus / as sysdba <<EOF
SHUTDOWN ABORT
STARTUP MOUNT
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE OPEN;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
select d.dbid dbid, d.name db_name, i.instance_name inst_name, d.database_role ,SWITCHOVER_STATUS from v\$database d,v\$instance i;
EOF
srvctl status database -d $1
srvctl stop database -d $1
srvctl status database -d $1
srvctl start database -d $1
srvctl status database -d $1
p2sstartrac.sh ---service不正常可以加上 RAC 都需要
srvctl status database -d $1
srvctl stop database -d $1
srvctl status database -d $1
srvctl start database -d $1
srvctl status database -d $1
--------------------------------------------------一步切
[oracle@mesdb-18-11 sh]$ sh ./p2srunonp.sh EMCC EMCCSTBY passwd 1
PRIMARY
Primary Instance
~~~~~~~~~~~~~~~~
DBID DB_NAME INST_NAME DATABASE_ROLE SWITCHOVER_STATUS
---------- --------- ---------------- ---------------- --------------------
2519900446 EMCC EMCC3 PRIMARY TO STANDBY
Standby Instance
~~~~~~~~~~~~~~~~
DBID DB_NAME INST_NAME DATABASE_ROLE SWITCHOVER_STATUS
---------- --------- ---------------- ---------------- --------------------
2519900446 EMCC EMCCSTBY PHYSICAL STANDBY NOT ALLOWED
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 7 19:58:27 2022
Version 19.15.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0
SYS@EMCC>
DBID DB_NAME INST_NAME DATABASE_ROLE SWITCHOVER_STATUS
---------- --------- ---------------- ---------------- --------------------
2519900446 EMCC EMCC1 PRIMARY TO STANDBY
SYS@EMCC>Alter database commit to switchover to physical standby with session shutdown
SYS@EMCC>
Database altered.
SYS@EMCC>Primary db now switched to physical standby and down
SYS@EMCC>Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 7 19:58:37 2022
Version 19.15.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0
SYS@EMCCSTBY>
DBID DB_NAME INST_NAME DATABASE_ROLE SWITCHOVER_STATUS
---------- --------- ---------------- ---------------- --------------------
2519900446 EMCC EMCCSTBY PHYSICAL STANDBY NOT ALLOWED
SYS@EMCCSTBY>
Database altered.
SYS@EMCCSTBY>
Database altered.
SYS@EMCCSTBY>
Database altered.
SYS@EMCCSTBY>
DBID DB_NAME INST_NAME DATABASE_ROLE SWITCHOVER_STATUS
---------- --------- ---------------- ---------------- --------------------
2519900446 EMCC EMCCSTBY PHYSICAL STANDBY NOT ALLOWED
SYS@EMCCSTBY>alter database commit to switchover to primary WITH SESSION SHUTDOWN;
SYS@EMCCSTBY>
Database altered.
SYS@EMCCSTBY>
DBID DB_NAME INST_NAME DATABASE_ROLE SWITCHOVER_STATUS
---------- --------- ---------------- ---------------- --------------------
2519900446 EMCC EMCCSTBY PRIMARY NOT ALLOWED
SYS@EMCCSTBY>alter database open
SYS@EMCCSTBY>
Database altered.
SYS@EMCCSTBY>
DBID DB_NAME INST_NAME DATABASE_ROLE SWITCHOVER_STATUS
---------- --------- ---------------- ---------------- --------------------
2519900446 EMCC EMCCSTBY PRIMARY FAILED DESTINATION
SYS@EMCCSTBY>Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 7 19:59:02 2022
Version 19.15.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to an idle instance.
SYS@EMCC1>ORACLE instance shut down.
SYS@EMCC1>ORACLE instance started.
Total System Global Area 8623485040 bytes
Fixed Size 13623408 bytes
Variable Size 2852126720 bytes
Database Buffers 5704253440 bytes
Redo Buffers 53481472 bytes
Database mounted.
SYS@EMCC1>
Database altered.
SYS@EMCC1>
Database altered.
SYS@EMCC1>
Database altered.
SYS@EMCC1>
Database altered.
SYS@EMCC1>
DBID DB_NAME INST_NAME DATABASE_ROLE SWITCHOVER_STATUS
---------- --------- ---------------- ---------------- --------------------
2519900446 EMCC EMCC1 PHYSICAL STANDBY RECOVERY NEEDED
SYS@EMCC1>Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 7 19:59:40 2022
Version 19.15.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0
SYS@EMCCSTBY>
DBID DB_NAME INST_NAME DATABASE_ROLE SWITCHOVER_STATUS
---------- --------- ---------------- ---------------- --------------------
2519900446 EMCC EMCCSTBY PRIMARY TO STANDBY
SYS@EMCCSTBY>Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0
[oracle@mesdb-18-11 sh]$
[oracle@mesdb-18-11 sh]$
[oracle@mesdb-18-11 sh]$
[oracle@mesdb-18-11 sh]$ sh ./p2sstarts.sh EMCC 1
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 7 20:00:38 2022
Version 19.15.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0
SYS@EMCC1>ORACLE instance shut down.
SYS@EMCC1>ORACLE instance started.
Total System Global Area 8623485040 bytes
Fixed Size 13623408 bytes
Variable Size 2852126720 bytes
Database Buffers 5704253440 bytes
Redo Buffers 53481472 bytes
Database mounted.
SYS@EMCC1>
Database altered.
SYS@EMCC1>
Database altered.
SYS@EMCC1>
Database altered.
SYS@EMCC1>
Database altered.
SYS@EMCC1>
DBID DB_NAME INST_NAME DATABASE_ROLE SWITCHOVER_STATUS
---------- --------- ---------------- ---------------- --------------------
2519900446 EMCC EMCC1 PHYSICAL STANDBY NOT ALLOWED
SYS@EMCC1>Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0
Instance EMCC1 is running on node mesdb-18-11
Instance EMCC2 is not running on node mesdb-18-12
Instance EMCC3 is not running on node mesdb-18-13
Instance EMCC1 is not running on node mesdb-18-11
Instance EMCC2 is not running on node mesdb-18-12
Instance EMCC3 is not running on node mesdb-18-13
Instance EMCC1 is running on node mesdb-18-11
Instance EMCC2 is running on node mesdb-18-12
Instance EMCC3 is running on node mesdb-18-13
[oracle@mesdb-18-11 sh]$ sh p2sstartrac.sh EMCC
Instance EMCC1 is running on node mesdb-18-11
Instance EMCC2 is running on node mesdb-18-12
Instance EMCC3 is running on node mesdb-18-13
Instance EMCC1 is not running on node mesdb-18-11
Instance EMCC2 is not running on node mesdb-18-12
Instance EMCC3 is not running on node mesdb-18-13
Instance EMCC1 is running on node mesdb-18-11
Instance EMCC2 is running on node mesdb-18-12
Instance EMCC3 is running on node mesdb-18-13
------------------------------------------------两步切
主库RAC 切换分两步,主要是FAN没有使用service下,如果主库mount会连主库
[oracle@mesdg-18-17 sh]$ sh ./p2srunonp.sh EMCCSTBY EMCC pawwd 0
PRIMARY
Primary Instance
~~~~~~~~~~~~~~~~
DBID DB_NAME INST_NAME DATABASE_ROLE SWITCHOVER_STATUS
---------- --------- ---------------- ---------------- --------------------
2519900446 EMCC EMCCSTBY PRIMARY TO STANDBY
Standby Instance
~~~~~~~~~~~~~~~~
DBID DB_NAME INST_NAME DATABASE_ROLE SWITCHOVER_STATUS
---------- --------- ---------------- ---------------- --------------------
2519900446 EMCC EMCC3 PHYSICAL STANDBY NOT ALLOWED
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 7 20:10:07 2022
Version 19.15.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0
SYS@EMCCSTBY>
DBID DB_NAME INST_NAME DATABASE_ROLE SWITCHOVER_STATUS
---------- --------- ---------------- ---------------- --------------------
2519900446 EMCC EMCCSTBY PRIMARY TO STANDBY
SYS@EMCCSTBY>Alter database commit to switchover to physical standby with session shutdown
SYS@EMCCSTBY>
Database altered.
SYS@EMCCSTBY>Primary db now switched to physical standby and down
SYS@EMCCSTBY>Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 7 20:10:12 2022
Version 19.15.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0
SYS@EMCC>
DBID DB_NAME INST_NAME DATABASE_ROLE SWITCHOVER_STATUS
---------- --------- ---------------- ---------------- --------------------
2519900446 EMCC EMCC2 PHYSICAL STANDBY NOT ALLOWED
SYS@EMCC>
Database altered.
SYS@EMCC>
Database altered.
SYS@EMCC>
Database altered.
SYS@EMCC>
DBID DB_NAME INST_NAME DATABASE_ROLE SWITCHOVER_STATUS
---------- --------- ---------------- ---------------- --------------------
2519900446 EMCC EMCC2 PHYSICAL STANDBY TO PRIMARY
SYS@EMCC>alter database commit to switchover to primary WITH SESSION SHUTDOWN;
SYS@EMCC>
Database altered.
SYS@EMCC>
DBID DB_NAME INST_NAME DATABASE_ROLE SWITCHOVER_STATUS
---------- --------- ---------------- ---------------- --------------------
2519900446 EMCC EMCC2 PRIMARY NOT ALLOWED
SYS@EMCC>alter database open
SYS@EMCC>
Database altered.
SYS@EMCC>
DBID DB_NAME INST_NAME DATABASE_ROLE SWITCHOVER_STATUS
---------- --------- ---------------- ---------------- --------------------
2519900446 EMCC EMCC2 PRIMARY FAILED DESTINATION
SYS@EMCC>Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 7 20:10:40 2022
Version 19.15.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to an idle instance.
SYS@EMCCSTBY0>ORACLE instance shut down.
SYS@EMCCSTBY0>ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/19c/dbs/initEMCCSTBY0.ora'
SYS@EMCCSTBY0>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
SYS@EMCCSTBY0>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
SYS@EMCCSTBY0>ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
SYS@EMCCSTBY0>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
SYS@EMCCSTBY0>select d.dbid dbid, d.name db_name, i.instance_name inst_name, d.database_role ,SWITCHOVER_STATUS from v$database d,v$instance i
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
SYS@EMCCSTBY0>Disconnected
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 7 20:10:44 2022
Version 19.15.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0
SYS@EMCC>
DBID DB_NAME INST_NAME DATABASE_ROLE SWITCHOVER_STATUS
---------- --------- ---------------- ---------------- --------------------
2519900446 EMCC EMCC2 PRIMARY FAILED DESTINATION
SYS@EMCC>Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0
备库为主后分两次
[oracle@mesdg-18-17 sh]$ sh ./p2sstarts.sh EMCCSTBY
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 7 20:11:36 2022
Version 19.15.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to an idle instance.
SYS@EMCCSTBY>ORACLE instance shut down.
SYS@EMCCSTBY>ORACLE instance started.
Total System Global Area 4194303768 bytes
Fixed Size 8904472 bytes
Variable Size 620756992 bytes
Database Buffers 3539992576 bytes
Redo Buffers 24649728 bytes
Database mounted.
SYS@EMCCSTBY>
Database altered.
SYS@EMCCSTBY>
Database altered.
SYS@EMCCSTBY>
Database altered.
SYS@EMCCSTBY>
Database altered.
SYS@EMCCSTBY>
DBID DB_NAME INST_NAME DATABASE_ROLE SWITCHOVER_STATUS
---------- --------- ---------------- ---------------- --------------------
2519900446 EMCC EMCCSTBY PHYSICAL STANDBY RECOVERY NEEDED
SYS@EMCCSTBY>Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0
Database is running.
Database is not running.
Database is running.
[oracle@mesdg-18-17 sh]$
重启RAC
[oracle@mesdb-18-11 sh]$ sh p2sstartrac.sh EMCC
Instance EMCC1 is running on node mesdb-18-11
Instance EMCC2 is running on node mesdb-18-12
Instance EMCC3 is running on node mesdb-18-13
Instance EMCC1 is not running on node mesdb-18-11
Instance EMCC2 is not running on node mesdb-18-12
Instance EMCC3 is not running on node mesdb-18-13
Instance EMCC1 is running on node mesdb-18-11
Instance EMCC2 is running on node mesdb-18-12
Instance EMCC3 is running on node mesdb-18-13
[oracle@mesdb-18-11 sh]$