set lines 200 col description format a50 col property_value format a30 col property_name format a30 SELECT * FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE '%TABLESPACE%';
alter database default temporary tablespace tmp;
alter database default tablespace user_data;
sqlnet.encryption_server=required sqlnet.encryption_types_server=AES128
SQLNET.ENCRYPTION_CLIENT = accepted
ADR_BASE = C:\app\k90387 TRACE_LEVEL_CLIENT = ADMIN
c:\app\vegard\oradiag_vegard\diag\clients\user_vegard\host_3450395457_76\trace
SQL> connect vegard/vegard@oras Connected. SQL> exit
c:\app\vegard\oradiag_vegard\diag\clients\user_vegard\host_3450395457_76\traceYou'll now see two files: one .trc file and one .trm file
2014-11-28 14:11:13.374358 : -> PARAMETER TABLE HAS THE FOLLOWING CONTENTS <- 2014-11-28 14:11:13.374375 : SQLNET.ENCRYPTION_CLIENT = accepted 2014-11-28 14:11:13.374392 : ADR_BASE = C:\app\vegard . . . 2014-11-28 13:48:28.033764 : na_tns: authentication is not active 2014-11-28 13:48:28.033781 : na_tns: encryption is active, using AES128 2014-11-28 13:48:28.033797 : na_tns: crypto-checksumming is not activeNote that neither authentication nor crypto-checksumming is active, but encryption is. As expected.
Sources:
oracle@myserver:[TESTDB01]# rman target / catalog uid/pwd@rmancat Recovery Manager: Release 11.2.0.4.0 - Production on Thu Nov 20 16:06:44 2014 connected to target database: TESTDB01 (DBID=411134280, not open) connected to recovery catalog database RMAN> list restore point all; SCN RSP Time Type Time Name ---------------- --------- ---------- --------- ---- 153050263689 GUARANTEED 17-NOV-14 AKSEPT_TEST_START
RMAN> FLASHBACK DATABASE TO RESTORE POINT AKSEPT_TEST_START; Starting flashback at 20-NOV-14 allocated channel: ORA_SBT_TAPE_1 channel ORA_SBT_TAPE_1: SID=585 device type=SBT_TAPE channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 6.3.0.0 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=683 device type=DISK starting media recovery archived log for thread 1 with sequence 4651 is already on disk as file /u04/fast_recovery_area/TESTDB01/archivelog/2014_11_17/o1_mf_1_4651_b6m91zog_.arc media recovery complete, elapsed time: 00:00:01 Finished flashback at 20-NOV-14 RMAN> ALTER DATABASE OPEN RESETLOGS; database opened new incarnation of database registered in recovery catalog starting full resync of recovery catalog full resync complete RMAN> exit
Completed: ALTER DATABASE MOUNT Thu Nov 20 16:10:37 2014 alter database recover datafile list clear Completed: alter database recover datafile list clear RMAN flashback database to before scn 153050263690 in incarnation 2 Flashback Restore Start Thu Nov 20 16:12:11 2014 Flashback Restore Complete Flashback Media Recovery Start started logmerger process Parallel Media Recovery started with 8 slaves Thu Nov 20 16:12:22 2014 Flashback Media Recovery Log /u04/fast_recovery_area/TESTDB01/archivelog/2014_11_17/o1_mf_1_4651_b6m91zog_.arc Thu Nov 20 16:12:22 2014 Incomplete Recovery applied until change 153050263690 time 11/17/2014 07:53:33 Flashback Media Recovery Complete Completed: RMAN flashback database to before scn 153050263690 in incarnation 2 Thu Nov 20 16:13:29 2014 alter database open resetlogs RESETLOGS after incomplete recovery UNTIL CHANGE 153050263690 Archived Log entry 4827 added for thread 1 sequence 4826 ID 0x18811648 dest 1: Archived Log entry 4828 added for thread 1 sequence 4823 ID 0x18811648 dest 1: Archived Log entry 4829 added for thread 1 sequence 4822 ID 0x18811648 dest 1: Archived Log entry 4830 added for thread 1 sequence 4827 ID 0x18811648 dest 1: Archived Log entry 4831 added for thread 1 sequence 4824 ID 0x18811648 dest 1: Archived Log entry 4832 added for thread 1 sequence 4825 ID 0x18811648 dest 1: Clearing online redo logfile 1 /u03/oradata/TESTDB01/redo01.log Clearing online log 1 of thread 1 sequence number 4826 Clearing online redo logfile 1 complete Clearing online redo logfile 2 /u03/oradata/TESTDB01/redo02.log Clearing online log 2 of thread 1 sequence number 4823 Clearing online redo logfile 2 complete Clearing online redo logfile 3 /u03/oradata/TESTDB01/redo03.log Clearing online log 3 of thread 1 sequence number 4822 Thu Nov 20 16:13:41 2014 Clearing online redo logfile 3 complete Clearing online redo logfile 4 /u03/oradata/TESTDB01/redo04.log Clearing online log 4 of thread 1 sequence number 4827 Clearing online redo logfile 4 complete Clearing online redo logfile 5 /u03/oradata/TESTDB01/redo05.log Clearing online log 5 of thread 1 sequence number 4824 Clearing online redo logfile 5 complete Clearing online redo logfile 6 /u03/oradata/TESTDB01/redo06.log Clearing online log 6 of thread 1 sequence number 4825 Thu Nov 20 16:13:52 2014 Clearing online redo logfile 6 complete Resetting resetlogs activation ID 411113032 (0x18811648) Online log /u03/oradata/TESTDB01/redo01.log: Thread 1 Group 1 was previously cleared Online log /u03/oradata/TESTDB01/redo02.log: Thread 1 Group 2 was previously cleared Online log /u03/oradata/TESTDB01/redo03.log: Thread 1 Group 3 was previously cleared Online log /u03/oradata/TESTDB01/redo04.log: Thread 1 Group 4 was previously cleared Online log /u03/oradata/TESTDB01/redo05.log: Thread 1 Group 5 was previously cleared Online log /u03/oradata/TESTDB01/redo06.log: Thread 1 Group 6 was previously cleared Thu Nov 20 16:13:52 2014 Setting recovery target incarnation to 3
SPM: SMB space usage (13523353600) exceeds 10.000000% of SYSAUX size (15728640000).Command:
exec dbms_spm.configure('Space_budget_percent', 30);
# become root su - # cd to the directory in which you keep your files you'd like to tar up: cd /u01/oracle/product/11204 # create the tarball, place it in the parent directory: tar cvf ../oracle11204.tar *
tar -cvzf ../oracle11204.tar.tgz .To create a tarball of the directory /tmp/dbscr and any file(s) in it:
cd /tmp tar cvf mytarball.tar dbscr dbscr/ dbscr/file1.txt dbscr/file2.rsp dbscr/file3.ora
|
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS -------------------- SESSIONS ACTIVE
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
idle dispatcher 'D000' terminated, pid = (16, 1) Tue Oct 28 19:13:39 2014 Switchover: Complete - Database shutdown required (proddb01) Tue Oct 28 19:13:39 2014 Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN
-- Connected User is Valid RFS[40]: Assigned to RFS process 7078566 RFS[40]: Identified database type as 'physical standby' RFS[40]: Successfully opened standby log 7: '/u02/oradata/stby01/stb_redo7.log Tue Oct 28 19:10:57 2014 Media Recovery Log /u04/oradata/stby01/archive/log1_75389_681750063.arc Media Recovery Waiting for thread 1 sequence 75390 Tue Oct 28 19:13:31 2014 Redo Shipping Client Connected as PUBLIC -- Connected User is Valid RFS[41]: Assigned to RFS process 34669144 RFS[41]: Identified database type as 'physical standby' RFS[41]: Archived Log: '/u04/oradata/stby01/archive/log1_75390_681750063.arc' Tue Oct 28 19:13:32 2014 Media Recovery Log /u04/oradata/stby01/archive/log1_75390_681750063.arc Identified End-Of-Redo for thread 1 sequence 75390 Tue Oct 28 19:13:32 2014 Media Recovery End-Of-Redo indicator encountered Tue Oct 28 19:13:32 2014 Media Recovery Applied until change 142570810888 Tue Oct 28 19:13:32 2014 MRP0: Media Recovery Complete: End-Of-REDO (stby01) Resetting standby activation ID 1529144623 (0x5b24e52f) Tue Oct 28 19:13:35 2014 MRP0: Background Media Recovery process shutdown (stby01)
shutdown abort startup mountFrom the primary (proddb01) alert log:
Tue Oct 28 19:15:37 2014
Successful mount of redo thread 1, with mount id 1709512659
Tue Oct 28 19:15:37 2014
Physical Standby Database mounted.
Starting control autobackup
Control autobackup written to DISK device
handle '/u01/oracle/product/ora10g/dbs/c-1529124399-20141028-12'
Completed: ALTER DATABASE MOUNT
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS -------------------- TO PRIMARY
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;From the standby (stby01) alert log:
-- Connected User is Valid RFS[41]: Assigned to RFS process 34669144 RFS[41]: Identified database type as 'physical standby' RFS[41]: Archived Log: '/u04/oradata/stby01/archive/log1_75390_681750063.arc' Tue Oct 28 19:13:32 2014 Media Recovery Log /u04/oradata/stby01/archive/log1_75390_681750063.arc Identified End-Of-Redo for thread 1 sequence 75390 Tue Oct 28 19:13:32 2014 Media Recovery End-Of-Redo indicator encountered Tue Oct 28 19:13:32 2014 Media Recovery Applied until change 142570810888 Tue Oct 28 19:13:32 2014 MRP0: Media Recovery Complete: End-Of-REDO (stby01) Resetting standby activation ID 1529144623 (0x5b24e52f) Tue Oct 28 19:13:35 2014 MRP0: Background Media Recovery process shutdown (stby01) Tue Oct 28 19:18:43 2014 ALTER DATABASE SWITCHOVER TO PRIMARY (stby01) Tue Oct 28 19:18:43 2014 If media recovery active, switchover will wait 900 seconds SwitchOver after complete recovery through change 142570810888 Online log /u02/oradata/stby01/redo1.log: Thread 1 Group 4 was previously cleared Online log /u02/oradata/stby01/redo2.log: Thread 1 Group 5 was previously cleared Online log /u02/oradata/stby01/redo3.log: Thread 1 Group 6 was previously cleared Standby became primary SCN: 142570810886 Converting standby mount to primary mount. Tue Oct 28 19:18:43 2014 Switchover: Complete - Database mounted as primary (stby01) Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY Tue Oct 28 19:18:43 2014 ARC0: STARTING ARCH PROCESSES ARC2: Archival started ARC0: STARTING ARCH PROCESSES COMPLETE ARC2 started with pid=15, OS id=8847906 Tue Oct 28 19:19:41 2014 ALTER DATABASE OPENThe primary database (proddb01) is from now on considering itself to be the physical standby database.
Tue Oct 28 19:15:37 2014
Physical Standby Database mounted.
Starting control autobackup
Control autobackup written to DISK device
handle '/u01/oracle/product/ora10g/dbs/c-1529124399-20141028-12'
Completed: ALTER DATABASE MOUNT
Tue Oct 28 19:26:10 2014
Using STANDBY_ARCHIVE_DEST parameter default value as /u01/oracle/admin/proddb01/archive/
Tue Oct 28 19:26:19 2014
Starting control autobackup
Control autobackup written to SBT_TAPE device
comment 'API Version 2.0,MMS Version 5.3.3.0',
media '1362'
handle 'c-1529124399-20141028-13'
SQL> SELECT DB_UNIQUE_NAME,NAME,DATABASE_ROLE FROM V$DATABASE; DB_UNIQUE_NAME NAME DATABASE_ROLE ------------------------------ -------------- ---------------- stby01 proddb01 PRIMARY
ORA-01187: cannot read from file 201 because it failed verification tests
ORA-01110: data file 201: '/u02/oradata/stby01/temp01.dbf'
SQL>
1 select tf.file#,tf.ts#,tf.status,tf.enabled, tf.bytes/1024/1024,tf.name "file_name",ts.name "ts_name"
2 from v$tempfile tf, v$tablespace ts
3* where tf.ts# = ts.ts#
FILE# TS# STATUS ENABLED TF.BYTES/1024/1024 file_name ts_name
---------- ---------- ------- ---------- ------------------ -------------------------------------- --------------------
1 3 ONLINE READ WRITE 32767 /u02/oradata/stby01/temp01.dbf TEMP
2 8 ONLINE READ WRITE 100 /u02/oradata/stby01/EDPROD_iastemp.dbf EDPROD_IAS_TEMP
3 3 ONLINE READ WRITE 9900 /u02/oradata/stby01/temp02.dbf TEMP
SQL> save temp_tabspc_overview.sql
Created file temp_tabspc_overview.sql
SQL> drop tablespace EDPROD_IAS_TEMP including contents and datafiles;
Tablespace dropped.
SQL> @temp_tabspc_overview.sql
FILE# TS# STATUS ENABLED TF.BYTES/1024/1024 file_name ts_name
---------- ---------- ------- ---------- ------------------ ------------------------------ --------------------
1 3 ONLINE READ WRITE 32767 /u02/oradata/stby01/temp01.dbf TEMP
3 3 ONLINE READ WRITE 9900 /u02/oradata/stby01/temp02.dbf TEMP
SQL> create temporary tablespace EDPROD_IAS_TEMP tempfile '/u02/oradata/stby01/EDPROD_iastemp.dbf' size 128M reuse autoextend on next 32M maxsize unlimited
Tablespace created.
SQL> @temp_tabspc_overview.sql
FILE# TS# STATUS ENABLED TF.BYTES/1024/1024 file_name ts_name
---------- ---------- ------- ---------- ------------------ -------------------------------------- --------------------
1 3 ONLINE READ WRITE 32767 /u02/oradata/stby01/temp01.dbf TEMP
2 8 ONLINE READ WRITE 128 /u02/oradata/stby01/EDPROD_iastemp.dbf EDPROD_IAS_TEMP
3 3 ONLINE READ WRITE 9900 /u02/oradata/stby01/temp02.dbf TEMP
SQL> alter database default temporary tablespace EDPROD_IAS_TEMP;
Database altered.
SQL> drop tablespace TEMP including contents and datafiles;
Tablespace dropped.
SQL> create temporary tablespace TEMP tempfile '/u02/oradata/stby01/temp01.dbf' size 128M reuse autoextend on next 32M maxsize unlimited;
Tablespace created.
SQL> @temp_tabspc_overview.sql
FILE# TS# STATUS ENABLED TF.BYTES/1024/1024 file_name ts_name
---------- ---------- ------- ---------- ------------------ -------------------------------------- --------------------
1 3 ONLINE READ WRITE 128 /u02/oradata/stby01/temp01.dbf TEMP
2 8 ONLINE READ WRITE 128 /u02/oradata/stby01/EDPROD_iastemp.dbf EDPROD_IAS_TEMP
|
|
SQL> select group# ,members, status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
4 1 ACTIVE
5 1 ACTIVE
6 1 CURRENT
Oracle recommends to add standby redo log file groups with the same size as the largest member in any group. They also recommend that the number of standby redo log groups is the same as the number of redo log groups + 1.SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u02/oradata/proddb01/stb_redo7.log') size 512M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 ('/u02/oradata/proddb01/stb_redo8.log') size 512M;
SQL> select group# ,members, status from v$log;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 9 ('/u02/oradata/proddb01/stb_redo9.log') size 512M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 ('/u02/oradata/proddb01/stb_redo10.log') size 512M;
Database altered.
Verify that they were created:
SQL> select group#,thread#,sequence#,archived,status from v$standby_log;
GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
7 0 0 YES UNASSIGNED
8 0 0 YES UNASSIGNED
9 0 0 YES UNASSIGNED
10 0 0 YES UNASSIGNED
3. enable force logging:
ALTER DATABASE FORCE LOGGING;
4. restart primary database
STARTUP MOUNT
Assuming that you have a valid tape backup solution in place, backup your current control file "for standby" using rman:
rman target / catalog uid/pwd@rmancat
run {
allocate channel c1 type 'sbt_tape' maxpiecesize 4000M;
BACKUP CURRENT CONTROLFILE FOR STANDBY;
release channel c1;
5. Open the database, and switch the current logfile
This must be done so that the last log file is indeed older than your backup controfile that you created in the previous step:
SQL> ALTER DATABASE OPEN;
SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT'; # so backup is consistent and recoverable
6. Finally, backup the latest archived redo log to tape:
rman target / catalog uid/pwd@rmancat
run {
allocate channel c1 type 'sbt_tape' maxpiecesize 4000M;
BACKUP ARCHIVELOG ALL NOT BACKED UP 1 TIMES;
release channel c1;
7. Set up SQL*net connection
...by adding an entry in the primary database's tnsnames.ora:
stby01 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = psu0erdb01)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stby01)
)
)
8. Prepare the standby database parameters.
# The db_name must from now on match your primary database db_name
db_name='proddb01'
# The db_unique_name is the name to which you will typically refer to the standby database
db_unique_name='stby01'
log_archive_config='DG_CONFIG=(proddb01,stby01)'
log_archive_format=log%t_%s_%r.arc
log_archive_dest_1='LOCATION=/u04/oradata/ stby01/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME= stby01'
log_archive_dest_2='SERVICE=proddb01 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=proddb01'
log_archive_dest_state_1=enable
log_archive_dest_state_2=defer
remote_login_passwordfile='EXCLUSIVE'
standby_file_management=auto
fal_server=proddb01
fal_client=stby01
db_file_name_convert='proddb01','stby01'
log_file_name_convert='proddb01','stby01'
9. shutdown the to-become standby database, open it in nomount-mode:
shutdown immediate
startup nomount pfile='your_pfile.ora'
Your standby database is now ready to be cloned from your primary.
10. Create an rman script:
in file duplicate_for_standby.sh
export ORACLE_SID=stby01
export ORACLE_HOME=/u01/oracle/product/10204
export NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS'
export NLS_LANG=AMERICAN_NORWAY.WE8ISO8859P15
rman target sys/password@proddb01 catalog uid/pwd@rmancat auxiliary / cmdfile='duplicate_for_standby.cmd' log='duplicate_for_standby.log' trace='duplicate_for_standby.trc'
exit
In file duplicate_for_standby.cmd:
run {
ALLOCATE AUXILIARY CHANNEL c1 TYPE 'SBT_TAPE';
ALLOCATE AUXILIARY CHANNEL c2 TYPE 'SBT_TAPE';
ALLOCATE AUXILIARY CHANNEL c3 TYPE 'SBT_TAPE';
ALLOCATE AUXILIARY CHANNEL c4 TYPE 'SBT_TAPE';
ALLOCATE AUXILIARY CHANNEL c5 TYPE 'SBT_TAPE';
ALLOCATE AUXILIARY CHANNEL c6 TYPE 'SBT_TAPE';
duplicate target database for standby
dorecover;
}
exit
chmod 755 duplicate_for_standby.sh
11. Start the cloning script in the background:
nohup ./duplicate_for_standby.sh &
Follow the progress with
tail -f duplicate_for_standby.log
As the directive "dorecover" indicates, rman will create a clone from the primary database, recover it, and leave the recovered database in mounted state.
12. Enable log transport services on the primary database:
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;
As a recap, this will instruct Oracle to ship logs to LOG_ARCHIVE_DEST_2, which is defined as a service that you have made known to your primary server through an entry in tnsnames.ora:
LOG_ARCHIVE_DEST_2='SERVICE=stby01 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stby01'
13. Check that your standby is applying logs.
You can use the script found here
Pay particularly attention that the following section looks like this:
Is the MRP process running?
PROCESS STATUS
--------- ------------
MRP0 WAIT_FOR_LOG
Crosscheck that with a look at the current archived log status:
set lines 200
col name format a60
alter session set nls_language='american';
alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss';
SELECT SEQUENCE#,NAME,STANDBY_DEST,ARCHIVED,APPLIED,DELETED,STATUS,COMPLETION_TIME
FROM V$ARCHIVED_LOG
ORDER BY SEQUENCE# ASC
/
The primary and standby should give similar views of the archived log situation:
Standby:
SEQUENCE# NAME STA ARC APP DEL S COMPLETION_TIME
---------- -------------------------------------------------------- --- --- --- --- - -------------------
75072 /u04/oradata/stby01/archive/log1_75072_681750063.arc NO YES YES NO A 22.10.2014 12:26:07
75073 /u04/oradata/stby01/archive/log1_75073_681750063.arc NO YES YES NO A 22.10.2014 12:26:37
Primary. Notice how the archived logs are sent to two destinations at every log switch:
SEQUENCE# NAME STA ARC APP DEL S COMPLETION_TIME
---------- -------------------------------------------------------- --- --- --- --- - -------------------
75072 stby01 YES YES YES NO A 22.10.2014 12:26:06
75072 /u01/oracle/admin/proddb01/archive/1_75072_681750063.arc NO YES NO NO A 22.10.2014 12:26:08
75073 stby01 YES YES YES NO A 22.10.2014 12:26:37
75073 /u01/oracle/admin/proddb01/archive/1_75073_681750063.arc NO YES NO NO A 22.10.2014 12:26:37
From the standby database's alert log, it's clear that logs are being applied:
Wed Oct 22 13:26:13 2014
Media Recovery Log /u04/oradata/stby01/archive/log1_75074_681750063.arc
Wed Oct 22 13:26:33 2014
Media Recovery Waiting for thread 1 sequence 75075 (in transit)
Wed Oct 22 13:26:39 2014
Primary database is in MAXIMUM PERFORMANCE mode
RFS[30]: Successfully opened standby log 7: '/u02/oradata/stby01/stb_redo7.log'
Wed Oct 22 13:26:43 2014
Media Recovery Log /u04/oradata/stby01/archive/log1_75075_681750063.arc
Media Recovery Waiting for thread 1 sequence 75076 (in transit)
Documentation used
"Creating a Physical Standby Database"
"Creating a Standby Database with Recovery Manager"