-- primary
---- redo log setup
------ check log information
select a.*,bytes/1024/1024 from v$log a;
select * from v$logfile;
------ configure redo log (optional, not affecting dataguard setup)
------ enlarge existing 3 redo log and add 3 additional log file
alter database add logfile
group 4 ('/u02/oradata/CDB_NAME/redo04.log') size 512M,
group 5 ('/u02/oradata/CDB_NAME/redo05.log') size 512M,
group 6 ('/u02/oradata/CDB_NAME/redo06.log') size 512M;
alter system switch logfile; --- until all new group were switched, i.e. until group 6
alter system checkpoint;
alter database drop logfile group 1, group 2, group 3;
[oracle@myserver1 ~]$ rm /u02/oradata/CDB_NAME/redo01.log --- remove os file
[oracle@myserver1 ~]$ rm /u02/oradata/CDB_NAME/redo02.log
[oracle@myserver1 ~]$ rm /u02/oradata/CDB_NAME/redo03.log
alter database add logfile
group 1 ('/u02/oradata/CDB_NAME/redo01.log') size 512M,
group 2 ('/u02/oradata/CDB_NAME/redo02.log') size 512M,
group 3 ('/u02/oradata/CDB_NAME/redo03.log') size 512M;
alter system switch logfile; --- until all new group were switched, i.e. until group 3
------ configure standby redo log
alter database add standby logfile ('/u02/oradata/CDB_NAME/standby_redo01.log') size 512M;
alter database add standby logfile ('/u02/oradata/CDB_NAME/standby_redo02.log') size 512M;
alter database add standby logfile ('/u02/oradata/CDB_NAME/standby_redo03.log') size 512M;
alter database add standby logfile ('/u02/oradata/CDB_NAME/standby_redo04.log') size 512M;
alter database add standby logfile ('/u02/oradata/CDB_NAME/standby_redo05.log') size 512M;
alter database add standby logfile ('/u02/oradata/CDB_NAME/standby_redo06.log') size 512M;
alter database add standby logfile ('/u02/oradata/CDB_NAME/standby_redo07.log') size 512M;
alter system set standby_file_management=auto; --- the standby will also have this parameter in below rman duplicate
replicate files addition and deletion on primary,
during below rman primary, the redo log files will also be created on standby
-- both primary and standby
---- tnsnames.ora
---- must use SID instead of service name
CDB_NAME =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = myserver1.local)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = CDB_NAME)
)
)
CDB_NAME_STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = myserver2.local)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = CDB_NAME)
)
)
---- listener.ora
-- primary
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = CDB_NAME_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = CDB_NAME)
)
)
-- standby
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = CDB_NAME_STBY_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = CDB_NAME)
)
)
-- both primary and standby
---- will affect database service, please do it in non-business time
[oracle@myserver ~]$ lsnrctl stop
[oracle@myserver ~]$ lsnrctl start
-- standby
---- pfile
[oracle@myserver2 ~]$ vi /tmp/initCDB_NAME_STBY.ora
*.db_name=CDB_NAME
*.memory_max_target=32212254720 --- if standby has lesser memory resources
*.memory_target=32212254720 ---
---- directories
[oracle@myserver2 ~]$ mkdir -p /u02/oradata/CDB_NAME/pdbseed
[oracle@myserver2 ~]$ mkdir -p /u02/oradata/CDB_NAME/PDB_NAME1
[oracle@myserver2 ~]$ mkdir -p /u02/oradata/CDB_NAME/PDB_NAME2
---- password
[oracle@myserver2 ~]$ orapwd file=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwCDB_NAME password=mypassword entries=10 format=12
startup nomount pfile='/tmp/initCDB_NAME_STBY.ora';
---- duplicate
rman TARGET sys/mypassword@CDB_NAME AUXILIARY sys/mypassword@CDB_NAME_STBY
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET db_unique_name='CDB_NAME_STBY' COMMENT 'Is standby'
SET memory_max_target='32212254720' --- if standby has lesser memory resources
SET memory_target='32212254720' --- customize based on primary value
SET pga_aggregate_limit='30G' ---
SET pga_aggregate_target='0' ---
SET db_file_name_convert='/u02/oradata/CDB_NAME/','/u02/oradata/CDB_NAME/'
SET log_file_name_convert='/u02/oradata/CDB_NAME/','/u02/oradata/CDB_NAME/'
SET job_queue_processes='0'
NOFILENAMECHECK;
---- possible errors during duplicate
ORA-04031: unable to allocate 104 bytes of shared memory
ORA-04031: unable to allocate 104 bytes of shared memory ("shared pool","select ks.inst_id,ksuxsins,k...","SQLA^d6bab27","opn: qkexrInitOpn")
solution: add memory_target in pfile
ORA-00845: MEMORY_TARGET not supported on this system
solution: check /dev/shm by [df -h]
-- both primary and standby
---- start data guard
alter system set dg_broker_start=true;
-- primary
dgmgrl sys/mypassword@CDB_NAME
CREATE CONFIGURATION cdb_name_dg_config AS PRIMARY DATABASE IS CDB_NAME CONNECT IDENTIFIER IS CDB_NAME;
ADD DATABASE CDB_NAME_STBY AS CONNECT IDENTIFIER IS CDB_NAME_STBY MAINTAINED AS PHYSICAL;
ENABLE CONFIGURATION;
SHOW CONFIGURATION;
SHOW DATABASE verbose cdb_name;
SHOW DATABASE verbose cdb_name_stby;
-- if primary cannot be restart at the end during switch over (but still succeed to switch over)
-- check dataguard service_name
edit database cdb_name set property staticconnectidentifier=
'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myserver1.local)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=CDB_NAME_DGMGRL.local)(INSTANCE_NAME=CDB_NAME)(SERVER=DEDICATED)))';
edit database cdb_name_stby set property staticconnectidentifier=
'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myserver2.local)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=CDB_NAME_STBY_DGMGRL.local)(INSTANCE_NAME=CDB_NAME)(SERVER=DEDICATED)))';
-- primary
SWITCHOVER TO cdb_name_stby;
-- standby
---- if flashback is off, primary cannot be switched back and need to rebuild
FAILOVER TO cdb_name_stby;
- the standby can additionally change to read-only mode for offloading some heavy sql, e.g. report