|
|
上完1Z0-052的第3章，共52章上完3章
第8次：2015-08-27星期四
PL/SQL Develop：
- select log_mode from v$database;
- select * from v$archived_log order by resetlogs_change#, sequence#;
- select * from v$log;
- select * from v$logfile;
- select * from v$thread;
- select checkpoint_change# from v$database;
2015-08-27.sql：
log_archive_dest_state_28 string enable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_29 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_30 string enable
log_archive_dest_state_31 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
log_archive_duplex_dest string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string %t_%s_%r.dbf
log_archive_local_first boolean TRUE
log_archive_max_processes integer 4
log_archive_min_succeed_dest integer 1
log_archive_start boolean FALSE
log_archive_trace integer 0
standby_archive_dest string ?/dbs/arch
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 100
Next log sequence to archive 102
Current log sequence 102
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@station90 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle_4U@orcl
Connected.
DGMGRL> show configuration;
Error:
ORA-16525: the Data Guard broker is not yet available
Configuration details cannot be determined by DGMGRL
DGMGRL> exit
[oracle@station90 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 27 20:40:13 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> show parameter log_archive_dest_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
log_archive_dest_18 string
log_archive_dest_19 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_3 string
log_archive_dest_30 string
log_archive_dest_31 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_11 string enable
log_archive_dest_state_12 string enable
log_archive_dest_state_13 string enable
log_archive_dest_state_14 string enable
log_archive_dest_state_15 string enable
log_archive_dest_state_16 string enable
log_archive_dest_state_17 string enable
log_archive_dest_state_18 string enable
log_archive_dest_state_19 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_20 string enable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_21 string enable
log_archive_dest_state_22 string enable
log_archive_dest_state_23 string enable
log_archive_dest_state_24 string enable
log_archive_dest_state_25 string enable
log_archive_dest_state_26 string enable
log_archive_dest_state_27 string enable
log_archive_dest_state_28 string enable
log_archive_dest_state_29 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_30 string enable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_31 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
SQL> show parameter DB_RECOVERY_FILE_DEST
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +FRA
db_recovery_file_dest_size big integer 3882M
SQL> show parameter log_archive_format
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string %t_%s_%r.dbf
SQL>
SQL> alter system set
2 log_archive_dest_1='location=use_db_recovery_file_dest' ;
System altered.
SQL> alter system set
2 log_archive_dest_2='location=/home/oracle/archivelog';
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 101
Next log sequence to archive 103
Current log sequence 103
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter database add logfile member '/home/oracle/onlinelog/redo03.log' to group 3 ;
Database altered.
SQL> alter database add logfile member '/home/oracle/onlinelog/redo02.log' to group 2 ;
Database altered.
SQL> alter database add logfile member '/home/oracle/onlinelog/redo01.log' to group 1 ;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter database drop logfile member '/home/oracle/onlinelog/redo03.log';
alter database drop logfile member '/home/oracle/onlinelog/redo03.log'
*
ERROR at line 1:
ORA-01609: log 3 is the current log for thread 1 - cannot drop members
ORA-00312: online log 3 thread 1: '+DATA/orcl/onlinelog/group_3.263.816169641'
ORA-00312: online log 3 thread 1: '+FRA/orcl/onlinelog/group_3.259.816169641'
ORA-00312: online log 3 thread 1: '/home/oracle/onlinelog/redo03.log'
SQL> alter database drop logfile member '/home/oracle/onlinelog/redo02.log';
Database altered.
SQL> alter database drop logfile member '/home/oracle/onlinelog/redo01.log';
Database altered.
SQL> alter database drop logfile member '/home/oracle/onlinelog/redo03.log';
alter database drop logfile member '/home/oracle/onlinelog/redo03.log'
*
ERROR at line 1:
ORA-01609: log 3 is the current log for thread 1 - cannot drop members
ORA-00312: online log 3 thread 1: '+DATA/orcl/onlinelog/group_3.263.816169641'
ORA-00312: online log 3 thread 1: '+FRA/orcl/onlinelog/group_3.259.816169641'
ORA-00312: online log 3 thread 1: '/home/oracle/onlinelog/redo03.log'
SQL> alter system switch logfile;
System altered.
SQL> alter database drop logfile member '/home/oracle/onlinelog/redo03.log';
Database altered.
SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +FRA
db_recovery_file_dest_size big integer 3882M
SQL> show parameter db_create
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string +DATA
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
SQL> alter database add logfile group 4 size 52428800;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter database drop logfile group 4 ;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-01624: log 4 needed for crash recovery of instance orcl (thread 1)
ORA-00312: online log 4 thread 1: '+DATA/orcl/onlinelog/group_4.267.888873499'
ORA-00312: online log 4 thread 1: '+FRA/orcl/onlinelog/group_4.324.888873501'
SQL> alter database drop logfile group 4 ;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-01624: log 4 needed for crash recovery of instance orcl (thread 1)
ORA-00312: online log 4 thread 1: '+DATA/orcl/onlinelog/group_4.267.888873499'
ORA-00312: online log 4 thread 1: '+FRA/orcl/onlinelog/group_4.324.888873501'
SQL> alter system switch logfile;
System altered.
SQL> alter database drop logfile group 4 ;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-01624: log 4 needed for crash recovery of instance orcl (thread 1)
ORA-00312: online log 4 thread 1: '+DATA/orcl/onlinelog/group_4.267.888873499'
ORA-00312: online log 4 thread 1: '+FRA/orcl/onlinelog/group_4.324.888873501'
SQL> alter system checkpoint;
System altered.
SQL> alter database drop logfile group 4 ;
Database altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 6664212480 bytes
Fixed Size 2240944 bytes
Variable Size 3640659536 bytes
Database Buffers 3003121664 bytes
Redo Buffers 18190336 bytes
Database mounted.
SQL> alter database open read only;
Database altered.
SQL> select open_mode from v$database ;
OPEN_MODE
--------------------
READ ONLY
SQL> conn hr/oracle_4U
ERROR:
ORA-28032: Your password has expired and the database is set to read-only
Warning: You are no longer connected to ORACLE.
SQL> conn system/oracle_4U
ERROR:
ORA-28032: Your password has expired and the database is set to read-only
SQL> show user
USER is ""
SQL> conn / as sysdba
Connected.
SQL> create table hr.t1( a number ) ;
create table hr.t1( a number )
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access
SQL> alter tablespace users offline ;
alter tablespace users offline
*
ERROR at line 1:
ORA-16000: database open for read-only access
SQL> alter database datafile 4 offline ;
Database altered.
SQL> alter database datafile 4 online ;
Database altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 6664212480 bytes
Fixed Size 2240944 bytes
Variable Size 3640659536 bytes
Database Buffers 3003121664 bytes
Redo Buffers 18190336 bytes
Database mounted.
Database opened.
SQL> alter database datafile 4 offline ;
Database altered.
SQL> alter database datafile 4 online ;
alter database datafile 4 online
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '+DATA/orcl/datafile/users.259.816169553'
SQL> startup force
ORACLE instance started.
Total System Global Area 6664212480 bytes
Fixed Size 2240944 bytes
Variable Size 3640659536 bytes
Database Buffers 3003121664 bytes
Redo Buffers 18190336 bytes
Database mounted.
Database opened.
SQL> select status from dba_data_files;
STATUS
---------
AVAILABLE
AVAILABLE
AVAILABLE
AVAILABLE
AVAILABLE
SQL> select file_name , status from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
STATUS
---------
+DATA/orcl/datafile/users.259.816169553
AVAILABLE
+DATA/orcl/datafile/undotbs1.258.816169553
AVAILABLE
+DATA/orcl/datafile/sysaux.257.816169553
AVAILABLE
FILE_NAME
--------------------------------------------------------------------------------
STATUS
---------
+DATA/orcl/datafile/system.256.816169553
AVAILABLE
+DATA/orcl/datafile/example.265.816169651
AVAILABLE
SQL> alter database datafile 4 offline ;
Database altered.
SQL> alter database datafile 4 online ;
alter database datafile 4 online
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '+DATA/orcl/datafile/users.259.816169553'
SQL> create tablespace tbs1 datafile '/home/oracle/tbs1.dbf' size 10M ;
Tablespace created.
SQL> select file_name , status from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
STATUS
---------
+DATA/orcl/datafile/users.259.816169553
AVAILABLE
+DATA/orcl/datafile/undotbs1.258.816169553
AVAILABLE
+DATA/orcl/datafile/sysaux.257.816169553
AVAILABLE
FILE_NAME
--------------------------------------------------------------------------------
STATUS
---------
+DATA/orcl/datafile/system.256.816169553
AVAILABLE
+DATA/orcl/datafile/example.265.816169651
AVAILABLE
/home/oracle/tbs1.dbf
AVAILABLE
6 rows selected.
SQL> alter tablespace tbs1 offline ;
Tablespace altered.
SQL> alter tablespace tbs1 online ;
alter tablespace tbs1 online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/home/oracle/tbs1.dbf'
SQL> alter database rename file '/home/oracle/tbs1.dbf' to '/home/oracle/tbs2.dbf';
Database altered.
SQL> alter tablespace tbs1 online;
Tablespace altered.
SQL>
|
|
发表于 2015-8-28 09:13:39
