Is it safe to move/recreate alertlog while the database is up and running

 Is it safe to move/recreate alertlog while the database is up and running??


It is totally safe to "mv" or rename it while we are running. Since chopping part of it out would be lengthly process, there is a good chance we would write to it while you are editing it so I would not advise trying to "chop" part off -- just mv the whole thing and we'll start anew in another file.

If you want to keep the last N lines "online", after you mv the file, tail the last 100 lines to "alert_also.log" or something before you archive off the rest.


[oracle@Linux03 trace]$ ls -ll alert_*
-rw-r-----. 1 oracle oracle    488012 Nov 14 10:23 alert_orcl.log

I will rename the existing alertlog file to something
 
[oracle@Linux03 trace]$ mv alert_orcl.log alert_orcl_Pre_14Nov2016.log

[oracle@Linux03 trace]$ ls -ll alert_*
-rw-r-----. 1 oracle oracle 488012 Nov 14 15:42 alert_orcl_Pre_14Nov2016.log
[oracle@Linux03 trace]$ ls -ll alert_*

Now lets create some activity that will need to update the alertlog.

[oracle@Linux03 bin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Nov 14 16:23:02 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter system switch logfile;

System altered.

SQL> /

lets see if the new alertlog file has been created.

[oracle@Linux03 trace]$ ls -ll alert_*
-rw-r-----. 1 oracle oracle    249 Nov 14 16:23 alert_orcl.log
-rw-r-----. 1 oracle oracle 488012 Nov 14 15:42 alert_orcl_Pre_14Nov2016.log

Directory permissions granted to a user in database

Querying directory permissions granted to a user

SELECT grantee, table_name directory_name, LISTAGG(privilege, ',') WITHIN GROUP (ORDER BY grantee)
  FROM dba_tab_privs
 WHERE table_name ='DPUMP' group by GRANTEE,TABLE_NAME;



SAMPLE output:

GRANTEE              DIRECTORY_NAME                 GRANTS            
-------------------- ------------------------------ --------------------
SCOTT                  DPUMP                       READ,WRITE         
TIGER                   DPUMP                       READ,WRITE         
TOM                      DPUMP                       READ,WRITE         
CAM                      DPUMP                       READ,WRITE         
SAM                      DPUMP                       READ,WRITE

Failed to auto-stop Oracle Net Listener using ORACLE_HOME/bin/tnslsnr

Usage : we can use dbshut script file in $ORACLE_HOME/bin to shutdown  database & listener.

 [oracle@Linux03 bin]$ ps -ef|grep pmon
oracle   20693     1  0 10:57 ?        00:00:00 ora_pmon_orcl
oracle   21133 19211  0 11:01 pts/0    00:00:00 grep pmon

[oracle@Linux03 bin]$ dbshut
Processing Database instance "orcl": log file /u01/app/oracle/product/12.1.0.2/db_1/shutdown.log

[oracle@Linux03 bin]$ ps -ef|grep pmon
oracle   21287 19211  0 11:09 pts/0    00:00:00 grep pmon
[oracle@Linux03 bin]$





Error : Failed to auto-stop Oracle Net Listener using ORACLE_HOME/bin/tnslsnr

[oracle@Linux03 bin]$ dbshut

Failed to auto-stop Oracle Net Listener using ORACLE_HOME/bin/tnslsnr


 Solution (same as above): edit dbshut script and change

From : ORACLE_HOME_LISTNER=$1

 To  :  ORACLE_HOME_LISTNER=$ORACLE_HOME 

Note : 
One pre-req for this script to run is set 'Y' in /etc/oratab. This basically tell the script to start all database instance running of this home. while 'N' tells not to start db using dbshut script.

[oracle@Linux03 Desktop]$ grep 'orcl' /etc/oratab
orcl:/u01/app/oracle/product/12.1.0.2/db_1:Y

dbstart: line 275: ORACLE_HOME_LISTNER: command not found

Usage : we can use dbstart script file in $ORACLE_HOME/bin to start database & listener.

[oracle@Linux03 bin]$ ps -ef|grep pmon
oracle   20588 19211  0 10:56 pts/0    00:00:00 grep pmon

[oracle@Linux03 bin]$ dbstart
Processing Database instance "orcl": log file /u01/app/oracle/product/12.1.0.2/db_1/startup.log

[oracle@Linux03 bin]$ ps -ef|grep pmon
oracle   20693     1  0 10:57 ?        00:00:00 ora_pmon_orcl
oracle   21035 19211  0 10:57 pts/0    00:00:00 grep pmon

[oracle@Linux03 bin]$ 



Common error with dbstart script :


Error : /u01/app/oracle/product/12.1.0.2/db_1/bin/dbstart: line 275: ORACLE_HOME_LISTNER: command not found

[oracle@Linux03 bin]$ dbstart

/u01/app/oracle/product/12.1.0.2/db_1/bin/dbstart: line 275: ORACLE_HOME_LISTNER: command not found


Solution : Edit dbstart script and change (~ line 275)

 From : ORACLE_HOME_LISTNER=$1

 To   : ORACLE_HOME_LISTNER=$ORACLE_HOME

Note : 
One pre-req for this script to run is set 'Y' in /etc/oratab. This basically tell the script to start all database instance running of this home. while 'N' tells not to start db using dbshut script.

[oracle@Linux03 Desktop]$ grep 'orcl' /etc/oratab
orcl:/u01/app/oracle/product/12.1.0.2/db_1:Y

expdp content=data_only

[oracle@oracle1 dpump]$ expdp atest/password directory=dpump dumpfile=test_tab1.dmp content=data_only tables=test_tab1 logfile=test_tab1.log

Export: Release 11.2.0.1.0 - Production on Wed Feb 11 10:58:23 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  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
Starting "ATEST"."SYS_EXPORT_TABLE_01":  atest/******** directory=dpump dumpfile=test_tab1.dmp content=data_only tables=test_tab1 logfile=test_tab1.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
. . exported "ATEST"."TEST_TAB1"                         5.937 KB      11 rows
Master table "ATEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ATEST.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/dpump/test_tab1.dmp
Job "ATEST"."SYS_EXPORT_TABLE_01" successfully completed at 10:58:26

[oracle@oracle1 dpump]$ clear
[oracle@oracle1 dpump]$ impdp atest2/password directory=dpump dumpfile=test_tab1.dmp content=data_only logfile=test_tab1_imp.log TABLE_EXISTS_ACTION=truncate remap_schema=atest:atest2

Import: Release 11.2.0.1.0 - Production on Wed Feb 11 10:58:50 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  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
Master table "ATEST2"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "ATEST2"."SYS_IMPORT_FULL_01":  atest2/******** directory=dpump dumpfile=test_tab1.dmp content=data_only logfile=test_tab1_imp.log TABLE_EXISTS_ACTION=truncate remap_schema=atest:atest2
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ATEST2"."TEST_TAB1"                        5.937 KB      11 rows
Job "ATEST2"."SYS_IMPORT_FULL_01" successfully completed at 10:58:52

ORA-14074: partition bound must collate higher than that of the last partition

I have a table AUDIT_LOGONS, it has 5 partitions in it and one partition is defined as MAXVALUE. All partitions has some data (see below screen) in it except the MAXVALUE partition. Now I want to add a new partition which has date values less than 2016-05-31

 But I am getting error ORA-14074

sql :

alter table AUDIT_LOGONS add partition AUDIT_LOGONS_P1 VALUES LESS THAN (TO_DATE(' 2016-05-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));

and I get this error :

SQL Error: ORA-14074: partition bound must collate higher than that of the last partition
14074. 00000 -  "partition bound must collate higher than that of the last partition"
*Cause:    Partition bound specified in ALTER TABLE ADD PARTITION

Solution 1:

We can add a sub-partition to the partition that was set with MAXVALUE (AUDIT_LOGONS5 in this case). In below sql we are modifying the partition audit_logons5 adding a sub-parition audit_logons6 which will have all the data which has date below "2016-09-30"


ALTER TABLE MONTHLY_SALES MODIFY PARTITION AUDIT_LOGONS5 ADD SUB-PARTITION AUDIT_LOGONS6  VALUES LESS THAN (TO_DATE('2016-09-30 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'));

Note : the partition can be renamed anytime

Solution 2 (This will not work for all):

One solution is to drop that Maxvalue (AUDIT_LOGONS5 in this case) partition if there is no data in it and then we can recreate another partitions with defined dates like below.

ALTER TABLE monthly_sales DROP PARTITION AUDIT_LOGONS5;

ALTER TABLE monthly_sales ADD PARTITION AUDIT_LOGONS5 VALUES LESS THAN (TO_DATE('2016-09-30 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'));

Real Time Log Apply on Standby Database

 

              Configure Real Time Log Apply on Standby

By default, log apply services wait for the full archived redo log file to arrive on the standby database before applying it to the standby database. If the real-time apply feature is enabled, log apply services can apply redo data as it is received from the Primary DB, without waiting for the current standby redo log file to be archived. We can use the ALTER DATABASE statement to enable the real-time apply feature, as below:

• For physical standby databases, issue the ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE statement.

• For logical standby databases, issue the ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE statement.

NOTE : Standby redo log files are required to use real-time apply.


Lets Test it:

oracle@ORCLSTDBY:[~] $ sqlplus /"as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 4 10:57:52 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> select dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

DEST_NAME            STATUS     TYPE       SRL RECOVERY_MODE
-------------------- ---------- ---------- --- ------------------------------
LOG_ARCHIVE_DEST_1   VALID      LOCAL      NO  IDLE

-- Dest_id can be different in your database. but mostly it will be set to local.
-- Lets start applying logs and start the recovery mode Default (apply on log fill up)

SQL> recover managed standby database disconnect from session;
Media recovery complete.

-- Query the Recovery Mode now:

SQL> col DEST_NAME format A20
col status format A10
col type format A10
col recovery_mode format A30

select dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

DEST_NAME            STATUS     TYPE       SRL RECOVERY_MODE
-------------------- ---------- ---------- --- ------------------------------
LOG_ARCHIVE_DEST_1   VALID      LOCAL      NO  MANAGED

-- See that Recovery Mode will be just Managed. 

-- Lets stop log Apply and change it the recovery mode to Real-Time Apply

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

DEST_NAME                       STATUS      TYPE       SRL        RECOVERY_MODE
--------------------                   ----------       ----------     ---          ------------------------------
LOG_ARCHIVE_DEST_1   VALID      LOCAL   NO  MANAGED REAL TIME APPLY


-- We can also check this in alertlog_File.log


Completed: ALTER DATABASE RECOVER  managed standby database cancel
Tue Oct 04 11:00:47 2016
.
.
alter database recover managed standby database using current logfile disconnect from session
Attempt to start background Managed Standby Recovery process (ORCLSTDBY)
Tue Oct 04 11:00:47 2016
MRP0 started with pid=58, OS id=40557
MRP0: Background Managed Standby Recovery process started (ORCLSTDBY)
 started logmerger process
Tue Oct 04 11:00:52 2016
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 64 slaves
Waiting for all non-current ORLs to be archived...


Reference Oracle Docs:

https://docs.oracle.com/cd/B19306_01/server.102/b14239/log_apply.htm#i1034632


 Similar Posts :

• How to add second standby database 
• How to create Standby database using RMAN
• Open Standby Database in Read-Only  
• Find Archive Gap between Primary and Standby Database